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 ;