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

发布时间：2020-12-12 14:54:45 所属栏目：百科来源：网络整理

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!