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

最新推荐文章于 2024-05-28 00:12:41 发布

hornsey2012

最新推荐文章于 2024-05-28 00:12:41 发布

阅读量8.9k

点赞数 2

分类专栏：数据库文章标签： oracle RAW 位操作

本文链接：https://blog.csdn.net/nalw2012/article/details/72466256

版权

数据库专栏收录该内容

10 篇文章 2 订阅

订阅专栏

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

hornsey2012

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Oracle RAW类型基本操作函数及使用示例

RAW类型是Oracle中用于保存位串的一种数据类型，类似于CHAR，使用RAW（L) 方式声明，最长可达32767字节。系统提供了一个SYS.utl_raw工具包用于RAW类型的操作，常用的函数如下：1、长度计算函数，得到一个raw类型变量的长度，单位为字节 FUNCTIONlength(rINRAW)RETURNNUMBER;如：
复制链接

扫一扫