ORACLE utl_raw函数与dbms_stats.convert_raw_value函数使用
ORACLE VERSION 11.2.0.4
下面是utl_raw包下的所有函数
DBMS_STATS. CONVERT_RAW_VALUE函数
utl_raw、CONVERT_RAW_VALUE使用在 字符数值比对、统计信息等指标数值转换上。
下面是简单实验。
使用utl_raw.CAST_TO_NUMBER函数获取 DIS_NUMBER字段 ,LOW_VALUE与HIGH_VALUE的值。
同理,VARCHAR类型的值,也可使用utl_raw.CAST_TO_VARCHAR2
DATE类型使用 dbms_stats.convert_raw_value()函数。
下面自定义函数,利用 dbms_stats.convert_raw_value函数,可快速获得不同类型的真实值。
ORACLE VERSION 11.2.0.4
下面是utl_raw包下的所有函数
- SQL>desc utl_raw
-
- FUNCTION BIT_AND RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R1 RAW IN
- R2 RAW IN
-
- FUNCTION BIT_COMPLEMENT RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
-
- FUNCTION BIT_OR RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R1 RAW IN
- R2 RAW IN
-
- FUNCTION BIT_XOR RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R1 RAW IN
- R2 RAW IN
-
- FUNCTION CAST_FROM_BINARY_DOUBLE RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- N BINARY_DOUBLE IN
- ENDIANESS BINARY_INTEGER IN DEFAULT
-
- FUNCTION CAST_FROM_BINARY_FLOAT RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- N BINARY_FLOAT IN
- ENDIANESS BINARY_INTEGER IN DEFAULT
-
- FUNCTION CAST_FROM_BINARY_INTEGER RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- N BINARY_INTEGER IN
- ENDIANESS BINARY_INTEGER IN DEFAULT
-
- FUNCTION CAST_FROM_NUMBER RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- N NUMBER IN
-
- FUNCTION CAST_TO_BINARY_DOUBLE RETURNS BINARY_DOUBLE
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- ENDIANESS BINARY_INTEGER IN DEFAULT
-
- FUNCTION CAST_TO_BINARY_FLOAT RETURNS BINARY_FLOAT
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- ENDIANESS BINARY_INTEGER IN DEFAULT
-
- FUNCTION CAST_TO_BINARY_INTEGER RETURNS BINARY_INTEGER
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- ENDIANESS BINARY_INTEGER IN DEFAULT
-
- FUNCTION CAST_TO_NUMBER RETURNS NUMBER
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- FUNCTION CAST_TO_NVARCHAR2 RETURNS NVARCHAR2
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
-
- FUNCTION CAST_TO_RAW RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- C VARCHAR2 IN
-
- FUNCTION CAST_TO_VARCHAR2 RETURNS VARCHAR2
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- FUNCTION COMPARE RETURNS NUMBER
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R1 RAW IN
- R2 RAW IN
- PAD RAW IN DEFAULT
-
- FUNCTION CONCAT RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R1 RAW IN DEFAULT
- R2 RAW IN DEFAULT
- R3 RAW IN DEFAULT
- R4 RAW IN DEFAULT
- R5 RAW IN DEFAULT
- R6 RAW IN DEFAULT
- R7 RAW IN DEFAULT
- R8 RAW IN DEFAULT
- R9 RAW IN DEFAULT
- R10 RAW IN DEFAULT
- R11 RAW IN DEFAULT
- R12 RAW IN DEFAULT
-
- FUNCTION CONVERT RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- TO_CHARSET VARCHAR2 IN
- FROM_CHARSET VARCHAR2 IN
-
- FUNCTION COPIES RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- N NUMBER IN
- FUNCTION LENGTH RETURNS NUMBER
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
-
- FUNCTION OVERLAY RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- OVERLAY_STR RAW IN
- TARGET RAW IN
- POS BINARY_INTEGER IN DEFAULT
- LEN BINARY_INTEGER IN DEFAULT
- PAD RAW IN DEFAULT
- FUNCTION REVERSE RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
-
- FUNCTION SUBSTR RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- POS BINARY_INTEGER IN
- LEN BINARY_INTEGER IN DEFAULT
- FUNCTION TRANSLATE RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- FROM_SET RAW IN
- TO_SET RAW IN
-
- FUNCTION TRANSLITERATE RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- R RAW IN
- TO_SET RAW IN DEFAULT
- FROM_SET RAW IN DEFAULT
- PAD RAW IN DEFAULT
-
- FUNCTION XRANGE RETURNS RAW
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- START_BYTE RAW IN DEFAULT
- END_BYTE RAW IN DEFAULT
DBMS_STATS. CONVERT_RAW_VALUE函数
- SQL> desc dbms_stats
- PROCEDURE CONVERT_RAW_VALUE
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- RAWVAL RAW IN
- RESVAL VARCHAR2 OUT
-
- PROCEDURE CONVERT_RAW_VALUE
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- RAWVAL RAW IN
- RESVAL DATE OUT
-
- PROCEDURE CONVERT_RAW_VALUE
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- RAWVAL RAW IN
-
- RESVAL NUMBER OUT
- PROCEDURE CONVERT_RAW_VALUE
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- RAWVAL RAW IN
- RESVAL BINARY_FLOAT OUT
-
- PROCEDURE CONVERT_RAW_VALUE
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- RAWVAL RAW IN
- RESVAL BINARY_DOUBLE OUT
utl_raw、CONVERT_RAW_VALUE使用在 字符数值比对、统计信息等指标数值转换上。
下面是简单实验。
- --TABLE存在4个不同类型的字段
- SQL> DESC TABLE
- Name Null? Type
- ------------------------- -------- ----------------------------
- DIS_NUMBER NOT NULL NUMBER(12)
- RSVDC3 VARCHAR2(16)
- ORDER_PV NUMBER(12,2)
- SALE_DATE DATE
-
- --统计信息收集后,列的统计信息如下。SQL执行计划与索引的使用,会参考统计信息获得的值。
- --现在我们主要关注LOW_VALUE,HIGH_VALUE字段的值。
- COLUMN_NAME LOW_VALUE HIGH_VALUE
- -------------- ------------------ ------------------------
- DIS_NUMBER C102 C60A6464646464
- RSVDC3 3C6241395166 C40A4D4323
- ORDER_PV 3D582C5166 C4400707450B
- SALE_DATE 786D0305010101 78C70C04010101
使用utl_raw.CAST_TO_NUMBER函数获取 DIS_NUMBER字段 ,LOW_VALUE与HIGH_VALUE的值。
- SQL> select utl_raw.CAST_TO_NUMBER('C102') low_num,utl_raw.CAST_TO_NUMBER('C60A6464646464') high_num from dual
-
- LOW_NUM HIGH_NUM
- ---------- ----------------------
- 1 99999999999
-
- --同样,可以使用utl_raw.CAST_FROM_NUMBER函数转换成数据库的raw格式。
-
- SQL>select utl_raw.CAST_FROM_NUMBER(1) LOW_VALUE,utl_raw.CAST_FROM_NUMBER(99999999999) HIGH_VALUE from dual
-
- LOW_VALUE HIGH_VALUE
- ------------------ ------------------------
- C102 C60A6464646464
同理,VARCHAR类型的值,也可使用utl_raw.CAST_TO_VARCHAR2
DATE类型使用 dbms_stats.convert_raw_value()函数。
- 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;
/ -
LOW_DATE: 2009-03-05 00:00:00
下面自定义函数,利用 dbms_stats.convert_raw_value函数,可快速获得不同类型的真实值。
- 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
------------------------------ ------------------ ------------ ------------------ ------------------------ ----------------
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博客 ” ,链接:http://blog.itpub.net/17086096/viewspace-1983619/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17086096/viewspace-1983619/