解析Oracle各种数据类型-服务器专区

解析Oracle各种数据类型

作者：网络　佚名编辑： IT168 2005-12-29 00:00

　　【IT168 服务器学院】 前一阵写了Oracle基本数据类型存储格式浅析，对各种数量类型的存储进行了简单的描述，而后又写了一篇repare包修复坏块，其中自己写了一个程序包来恢复DUMP后的数据。但是那个程序包主要是针对repare包生成的结果的，因此通用性不好。

　　这篇文章将那个程序包修改并简化，变为一个函数。下面给出这个函数的实现和使用例子：

　　代码：——

SQL> CREATE OR REPLACE FUNCTION F_GET_FROM_DUMP 2 ( 3 P_DUMP IN VARCHAR2, 4 P_TYPE IN VARCHAR2 5 ) 6 RETURN VARCHAR2 AS 7 V_LENGTH_STR VARCHAR2(10); 8 V_LENGTH NUMBER DEFAULT 7; 9 V_DUMP_ROWID VARCHAR2(30000); 10 11 V_DATE_STR VARCHAR2(100); 12 TYPE T_DATE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 13 V_DATE T_DATE; 14 15 FUNCTION F_ADD_PREFIX_ZERO (P_STR IN VARCHAR2, P_POSITION IN NUMBER) RETURN VARCHAR2 16 AS 17 V_STR VARCHAR2(30000) := P_STR; 18 V_POSITION NUMBER := P_POSITION; 19 V_STR_PART VARCHAR2(2); 20 V_RETURN VARCHAR2(30000); 21 BEGIN 22 WHILE (V_POSITION != 0) LOOP 23 V_STR_PART := SUBSTR(V_STR, 1, V_POSITION - 1); 24 V_STR := SUBSTR(V_STR, V_POSITION + 1); 25 26 IF V_POSITION = 2 THEN 27 V_RETURN := V_RETURN || ''0'' || V_STR_PART; 28 ELSIF V_POSITION = 3 THEN 29 V_RETURN := V_RETURN || V_STR_PART; 30 ELSE 31 RAISE_APPLICATION_ERROR(-20002, ''DUMP ERROR CHECK THE INPUT ROWID''); 32 END IF; 33 34 V_POSITION := INSTR(V_STR, '',''); 35 END LOOP; 36 RETURN REPLACE(V_RETURN , '',''); 37 END F_ADD_PREFIX_ZERO; 38 39 BEGIN 40 IF SUBSTR(P_DUMP, 1, 3) = ''Typ'' THEN 41 V_DUMP_ROWID := SUBSTR(P_DUMP, INSTR(P_DUMP, '':'') + 2); 42 ELSE 43 V_DUMP_ROWID := P_DUMP; 44 END IF; 45 46 IF P_TYPE = ''VARCHAR2'' OR P_TYPE = ''CHAR'' THEN 47 48 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || '','', INSTR(V_DUMP_ROWID, '','')); 49 50 RETURN(UTL_RAW.CAST_TO_VARCHAR2(V_DUMP_ROWID)); 51 52 ELSIF P_TYPE = ''NUMBER'' THEN 53 54 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || '','', INSTR(V_DUMP_ROWID, '','')); 55 56 RETURN(TO_CHAR(UTL_RAW.CAST_TO_NUMBER(V_DUMP_ROWID))); 57 58 ELSIF P_TYPE = ''DATE'' THEN 59 60 V_DUMP_ROWID := '','' || V_DUMP_ROWID || '',''; 61 62 FOR I IN 1..7 LOOP 63 V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, '','', 1, I) + 1, 64 INSTR(V_DUMP_ROWID, '','', 1, I + 1) - INSTR(V_DUMP_ROWID, '','', 1, I) - 1), ''XXX''); 65 END LOOP; 66 67 V_DATE(1) := V_DATE(1) - 100; 68 V_DATE(2) := V_DATE(2) - 100; 69 70 IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN 71 V_DATE_STR := ''-'' || LTRIM(TO_CHAR(ABS(V_DATE(1)), ''00'')) || LTRIM(TO_CHAR(ABS(V_DATE(2)), ''00'')); 72 ELSE 73 V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), ''00'')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),''00'')); 74 END IF; 75 76 V_DATE_STR := V_DATE_STR || ''-'' || TO_CHAR(V_DATE(3)) || ''-'' || TO_CHAR(V_DATE(4)) || '' '' || 77 TO_CHAR(V_DATE(5) - 1) || '':'' || TO_CHAR(V_DATE(6) - 1) || '':'' || TO_CHAR(V_DATE(7) - 1); 78 RETURN (V_DATE_STR); 79 80 ELSIF ((P_TYPE LIKE ''TIMESTAMP(_)'') OR (P_TYPE = ''TIMESTAMP'')) THEN 81 82 V_DUMP_ROWID := '','' || V_DUMP_ROWID || '',''; 83 84 FOR I IN 1..11 LOOP 85 V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, '','', 1, I) + 1, 86 INSTR(V_DUMP_ROWID, '','', 1, I + 1) - INSTR(V_DUMP_ROWID, '','', 1, I) - 1), ''XXX''); 87 END LOOP; 88 89 V_DATE(1) := V_DATE(1) - 100; 90 V_DATE(2) := V_DATE(2) - 100; 91 92 IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN 93 V_DATE_STR := ''-'' || LTRIM(TO_CHAR(ABS(V_DATE(1)), ''00'')) || LTRIM(TO_CHAR(ABS(V_DATE(2)), ''00'')); 94 ELSE 95 V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), ''00'')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),''00'')); 96 END IF; 97 98 V_DATE_STR := V_DATE_STR || ''-'' || TO_CHAR(V_DATE(3)) || ''-'' || TO_CHAR(V_DATE(4)) || '' '' || 99 TO_CHAR(V_DATE(5) - 1) || '':'' || TO_CHAR(V_DATE(6) - 1) || '':'' || TO_CHAR(V_DATE(7) - 1) || ''.'' || 100 SUBSTR(TO_CHAR(V_DATE(8) * POWER(256, 3) + V_DATE(9) * POWER(256, 2) + V_DATE(10) * 256 + V_DATE(11)), 101 1, NVL(TO_NUMBER(SUBSTR(P_TYPE, 11, 1)), 6));102 RETURN (V_DATE_STR);103 104 ELSIF P_TYPE = ''RAW'' THEN105 106 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || '','', INSTR(V_DUMP_ROWID, '',''));107 108 RETURN(V_DUMP_ROWID);109 110 ELSIF P_TYPE = ''ROWID'' THEN111 112 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || '','', INSTR(V_DUMP_ROWID, '',''));113 RETURN (DBMS_ROWID.ROWID_CREATE(114 1, 115 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 1, 8), ''XXXXXXXXXXX''), 116 TRUNC(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), ''XXXXXX'')/64), 117 TO_NUMBER(MOD(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), ''XXXXXX''), 64) || 118 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 13, 4), ''XXXXXXXXXXX'')),119 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 17, 4), ''XXXXXX''))); 120 121 ELSE122 RAISE_APPLICATION_ERROR(-20001, ''TYPE NOT VALID OR CAN''''T TRANSALTE '' || P_TYPE || '' TYPE'');123 END IF;124 125 END;126 /
　　函数已创建。

SQL> SELECT F_GET_FROM_DUMP(DUMP(2342.231, 16), ''NUMBER'') FROM DUAL;F_GET_FROM_DUMP(DUMP(2342.231,16),''NUMBER'')--------------------------------------------2342.231SQL> SELECT F_GET_FROM_DUMP(DUMP(-0.00234, 16), ''NUMBER'') FROM DUAL;F_GET_FROM_DUMP(DUMP(-0.00234,16),''NUMBER'')----------------------------------------------.00234SQL> SELECT F_GET_FROM_DUMP(DUMP(''23EJF.M>'', 16), ''VARCHAR2'') FROM DUAL;F_GET_FROM_DUMP(DUMP(''23EJF.M>'',16),''VARCHAR2'')------------------------------------------------23EJF.M>SQL> SELECT F_GET_FROM_DUMP(DUMP(''测试'', 16), ''VARCHAR2'') FROM DUAL;F_GET_FROM_DUMP(DUMP(''测试'',16),''VARCHAR2'')------------------------------------------------　　测试。——
　　由于在SQL中直接使用DATE类型和Oracle存储的不一致，因此解析DATE和TIMESTAMP类型需要通过表中存储的数据，而不能通过SQL中的TO_DATE或SYSDATE.在SQL中直接使用的DATE类型的解析由于意义不大而没有给出。关于在SQL中直接使用DATE和存储在表中的DATE类型的区别，可以参考我的Oracle基本数据类型存储格式浅析中日期类型的文章，连接在文章末尾给出。
　　代码：——
　　SQL> CREATE TABLE TEST_DATE （TIME1 DATE， TIME2 TIMESTAMP， TIME3 TIMESTAMP（9））；
　　表已创建。
SQL> INSERT INTO TEST_DATE VALUES (SYSDATE,   2 TO_TIMESTAMP(''2004-4-9 22:59:43.234232222'', ''YYYY-MM-DD HH24:MI:SS.FF''),   3 TO_TIMESTAMP(''2004-4-9 22:59:43.234232222'', ''YYYY-MM-DD HH24:MI:SS.FF''));
　　已创建 1 行。
SQL> COL GET_DUMP FORMAT A30SQL> ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'';
　　会话已更改
SQL> SELECT TIME1, F_GET_FROM_DUMP(DUMP(TIME1, 16), ''DATE'') GET_DUMP FROM TEST_DATE;TIME1               GET_DUMP------------------- ------------------------------2005-04-09 23:00:04 2005-4-9 23:0:4SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF'';
　　会话已更改
SQL> SELECT TIME2, F_GET_FROM_DUMP(DUMP(TIME2, 16), ''TIMESTAMP'') GET_DUMP   2 FROM TEST_DATE;TIME2                              GET_DUMP---------------------------------- ------------------------2004-04-09 22:59:43.234232         2004-4-9 22:59:43.234232SQL> SELECT TIME3, F_GET_FROM_DUMP(DUMP(TIME3, 16), ''TIMESTAMP(9)'') GET_DUMP   2 FROM TEST_DATE;TIME3                              GET_DUMP---------------------------------- ------------------------2004-04-09 22:59:43.234232222      2004-4-9 22:59:43.234232222.--------------------------------------------------------------------------------
　　对于SQL中直接使用的DATE类型会报错：
　　代码：——
SQL> SELECT SYSDATE, F_GET_FROM_DUMP(DUMP(SYSDATE, 16), ''DATE'') GET_DUMP FROM DUAL;SYSDATE             GET_DUMP------------------- ------------------------------2005-04-09 23:04:58 -###93-4-9 22:3:57SQL> SELECT RAW_DATA, F_GET_FROM_DUMP(DUMP(RAW_DATA, 16), ''RAW'') GET_DUMP 2 FROM TEST_RAW;RAW_DATA             GET_DUMP-------------------- ------------------------------F5021C               f5021c.--------------------------------------------------------------------------------
　　这个函数目前支持CHAR、VARCHAR2、NUMBER、DATE、TIMESTAMP和RAW类型，上面分别举了例子。

　　函数的第一个参数可以是DUMP函数的输出，也可以是数据库中的直接存储信息（需要用逗号分隔）。

　　代码：——

SQL> SELECT F_GET_FROM_DUMP(''Typ=96 Len=4: 74,65,73,74'', ''VARCHAR2'') GET_DUMP 2 FROM DUAL;GET_DUMP------------------------------testSQL> SELECT F_GET_FROM_DUMP(''74,65,73,74'', ''VARCHAR2'') GET_DUMP 2 FROM DUAL;GET_DUMP------------------------------test

关注我们