动态SQL四种类型的语句格式

文章作者 100test 发表时间 2007:03:10 18:27:01
来源 100Test.Com百考试题网

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 .