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位所在的位置及总位数。

@H_429_2404@

-- 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;

计算结果如下：

@H_429_2404@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

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

猜你在找的Oracle相关文章