動態SQL運用實例

動態sql運用實例

語法
8.1.6之前:

execute immediate dynamic_sql_string
[into {define_var1 [, define_var2] ... | plsql_record}]
[using [in | out | in out] bind_arg1 [,
[in | out | in out] bind_arg2] ...];

8.1.6 開始包括 "returning into"

execute immediate dynamic_sql_string
[into {define_var1 [, define_var2] ... | plsql_record}]
[using [in | out | in out] bind_arg1 [,
[in | out | in out] bind_arg2] ...]
[{returning | return} into bind_arg3 [, bind_arg4] ...];

1.執行 ddl:
declare
str varchar2(200);
begin
str := 'create table dy_sql (dno number,dtext varchar2(120))';
execute immediate str;
end;
/
2.執行非查詢dml:
declare
str varchar2(200);
begin
str := 'insert into dy_sql values (1,''hello'')';
execute immediate str;
end;
/

3.執行非查詢dml，並使用變數 :
declare
str varchar2(200);
val varchar2(20);
begin
str := 'insert into dy_sql values (2,:b1)';
val := 'sql';
execute immediate str using val;
commit;
end;
/

4.執行查詢單一變數,單一筆回傳
set serverout on
declare
str varchar2(200);
val varchar2(20);
ret varchar2(20);
begin
str := 'select dtext from dy_sql where dtext = :b1';
val := 'sql';
execute immediate str into ret using val;
dbms_output.put_line('value fetched from table: '||ret);
end;
/

5.使用pl/sql record type
declare
str varchar2(200);
val varchar2(20);
ret dy_sql%rowtype;
begin
str := 'select dno,dtext from dy_sql where dno = :b1';
val := 2;
execute immediate str into ret using val;
dbms_output.put_line('value fetched number:'||ret.dno||' ; name:'||ret.dtext);
end;
/

6.returning、out變數運用
declare
str varchar2(200);
val varchar2(20);
ret varchar2(20);
begin
val := 1;
str := 'delete from dy_sql where dno = :b1 returning dtext into :b2';
execute immediate str using val, out ret;
dbms_output.put_line('deleted '||sql%rowcount||' row(s) with value: '||ret);
end;
/

7.動態創建function,並使用它,再將function 移除
declare
str varchar2(200);
val number;
ret number;
begin
begin
str := 'create or replace function doubleit (p1 in number)'||
' return number as begin return p1*2; end;';
execute immediate str;
end;
-- call the stored function
str := 'begin :b1 := doubleit(:b2); end;';
val := 30;
execute immediate str using out ret, in val;
dbms_output.put_line('result of '||val||' doubled is '||ret);
execute immediate 'drop function doubleit';
end;

/

8.利用動態sql暫時使用index
declare
str varchar2(200);
val varchar2(120);
ret number;
ret2 dy_sql.dtext%type;
begin
str := 'create index i_dy_sql_1 on dy_sql(dtext)';
execute immediate str;
str := 'select dno,dtext from dy_sql where dtext = :b1';
val := 'sql';
execute immediate str into ret,ret2 using val ;
dbms_output.put_line('result of dtext='||val||':dno is '||ret||',dtext is '||ret2);
str := 'drop index i_dy_sql_1 ';
execute immediate str;
end;
/

9.宣告 ref cursor
declare
type my_curs_type is ref cursor;
curs my_curs_type;
str varchar2(200);
ret varchar2(20);
begin
str := 'select dtext from dy_sql';
open curs for str;
loop
fetch curs into ret;
exit when curs%notfound;
dbms_output.put_line(ret);
end loop;
close curs;
end;
/

10.使用變數
declare
type my_curs_type is ref cursor;
curs my_curs_type;
str varchar2(200);
ret dy_sql%rowtype;
val varchar2(20);
begin
str := 'select dno,dtext from dy_sql where dno <> :b1';
val := 0;
open curs for str using val;
loop fetch curs into ret;
exit when curs%notfound;
dbms_output.put_line('value fetched dno:'||ret.dno||',dtext:'||ret.dtext);
end loop;
close curs;
end;

/

11.使用bulk collect得到多筆資料

declare
type my_cur is ref cursor;
curs my_cur;
row_dy_sql dy_sql%rowtype;
type str_tab is table of dy_sql%rowtype;
rec_tab str_tab;
str varchar2(120);
begin
str:= ' select dno,dtext from dy_sql';
open curs for str;
fetch curs bulk collect into rec_tab limit 100;
close curs;
for i in 1..rec_tab.count
loop
dbms_output.put_line('rec_tab:' ||rec_tab(i).dno ||'，'||rec_tab(i).dtext);
end loop;
end;

/