利用 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，实现下列位操作真值表。

以下就是这组 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 来支持布尔逻辑运算。