利用 UDF 轻松迁移：位运算、布尔运算和逐位运算的乐趣

核心提示：简介许多关系数据库（包括 Sybase、Oracle、Microsoft® SQL Server 和 Informix®）都支持位数据类型或布尔（Boolean）数据类型的列，并为这些数据类型提供了逐位（bitwise）函数或布尔（Boolean）函数，利用 UDF 轻松迁移：位运算、布尔运算和逐位运算

简介

许多关系数据库（包括 Sybase、Oracle、Microsoft® SQL Server 和 Informix®）都支持位数据类型或布尔（Boolean）数据类型的列，并为这些数据类型提供了逐位（bitwise）函数或布尔（Boolean）函数。T-SQL 也提供了逐位（bitwise）运算 —— integer、smallint 和 tinyint 数据类型之间进行的 AND、OR、NOT、EXCLUSIVE OR 运算；而 PL/SQL 支持 BITAND —— 或用于 integer 数据类型的逻辑 AND。DB2 UDB 没有为位数据类型或布尔数据类型提供本机支持，它既不支持逐位操作，也不支持布尔代数操作。

本文将提供一种方法，该方法包括使用约束条件或触发器创建了一个表，表中包含类似于位（bit-like）或布尔数据类型的列；该方法还包括一组用户定义函数（UDF），用于支持模仿位数据或布尔数据类型的行为的逐位运算和布尔运算。本文还提供了一组在整型变量之间执行逐位运算的 UDF。

模仿 T-SQL 位数据类型

以下是 T-SQL 参考中对位数据类型的定义：“使用位列（bit column）来获得真（true）和假（false）数据类型，或是（yes）和否（no）的数据类型。位列保存 0 或 1。位列接受 0 或 1 之外的整数值，但总是将它解释为 1。位（bit）数据类型的列不能为 NULL，且不能对其进行索引。”

例如，我们有一个表，它在 Sybase 或 SQL Server 数据库中的声明如下：

create　table　mytab　
　　　　　　(custname　varchar(30)　not　null,　
　　　　　　age　integer　not　null,　
　　　　　　flag1　bit　not　null,　
　　　　　　flag2　bit　not　null)

可以使用 DB2 SMALLINT 数据类型和 NOT NULL 约束条件，将该表转换成 DB2：

　　　CREATE　TABLE　mytab　
　　　　(name　varchar(30)　not　null,　
　　　　age　int　not　null,　
　　　　flag1　smallint　NOT　NULL,　　　　
　　　　flag2　smallint　NOT　NULL);

同时，我们还需要强制实施特殊规则，以复制 T-SQL 处理位数据类型列的方式。在位数据类型的定义中：“位列保存 0 或 1，它也接受 0 或 1 之外的整数值，但总是将该值解释为 1。”例如，在 Sybase 和 Microsoft SQL Server 中，如果向位列插入值 10，那么它将被解释为 1，而该列将保存值 1。为了确保列 flag1 和 flag2 只保存 1 或 0（无论 INSERT 语句中提供的是何值），需要创建下列 INSERT 触发器：

CREATE　TRIGGER　DB2ADMIN.INSFORBIT　
　　　NO　CASCADE　BEFORE　INSERT　ON　DB2ADMIN.MAR1　
　　　REFERENCING　NEW　AS　new　
　　　FOR　EACH　ROW　MODE　DB2SQL　
　　　BEGIN　ATOMIC　
　　　　　　if　new.c2　<>0　then　set　new.c2　=　1;　
　　　　　　end　if;　
　　　END

该触发器将确保 0 之外的任何值都被解释为 1。您还需要为 UPDATE 操作创建一个类似的触发器，以确保位列上的值是正确的。

现在，我们需要提供逐位运算函数：& (and)、| (or)、^ (exclusive or)、or ~ (not)。实质上，我们需要编写一组 UDF，实现下列位操作真值表。

& (and)	1	0
1	1	0
0	0	0

| (or)	1	0
1	1	1
0	1	0

^ (exclusive or)	1	0
1	0	1
0	1	0

~ (not)
1	FALSE
0	0

以下就是这组 UDF：

CREATE　FUNCTION　DB2ADMIN.BIT_AND(X　smallint,　Y　smallint)　
　　RETURNS　INTEGER　
------------------------------------------------------------------------　
--　SQL　UDF　(Scalar)　
------------------------------------------------------------------------　
F1:　BEGIN　ATOMIC　
　　IF　x　=1　and　y　=　1　THEN　
　　　RETURN　1　;　
　　ELSEIF　(x　<　0　or　x　>　1)　or　(y　<　0　or　y　>　1)　THEN　
　　　SIGNAL　SQLSTATE　'77701'　SET　MESSAGE_TEXT　='ONLY　1　OR　0　ARGUMENTS　VALUE　ACCEPTED';　
　　ELSE　RETURN　0;　
　　END　IF;　
END

请注意，我们通过在变量不为 1 或 0 时发出应用程序错误来限制变量值。

CREATE　FUNCTION　DB2ADMIN.BIT_OR(X　smallint,　Y　smallint)　
　　RETURNS　INTEGER　
------------------------------------------------------------------------　
--　SQL　UDF　(Scalar)　
------------------------------------------------------------------------　
BEGIN　ATOMIC　
　　IF　x　=0　AND　y　=　0　THEN　
　　　RETURN　0;　
　　ELSEIF　(x　<　0　or　x　>　1)　or　(y　<　0　or　y　>　1)　THEN　
　　　SIGNAL　SQLSTATE　'77701'　SET　MESSAGE_TEXT　='ONLY　1　OR　0　ARGUMENTS　VALUE　ACCEPTED';　
　　ELSE　RETURN　1;　
　　END　IF;　
END　　　　　　　　　
CREATE　FUNCTION　DB2ADMIN.EXCLUSIVE_OR(X　smallint,　Y　smallint)　
　　RETURNS　INTEGER　
------------------------------------------------------------------------　
--　SQL　UDF　(Scalar)　
------------------------------------------------------------------------　
F1:　BEGIN　ATOMIC　
　　IF　(x　<　0　or　x　>　1)　or　(y　<　0　or　y　>　1)　THEN　
　　　SIGNAL　SQLSTATE　'77701'　SET　MESSAGE_TEXT　='ONLY　1　OR　0　ARGUMENTS　VALUE　ACCEPTED';　
　　ELSEIF　(x　=　y)　THEN　
　　　　RETURN　0;　
　　ELSE　RETURN　1;　
　　END　IF;　
END　　　　　　　　　
CREATE　FUNCTION　DB2ADMIN.bit_not(x　smallint　)　
　　RETURNS　INTEGER　
------------------------------------------------------------------------　
--　SQL　UDF　(Scalar)　
------------------------------------------------------------------------　
F1:　BEGIN　ATOMIC　
　　IF　x　=　1　THEN　
　　　RETURN　0;　
　　　ELSEIF　(x　<　0　or　x　>　1)　THEN　
　　　　SIGNAL　SQLSTATE　'77701'　SET　MESSAGE_TEXT　='ONLY　1　OR　0　ARGUMENT　VALUE　ACCEPTED';　
　　ELSE　
　　　RETURN　1;　
　　END　IF;　
END

现在，通过 mytab 表定义，我们可以使用以上 UDF 将 T-SQL 语句转换成 DB2 。

T-SQL SQL 语句：

select　flag1&flag2　from　mytab　where　custname　=　'JOHN　SMITH'

将被转换成 DB2 为：

SELECT　bit_and(flag1,flag2)　FROM　mytab　where　custname　=　'JOHN　SMITH';

T-SQL SQL 语句：

select　flag1　|　flag2　from　mytab　where　custname　=　'SAM　BROWN'

将被转换成 DB2 为：

SELECT　bit_or(flag1,flag2)　where　mytab　where　custname　=　'SAM　BROWN';

模仿 Oracle 的布尔数据类型

让我们考虑下列需要转换成 DB2 UDB 的 PL/SQL 代码。

我们有一个 Oracle 表，如下所示：

　　　　create　table　myOracle_tab　
　　　　　(custname　varchar(30)　not　null,　
　　　　　　age　integer　not　null,　
　　　　　　flag1　BOOLEAN,　
　　　　　　flag2　BOOLEAN);

并且具有下列 PL/SQL SQL 语句，可以使用它们在列 flag1 和 flag2（均为 BOOLEAN 数据类型）上进行运算：

　　　select　flag1　AND　flag2　from　mytab　where　custname　=　'JOHN　SMITH';　
　　　select　flag1　OR　flag2　from　mytab　where　custname　=　'SAM　BROWN';

DB2 smallint 数据类型可用于转换 Oracle Boolean 数据类型。PL/SQL 支持下列用于布尔（Boolean）列的值 —— TRUE、FALSE 和 NULL。我们可以用 1 表示 TRUE，0 表示 FALSE，并允许该列为空。以下展示了可以如何转换 CREATE TABLE myOracle_tab 语句：

　　create　table　myOracle_tab　
　　(name　　char(20),　
　　boolcol　smallint　constraint　bool_cnst　check　(c2　in(0,1)));

bool_cnst 将确保所插入的值只能是 0 或 1。如果没有提供任何值，则该列将为 NULL。

PL/SQL 支持三种操作符 —— AND、OR 和 NOT，对布尔变量进行运算并返回布尔值。为了将该行为转换到 DB2 中，我们需要创建 UDF 来支持布尔逻辑运算。

x	y	x AND y	x OR y	NOT x
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	NULL	NULL	TRUE	FALSE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	NULL	FALSE	NULL	TRUE
NULL	TRUE	NULL	TRUE	NULL
NULL	FALSE	FALSE	NULL	NULL
NULL	NULL	NULL	NULL	NULL

下列 UDF 将实现以上运算：

　CREATE　FUNCTION　DB2ADMIN.bool_and(x　smallint,　y　smallint)　
　　RETURNS　SMALLINT　
------------------------------------------------------------------------　
--　SQL　UDF　(Scalar)　
------------------------------------------------------------------------　
F1:　BEGIN　ATOMIC　
　　IF　x　IS　NULL　OR　y　IS　NULL　THEN　
　　ELSEIF　x　=1　AND　y　=　1　THEN　
　　　RETURN　1　;　
　　ELSE　RETURN　0;　
　　END　IF;　
END　　　　　　
　CREATE　FUNCTION　DB2ADMIN.bool_NOT(x　smallint)　
　　RETURNS　SMALLINT　
------------------------------------------------------------------------　
--　SQL　UDF　(Scalar)　
------------------------------------------------------------------------　
BEGIN　ATOMIC　
　　IF　x　IS　NULL　THEN　
　　　　RETURN　NULL;　
　　ELSEIF　x=1　THEN　RETURN　0;　
　　ELSE　RETURN　1;　
　　END　IF;　　
END　　
CREATE　FUNCTION　DB2ADMIN.bool_or(x　smallint,　y　smallint)　
　　RETURNS　SMALLINT　
------------------------------------------------------------------------　
--　SQL　UDF　(Scalar)　
------------------------------------------------------------------------　
BEGIN　ATOMIC　
　　IF　x　=　1　THEN　RETURN　1;　
　　ELSEIF　x　=　0　THEN　
　　　　　RETURN　y;　
　　ELSEIF　y　=　1　THEN　RETURN　1;　
　　ELSE　RETURN　NULL;　
　　END　IF;　
END

Oracle SQL 语句：

select　flag1　AND　flag2　from　mytab　where　custname　=　'JOHN　SMITH'

将被转换成 DB2 为：

SELECT　bool_and(flag1,flag2)　FROM　mytab　where　custname　=　'JOHN　SMITH';　

Oracle SQL 语句：

select　flag1　OR　flag2　from　mytab　where　custname　=　'SAM　BROWN'

将被转换成 DB2 为：

SELECT　bool_or(flag1,flag2)　FROM　mytab　where　custname　=　'SAM　BROWN';　

用于整型变量的逐位操作

正如简介中提到的，PL/SQL 和 T-SQL 支持声明为 integer 的变量之间的逐位运算。逐位运算在其二进制形式的整型变量上执行逻辑 AND、OR、EXLUSIVE OR 和 NOT 运算。

让我们看一个它是如何工作的特定例子。假设我们需要在 110 和 85 这两个整数之间执行逻辑 AND 和逻辑 OR 运算。

首先，要将这两个数字转换成二进制数字，然后通过真值表对每一个位进行 AND 和 OR 运算，最后将二进制结果还原成整型数字。

整数　　　　　　　　　　　　　　　　　　　　　　　　　　　　　二进制形式　
　　　　
　　110　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1101110　
　　85　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1010101　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　-----------　　　　　逻辑　AND　
　　64　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1000100　
　
整数　　　　　　　　　　　　　　　　　　　　　　　　　　　　　二进制形式　
　　　　
　　110　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1101110　
　　85　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1010101　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　-----------　　　　　逻辑　OR　
　　127　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1111111

对于无法在头脑中轻易将整型数字转换成二进制数字的人们来说，下列将整型转换成二进制的 UDF 也许对他们很有帮助。

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

既然已经了解了逐位运算和变量转换的定义，那么我们现在就可以提供 DB2 UDF 来支持这些操作了。

为了将应用程序从 Oracle 转换成 DB2，Takashi Tokunaga 编写了一个函数来支持 BITAND，而且还提供了其他一些有用的迁移 UDF：http://www.ibm.com/developerworks/db2/library/samples/db2/0205udfs/index.html。

为了完整起见，下面还提供了该函数的代码：

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

现在，让我们从 CLP 提示符调用该函数：

C:\Program　Files\IBM\SQLLIB\BIN>db2　values　bitand(110,85)　
1　
-----------　
　　　　　68　
1　record(s)　selected.

为了支持从 Sybase 和 Microsoft SQL Server 进行迁移，需要具有那些用于 BITOR、EXLUSIVE OR 和 NOT 的 UDF。以下是 BITOR UDF：

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　　
　SET　temp1　=　bit_or(mod(m1,2),mod(m2,2));　
　SET　RetVal　=　RetVal　+　temp1*power(2,S);　
　SET　(M1,　M2,　S)　=　(M1/2,　M2/2,　S+1);　
END　WHILE;　　
RETURN　RetVal;　
END

注意，该函数使用了我们编写的 BIT_OR UDF 对类似于位数据类型的参数进行运算。

现在，让我们从 CLP 调用这个函数：

C:\Program　Files\IBM\SQLLIB\BIN>db2　values　bitor(110,85)　
1　
-----------　
　　　　127　
1　record(s)　selected.

下一个 UDF 在给定的两个整数值之间执行 EXLUSIVE OR 运算，这两个值已经被转换成二进制形式：

CREATE　FUNCTION　BIT_EXLOR　(N1　Integer,　N2　Integer)　
　RETURNS　Integer　
　LANGUAGE　SQL　
　SPECIFIC　BITOREXL　
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　
　　SET　temp1　=　EXCLUSIVE_OR(smallint(mod(m1,2)),smallint(mod(m2,2)));　
　　SET　RetVal　=　RetVal　+　temp1*power(2,S);　
　　SET　(M1,　M2,　S)　=　(M1/2,　M2/2,　S+1);　
END　WHILE;　
RETURN　RetVal;　
END

同样，该函数使用前面提供的函数 EXLUSIVE_OR，并且可以按如下方式执行：

C:\Program　Files\IBM\SQLLIB\BIN>db2　values　bit_exlor　(110,85)　
1　
-----------　
　　　　　59　
1　record(s)　selected.

为了验证该函数确实按照所设计的方式工作，我们需要再次将每个整型数字转换成二进制数字，对每一个位执行 EXLUSIVE OR 运算，然后将结果还原成 INTEGER：

　　整型　　　　　　　　　　　　　　　　　　　　　　　　　　　　二进制形式　
　　　　
　　110　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1101110　
　　　85　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1010101　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　-----------　　　　　Exclusive　OR　
　　　59　　　　　　　　　　　　　　　　　　　　　　　　　　　　　0111011

下一个也是最后一个涉及逐位运算的 UDF 是逐位 NOT 运算，在转换成二进制表达式时，该 UDF 对给定的整数值执行逐位逻辑 NOT 运算。

CREATE　FUNCTION　BITWISE_not　(N1　Integer)　
　RETURNS　Integer　
　LANGUAGE　SQL　
　SPECIFIC　BITWNOT　
BEGIN　ATOMIC　
DECLARE　M1,　S　,　temp1　Integer;　
DECLARE　RetVal　Integer　DEFAULT　0;　
SET　(M1,　S)　=　(N1,　0);　
WHILE　M1　>　0　AND　S　<　32　DO　
　SET　temp1　=　bit_not(mod(m1,2));　
　SET　RetVal　=　RetVal　+　temp1*power(2,S);　
　　SET　(M1,　　S)　=　(M1/2,　　S+1);　
END　WHILE;　　
RETURN　RetVal;　
END

下面展示了它是如何工作的：

C:\Program　Files\IBM\SQLLIB\BIN>db2　values　bitwise_NOT(110)　
1　
-----------　
　　　　　17　
1　record(s)　selected.　
　　　整型　　　　　　　　　　　　　　　　　　　　　　　　　　　　二进制形式　
　　
　　　110　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1101110　
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　-----------　　　　　逻辑　NOT　
　　　　17　　　　　　　　　　　　　　　　　　　　　　　　　　　　0010001

结束语

模仿位和布尔数据类型以及函数并非一定是一个挑战性的过程。使用本文中所谈论的 UDF 和触发器，您可以很轻松地将数据和应用程序迁移到 DB2 Universal Database 中。