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

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.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 Cursor | ProcedureDYNAMIC CURSOR | PROCEDUREFOR DynamicStagingArea ;PREPARE DynamicStagingArea FROM SQLStatement{USING TransactionObject} ;OPEN DYNAMIC Cursor{USING ParameterList} ;EXECUTE DYNAMIC Procedure{USING ParameterList} ;FETCH Cursor | ProcedureINTO 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 | ProcedureDYNAMIC CURSOR | PROCEDUREFOR DynamicStagingArea ;PREPARE DynamicStagingArea FROM SQLStatement{USING TransactionObject} ;DESCRIBE DynamicStagingAreaINTO DynamicDescriptionArea ;OPEN DYNAMIC Cursor | ProcedureUSING DESCRIPTOR DynamicDescriptionArea ;EXECUTE DYNAMIC Cursor | ProcedureUSING DESCRIPTOR DynamicDescriptionArea ;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 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.OutParmType1CASE TypeString!Stringvar = GetDynamicString(SQLDA, 1)CASE TypeInteger!Intvar = GetDynamicNumber(SQLDA, 1)END CHOOSECLOSE my_cursor ;