动态SQL四种类型的语句格式
浏览:58日期:2023-05-05
1.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:INTEmp_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 ;
相关文章:
热门推荐

- 把jar包变成docker容器的方法
- 1.ASP+全新接触(2)
- 2.SYBASE数据库的事务管理
- 3.table自动拉伸在chrome与IE中的兼容性问题解决
- 4.ie6,ie7,ie8完美支持position:fixed的终极解决方案
- 5.原生js方法document.getElementsByClassName在ie8及其以下的兼容性问题
- 6.IE6,IE7,IE8 css bug搜集及浏览器兼容性问题解决方法汇总
- 7.Golang定时器的2种实现方法与区别
- 8.IE中关于使用innerHTML加入HTML代码的问题
- 9.解决Vue+Element ui开发中碰到的IE问题
- 10.粗上加粗的IE10字体宽到超出原本的容器
