动态SQL有4种类型:
·既无输入参数,也无结果集; 有输入参数,无结果集;
·编译时已经知道了参数和结果集的列;
·开发程序时尚不知道参数和结果集。
1.既无输入参数,也无结果集(类型1)
语法格式为:
execute immediate SQLSatement
{ using TransactionObject}
其中,参数解释如下:
SQLStatement 是个字符串,其内容是有效的SQL语句。
TransactionObject 是事务对象名,大括号表示该子句可以省略,默认时使用SQLCA。
这种类型的动态SQL通常用于执行数据操作语句(例如定义表、删除表等)以及某数据库特有的SQL语句,下面是一个创建表的示例。
String MysQL
MySQL="CREATE TABLE Employee" &=
+"(emp_id integer not null,"&
+"dept_id integer not null,"&
+"emp_fname char(10) not null,"&
+"emp+Iname char(20) not null)"
EXECUTE IMMEDIATE: MySQL USING SQLCA;
2.有输入参数,无结果集 (类型2)
第2种类型的动态SQL语句通常用于设计时已经知道SQL语句需要的参数个数、并且该SQL语句没有返回值的情况。这种类型的动态SQL语句也能处理在运行时定义参数的数据操作语句。其语法格式为:
prepare DynamicStagingArea from SQLStatement { using TransactionObject }
execute DynamicStagingArea using {ParameterList} ;
其中,参数解释如下:
DynamicStagingArea是个动态策略变量(类型为 DynamicStagingArea),通常使用 SQLSA. SQLStatement是个字符串(可以是常量,也可以是变量),其内容是有效的SQL语句,SQL语句中使用问号“?”代表所需参数。
TransactionObject是事务对象名,大括号表示该子句可以省略,省略时使用SQLCA。ParameterList是参数列表,各参数对应于SQLStatement中的问号。
动态策略区用于准备SQL 语句及所需参数个数,在运行时应用程序不能访问它的属性,SQLSA是默认的动态策略区变量。
下面是这类动态SQL 语句的应用示例,代码实现的功能是删除编号为56的雇员信息。
int Emp id var = 56
prepare SQLSA FROM "DELETE FROM employee WHERE emp_id +?";
EXECUTE SQLSA USING Emp_id_var;
3.编译时已经知道了参数和结果集的列 (类型3)
第3类动态SQL处理参数个数和结果集在编译时已知的情况,它的语法格式为:
declare Cursor dynamic cursor for DynamicStagingArea ;
prepare DynamicStagingArea from SQLSatement
(using TransactionObject };
open dynamic Cursor
{ using ParameterList };
fetch {Cursor into host VariableList};
close Cursor ;
其中,参数解释如下:
DynamicStagingArea是动态策略区变量,通常使用系统预定的全局变量SQLSA.
SQLStatement 是字符串(常量或变量均可,变量时变量名前面加上冒号“:”,其内容是有效的SQL语句,并使用问号代表参数)。
ParameterList对应于SQLStatement中问号的参数列表。
HostVariableList是 PowerScript主变量:(即在其前面加上冒号的PowerScript变量)。 TransactionObject是事务对象名,默认时使用SQLCA。
declare 语句声明动态游标或动态过程,prepare 准备动态策略区,open或execute 语句打开动态游标或执行动态过程,fetch语句读取一行数据,如果需要读取多行数据,那么需要反复fetch语句。最后,close语句关闭动态游标或动态过程。
下面是第3类动态SQL的一个例子,得到籍贯是“北京”的雇员。
declare my_cursor dynamic cursor for SQLSA;
int Emp_id_var
String Emp_state_var="北京"
String SqIstatement
Sqlstatament = "select emp_id from employee where emp_state ="?"
prepare SQLSA from :Sqlstatement;
open dynamic my_cursor using: Emp_state_var;
fetch my_cursor into :Emp_id_var;
close my_cursor;
4.开发程序时尚不知道参数和结果集(类型4 )
第4类动态SQL语句最复杂,功能也最强,它能够处理编程时尚不知道参数和结果集的SQL 语句,它的语法格式为:
declare Cursor
dynamic cursor
for DynamicStagingArea;
prepare DynamicStagingArea from SQLStatement
{using TransactionObject};
describe DynamicStagingArea
into DynamicDescriptionArea;
open dynamic Cursor
using descriptor DynamicDescriptionArea };
execute dynamic procedure
using descriptor DynamicDescriptionArea;
fetch Cursor
using descriptor DynamicDescriptionArea;
close Cursor;
其中,参数解释如下:
DynamicStagingArea是动态策略区变量,通常使用系统预定义的全局变量SQLSA。SQLStatement是个字符串(常量或变量均可,变量时变量名前面加上冒号(:) ),其内容是有效的 SQL 语句,并使用问号代表参数。
DynamicDescriptionArea是动态描述区变量,其类型为DynamicDescriptionArea,通常使用系统预定义的全局变量SQLDA。
TransactionObject是事务对象名,默认时使用SQLCA。
第4类动态SQL使用了动态描述区对象变量,通过该对象变量的4个属性 NumInputs、InParmType、NumOutputs、OutParmType能够得到输入参数个数、输入参数类型、输出参数个数和输出参数类型的信息。
InParmType是个数组,每个元素依次对应于SQL语句中的一个问号
OutParmType 也是个数组,每个元素依次对应于一个输出参数。
InParmType和 OutParmType 的数据类型是枚举类型ParmType,通过使用该对象变量的函数SetDynamicParm()设置具体的输入参数值,通过使用下表中的对象函数得到输出参数(实际上就是SQL语句的返回数据)的值,每个函数都针对特定的数据类型。
第4类动态SQL输出参数的函数
函数名 | |
GetDynamicNumber() | TypeInteger! TypeDecimal! TypeDouble! TypeLong! TypeReal! TypeBoolean! TypeUnsignedInteger! TypeUnsignedLong! |
GetDynamicString() | TypeString! |
GetDynamicDate() | TypeDate! |
GetDynamicTime() | TypeTime! |
GetDynamicDateTime() | TypeDateTime! |
在第4类动态SQL语句语法格式中,各语句的执行次序十分重要,只有在前一条语句执行成功时,后一条语句的执行才有意义,因此,除 DECLARE 语句外,其他语句执行后都应该检查事务对象的SQLCode属性,以判断当前SQL语句的执行是否成功。
通过多次调用 FETCH语句,能够读取多条数据,每读出一条数据后,通常在循环语句中使用CHOOSE CASE 确定输出参数的类型后,再用上表中的对象函数得到其值。
下面是第4类动态SQL的一个应用示例,其中省略了实际编程中必须具备的检查事务对象SQLCode属性的过程。
string Stringvar, Sqlstatement
int Intvar
Sqlstatement = "select emp id from employee
prepare SQLSA from :Sqlstatement ;
describe SQLSA into SQLDA ;
declare my_cursor dynamic cursor for SQLSA ;
open dynamic my_cursor using descriptor SQLDA ;
fetch my_ cursor using descriptor SQLDA;
//当fetch语句执行成功时,动态描述区SQLDA中包含了结果集的
//第一行数据,反复执行FETCH语句即可得到其余数据
// SQLDA.NumOutputs中包含了输出参数的个数
// SQLDA.OutParmType数组中包含了各参数的数据类型
//例如TypeInteger!、TypeString!等
//使用CHOOSE CASE语句针对不同的输出参數类型调用不同的对象函数
//得到相应参数的值
choose case SQLDA.OutParmType[1]
CASE TypeString!
Stringvar = GetDynamicString(SQLDA, 1)
CASE TypeInteger!
Intvar = GetDynamicNumber(SQLDA, 1)
end choose
close my_cursor ;