ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 数据类型转换

oracle 数据类型转换

原创 Linux操作系统 作者:aaqwsh 时间:2012-12-17 15:27:43 0 删除 编辑

我们经常会遇到oracle数据类型的转换,整理如下:
1 oracle histogram
里的ENDPOINT_VALUEdate型):

 

--参考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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 盘点2012年
请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    261386