解析Oracle各种数据类型 [转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3682 | 回复: 0   主题: 解析Oracle各种数据类型 [转帖]        下一篇 
wayne
注册用户
等级:中校
经验:1690
发帖:221
精华:0
注册:2011-7-21
状态:离线
发送短消息息给wayne 加好友    发送短消息息给wayne 发消息
发表于: IP:您无权察看 2011-8-16 16:15:43 | [全部帖] [楼主帖] 楼主

前一阵写了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),'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 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


这个函数目前支持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
------------------------------
test
SQL> SELECT F_GET_FROM_DUMP('74,65,73,74', 'VARCHAR2') GET_DUMP
2  FROM DUAL;
GET_DUMP
------------------------------
test


编写这个函数所根据的规则来自下列文章:

Oracle基本数据类型存储格式浅析(一)——字符类型:http://blog.itpub.net/post/468/9287

Oracle基本数据类型存储格式浅析(二)——数字类型:http://blog.itpub.net/post/468/9445

Oracle基本数据类型存储格式浅析(三)——日期类型(一):http://blog.itpub.net/post/468/10113

Oracle基本数据类型存储格式浅析(三)——日期类型(二):http://blog.itpub.net/post/468/10293

Oracle基本数据类型存储格式浅析(三)——日期类型(三):http://blog.itpub.net/post/468/10582

Oracle基本数据类型存储格式浅析(三)——日期类型(四):http://blog.itpub.net/post/468/13636

Oracle基本数据类型存储格式浅析(四)——ROWID类型(一):http://blog.itpub.net/post/468/11046

Oracle基本数据类型存储格式浅析(四)——ROWID类型(二):http://blog.itpub.net/post/468/11363

Oracle基本数据类型存储格式浅析(五)——RAW类型:http://blog.itpub.net/post/468/11490

这个函数是由下面这个文章中的包进行修改的:

DBMS_REPAIR的使用(二):http://blog.itpub.net/post/468/13241




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论