前一陣寫了Oracle基本數(shù)據(jù)類型存儲格式淺析，對各種數(shù)量類型的存儲進(jìn)行了簡單的描述，而后又寫了一篇repare包修復(fù)壞塊，其中自己寫了一個程序包來恢復(fù)DUMP后的數(shù)據(jù)。但是那個程序包主要是針對repare包生成的結(jié)果的，因此通用性不好。 這篇文章將那個程序包修改并簡化，變?yōu)橐粋€函數(shù)。下面給出這個函數(shù)的實(shí)現(xiàn)和使用例子： 代碼：——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' THEN
105
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' THEN
111
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 ELSE
122 RAISE_APPLICATION_ERROR(-20001, 'TYPE NOT VALID OR CAN''T TRANSALTE ' P_TYPE ' TYPE');
123 END IF;
124
125 END;
126 / 函數(shù)已創(chuàng)建。
SQL> SELECT F_GET_FROM_DUMP(DUMP(2342.231, 16), 'NUMBER') FROM DUAL;
F_GET_FROM_DUMP(DUMP(2342.231,16),'NUMBER')
--------------------------------------------
2342.231
SQL> SELECT F_GET_FROM_DUMP(DUMP(-0.00234, 16), 'NUMBER') FROM DUAL;
F_GET_FROM_DUMP(DUMP(-0.00234,16),'NUMBER')
---------------------------------------------
-.00234
SQL> 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')
------------------------------------------------
測試?！?/PRE> 由于在SQL中直接使用DATE類型和Oracle存儲的不一致，因此解析DATE和TIMESTAMP類型需要通過表中存儲的數(shù)據(jù)，而不能通過SQL中的TO_DATE或SYSDATE.在SQL中直接使用的DATE類型的解析由于意義不大而沒有給出。關(guān)于在SQL中直接使用DATE和存儲在表中的DATE類型的區(qū)別，可以參考我的Oracle基本數(shù)據(jù)類型存儲格式淺析中日期類型的文章，連接在文章末尾給出。 代碼：—— SQL> CREATE TABLE TEST_DATE （TIME1 DATE， TIME2 TIMESTAMP， TIME3 TIMESTAMP（9））； 表已創(chuàng)建。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')); 已創(chuàng)建 1 行。SQL> COL GET_DUMP FORMAT A30
SQL> 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:4
SQL> 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.234232
SQL> 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:57
SQL> SELECT RAW_DATA, F_GET_FROM_DUMP(DUMP(RAW_DATA, 16), 'RAW') GET_DUMP
2 FROM TEST_RAW;
RAW_DATA GET_DUMP
-------------------- ------------------------------
F5021C f5021c
.--------------------------------------------------------------------------------
這個函數(shù)目前支持CHAR、VARCHAR2、NUMBER、DATE、TIMESTAMP和RAW類型，上面分別舉了例子?！ 『瘮?shù)的第一個參數(shù)可以是DUMP函數(shù)的輸出，也可以是數(shù)據(jù)庫中的直接存儲信息（需要用逗號分隔）。 代碼：——SQL> SELECT F_GET_FROM_DUMP('Typ=96 Len=4: 74,65,73,74', 'VARCHAR2') GET_DUMP
2 FROM DUAL;
GET_DUMP
------------------------------
test
SQL> SELECT F_GET_FROM_DUMP('74,65,73,74', 'VARCHAR2') GET_DUMP
2 FROM DUAL;
GET_DUMP
------------------------------
test
.--------------------------------------------------------------------------------
