分享
 
 
 

解析Oracle各种数据类型

王朝oracle·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

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

6RETURN 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

17V_STR VARCHAR2(30000) := P_STR;

18V_POSITION NUMBER := P_POSITION;

19V_STR_PART VARCHAR2(2);

20V_RETURN VARCHAR2(30000);

21 BEGIN

22WHILE (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

27V_RETURN := V_RETURN || '0' || V_STR_PART;

28 ELSIF V_POSITION = 3 THEN

29V_RETURN := V_RETURN || V_STR_PART;

30 ELSE

31RAISE_APPLICATION_ERROR(-20002, 'DUMP ERROR CHECK THE INPUT ROWID');

32 END IF;

33

34 V_POSITION := INSTR(V_STR, ',');

35END LOOP;

36RETURN REPLACE(V_RETURN , ',');

37 END F_ADD_PREFIX_ZERO;

38

39BEGIN

40 IF SUBSTR(P_DUMP, 1, 3) = 'Typ' THEN

41V_DUMP_ROWID := SUBSTR(P_DUMP, INSTR(P_DUMP, ':') + 2);

42 ELSE

43V_DUMP_ROWID := P_DUMP;

44 END IF;

45

46 IF P_TYPE = 'VARCHAR2' OR P_TYPE = 'CHAR' THEN

47

48V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));

49

50RETURN(UTL_RAW.CAST_TO_VARCHAR2(V_DUMP_ROWID));

51

52 ELSIF P_TYPE = 'NUMBER' THEN

53

54V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));

55

56RETURN(TO_CHAR(UTL_RAW.CAST_TO_NUMBER(V_DUMP_ROWID)));

57

58 ELSIF P_TYPE = 'DATE' THEN

59

60V_DUMP_ROWID := ',' || V_DUMP_ROWID || ',';

61

62FOR I IN 1..7 LOOP

63 V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1,

64INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX');

65END LOOP;

66

67V_DATE(1) := V_DATE(1) - 100;

68V_DATE(2) := V_DATE(2) - 100;

69

70IF ((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'));

72ELSE

73 V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),'00'));

74END IF;

75

76V_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);

78RETURN (V_DATE_STR);

79

80 ELSIF ((P_TYPE LIKE 'TIMESTAMP(_)') OR (P_TYPE = 'TIMESTAMP')) THEN

81

82V_DUMP_ROWID := ',' || V_DUMP_ROWID || ',';

83

84FOR I IN 1..11 LOOP

85 V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1,

86INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX');

87END LOOP;

88

89V_DATE(1) := V_DATE(1) - 100;

90V_DATE(2) := V_DATE(2) - 100;

91

92IF ((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'));

94ELSE

95 V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) || LTRIM(TO_CHAR(ABS(V_DATE(2)),'00'));

96END IF;

97

98V_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)),

1011, NVL(TO_NUMBER(SUBSTR(P_TYPE, 11, 1)), 6));

102RETURN (V_DATE_STR);

103

104 ELSIF P_TYPE = 'RAW' THEN

105

106V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID || ',', INSTR(V_DUMP_ROWID, ','));

107

108RETURN(V_DUMP_ROWID);

109

110 ELSIF P_TYPE = 'ROWID' THEN

111

112V_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) ||

118TO_NUMBER(SUBSTR(V_DUMP_ROWID, 13, 4), 'XXXXXXXXXXX')),

119 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 17, 4), 'XXXXXX')));

120

121 ELSE

122RAISE_APPLICATION_ERROR(-20001, 'TYPE NOT VALID OR CAN''T TRANSALTE ' || P_TYPE || ' TYPE');

123 END IF;

124

125END;

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,

2TO_TIMESTAMP('2004-4-9 22:59:43.234232222', 'YYYY-MM-DD HH24:MI:SS.FF'),

3TO_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

2FROM TEST_DATE;

TIME2GET_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

2FROM TEST_DATE;

TIME3GET_DUMP

---------------------------------- ------------------------

2004-04-09 22:59:43.2342322222004-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

2FROM 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

2FROM DUAL;

GET_DUMP

------------------------------

test

SQL> SELECT F_GET_FROM_DUMP('74,65,73,74', 'VARCHAR2') GET_DUMP

2FROM DUAL;

GET_DUMP

------------------------------

test

.--------------------------------------------------------------------------------

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有