動態SQL四種類型的語句格式
2024-07-21 02:11:02
供稿：網友
，歡迎訪問網頁設計愛好者web開發。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 ;
