| « | September 2025 | » | | 日 | 一 | 二 | 三 | 四 | 五 | 六 | | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | | | | | |
|
公告 |
| 暂无公告... |
| Blog信息 |
|
blog名称:南海大狼的blogger
日志总数:41
评论数量:34
留言数量:-7
访问次数:270680
建立时间:2005年8月15日 |
| |
|
 [oracle]oracle 动态语句
文章收藏, 软件技术
南海大狼 发表于 2006/12/14 16:29:06 |
| 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:INT Emp_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 ; |
|
|