動態SQL四種類型的語句格式

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 USIN