powerbuilder动态SQL语句

西门清水

//动态SQL语句的格式之一

//<<<<<<<<<<<<<<<<<<<<

EXECUTE IMMEDIATE SQLStatement

{USING TransactionObject};

//>>>>>>>>>>>>>>>>>>>>>>>>

string sql
int nn
sql='SELECT "tb_student"."s_name"  &
    FROM "tb_student"  &
   WHERE "tb_student"."s_id" = 10'  
sql='SELECT "tb_student"."s_name"  &
    FROM "tb_student"  &
   WHERE "tb_student"."s_id" = 100'
EXECUTE IMMEDIATE :sql using sqlca;
nn=sqlca.sqlcode
messagebox('',string(nn))

//动态SQL语句的格式之二

//<<<<<<<<<<<<<<<<<<<<

PREPARE DynamicStagingArea FROM SQLStatement

{USING TransactionObject};

EXECUTE DynamicStagingArea USING {ParameterList};

//>>>>>>>>>>>>>>>>>>>>>>>>

string sql
int n
  sql='SELEeCT "tb_student"."s_name"  &
    FROM "tb_student"  &
   WHERE "tb_student"."s_id" = ? '
PREPARE sqlsa FROM :sql
USING sqlca;
EXECUTE sqlsa USING :n;
messagebox('',string(sqlca.sqlcode))

//动态SQL语句的格式之三

//<<<<<<<<<<<<<<<<<<<<

DECLARE CursorName | ProcedureName

DYNAMIC CURSOR | PROCEDURE

FOR DynamicStagingArea;

PREPARE DynamicStagingArea FROM SQLStatement

{USING TransactionObject};

OPEN DYNAMIC

{USING ParameterList};

EXECUTE DYNAMIC ProcedureName

{USING ParameterList};

FETCH CursorName | ProcedureName

INTO HostVariableList;

CLOSE CursorName | ProcedureName

//>>>>>>>>>>>>>>>>>>>>>>>>

string sql,nm
int id1,id2
id1=100
id2=101
boolean b
b=true
  sql='SELECT "tb_student"."s_name"  &
    FROM "tb_student"  &
   WHERE ( "tb_student"."s_id" = ? ) OR  &
         (  "tb_student"."s_id" = ? )  '
DECLARE cs1 DYNAMIC CURSOR FOR SQLSA;
PREPARE SQLSA FROM :sql;
OPEN DYNAMIC cs1 USING :id1,:id2;
do while b=true
fetch cs1 into :nm;
	if sqlca.sqlcode <> 0 then
		b=false
		messagebox('',nm)
	end if
close cs1;

//动态SQL语句的格式之四

//<<<<<<<<<<<<<<<<<<<<

DECLARE CursorName | ProcedureName

DYNAMIC CURSOR | PROCEDURE

FOR DynamicStagingArea;

PREPARE DynamicStagingArea FROM SQLStatement

{USING TransactionObject};

DESCRIBE DynamicStagingArea INTO DynamicDescriptionArea

OPEN DYNAMIC CursorName | ProcedureName USING DESCRIPTOR DynamicDescriptionArea;

EXECUTE DYNAMIC CursorName | ProcedureName USING DESCRIPTOR DynamicDescriptionArea;

FETCH CursorName | ProcedureName USING DESCRIPTOR DynamicDescriptionArea;

CLOSE CursorName | ProcedureName

实例1：

//>>>>>>>>>>>>>>>>>>>>>>>>

string sql
string name,sex
int age
sql= ' SELECT "tb_student"."s_name",   &
         "tb_student"."s_sex",   &
         "tb_student"."s_age"  &
    FROM "tb_student"  &
   WHERE "tb_student"."s_id" in ( ?,? )   '
DECLARE cs1 DYNAMIC CURSOR FOR sqlsa;//SQLSA
PREPARE SQLSA FROM :sql	
USING sqlca;
DESCRIBE sqlsa INTO sqlda;
setdynamicparm(sqlda,1,100)
setdynamicparm(sqlda,2,101)
OPEN DYNAMIC cs1 USING DESCRIPTOR sqlda;
FETCH cs1 USING DESCRIPTOR sqlda;
name=getdynamicstring(sqlda,1)
sex=getdynamicstring(sqlda,2)
age=getdynamicnumber(sqlda,3)
if sqlca.sqlcode=0 then
	messagebox('',name+sex+string(age))
end if
loop while sqlca.sqlcode=0
CLOSE cs1;
实例2：

string sql
string name,sex
int age
string s
s='马'
sql='  SELECT "tb_student"."s_name",   &
         "tb_student"."s_sex",   &
         "tb_student"."s_age"  &
    FROM "tb_student"  &
   WHERE "tb_student"."s_name" like ?   ' 
DECLARE cs1 DYNAMIC CURSOR FOR sqlsa;//SQLSA
PREPARE SQLSA FROM :sql	
USING sqlca;
DESCRIBE sqlsa INTO sqlda;
setdynamicparm(sqlda,1,s+'%')
//setdynamicparm(sqlda,2,101)
OPEN DYNAMIC cs1 USING DESCRIPTOR sqlda;
FETCH cs1 USING DESCRIPTOR sqlda;
name=getdynamicstring(sqlda,1)
sex=getdynamicstring(sqlda,2)
age=getdynamicnumber(sqlda,3)
if sqlca.sqlcode=0 then