CREATETABLE mytab (name varchar(30) notnull, age intnotnull, flag1 smallintNOTNULL, flag2 smallintNOTNULL); select NAME, AGE, FLAG1, FLAG2 from UU.MYTAB; --转化为二进制 CREATEFUNCTION int_to_binary (N1 Integer) RETURNSvarchar(32) LANGUAGE SQL SPECIFIC int2bin BEGIN ATOMIC DECLARE M1, i, lenIntegerdefault0; 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) aschar(1)); set m1 = m1/2; ENDWHILE; setlen= length (temp_str); while i <len do set result_str = result_str || substr(temp_str,len-i,1); set i = i+1; endwhile; RETURN result_str; END select int_to_binary(8) from mytab; dropfunction int_to_binary;
--按位与
--按位或 CREATEFUNCTION BITOR (N1 Integer, N2 Integer) RETURNSInteger LANGUAGE SQL SPECIFIC BITORCONV BEGIN ATOMIC DECLARE M1, M2, S , temp1 Integer; DECLARE RetVal IntegerDEFAULT0; SET (M1, M2, S) = (N1, N2, 0); WHILE ( M1 >0OR M2 >0) AND S <32 DO IF MOD(M1,2)=0AND MOD(M2,2)=0THEN SET temp1=0; ELSE SET temp1=1; ENDIF; SET RetVal = RetVal + temp1*power(2,S); SET (M1, M2, S) = (M1/2, M2/2, S+1); ENDWHILE; RETURN RetVal; END select BITAND(8,12) from mytab;
CREATEFUNCTION BITAND (N1 Integer, N2 Integer) RETURNSInteger LANGUAGE SQL SPECIFIC BITANDOracle CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC BEGIN ATOMIC DECLARE M1, M2, S Integer; DECLARE RetVal IntegerDEFAULT0; SET (M1, M2, S) = (N1, N2, 0); WHILE M1 >0AND M2 >0AND 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); ENDWHILE; RETURN RetVal; END select BITAND(8,12) from mytab; selectpower(2,4) from mytab; dropfunction BIT_OR;
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