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

最新推荐文章于 2023-02-17 16:12:48 发布

原创最新推荐文章于 2023-02-17 16:12:48 发布 · 9.7k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #RAW #位操作

数据库专栏收录该内容

10 篇文章

订阅专栏

本文介绍了 Oracle 数据库中 RAW 类型的基本概念及其常用操作函数，包括长度计算、字符串拼接、子串获取等，并通过实例展示了如何进行位操作、类型转换及其它高级功能。

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