DB2的函数（实现按位与或）
CREATE TABLE mytab(name varchar(30) not null,
age int not null,
flag1 smallint NOT NULL,
flag2 smallint NOT NULL);
select NAME, AGE, FLAG1, FLAG2
from UU.MYTAB;
--转化为二进制
CREATE FUNCTION int_to_binary (N1 Integer)
RETURNS varchar(32)
LANGUAGE SQL
SPECIFIC int2bin
BEGIN ATOMIC
DECLARE M1, i, len Integer default 0;
DECLARE temp_str varchar(32) default ' ';
DECLARE result_str varchar(32) default ' ';
SET M1 = N1;
WHILE M1 > 0 DO
SET temp_str = temp_str || cast(mod(m1,2) as char(1));
set m1 = m1/2;
END WHILE;
set len = length (temp_str);
while i < len do
set result_str = result_str || substr(temp_str,len-i,1);
set i = i+1;
end while;
RETURN result_str;
END
select int_to_binary(8) from mytab;
drop function int_to_binary;
--按位与
--按位或
CREATE FUNCTION BITOR (N1 Integer, N2 Integer)
RETURNS Integer
LANGUAGE SQL
SPECIFIC BITORCONV
BEGIN ATOMIC
DECLARE M1, M2, S , temp1 Integer;
DECLARE RetVal Integer DEFAULT 0;
SET (M1, M2, S) = (N1, N2, 0);
WHILE ( M1 > 0 OR M2 > 0) AND S < 32 DO
IF MOD(M1,2)=0 AND MOD(M2,2)=0 THEN
SET temp1= 0;
ELSE
SET temp1=1;
END IF;
SET RetVal = RetVal + temp1*power(2,S);
SET (M1, M2, S) = (M1/2, M2/2, S+1);
END WHILE;
RETURN RetVal;
END
select BITAND(8,12) from mytab;
CREATE FUNCTION BITAND (N1 Integer, N2 Integer)
RETURNS Integer
LANGUAGE SQL
SPECIFIC BITANDOracle
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC
DECLARE M1, M2, S Integer;
DECLARE RetVal Integer DEFAULT 0;
SET (M1, M2, S) = (N1, N2, 0);
WHILE M1 > 0 AND M2 > 0 AND S < 32 DO
SET RetVal = RetVal + MOD(M1,2)*MOD(M2,2)*power(2,S);
SET (M1, M2, S) = (M1/2, M2/2, S+1);
END WHILE;
RETURN RetVal;
END
select BITAND(8,12) from mytab;
select power(2,4) from mytab;
drop function BIT_OR;
不过现在内存空间大了，像这样巧的按位操作来实现的方法似乎已经过时了，哎~~ 进行途中就放弃了写这样的函数来实现按位的操作。这样的设计大大的影响了索引的效率。
页:
[1]