《2022年动态SQL四种类型的语句格式SQL server]教程.docx》由会员分享，可在线阅读，更多相关《2022年动态SQL四种类型的语句格式SQL server]教程.docx（5页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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 ;