ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > ORACLE utl_raw函数与dbms_stats.convert_raw_value函数使用

ORACLE utl_raw函数与dbms_stats.convert_raw_value函数使用

原创 Oracle 作者：startay 时间：2016-01-28 16:38:16 0 删除 编辑
ORACLE utl_raw函数与dbms_stats.convert_raw_value函数使用

ORACLE VERSION 11.2.0.4

下面是utl_raw包下的所有函数
  1. SQL>desc utl_raw

  3. FUNCTION BIT_AND RETURNS RAW
  4.  Argument Name Type In/Out Default?
  5.  ------------------------------ ----------------------- ------ --------
  6.  R1 RAW IN
  7.  R2 RAW IN

  8.
  9. FUNCTION BIT_COMPLEMENT RETURNS RAW
  10.  Argument Name Type In/Out Default?
  11.  ------------------------------ ----------------------- ------ --------
  12.  R RAW IN

  13.
  14. FUNCTION BIT_OR RETURNS RAW
  15.  Argument Name Type In/Out Default?
  16.  ------------------------------ ----------------------- ------ --------
  17.  R1 RAW IN
  18.  R2 RAW IN

  19.
  20. FUNCTION BIT_XOR RETURNS RAW
  21.  Argument Name Type In/Out Default?
  22.  ------------------------------ ----------------------- ------ --------
  23.  R1 RAW IN
  24.  R2 RAW IN

  25.
  26. FUNCTION CAST_FROM_BINARY_DOUBLE RETURNS RAW
  27.  Argument Name Type In/Out Default?
  28.  ------------------------------ ----------------------- ------ --------
  29.  N BINARY_DOUBLE IN
  30.  ENDIANESS BINARY_INTEGER IN DEFAULT

  31.
  32. FUNCTION CAST_FROM_BINARY_FLOAT RETURNS RAW
  33.  Argument Name Type In/Out Default?
  34.  ------------------------------ ----------------------- ------ --------
  35.  N BINARY_FLOAT IN
  36.  ENDIANESS BINARY_INTEGER IN DEFAULT

  37.
  38. FUNCTION CAST_FROM_BINARY_INTEGER RETURNS RAW
  39.  Argument Name Type In/Out Default?
  40.  ------------------------------ ----------------------- ------ --------
  41.  N BINARY_INTEGER IN
  42.  ENDIANESS BINARY_INTEGER IN DEFAULT

  43.
  44. FUNCTION CAST_FROM_NUMBER RETURNS RAW
  45.  Argument Name Type In/Out Default?
  46.  ------------------------------ ----------------------- ------ --------
  47.  N NUMBER IN

  48.
  49. FUNCTION CAST_TO_BINARY_DOUBLE RETURNS BINARY_DOUBLE
  50.  Argument Name Type In/Out Default?
  51.  ------------------------------ ----------------------- ------ --------
  52.  R RAW IN
  53.  ENDIANESS BINARY_INTEGER IN DEFAULT

  54.
  55. FUNCTION CAST_TO_BINARY_FLOAT RETURNS BINARY_FLOAT
  56.  Argument Name Type In/Out Default?
  57.  ------------------------------ ----------------------- ------ --------
  58.  R RAW IN
  59.  ENDIANESS BINARY_INTEGER IN DEFAULT

  60.
  61. FUNCTION CAST_TO_BINARY_INTEGER RETURNS BINARY_INTEGER
  62.  Argument Name Type In/Out Default?
  63.  ------------------------------ ----------------------- ------ --------
  64.  R RAW IN
  65.  ENDIANESS BINARY_INTEGER IN DEFAULT

  66.
  67. FUNCTION CAST_TO_NUMBER RETURNS NUMBER
  68.  Argument Name Type In/Out Default?
  69.  ------------------------------ ----------------------- ------ --------
  70.  R RAW IN
  71. FUNCTION CAST_TO_NVARCHAR2 RETURNS NVARCHAR2
  72.  Argument Name Type In/Out Default?
  73.  ------------------------------ ----------------------- ------ --------
  74.  R RAW IN

  75.
  76. FUNCTION CAST_TO_RAW RETURNS RAW
  77.  Argument Name Type In/Out Default?
  78.  ------------------------------ ----------------------- ------ --------
  79.  C VARCHAR2 IN

  80.
  81. FUNCTION CAST_TO_VARCHAR2 RETURNS VARCHAR2
  82.  Argument Name Type In/Out Default?
  83.  ------------------------------ ----------------------- ------ --------
  84.  R RAW IN
  85. FUNCTION COMPARE RETURNS NUMBER
  86.  Argument Name Type In/Out Default?
  87.  ------------------------------ ----------------------- ------ --------
  88.  R1 RAW IN
  89.  R2 RAW IN
  90.  PAD RAW IN DEFAULT

  91.
  92. FUNCTION CONCAT RETURNS RAW
  93.  Argument Name Type In/Out Default?
  94.  ------------------------------ ----------------------- ------ --------
  95.  R1 RAW IN DEFAULT
  96.  R2 RAW IN DEFAULT
  97.  R3 RAW IN DEFAULT
  98.  R4 RAW IN DEFAULT
  99.  R5 RAW IN DEFAULT
  100.  R6 RAW IN DEFAULT
  101.  R7 RAW IN DEFAULT
  102.  R8 RAW IN DEFAULT
  103.  R9 RAW IN DEFAULT
  104.  R10 RAW IN DEFAULT
  105.  R11 RAW IN DEFAULT
  106.  R12 RAW IN DEFAULT

  107.
  108. FUNCTION CONVERT RETURNS RAW
  109.  Argument Name Type In/Out Default?
  110.  ------------------------------ ----------------------- ------ --------
  111.  R RAW IN
  112.  TO_CHARSET VARCHAR2 IN
  113.  FROM_CHARSET VARCHAR2 IN

  114.
  115. FUNCTION COPIES RETURNS RAW
  116.  Argument Name Type In/Out Default?
  117.  ------------------------------ ----------------------- ------ --------
  118.  R RAW IN
  119.  N NUMBER IN
  120. FUNCTION LENGTH RETURNS NUMBER
  121.  Argument Name Type In/Out Default?
  122.  ------------------------------ ----------------------- ------ --------
  123.  R RAW IN

  124.
  125. FUNCTION OVERLAY RETURNS RAW
  126.  Argument Name Type In/Out Default?
  127.  ------------------------------ ----------------------- ------ --------
  128.  OVERLAY_STR RAW IN
  129.  TARGET RAW IN
  130.  POS BINARY_INTEGER IN DEFAULT
  131.  LEN BINARY_INTEGER IN DEFAULT
  132.  PAD RAW IN DEFAULT
  133. FUNCTION REVERSE RETURNS RAW
  134.  Argument Name Type In/Out Default?
  135.  ------------------------------ ----------------------- ------ --------
  136.  R RAW IN

  137.
  138. FUNCTION SUBSTR RETURNS RAW
  139.  Argument Name Type In/Out Default?
  140.  ------------------------------ ----------------------- ------ --------
  141.  R RAW IN
  142.  POS BINARY_INTEGER IN
  143.  LEN BINARY_INTEGER IN DEFAULT
  144. FUNCTION TRANSLATE RETURNS RAW
  145.  Argument Name Type In/Out Default?
  146.  ------------------------------ ----------------------- ------ --------
  147.  R RAW IN
  148.  FROM_SET RAW IN
  149.  TO_SET RAW IN

  150.
  151. FUNCTION TRANSLITERATE RETURNS RAW
  152.  Argument Name Type In/Out Default?
  153.  ------------------------------ ----------------------- ------ --------
  154.  R RAW IN
  155.  TO_SET RAW IN DEFAULT
  156.  FROM_SET RAW IN DEFAULT
  157.  PAD RAW IN DEFAULT

  158.
  159. FUNCTION XRANGE RETURNS RAW
  160.  Argument Name Type In/Out Default?
  161.  ------------------------------ ----------------------- ------ --------
  162.  START_BYTE RAW IN DEFAULT
  163.  END_BYTE RAW IN DEFAULT

DBMS_STATS.CONVERT_RAW_VALUE函数
  1. SQL> desc dbms_stats
  2. PROCEDURE CONVERT_RAW_VALUE
  3.  Argument Name Type In/Out Default?
  4.  ------------------------------ ----------------------- ------ --------
  5.  RAWVAL RAW IN
  6.  RESVAL VARCHAR2 OUT

  8. PROCEDURE CONVERT_RAW_VALUE
  9.  Argument Name Type In/Out Default?
  10.  ------------------------------ ----------------------- ------ --------
  11.  RAWVAL RAW IN
  12.  RESVAL DATE OUT

  14. PROCEDURE CONVERT_RAW_VALUE
  15.  Argument Name Type In/Out Default?
  16.  ------------------------------ ----------------------- ------ --------
  17.  RAWVAL RAW IN

  18.
  19.  RESVAL NUMBER OUT
  20. PROCEDURE CONVERT_RAW_VALUE
  21.  Argument Name Type In/Out Default?
  22.  ------------------------------ ----------------------- ------ --------
  23.  RAWVAL RAW IN
  24.  RESVAL BINARY_FLOAT OUT

  26. PROCEDURE CONVERT_RAW_VALUE
  27.  Argument Name Type In/Out Default?
  28.  ------------------------------ ----------------------- ------ --------
  29.  RAWVAL RAW IN
  30.  RESVAL BINARY_DOUBLE OUT

utl_raw、CONVERT_RAW_VALUE使用在 字符数值比对、统计信息等指标数值转换上。 

下面是简单实验。
  1. --TABLE存在4个不同类型的字段
  2. SQL> DESC TABLE
  3.  Name                      Null?     Type
  4.  ------------------------- -------- ----------------------------
  5.  DIS_NUMBER                NOT NULL NUMBER(12)
  6.  RSVDC3                             VARCHAR2(16)
  7.  ORDER_PV                           NUMBER(12,2)
  8.  SALE_DATE                          DATE

  10. --统计信息收集后，列的统计信息如下。SQL执行计划与索引的使用，会参考统计信息获得的值。
  11. --现在我们主要关注LOW_VALUE,HIGH_VALUE字段的值。
  12. COLUMN_NAME    LOW_VALUE          HIGH_VALUE
  13. -------------- ------------------ ------------------------
  14. DIS_NUMBER     C102               C60A6464646464
  15. RSVDC3         3C6241395166       C40A4D4323
  16. ORDER_PV       3D582C5166         C4400707450B
  17. SALE_DATE      786D0305010101     78C70C04010101


使用utl_raw.CAST_TO_NUMBER函数获取DIS_NUMBER字段，LOW_VALUE与HIGH_VALUE的值。

  1. SQL> select utl_raw.CAST_TO_NUMBER('C102') low_num,utl_raw.CAST_TO_NUMBER('C60A6464646464') high_num from dual

  3.    LOW_NUM HIGH_NUM
  4. ---------- ----------------------
  5.          1 99999999999
  6.   
  7. --同样，可以使用utl_raw.CAST_FROM_NUMBER函数转换成数据库的raw格式。
  8.   
  9. SQL>select utl_raw.CAST_FROM_NUMBER(1) LOW_VALUE,utl_raw.CAST_FROM_NUMBER(99999999999) HIGH_VALUE from dual
  10.   
  11.          LOW_VALUE  HIGH_VALUE
  12. ------------------ ------------------------
  13.              C102   C60A6464646464

同理，VARCHAR类型的值，也可使用utl_raw.CAST_TO_VARCHAR2 

DATE类型使用dbms_stats.convert_raw_value()函数。
  1. DECLARE
     rv RAW(32) := '786D0305010101';
     dt DATE := NULL;
    BEGIN
      dbms_stats.convert_raw_value(rv, dt);
      dbms_output.put_line('LOW_DATE: '||TO_CHAR(dt, 'YYYY-MM-DD hh24:mi:ss'));
    END;
    /

  2. LOW_DATE: 2009-03-05 00:00:00

下面自定义函数，利用dbms_stats.convert_raw_value函数，可快速获得不同类型的真实值。
  1. create or replace function display_raw (rawval raw, type varchar2)
    return varchar2
    is
       cn     number;
       cv     varchar2(32);
       cd     date;
       cnv    nvarchar2(32);
       cr     rowid;
       cc     char(32);
    begin
      if (type = 'NUMBER') then
         dbms_stats.convert_raw_value(rawval, cn);
         return to_char(cn);
      elsif (type = 'VARCHAR2') then
         dbms_stats.convert_raw_value(rawval, cv);
         return to_char(cv);
      elsif (type = 'DATE') then
         dbms_stats.convert_raw_value(rawval, cd);
         return to_char(cd);
      elsif (type = 'NVARCHAR2') then
         dbms_stats.convert_raw_value(rawval, cnv);
         return to_char(cnv);
      elsif (type = 'ROWID') then
         dbms_stats.convert_raw_value(rawval, cr);
         return to_char(cnv);
      elsif (type = 'CHAR') then
         dbms_stats.convert_raw_value(rawval, cc);
         return to_char(cc);
      else
         return 'UNKNOWN DATATYPE';
      end if;
    end;
    /


    select
        a.column_name, a.partition_name,
        a.num_distinct,
        display_raw(a.low_value,b.data_type) as low_val,
        display_raw(a.high_value,b.data_type) as high_val,
        b.data_type
     from
        dba_part_col_statistics a, dba_tab_cols b
     where
       a.owner='&OWNER' and
       a.table_name='&TABLE_NAME' and
       a.table_name=b.table_name and
       a.column_name=b.column_name and
       a.low_value is not null
    order by 1, 2;

COLUMN_NAME                    PARTITION_NAME     NUM_DISTINCT LOW_VALUE          HIGH_VALUE               DATA_TYPE
------------------------------ ------------------ ------------ ------------------ ------------------------ ----------------
ALLOCATION_DIST_NUMBER         P_MOH_2012_10              4964 6737               9999520                  NUMBER
ALLOCATION_DIST_NUMBER         P_MOH_2012_11              4989 6737               9999956                  NUMBER
ALLOCATION_DIST_NUMBER         P_MOH_2012_12              5351 6737               9999936                  NUMBER
ALLOCATION_DIST_NUMBER         P_MOH_2012_6                 12 7007633            9961247                  NUMBER
ALLOCATION_DIST_NUMBER         P_MOH_2012_7                380 7000028            9996912                  NUMBER
CURRENCY_CODE                  P_MOH_2014_4                  3 HKD                RMB                      VARCHAR2
CURRENCY_CODE                  P_MOH_2014_5                  3 HKD                RMB                      VARCHAR2
CURRENCY_CODE                  P_MOH_2014_6                  3 HKD                RMB                      VARCHAR2
CURRENCY_CODE                  P_MOH_2014_7                  3 HKD                RMB                      VARCHAR2
INVOICE_SALES_DATE             P_MOH_2014_10                78 06-SEP-14          23-JAN-15                DATE
INVOICE_SALES_DATE             P_MOH_2014_11                77 14-OCT-14          26-JAN-15                DATE
INVOICE_SALES_DATE             P_MOH_2014_12                75 10-NOV-14          01-FEB-15                DATE
INVOICE_SALES_DATE             P_MOH_2014_2                 40 12-FEB-14          29-MAR-14                DATE
INVOICE_SALES_DATE             P_MOH_2014_3                 81 01-MAR-14          30-APR-14                DATE

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17086096/viewspace-1983619/ ，如需转载，请注明出处，否则将追究法律责任。

0
1
上一篇： 大量insert引起的enq: HW - contention等待
下一篇： 11g密码错误延时造成大量"library cache lock"等待
请登录后发表评论 登录
全部评论
startay

注册时间：2013-07-29

  • 博文量
    45
  • 访问量
    117895

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593