|
http://yangtingkun.itpub.net/post/468/25748
发表人:yangtingkun | 发表时间: 2005年四月09日, 23:25
前一阵写了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'''' 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 /
函数已创建。
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),''''VARCHA [1] [2] 下一页 |
