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

动态SQL四种类型的语句格式1.Dynamic SQL Format 1 EXECUTE 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 2 PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject} ;EXECUTE DynamicStagingArea USING {ParameterList} ; eg:INT Emp_id_var = 56PREPARE SQLSA FROM DELETE FROM employee WHERE emp_id=? ;EXECUTE SQLSA USING :Emp_id_var ; 3.Dynamic SQL Format 3 DECLARE 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_var DECLARE 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 4 DECLARE 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 employeePREPARE 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