2022年动态SQL四种类型的语句格式SQL server]教程.docx

《2022年动态SQL四种类型的语句格式SQL server]教程.docx》由会员分享，可在线阅读，更多相关《2022年动态SQL四种类型的语句格式SQL server]教程.docx（5页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、2022年动态SQL四种类型的语句格式SQL server教程1.Dynamic SQL Format 1EXECUTE IMMEDIATE SQLStatement{USING TransactionObject} ;eg:stringMysqlMysql = 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

2、.Dynamic SQL Format 2PREPARE DynamicStagingArea FROM SQLStatement{USING TransactionObject} ;EXECUTE DynamicStagingArea USING {ParameterList} ;eg:INTEmp_id_var = 56PREPARE SQLSAFROM DELETE FROM employee WHERE emp_id=? ;EXECUTE SQLSA USING :Emp_id_var ;3.Dynamic SQL Format 3DECLARE

3、 Cursor | ProcedureDYNAMIC CURSOR | PROCEDUREFOR DynamicStagingArea ;PREPARE DynamicStagingArea FROM SQLStatement{USING TransactionObject} ;OPEN DYNAMIC Cursor{USING ParameterList} ;EXECUTE DYNAMIC Procedure{USING ParameterList} ;FETCH Cursor | ProcedureINTO HostVariabl

4、eList ;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 | ProcedureDYNAMIC CURSOR | PROCEDUREFOR D

5、ynamicStagingArea ;PREPARE DynamicStagingArea FROM SQLStatement{USING TransactionObject} ;DESCRIBE DynamicStagingAreaINTO DynamicDescriptionArea ;OPEN DYNAMIC Cursor | ProcedureUSING DESCRIPTOR DynamicDescriptionArea ;EXECUTE DYNAMIC Cursor | ProcedureUSING DESCRIPTOR DynamicDescriptionAre

6、a ;FETCH Cursor | ProcedureUSING 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

7、 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 co

8、ntain/ NumOutput entries and each entry will contain/ an value of the enumerated data type ParmType/ (such as TypeInteger!, or TypeString!).CHOOSE CASE SQLDA.OutParmType1CASE TypeString!Stringvar = GetDynamicString(SQLDA, 1)CASE TypeInteger!Intvar = GetDynamicNumber(SQLDA, 1)END CHOOSECLOSE my_cursor ;