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,

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

3 to_char(trunc(sysdate) +

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

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

6 'hh24:mi:ss') source_value

7 from user_tab_histograms a

8 where a.table_name = 'HTC3'

9 and a.COLUMN_NAME = 'B'

10 order 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;

Name Null? Type

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

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

NUM_DISTINCT NUMBER

LOW_VALUE RAW(32)

HIGH_VALUE RAW(32)

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

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

網上找到的一個函式：

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,'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;
/

3 yangtingkun 寫的一個恢復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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/758322/viewspace-751219/，如需轉載，請註明出處，否則將追究法律責任。

oracle 資料型別轉換

相關文章