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] 下一页 没有相关教程
|