Oracle RAW类型基本操作函数及使用示例

来源：互联网发布：群智能算法应用编辑：程序博客网时间：2024/05/17 08:55

RAW类型是Oracle中用于保存位串的一种数据类型，类似于CHAR，使用RAW（L) 方式声明，最长可达32767字节。

系统提供了一个SYS.utl_raw工具包用于RAW类型的操作，常用的函数如下：

1、长度计算函数，得到一个raw类型变量的长度，单位为字节

FUNCTIONlength(rINRAW)RETURNNUMBER;

如：

selectutl_raw.length('12344321')fromdual;

结果为:4

2、拼接函数，用于拼接两个raw类型变量

FUNCTIONconcat(r1 INRAWDEFAULTNULL,

r2 INRAWDEFAULTNULL,

r3 INRAWDEFAULTNULL,

r4 INRAWDEFAULTNULL,

r5 INRAWDEFAULTNULL,

r6 INRAWDEFAULTNULL,

r7 INRAWDEFAULTNULL,

r8 INRAWDEFAULTNULL,

r9 INRAWDEFAULTNULL,

r10INRAWDEFAULTNULL,

r11INRAWDEFAULTNULL,

r12INRAWDEFAULTNULL)RETURNRAW;

如：

selectutl_raw.concat('12','34')fromdual;

结果为:1234

3、获取子串函数

FUNCTIONsubstr(r INRAW,

posINBINARY_INTEGER,

lenINBINARY_INTEGERDEFAULTNULL)RETURNRAW;

如：

selectutl_raw.substr('12344321',2,1)fromdual;

结果为：34

4、位操作函数

FUNCTIONbit_and(r1INRAW,

r2INRAW)RETURNRAW;

FUNCTIONbit_or(r1INRAW,

r2INRAW)RETURNRAW;

FUNCTIONbit_xor(r1INRAW,

r2INRAW)RETURNRAW;

如：

selectutl_raw.bit_and('12344321','0f')fromdual;

selectutl_raw.bit_or('12344321','0f')fromdual;

selectutl_raw.bit_xor('12344321','0f')fromdual;

结果分别为：

02344321、1F344321、1D344321

5、给指定字节赋值

FUNCTIONoverlay(overlay_strINRAW,

target INRAW,

pos INBINARY_INTEGERDEFAULT1,

len INBINARY_INTEGERDEFAULTNULL,

pad INRAW DEFAULTNULL)RETURNRAW;

如：

select utl_raw.overlay('aa','12344321',2,1) from dual;

结果为：12AA4321

6、类型转换函数

FUNCTIONcast_to_raw(cINVARCHAR2CHARACTERSETANY_CS)RETURNRAW;

FUNCTIONcast_to_varchar2(rINRAW)RETURNVARCHAR2;

FUNCTIONcast_to_nvarchar2(rINRAW)RETURNNVARCHAR2;

FUNCTIONcast_to_number(rINRAW)RETURNNUMBER;

FUNCTIONcast_from_number(nINNUMBER)RETURNRAW;

FUNCTIONcast_to_binary_integer(rINRAW,

endianessINPLS_INTEGER

DEFAULT1)

RETURNBINARY_INTEGER;

FUNCTIONcast_from_binary_integer(n INBINARY_INTEGER,

endianessINPLS_INTEGER

DEFAULT1)

RETURNRAW;

FUNCTIONcast_from_binary_float(n INBINARY_FLOAT,

endianessINPLS_INTEGER

DEFAULT1)

RETURNRAW;

FUNCTIONcast_to_binary_float(rINRAW,

endianessINPLS_INTEGER

DEFAULT1)

RETURNBINARY_FLOAT;

FUNCTIONcast_from_binary_double(n INBINARY_DOUBLE,

endianessINPLS_INTEGER

DEFAULT1)

RETURNRAW;

FUNCTIONcast_to_binary_double(rINRAW,

endianessINPLS_INTEGER

DEFAULT1)

RETURNBINARY_DOUBLE;

7、其他函数

指定值替换

FUNCTIONtranslate(r INRAW,

from_setINRAW,

to_set INRAW)RETURNRAW;

指定值替换，长度不足填充

FUNCTIONtransliterate(r INRAW,

to_set INRAWDEFAULTNULL,

from_setINRAWDEFAULTNULL,

pad INRAWDEFAULTNULL)RETURNRAW;

复制函数

FUNCTIONcopies(rINRAW,

nINNUMBER)RETURNRAW;

得到指定范围内值组成的串

FUNCTIONxrange(start_byteINRAWDEFAULTNULL,

end_byte INRAWDEFAULTNULL)RETURNRAW;

反转函数

FUNCTIONreverse(rINRAW)RETURNRAW;

比较函数

FUNCTIONcompare(r1 INRAW,

r2 INRAW,

padINRAWDEFAULTNULL) RETURNNUMBER;

转换函数

FUNCTIONconvert(r INRAW,

to_charset INVARCHAR2,

from_charsetINVARCHAR2)RETURNRAW;

按位求余函数

FUNCTIONbit_complement(rINRAW)RETURNRAW;

下面是一个小例子，求出指定raw类型数值中0位所在的位置及总位数。

-- Created on 2017/5/17 by ADMINISTRATOR declare   -- Local variables here  i integer;  j integer;  len number(3) := 0;  pos number(3) := 0;  count1 number(3) := 0;  vec RAW(32) := 'FF11AA3344DDEEBBAA11998855367833FF11AA3344DDEEBBAA11998855367833';  nvec RAW(4);  nvec2 RAW(4);    v_start     TIMESTAMP(8) ;  v_end     TIMESTAMP(8) ;  v_interval INTERVAL DAY TO SECOND;   begin  -- Test statements here  len := utl_raw.length(vec);  DBMS_OUTPUT.put_line('Vector = ' || vec);  v_start := sysdate;  DBMS_OUTPUT.put_line('v_start = ' || v_start);  for i in 1..len  LOOP          nvec := utl_raw.substr(vec,i,1);     IF utl_raw.compare(nvec,'ff') != 0     THEN                       FOR j IN 1..8          LOOP              nvec2 := utl_raw.substr(utl_raw.cast_from_binary_integer(1*power(2,(8-j))),4,1);                            IF utl_raw.bit_and(nvec, nvec2) != nvec2              THEN                    pos := 8*(i-1)+j;                 count1 := count1 + 1;                 DBMS_OUTPUT.put_line('pos = ' || pos);              END IF;          END LOOP;     END IF;  END LOOP;    DBMS_OUTPUT.put_line('count1 = ' || count1);    v_end := sysdate;  DBMS_OUTPUT.put_line('v_end = ' || v_end);  v_interval := (v_end - v_start) DAY TO SECOND;  DBMS_OUTPUT.put_line('v_interval = ' || v_interval);end;

计算结果如下：

Vector = FF11AA3344DDEEBBAA11998855367833FF11AA3344DDEEBBAA11998855367833
v_start = 18-5月 -17 02.03.12.00000000 下午
pos = 9
pos = 10
pos = 11
pos = 13
pos = 14
pos = 15
pos = 18
pos = 20
pos = 22
pos = 24
pos = 25
pos = 26
pos = 29
pos = 30
pos = 33
pos = 35
pos = 36
pos = 37
pos = 39
pos = 40
pos = 43
pos = 47
pos = 52
pos = 56
pos = 58
pos = 62
pos = 66
pos = 68
pos = 70
pos = 72
pos = 73
pos = 74
pos = 75
pos = 77
pos = 78
pos = 79
pos = 82
pos = 83
pos = 86
pos = 87
pos = 90
pos = 91
pos = 92
pos = 94
pos = 95
pos = 96
pos = 97
pos = 99
pos = 101
pos = 103
pos = 105
pos = 106
pos = 109
pos = 112
pos = 113
pos = 118
pos = 119
pos = 120
pos = 121
pos = 122
pos = 125
pos = 126
pos = 137
pos = 138
pos = 139
pos = 141
pos = 142
pos = 143
pos = 146
pos = 148
pos = 150
pos = 152
pos = 153
pos = 154
pos = 157
pos = 158
pos = 161
pos = 163
pos = 164
pos = 165
pos = 167
pos = 168
pos = 171
pos = 175
pos = 180
pos = 184
pos = 186
pos = 190
pos = 194
pos = 196
pos = 198
pos = 200
pos = 201
pos = 202
pos = 203
pos = 205
pos = 206
pos = 207
pos = 210
pos = 211
pos = 214
pos = 215
pos = 218
pos = 219
pos = 220
pos = 222
pos = 223
pos = 224
pos = 225
pos = 227
pos = 229
pos = 231
pos = 233
pos = 234
pos = 237
pos = 240
pos = 241
pos = 246
pos = 247
pos = 248
pos = 249
pos = 250
pos = 253
pos = 254
count1 = 124
v_end = 18-5月 -17 02.03.12.00000000 下午
v_interval = +00 00:00:00.000000

阅读全文

1 0