oracle date转integer,oracle 数据类型转换

我们经常会遇到oracle数据类型的转换，整理如下：

1 oracle histogram里的ENDPOINT_VALUE(date型)：

--参考CBO Statistics data gathering and internal arithmetic analysis -- Converting to endpoint value [HelloDBA_COM]

To store the data as endpoint value, it needs convert the date to number. The rule is converting the value to days, both date & time parts. To convert time parts to day, it will convert it to seconds then divide 86400(24*60*60), which is the number of seconds of one day. After that, the number should also be ROUND to the 15th position before be stored.

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select B from htc3 t;

B

-------------------

2010-12-07 00:00:01

2010-12-07 00:00:02

2010-12-07 00:00:03

2010-12-07 00:00:04

2010-12-07 00:00:05

2010-12-07 12:50:01

2010-12-08 12:50:02

2010-12-09 12:50:03

2010-12-10 12:50:04

2010-12-07 12:50:05

10 rows selected.

--可通过如下函数把对应的值转换为date型，这对我们调查某些执行计划不稳定及使用直方图等很有用：

SQL> col B for 9999999999.999999999

SQL>

SQL> select a.ENDPOINT_VALUE b,

2to_char(to_date(trunc(a.ENDPOINT_VALUE), 'J'), 'yyyy-mm-dd') || ' ' ||

3to_char(trunc(sysdate) +

4to_number(substr(to_char(a.ENDPOINT_VALUE),

5instr(to_char(a.ENDPOINT_VALUE), '.'))),

6'hh24:mi:ss') source_value

7from user_tab_histograms a

8where a.table_name = 'HTC3'

9and a.COLUMN_NAME = 'B'

10order by endpoint_number;

B SOURCE_VALUE

--------------------- -------------------

2455538.000011570 2010-12-07 00:00:01

2455538.000023150 2010-12-07 00:00:02

2455538.000034720 2010-12-07 00:00:03

2455538.000046300 2010-12-07 00:00:04

2455538.000057870 2010-12-07 00:00:05

2455538.534733800 2010-12-07 12:50:01

2455538.534780090 2010-12-07 12:50:05

2455539.534745370 2010-12-08 12:50:02

2455540.534756950 2010-12-09 12:50:03

2455541.534768520 2010-12-10 12:50:04

10 rows selected.

2 dbms_stats.convert_raw_value来转换RAW数据，也可以还原ORACLE DUMP的值：

SQL> desc user_tab_col_statistics;

NameNull?Type

----------------------------------------- -------- ----------------------------

TABLE_NAMEVARCHAR2(30)

COLUMN_NAMEVARCHAR2(30)

NUM_DISTINCTNUMBER

LOW_VALUERAW(32)

HIGH_VALUERAW(32)

•low_valueis the lowest value in the column. It is shown in the internal representation.Note that for string columns (in the example, the columnpad), only the first 32 bytes are used.

•high_valueis the highest value in the column. It is shown in the internal representation.Notice that for string columns (in the example, the columnpad), only the first 32 bytes are used.

网上找到的一个函数：

createor replace functiondisplay_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,'dd-mon-yyyy');

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;

/

3yangtingkun写的一个恢复DUMP后数据的函数，摘录如下：

CREATE OR REPLACE FUNCTION F_GET_FROM_DUMP

(

P_DUMP IN VARCHAR2,

P_TYPE IN VARCHAR2

)

RETURN VARCHAR2 AS

V_LENGTH_STR VARCHAR2(10);

V_LENGTH NUMBER DEFAULT 7;

V_DUMP_ROWID VARCHAR2(30000);

V_DATE_STR VARCHAR2(100);

TYPE T_DATE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

V_DATE T_DATE;

FUNCTION F_ADD_PREFIX_ZERO (P_STR IN VARCHAR2, P_POSITION IN NUMBER) RETURN VARCHAR2

AS

V_STR VARCHAR2(30000) := P_STR;

V_POSITION NUMBER := P_POSITION;

V_STR_PART VARCHAR2(2);

V_RETURN VARCHAR2(30000);

BEGIN

WHILE (V_POSITION != 0) LOOP

V_STR_PART := SUBSTR(V_STR, 1, V_POSITION - 1);

V_STR := SUBSTR(V_STR, V_POSITION + 1);

IF V_POSITION = 2 THEN

V_RETURN := V_RETURN || '0' || V_STR_PART;

ELSIF V_POSITION = 3 THEN

V_RETURN := V_RETURN || V_STR_PART;

ELSE

RAISE_APPLICATION_ERROR(-20002, 'DUMP ERROR CHECK THE INPUT ROWID');

END IF;

V_POSITION := INSTR(V_STR, ',');

END LOOP;

RETURN REPLACE(V_RETURN , ',');

END F_ADD_PREFIX_ZERO;

BEGIN

IF SUBSTR(P_DUMP, 1, 3) = 'Typ' THEN

V_DUMP_ROWID := SUBSTR(P_DUMP, INSTR(P_DUMP, ':') + 2);

ELSE

V_DUMP_ROWID := P_DUMP;

END IF;

IF P_TYPE = 'VARCHAR2' OR P_TYPE = 'CHAR' THEN

V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));

RETURN(UTL_RAW.CAST_TO_VARCHAR2(V_DUMP_ROWID));

ELSIF P_TYPE = 'NUMBER' THEN

V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));

RETURN(TO_CHAR(UTL_RAW.CAST_TO_NUMBER(V_DUMP_ROWID)));

ELSIF P_TYPE = 'DATE' THEN

V_DUMP_ROWID := ',' || V_DUMP_ROWID || ',';

FOR I IN 1..7 LOOP

V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1,

INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX');

END LOOP;

V_DATE(1) := V_DATE(1) - 100;

V_DATE(2) := V_DATE(2) - 100;

IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN

V_DATE_STR := '-' || LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)), '00'));

ELSE

V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),'00'));

END IF;

V_DATE_STR := V_DATE_STR || '-' || TO_CHAR(V_DATE(3)) || '-' || TO_CHAR(V_DATE(4)) || ' ' ||

TO_CHAR(V_DATE(5) - 1) || ':' || TO_CHAR(V_DATE(6) - 1) || ':' || TO_CHAR(V_DATE(7) - 1);

RETURN (V_DATE_STR);

ELSIF ((P_TYPE LIKE 'TIMESTAMP(_)') OR (P_TYPE = 'TIMESTAMP')) THEN

V_DUMP_ROWID := ',' || V_DUMP_ROWID || ',';

FOR I IN 1..11 LOOP

V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1,

INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX');

END LOOP;

V_DATE(1) := V_DATE(1) - 100;

V_DATE(2) := V_DATE(2) - 100;

IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN

V_DATE_STR := '-' || LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)), '00'));

ELSE

V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),'00'));

END IF;

V_DATE_STR := V_DATE_STR || '-' || TO_CHAR(V_DATE(3)) || '-' || TO_CHAR(V_DATE(4)) || ' ' ||

TO_CHAR(V_DATE(5) - 1) || ':' || TO_CHAR(V_DATE(6) - 1) || ':' || TO_CHAR(V_DATE(7) - 1) || '.' ||

SUBSTR(TO_CHAR(V_DATE(8) * POWER(256, 3) + V_DATE(9) * POWER(256, 2) + V_DATE(10) * 256 + V_DATE(11)),

1, NVL(TO_NUMBER(SUBSTR(P_TYPE, 11, 1)), 6));

RETURN (V_DATE_STR);

ELSIF P_TYPE = 'RAW' THEN

V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));

RETURN(V_DUMP_ROWID);

ELSIF P_TYPE = 'ROWID' THEN

V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));

RETURN (DBMS_ROWID.ROWID_CREATE(

1,

TO_NUMBER(SUBSTR(V_DUMP_ROWID, 1, 8), 'XXXXXXXXXXX'),

TRUNC(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), 'XXXXXX')/64),

TO_NUMBER(MOD(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), 'XXXXXX'), 64) ||

TO_NUMBER(SUBSTR(V_DUMP_ROWID, 13, 4), 'XXXXXXXXXXX')),

TO_NUMBER(SUBSTR(V_DUMP_ROWID, 17, 4), 'XXXXXX')));

ELSE

RAISE_APPLICATION_ERROR(-20001, 'TYPE NOT VALID OR CAN''T TRANSALTE ' || P_TYPE || ' TYPE');

END IF;

END;