动态SQL四种类型的语句格式_mssql学习_编程技术-你的首页-uuhomepage.com 1.Dynamic SQL Format 1 EXECUTE IMMEDIATE SQLStatement {USING TransactionObject} ; eg: string Mysql Mysql = "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 = 56 PREPARE 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, Sqlstatement integer Intvar Sqlstatement = "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 CHOOSE CLOSE my_cursor ;
|