|
--create test table create table AAA_TEST (aa varchar2(100),bb varchar2(100),cc varchar2(50)); --簡(jiǎn)單的動(dòng)態(tài)執(zhí)行 begin execute immediate 'insert into table(field1) values(value)'; end; --代參數(shù)和返回的動(dòng)態(tài)執(zhí)行 declare v_str varchar2(100); v_sql varchar2(500); v_out varchar2(100); begin v_str := '111'; v_sql := 'update aaa_test set aa=''ttt'' where aa=:1 returning aa into :2'; execute immediate v_sql using v_str returning into v_out; end; --使用open-for,fetch和close聲明 declare type cursorType is ref cursor; mycursor cursorType; v_sql varchar2(100); v_aa varchar2(50); v_bb varchar2(100); begin v_sql := 'select bb from aaa_test where aa like :1'; v_aa := '11%'; open mycursor for v_sql using v_aa; loop fetch mycursor into v_bb; exit when mycursor%notfound; end loop; close mycursor; end; --Using Bulking Dynamic Sql(提高性能,減少上下文等待時(shí)間) declare type cursorType is ref cursor; mycursor cursorType; type numList is table of varchar2(100); bbs numList; v_sql varchar2(500); begin v_sql := 'select bb from aaa_test'; open mycursor for v_sql; fetch mycursor bulk collect into bbs; close mycursor; execute immediate v_sql bulk collect into bbs; end; --第二種參數(shù)方式的動(dòng)態(tài)Sql性能更優(yōu) execute immediate 'delete from table1 where empno =' || v_str; execute immediate 'delete from table1 where empno = :1' --但參數(shù)綁定方式對(duì)表,視圖等對(duì)象不適用,因此下面報(bào)錯(cuò) execute immediate 'drop table :tab' using v_tablename |
|
|
