in 9i we could do a user defined aggregate to to a bitor. In 8i, we have to "fake it" ops$tkyte@ORA817DEV> create or replace function set_bitor( p_key in number ) return number 2 as 3 l_bitor number := 0; 4 begin 5 for x in ( select sal from emp where deptno = p_key ) 6 loop 7 l_bitor := (l_bitor + x.sal) - bitand( l_bitor, x.sal ); 8 end loop; 9 return l_bitor; 10 end; 11 /Function created.ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> select set_bitor( 10 ) from dual;SET_BITOR(10) ------------- 8094ops$tkyte@ORA817DEV> select lpad( to_bin( sal), 32, '0' ) from emp where deptno = 10;LPAD(TO_BIN(SAL),32,'0') -------------------------------- 00000000000000000000100110010010 00000000000000000001001110001000 00000000000000000000010100010100ops$tkyte@ORA817DEV> select to_dec( '1111110011110', 2 ) from dual;TO_DEC('1111110011110',2) ------------------------- 8094ops$tkyte@ORA817DEV> ops$tkyte@ORA817DEV> select deptno, set_bitor( deptno ) 2 from ( select distinct deptno from emp ) 3 / DEPTNO SET_BITOR(DEPTNO) ---------- ----------------- 10 8094 20 4095 30 4094ops$tkyte@ORA817DEV>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~utl_raw.bit_and( my_raw_value, hextoraw( rpad('1F',length(my_raw_value),'1F') ) );that'll ensure that the mask 1f is as long as it needs be and no longer. when we use length on my_raw_value, my_raw_value will be turned into hex first so we'll get the right number of 1F's ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ create or replace function to_base( p_dec in number, p_base in number ) return varchar2 is l_str varchar2(255) default NULL; l_num number default p_dec; l_hex varchar2(16) default '0123456789ABCDEF'; begin if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then raise PROGRAM_ERROR; end if; loop l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str; l_num := trunc( l_num/p_base ); exit when ( l_num = 0 ); end loop; return l_str; end to_base; / create or replace function to_dec ( p_str in varchar2, p_from_base in number default 16 ) return number is l_num number default 0; l_hex varchar2(16) default '0123456789ABCDEF'; begin for i in 1 .. length(p_str) loop l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1; end loop; return l_num; end to_dec; / show errorscreate or replace function to_hex( p_dec in number ) return varchar2 is begin return to_base( p_dec, 16 ); end to_hex; / create or replace function to_bin( p_dec in number ) return varchar2 is begin return to_base( p_dec, 2 ); end to_bin; / create or replace function to_oct( p_dec in number ) return varchar2 is begin return to_base( p_dec, 8 ); end to_oct; /
可能到了9i才真的比较好作 不过注意我给的函数都是oracle的文档中没记录的 就是oracle没支持的喔 [email protected]> create table t(v varchar2(20));Table [email protected]> insert into t values('1100');1 row [email protected]> insert into t values('1100');1 row [email protected]> commit;Commit [email protected]> select SYS_OP_VECOR(v,'1100') from dual; select SYS_OP_VECOR(v,'1100') from dual * ERROR at line 1: ORA-00904: "V": invalid identifier [email protected]> select SYS_OP_VECOR(v,'1100') from t;SYS_OP_VECOR(V,'1100 -------------------- 1100 [email protected]> select SYS_OP_VECxOR(v,'1100') from t;SYS_OP_VECXOR(V,'110 -------------------- 0000 [email protected]>
看错了,以为是"或"运算 如果你的a64是64位字符串的话,还是用我的吧 select * from table where instr(a64+'111100010000111000000000001011101010000','2')>0; 可以去试试,一般没问题的,字符串可以当数值加减的,只有2个全是1的时候加出来的结果中才会出现'2',所以,如果有'2'出现的话,这a64和'111100010000111000000000001011101010000'的与运算的结果肯定不是0.
to l2g32003(李广): 你这些资料去哪里查?有电子文档吗?
asktom.oracle.com 上面有很多开发的例子 很实用,专业的
SYS_OP_VECOR 位运算 or SYS_OP_VECXOR 位运算 xor SYS_OP_VECAND 位运算 and SYS_OP_VECBIT 位运算 看SYS_OP_VECBIT('f',4) 第n位是为 1 或 0 位数的
where instr(a64+'111100010000111000000000001011101010000','1')>0;
help me!
create table tab2 as select * from tab1
where a64 not like '0000???0????000???????????0?000?0?0????%';
其中:?代表0获1
“按位与运算” ----- 这个是不是两个都为1时结果为1 任何一个为0结果为[email protected]> select to_number('111100010000111000000000001011101010000') from dual;TO_NUMBER('111100010000111000000000001011101010000')
----------------------------------------------------
1.1110E+38
[email protected]> select to_char(to_number('111100010000111000000000001011101010000'),'XXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXX') from dual;TO_CHAR(TO_NUMBER('1111000100001110000000
-----------------------------------------
[email protected]>
9i开始提供的位运算函数
SYS_OP_VECOR 位运算 or
SYS_OP_VECXOR 位运算 xor
SYS_OP_VECAND 位运算 and
SYS_OP_VECBIT 位运算 看SYS_OP_VECBIT('f',4) 第n位是为 1 或 0 位数的其实值从0开始的以前的有一个bitand
按位或 两个相应的位只要一个为 1 结果为 1
(x + y) - BitAND(x, y) 可实现
异或 位上的同为0,1 为 0 不同的时候为 1
(x + y) - BitAND(x, y) * 2 或 BitOR(x, y) - BitAND(x, y) 可实现
ops$tkyte@ORA817DEV> create or replace function set_bitor( p_key in number )
return number
2 as
3 l_bitor number := 0;
4 begin
5 for x in ( select sal from emp where deptno = p_key )
6 loop
7 l_bitor := (l_bitor + x.sal) - bitand( l_bitor, x.sal );
8 end loop;
9 return l_bitor;
10 end;
11 /Function created.ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select set_bitor( 10 ) from dual;SET_BITOR(10)
-------------
8094ops$tkyte@ORA817DEV> select lpad( to_bin( sal), 32, '0' ) from emp where deptno
= 10;LPAD(TO_BIN(SAL),32,'0')
--------------------------------
00000000000000000000100110010010
00000000000000000001001110001000
00000000000000000000010100010100ops$tkyte@ORA817DEV> select to_dec( '1111110011110', 2 ) from dual;TO_DEC('1111110011110',2)
-------------------------
8094ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select deptno, set_bitor( deptno )
2 from ( select distinct deptno from emp )
3 / DEPTNO SET_BITOR(DEPTNO)
---------- -----------------
10 8094
20 4095
30 4094ops$tkyte@ORA817DEV>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~utl_raw.bit_and( my_raw_value, hextoraw( rpad('1F',length(my_raw_value),'1F') ) );that'll ensure that the mask 1f is as long as it needs be and no longer. when
we use length on my_raw_value, my_raw_value will be turned into hex first so
we'll get the right number of 1F's ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_dec;
/
show errorscreate or replace function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
/
不过注意我给的函数都是oracle的文档中没记录的 就是oracle没支持的喔
[email protected]> create table t(v varchar2(20));Table [email protected]> insert into t values('1100');1 row [email protected]> insert into t values('1100');1 row [email protected]> commit;Commit [email protected]> select SYS_OP_VECOR(v,'1100') from dual;
select SYS_OP_VECOR(v,'1100') from dual
*
ERROR at line 1:
ORA-00904: "V": invalid identifier
[email protected]> select SYS_OP_VECOR(v,'1100') from t;SYS_OP_VECOR(V,'1100
--------------------
1100
[email protected]> select SYS_OP_VECxOR(v,'1100') from t;SYS_OP_VECXOR(V,'110
--------------------
0000
[email protected]>
如果你的a64是64位字符串的话,还是用我的吧
select * from table
where instr(a64+'111100010000111000000000001011101010000','2')>0;
可以去试试,一般没问题的,字符串可以当数值加减的,只有2个全是1的时候加出来的结果中才会出现'2',所以,如果有'2'出现的话,这a64和'111100010000111000000000001011101010000'的与运算的结果肯定不是0.
你这些资料去哪里查?有电子文档吗?
SYS_OP_VECXOR 位运算 xor
SYS_OP_VECAND 位运算 and
SYS_OP_VECBIT 位运算 看SYS_OP_VECBIT('f',4) 第n位是为 1 或 0 位数的