OTL翻译(5) -- otl_stream流相关绑定变量

声明绑定变量

本章节将详细的说明如何在otl_stream流里面声明绑定变量。

SQL语句、SQL语句块或存储过程在程序里面使用的时候总是带有占位符。OTL里面带有一个小的解析器用来解析这些占位符，并且在内部进行变量的内存分配操作。

在ORACLE里面占位符的表示方法与其他数据库不同，在ORACLE里面的占位符是通过带有冒号的前缀来表示的，如：:f1/:supervisor_name/:employee_id等，并且同一个占位符可能在同一个SQL语句里面使用多次。

在ODBC或DB2 CLI里面，占位符则是通过问号来表示的。如：

INSERT INTO TABLE_NAEM VALUES(?,?,?,?).

在OTL　2.0/ODBC版本里面仍然采用了位置名的占位符。如：

INSERT INTO TABLE_NAME(:1<INT>,:2<INT>,:3<INT>,:4<INT>);

在这里，数字被转换为问号。

在OTL4.0里面仍然支持上面的数字占位符。但是4.0版本里面还有名字占位符，因此推荐两种都可以使用。但是有一点不同的是，在4.0的名字占位符使用的时候，对于同一个SQL语句，里面的占位符不能同名使用。

ORACLE传统的名字占位符，后面都会扩展带有字段的类型信息，如：

INSERT INTO TABLE_NAME VALUES(:EMP_ID<INT>);

这样一次性把占位符信息定义完全，程序里面就不用再去声明宿主变量和单独调用绑定函数。对于标量的变量来说，容纳一行足够了。但是在OTL里面，它把占位符进行了扩展。下面的类型就是对扩展的占位符相对应的表：

序号

类型

OTL扩展类型

bigint

64有符号整型。它被用在MS SQL SERVER/DB2/MYSQL/POSTGERSQL等数据库上，表示这些数据库上原BIGING类型。ODBC和DB2 CLI是在原生API基础上已经支持了64位的BIGINT类型，所以OTL也是如此。

但对于32位的OCIx来讲，它们并不支持64位长整型，像这种情况下，OTL提供了OTL_BIGINT,OTL_BIGINT_TO_STRING,OTL_STRING_TO_BIGINT宏来帮助在BIGINT与STRING类型之间作转换。它把BIGINT转换为VHAR[]类型来存储。64位的OCI在64位的编译平台下面，是支持BIGINT的，但它需要定义OTL_ORA_MAP_BIGINT_TO_LONG宏来支持。

OCI11.2版本里面开始支持BIGINT，所以只需要定义OTL_BIGINT和OTL_ORA11G_R2就可以啦。

blob

针对ORACLE的8、9、10、11版本里面的BLOB类型。

char[length]

otl 4.0.118及以上版本

char(length)

以NULL结尾的字符串类型。

最大长度取决于数据库。对ORACLE来说是332545,对ODBC来说它取决于数据库与ODBC驱动，对DB2 CLI来说大于2.在定义了OTL_UNICODE宏的情况下，它是双字节的以NULL结尾的字符串。实际上使用的时候会把最后一个字符写为结尾。如想存储数据库里面VARCHAR(10)长度的字符串的时候，必须定义为11位（或12位UNICODE）。

对于未定义长度的CHAR类型，OTL会抛出一个异常信息。如下：

     "INSERT INTO test_tab VALUES(:f1<int>,:f2<char>)"
     Error code: 32013
     Error message: Invalid bind variable declaration
     var info: f2 char

charz

在ORACLE7、8、8I、9I、10G的时候等同于CHAR[]。仅被用于数据库里类型为CHAR[]的时候。charz实际上是用来解决“PLS-00418:array bind type must match PL/SQL table row type”错误的。在OCI内部对VARCHAR2/CHAR都能匹配的很好，只是对CHAR[]必须通过CHARZ来支持。

clob

对ORACLE8、9里面的CLOB/NCLOB

db2date

对应DB2的DATE类型。它仅用于DB2数据库的DATE类型占位符。

db2time

对应DB2的TIME类型。它仅用于DB2 CLI或DB2 ODBC时候TIME的占位符。同时它需要otl_datetime类的支持。详细见例：91.

double

8字节长度的浮点数

float

4字节长度的浮点数

int

32位有符号整型

long

对OCIx：在32位平台或LLP64位的WINDOWS平台上为32位有符号的整型。在LP64位的64位编译器平台上（XNUX）平台上为64位有符号整型。

对ODBC：32位平台上为32位有符号整型。对某些ODBC驱动下是64位有符号整型。对于标准的ODBC驱动来说，SQL_C_LONG是32位的，但某些驱动把它定义为64位。如果你想把它关联为64位，你需要定义OTL_MAP_LONG_TO_SQL_C_SBIGINT宏。

对DB2 CLI：所有平台都为32位有符号整型。如果你想把它关联到64位，你需要定义OTL_MAP_LONG_TO_SQL_C_SBIGIN宏。

LONG类型在不同的平台（WIN或LINUX）平台上，或是不同的数据库之间并不是通用的。但是如果你的代码只是在同一个平台或同一个数据库下面，LONG还是可用的。

ltz_timestamp

对ORACLE　9i里面的带有本地时区的TIMESTAMP类型。必须定义OTL_ORA_TIMESTAMP宏，使用otl_datetime类。

nchar[length]

仅在oracle 8/9/10g，在定义了OTL_UNICODE或OTL_ORA_UTF8宏的情况下，在设置了otl_connect::set_character_set(SQLCS_NCHAR)的时候等同于char[]。

仅用于在同一个SQL语句里面同时使用了VARCHAR2/CHAR和NVARCHAR2/NCHAR的时候使用。

nclob

仅在oracle 8/9/10g，在定义了OTL_UNICODE或OTL_ORA_UTF8宏的情况下，在设置了otl_connect::set_character_set(SQLCS_NCHAR)的时候等同于clob。

仅用于在同一个SQL语句里面同时使用了CLOB和NCLOB的时候使用。

raw[length]

ORACLE7/8/9/10里的raw/long raw；

ODBC里的SQL_BINARY(BINARY 在MS SQL/SYBASE 15/MYSQL;CHAR(XXX)BYTE在SAP/MAX DB)；SQL_VARBINARY(VARBINARY在MS SQL/SYBASE 15/MYSQL；BYTEA在POSTGRESQL，VARCHAR(XXX)BYTE在SAP/MAX DB)。对于这种类型的字段还可以通过otl_long_string来读取。具体的长度取决于具体的数据库。

其他可以见宏OTL_MAP_SQL_VARBINARY_TO_RAW_LONG/OTL_MAP_SQL_GUID_TO_CHAR/OTL_MAP_SQL_BINARY_TO_CHAR

raw_long

ORACLE　7、8、9、10、11：RAW LONGRAW；

ODBC:SQL_LONGVARBINARY,SQL_VARBINARY

DB2 CLI:BLOB

refcur

对ORACLE的8、9、10、11来说，当一个存储过程返回一个游标的时候，一个游标的占位符必须定义在SQL块中。示例如下:

              "begin "
              " my_pkg.my_proc(:f1<int,in>,:f2<int,in>, "
              "         :str1<char[100],out>, "
      // :str1 is an output string parameter
              "         :cur1<refcur,out[50]>, "
              "         :cur2<refcur,out[50]>); "
     // :cur1, :cur2 are a bind variable names, refcur -- their types, 
     // out -- output parameter, 50 -- the buffer size when this
     // reference cursor will be attached to otl_refcur_stream              "end;"

short

16位有符号整型

timestamp

序号	数据库	类型
1	MS SQL SERVER/SYBASE	DATETIME
2	DB2	TIMESTAMP
3	ORACLE	DATE
4	ORACLE 9I	TIMESTAMP(OTL_ORA_TIMESTAMP)

它需要otl_datetime类协助处理。

tz_timestamp

对ORACLE　9i里面的带有时区的TIMESTAMP类型。必须定义OTL_ORA_TIMESTAMP宏，使用otl_datetime类。

unsigned

32位无符号整型

varchar_long

ORACLE 7/8/9里的LONG；

ODBC里的SQL_LONGVARCHAR；

DB2里的CLOB

varchar_long,raw_long clob和blob需要otl_long_string类作为数据容器。otl_connect类里的set_max_long_size()函数能设置最大长度。详细请见otl_connect类。

下面三个词用来说明对SQL语句或SQL语句块或存储过程里面表明变量的输入与输出属性。

in – input variable

out – output variable

inout – in/out variable

例　1　（ORACLE）

 BEGIN
   :rc<int,out> := my_func(:salary<float,in>,  
                           :ID<int,inout>, 
                           :name<char[32],out>
                          );
 END;

例　2（ODBC 或DB2 CLI）

New (OTL 4.0/ODBC, OTL 4.0/DB2-CLI) style

   {
    call :rc<int,out> := my_func(:salary<float,in>,
                                 :ID<int,inout>,
                                 :name<char[32],out>
                                )
   }

Old (OTL 2.0/ODBC) style:

   {
    call :1<int,out> := my_func(:2<float,in>,
                                :3<int,inout>,
                                :4<char[32],out>
                               )
   }

在绑定变量的时候，括号与类型之间是不能有空格的。如果有空格会报错。例如以下两个例子会报错：

例1：

insert into tab1 values(:salary< double >, :name< char [ 32 ] > , :emp_id< int>);

例2：

:rc< int, out > := ...;

在SQL语句中嵌入冒号

infomix的CLI能够组合OTL/ODBC实现。informix的SQL语句里面能够把冒号当作SQL语句的一部分，因此为了增加对冒号当作SQL语句的一部分的支持，OTL里面使用“\:”来转义冒号。

在SELECT输出中明确绑定变量

OTL在解析了SELECT语句以后，就动态的识别字段的名字、类型和长度。OTL把这些数据类型与C＋＋的数据类型关联的时候采用的是默认的关联方式。我们可以通过调用otl_stream::set_column_type()和otl_stream::set_all_column_type()函数来更改这种默认的类型关联。不过相比在SQL语句中明确绑定变量的类型来说，它并不是很准确。在SELECT语句中明确绑定变量格式如：:#N<datatype>。这里的N指的是SELECT的时候字段的相对位置。因为数据库并不能解析这种格式的绑定变量，所以在SQL语句传给数据库API前，必须把这种格式的绑定变量处理掉。如：

SELECT F1:#1<SHORT>,F2 FROM TABLE；

处理后的结果如下：

SELECT F1,F2 FROM TABLE;

这种情况下的数据类型仅支持如下：

char<xxx>/double/float/int/bigint/unsigned/short/long/raw<xxx>/raw_long/timestamp/varchar_long

当然实际代码中对以上数据类型的支持还得看具体的数据库，详细的信息请参考具体数据库手册或者更方便的直接看代码有无报错。

OTL里面默认的数据类型对绝大多数的程序来说都是可用的，只有少数的情况会出错需要人工明确类型。

在4.0.117及以上版本里面，对于ORACLE的游标和ODBC/DB2 CLI的结果集，以上的明确绑定变量同样也适用。

明确输出变量替代默认方案是可选的，如果明确后没有覆盖，则还是会使用原来默认的类型关联。

1.1.4. 声明PLSQL的表

对于OCI从OTL3.0版本开始，OTL开始支持otl_stream里面带有表，通过PL/SQL表容器实现。这个特性仅是针对SQL块或存储过程。例如，在一个存储过程里面，表名作为参数，被作为一个SQL块调用的时候，OTL能够写入或读取流里面的表名。这个技术在OCI或PROC里面是一个比较常用的，只是用接口实现稍有复杂。

在OTL4.0及以后版本里面，表名可以作为输入/输出/输入输出参数。例如：

BEGIN
   my_pkg.my_proc(:salary<float,in[100]>,
                  :ID<int,inout[200]>,
                  :name<char[32],out[150]>
                 );
END;

上面的示例中，salary是一个最大为100位的输入参数，ID是最大为200位的输入输出参数，name是150位的输出参数。

因为存储过程是不能直接放置在代码里面的，所以上面的示例中的otl_stream大小必须设置为1。但是上面的参数并不是标量，而是预先定义好了的矢量。

从OTL4.0.115及以后版本，表的最大长度已经不再局限于32767。但是为了限制表的最大长度，仍可以通过定义OTL_STREAM_LEGACY_BUFFER_SIZE_TYPE宏来实现。

更详细的实现请见例：49/50/51/52

为ORACLE声明绑定变量的替代方法

OTL4.0.209及以上版本可以通过替代的绑定变量来同时支持ORACLE的SQL工具和OTL。这里“同时支持”意思指的是同一个SQL语句即可以通过OTL来调用，也可以通过SQL工具运行，所有的OTL的绑定变量这里都被定义为注释。如下：

INSERT INTO TABLE VALUES ( :F1,:F2);

一般都会把原来的绑定占位符进行如上例中的替换方法进行替换（变量与括号之间仍不能有空格）。这规则的目的是为了能为OTL提供一个轻量级的SQL解析器。对于SELECT语句，该功能也可以支持，只不过在SELECT里面是把空格换成注释。如下：

原来的：SELECT F1, #1<INT>,F2 :#2<INT> FROM TABLE

替换后的：SELECT F2,,F2 FROM TABLE;

上面这样替换后的SQL语句就可以直接传送给SQL工具执行啦，并且还支持“EXPLAIN PLAN”分析。