PB提供了四种不同的动态sql方式,每种格式都有不同的特点和作用。
A、无输入参数且无返回结果集,如:建表(CREATE TABLE)、修改表(ALTER TABLE)、插入(INSERT )等等。
语法:EXECUTE IMMEDIATE SQLStatement{USING TransactionObject};
SQLStatement: sql语句
TransactionObject:事物对象(SQLCA)
例子:
String ls_sql = "INSERT INTO T_TEST VALUES(1,'张三') INSERT INTO T_TEST VALUES(2,'张三') "+&
"INSERT INTO T_TEST VALUES(3,'张三') INSERT INTO T_TEST VALUES(4,'李四') "+&
"INSERT INTO T_TEST VALUES(5,'李四') INSERT INTO T_TEST VALUES(6,'李四') "
CONNECT USING SQLCA;
EXECUTE IMMEDIATE 'if exists (select * from sysobjects where id = object_id(N~'[dbo].[T_TEST]~') and OBJECTPROPERTY(id, N~'IsUserTable~') = 1) drop table [dbo].[T_TEST] CREATE TABLE T_TEST(ID int,NAME varchar(50))' USING SQLCA;
EXECUTE IMMEDIATE :ls_sql USING SQLCA;
DISCONNECT USING SQLCA;
B、有输入参数但无返回结果集,如:删除(DELETE)
语法:PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject};
EXECUTE DynamicstagingArea{USING Parameterlist};
DynamicstagingArea是PB提供的一种数据类型,一般可用SQLSA这个全局变量
例子:Int li_id = 1
String ls_name = "张三"
CONNECT USING SQLCA;
PREPARE SQLSA FROM 'DELETE FROM T_TEST WHERE ID=? AND name =?';
EXECUTE SQLSA USING :li_id,:ls_name;
DISCONNECT USING SQLCA;
C、有输入参数且有返回结果集并且能确定结果集,此方式用游标实现
语法:DECLARE cursor DYNAMIC CURSOR FOR DynamicStagingArea;
PREPARE DynamicStagingArea FROM SQLStatement{USING TransactionObject};
OPEN DYNAMIC cursor {USING Parameterlist};
FETCH cursor INTO VariableList;
DO WHILE SQLCA.SQLCODE=0
//循环处理数据
FETCH cursor INTO VariableList;
LOOP
CLOSE cursor;
cursor:是用户所定义的游标的名字。
DynamicStagingArea:用默认全局变量SQLSA。
SQLStatement为sql语句
例子:
INT li_id,li_idd
String ls_sql,ls_name
ls_sql = "select id,name from t_test where name =?"
ls_name = "李四"
CONNECT USING SQLCA;
DECLARE cur DYNAMIC CURSOR FOR SQLSA;
PREPARE SQLSA FROM :ls_sql USING SQLCA;
OPEN DYNAMIC cur USING :ls_name;
FETCH cur INTO :li_id,:ls_name;
//添加处理
DO WHILE SQLCA.SQLCode =0
if li_id=5 then
update t_test
set name = '王五'
where id=:li_id;
end if
FETCH cur INTO :li_id,:ls_name;
LOOP
CLOSE cur;
DISCONNECT USING SQLCA;
第四种用的比较少,懒写的了