Four types of dynamic SQL statements

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