静态SQL四种类型的语句格式
日期：2013-06-15 浏览次数：20519 次
1.Dynamic SQL Format 1
EXECUTE IMMEDIATE SQLStatement {USING TransactionObject} ;
eg:
string Mysql
Mysql = "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 2
PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject} ;
EXECUTE DynamicStagingArea USING {ParameterList} ;
eg:
INT Emp_id_var = 56
PREPARE SQLSA
FROM "DELETE FROM employee WHERE emp_id=?" ;
EXECUTE SQLSA USING :Emp_id_var ;
3.Dynamic SQL Format 3
DECLARE 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_var
DECLARE 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 4
DECLARE 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, Sqlstatement
integer Intvar
Sqlstatement = "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 CHOOSE
CLOSE my_cursor ;
EXECUTE IMMEDIATE SQLStatement {USING TransactionObject} ;
eg:
string Mysql
Mysql = "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 2
PREPARE DynamicStagingArea FROM SQLStatement {USING TransactionObject} ;
EXECUTE DynamicStagingArea USING {ParameterList} ;
eg:
INT Emp_id_var = 56
PREPARE SQLSA
FROM "DELETE FROM employee WHERE emp_id=?" ;
EXECUTE SQLSA USING :Emp_id_var ;
3.Dynamic SQL Format 3
DECLARE 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_var
DECLARE 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 4
DECLARE 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, Sqlstatement
integer Intvar
Sqlstatement = "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 CHOOSE
CLOSE my_cursor ;
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
