动态SQL四种类型的语句格式_mssql学习_编程技术-你的首页-uuhomepage.com1.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:INT Emp_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 ;