动态SQL四种类型的语句格式

1.Dynamic SQL Format 1EXECUTE IMMEDIATE SQLStatement;;;;;{USING TransactionObject} ;eg:string;;;;MysqlMysql = "CREATE TABLE Employee "&;;;;+"(emp_id integer not null,"&;;;;+"dept_id integer not null, "&;;;;+"emp_fname char(10) not null, "&;;;;+"emp_lname char(20) not null)"EXECUTE IMMEDIATE :Mysql ;2.Dynamic SQL Format 2PREPARE DynamicStagingArea FROM SQLStatement;;;;;{USING TransactionObject} ;EXECUTE DynamicStagingArea USING {ParameterList} ;eg:INTEmp_id_var = 56PREPARE SQLSA ;;;;FROM "DELETE FROM employee WHERE emp_id=?" ;EXECUTE SQLSA USING :Emp_id_var ;3.Dynamic SQL Format 3DECLARE Cursor | Procedure;;;;;DYNAMIC CURSOR | PROCEDURE;;;;;;FOR DynamicStagingArea ;PREPARE DynamicStagingArea FROM SQLStatement;;;;;{USING TransactionObject} ;OPEN DYNAMIC Cursor;;;;;{USING ParameterList} ;EXECUTE DYNAMIC Procedure;;;;{USING ParameterList} ;FETCH Cursor | Procedure;;;;;INTO HostVariableList ;CLOSE Cursor | Procedure ;eg:integer Emp_id_varDECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;PREPARE SQLSA FROM "SELECT emp_id FROM employee" ;OPEN DYNAMIC my_cursor ;FETCH my_cursor INTO :Emp_id_var ;CLOSE my_cursor ;4.Dynamic SQL Format 4DECLARE Cursor | Procedure;;;;;DYNAMIC CURSOR | PROCEDURE;;;;;;FOR DynamicStagingArea ;PREPARE DynamicStagingArea FROM SQLStatement;;;;{USING TransactionObject} ;DESCRIBE DynamicStagingArea;;;;INTO DynamicDescriptionArea ;OPEN DYNAMIC Cursor | Procedure;;;;USING DESCRIPTOR DynamicDescriptionArea ;EXECUTE DYNAMIC Cursor | Procedure;;;;USING DESCRIPTOR DynamicDescriptionArea ;FETCH Cursor | Procedure;;;;;USING DESCRIPTOR DynamicDescriptionArea ;CLOSE Cursor | Procedure ;eg:string Stringvar, Sqlstatementinteger IntvarSqlstatement = "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 ;// If the FETCH is successful, the output // descriptor array will contain returned // values from the first row of the result set.// SQLDA.NumOutputs contains the number of // output descriptors.// The SQLDA.OutParmType array will contain // NumOutput entries and each entry will contain// an value of the enumerated data type ParmType// (such as TypeInteger!, or TypeString!).CHOOSE CASE SQLDA.OutParmType[1]CASE TypeString!Stringvar = GetDynamicString(SQLDA, 1);;;;CASE TypeInteger!Intvar = GetDynamicNumber(SQLDA, 1)END CHOOSECLOSE my_cursor ;