RAW类型是Oracle中用于保存位串的一种数据类型,类似于CHAR,使用RAW(L) 方式声明,最长可达32767字节。
系统提供了一个SYS.utl_raw工具包用于RAW类型的操作,常用的函数如下:
1、长度计算函数,得到一个raw类型变量的长度,单位为字节
FUNCTION
length(r
IN
RAW
)
RETURN
NUMBER
;
如:
select
utl_raw.length(
'12344321'
)
from
dual;
结果为:4
2、拼接函数,用于拼接两个raw类型变量
FUNCTION
concat(r1
IN
RAW
DEFAULT
NULL
,
r2
IN
RAW
DEFAULT
NULL
,
r3
IN
RAW
DEFAULT
NULL
,
r4
IN
RAW
DEFAULT
NULL
,
r5
IN
RAW
DEFAULT
NULL
,
r6
IN
RAW
DEFAULT
NULL
,
r7
IN
RAW
DEFAULT
NULL
,
r8
IN
RAW
DEFAULT
NULL
,
r9
IN
RAW
DEFAULT
NULL
,
r10
IN
RAW
DEFAULT
NULL
,
r11
IN
RAW
DEFAULT
NULL
,
r12
IN
RAW
DEFAULT
NULL
)
RETURN
RAW
;
如:
select
utl_raw.concat(
'12'
,
'34'
)
from
dual;
结果为:1234
3、获取子串函数
FUNCTION
substr
(r
IN
RAW
,
pos
IN
BINARY_INTEGER
,
len
IN
BINARY_INTEGER
DEFAULT
NULL
)
RETURN
RAW
;
如:
select
utl_raw.substr(
'12344321'
,
2
,
1
)
from
dual;
结果为:34
4、位操作函数
FUNCTION
bit_and(r1
IN
RAW
,
r2
IN
RAW
)
RETURN
RAW
;
FUNCTION
bit_or(r1
IN
RAW
,
r2
IN
RAW
)
RETURN
RAW
;
FUNCTION
bit_xor(r1
IN
RAW
,
r2
IN
RAW
)
RETURN
RAW
;
如:
select
utl_raw.bit_and(
'12344321'
,
'0f'
)
from
dual;
select
utl_raw.bit_or(
'12344321'
,
'0f'
)
from
dual;
select
utl_raw.bit_xor(
'12344321'
,
'0f'
)
from
dual;
结果分别为:
02344321、1F344321、1D344321
5、给指定字节赋值
FUNCTION
overlay(overlay_str
IN
RAW
,
target
IN
RAW
,
pos
IN
BINARY_INTEGER
DEFAULT
1
,
len
IN
BINARY_INTEGER
DEFAULT
NULL
,
pad
IN
RAW
DEFAULT
NULL
)
RETURN
RAW
;
如:
select utl_raw.overlay('aa','12344321',2,1) from dual;
结果为:12AA4321
6、类型转换函数
FUNCTION
cast_to_raw(c
IN
VARCHAR2
CHARACTER
SET
ANY_CS)
RETURN
RAW
;
FUNCTION
cast_to_varchar2(r
IN
RAW
)
RETURN
VARCHAR2
;
FUNCTION
cast_to_nvarchar2(r
IN
RAW
)
RETURN
NVARCHAR2
;
FUNCTION
cast_to_number(r
IN
RAW
)
RETURN
NUMBER
;
FUNCTION
cast_from_number(n
IN
NUMBER
)
RETURN
RAW
;
FUNCTION
cast_to_binary_integer(r
IN
RAW
,
endianess
IN
PLS_INTEGER
DEFAULT
1
)
RETURN
BINARY_INTEGER
;
FUNCTION
cast_from_binary_integer(n
IN
BINARY_INTEGER
,
endianess
IN
PLS_INTEGER
DEFAULT
1
)
RETURN
RAW
;
FUNCTION
cast_from_binary_float(n
IN
BINARY_FLOAT,
endianess
IN
PLS_INTEGER
DEFAULT
1
)
RETURN
RAW
;
FUNCTION
cast_to_binary_float(r
IN
RAW
,
endianess
IN
PLS_INTEGER
DEFAULT
1
)
RETURN
BINARY_FLOAT;
FUNCTION
cast_from_binary_double(n
IN
BINARY_DOUBLE,
endianess
IN
PLS_INTEGER
DEFAULT
1
)
RETURN
RAW
;
FUNCTION
cast_to_binary_double(r
IN
RAW
,
endianess
IN
PLS_INTEGER
DEFAULT
1
)
RETURN
BINARY_DOUBLE;
7、其他函数
指定值替换
FUNCTION
translate(r
IN
RAW
,
from_set
IN
RAW
,
to_set
IN
RAW
)
RETURN
RAW
;
指定值替换,长度不足填充
FUNCTION
transliterate(r
IN
RAW
,
to_set
IN
RAW
DEFAULT
NULL
,
from_set
IN
RAW
DEFAULT
NULL
,
pad
IN
RAW
DEFAULT
NULL
)
RETURN
RAW
;
复制函数
FUNCTION
copies(r
IN
RAW
,
n
IN
NUMBER
)
RETURN
RAW
;
得到指定范围内值组成的串
FUNCTION
xrange(start_byte
IN
RAW
DEFAULT
NULL
,
end_byte
IN
RAW
DEFAULT
NULL
)
RETURN
RAW
;
反转函数
FUNCTION
reverse
(r
IN
RAW
)
RETURN
RAW
;
比较函数
FUNCTION
compare(r1
IN
RAW
,
r2
IN
RAW
,
pad
IN
RAW
DEFAULT
NULL
)
RETURN
NUMBER
;
转换函数
FUNCTION
convert(r
IN
RAW
,
to_charset
IN
VARCHAR2
,
from_charset
IN
VARCHAR2
)
RETURN
RAW
;
按位求余函数
FUNCTION
bit_complement(r
IN
RAW
)
RETURN
RAW
;
下面是一个小例子,求出指定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
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