PL/SQL-4 動态SQL

---------------------------------------------------------------------------------------

***************************************************************************************

第四部分：動态SQL

/*

1.什麼是動态SQL？

動态SQL是指在運作PL/SQL塊時動态輸入SQL語句。在PL/SQL塊中隻能執行DDL（create、alter、drop）、DCL

（grant、revoke）或比較靈活的SQL語句（如select子句不帶where條件）；

動态SQL的性能不如靜态SQL，但是比較靈活；

在PL/SQL塊中編寫動态SQL語句時需要将SQL語句存放到字元串變量中而且SQL語句可以包含占位符（以冒号開始）；

2.動态SQL的文法

2.1 使用execute immediate語句

可以處理多數動态SQL操作如：DDL語句(create、alter、drop)、DML語句(insert、update、delete)、DCL(grant、revoke)

以及單行的select子句；但是不能處理多行查詢語句。

2.2 使用open...for，fetch和close語句

在遊标中使用。

2.3 使用批量動态SQL語句

*/

---------------------------------------------------------------

***************************************************************

1. 使用execute immediate

1.1 使用execute immediate處理DDL語句

--案例01：使用execute immediate處理DDL語句--create

declare

create_table  varchar2(200);

begin

create_table:='create table ' ||'&table_name' ||'(sid int, sno int)';

execute  immediate create_table  ;

end;

--案例02：使用execute immediate處理DDL語句---alter

alter_table  varchar2(200);

alter_table:='alter table &target_table_name modify &column_name varchar2(10)';

execute  immediate alter_table  ;

--案例03：使用execute immediate處理DDL語句---drop

drop_table  varchar2(200);

drop_table:='drop table ' ||'&target_table_name';

execute  immediate drop_table  ;

--案例04：使用execute immediate處理DDL語句--drop table

create or replace procedure drop_table (table_name varchar2)

is

sql_sta varchar2(200);

sql_sta:='drop table ' ||table_name;

execute immediate sql_sta;

--調用方法：

exec drop_table('accp');

--案例05：使用execute immediate處理DDL語句--create+select

select_sta varchar2(200);

emp_rec emp%rowtype;

execute immediate

'create table sodi(sid int, sno int)';

select_sta:='select * from emp where empno=:id';

execute  immediate select_sta  into emp_rec using &1;   /*使用占位符時，這個占位符是在引号内使用的*/

---------------------------------------------------------

1.2 使用execute immediate處理DCL語句

----------------------------------------------------------

--案例01:使用execute immediate處理DCL語句--grant

create or replace procedure grant_priv(priv varchar2, username varchar2)

priv_stat varchar2(200);

priv_stat:=' grant '|| priv || ' to ' || username; --注意字元串和連接配接符之間的空格

execute immediate priv_stat;

--調用方法

 exec grant_priv('create session', 'scott');

--案例02:使用execute immediate處理DCL語句--revoke

create or replace procedure revoke_priv(priv varchar2, username varchar2)

priv_stat:=' revoke '|| priv || ' from ' || username; --注意字元串和連接配接符之間的空格

 exec revoke_priv('create session', 'scott');

----------------------------------------------------------------

1.3.使用execute immediate處理DML語句

1.處理無占位符和return子句的DML語句

--案例01：查詢子句:select

sql_stat varchar2(100);

sql_stat:='select * from emp';

execute immediate sql_stat;

--案例02：處理無占位符和return子句的DML語句--insert

insert_table varchar2(200);

insert_table:='insert into &table_name values (&sid, &sno)';

execute immediate insert_table;

--案例03：處理無占位符和return子句的DML語句--update

update_table varchar2(200);

update_table:='update &table_name set &column_name=&new_value ';

execute immediate update_table;

--案例06:處理無占位符和return子句的DML語句--delete

delete_table varchar2(200);

delete_table:='delete from  &table_name  ';

execute immediate delete_table;

--案例05：建立一個表然後插入資料--create+insert

create_table:='create table &table_name(sid int, sno int)';

--案例06：同時實作建立一個表并插入資料--create+insert

create_table:='create table &table_name (sid int, sno int)';

--案例07：同時實作建立一個表并插入資料--create+insert+update

這裡也可以分開寫

update_table:='update &table_name set &column_name=&new_value where sid=&old_value';

--案例08：同時實作建立一個表并插入資料--create+insert+update+delete

delete_table:='delete from &delete_tablename';

--案例08：同時實作建立一個表并插入資料--create+insert+update+delete+insert

re_insert_table varchar2(200);

re_insert_table:='insert into &re_table_name values (&new_sid, &new_sno)';

execute immediate re_insert_table;

2.處理占位符的DML語句

--案例01：處理占位符的DML語句:create+insert

insert_table:='insert into &target_table_name values (:sid, :sno)';

execute immediate insert_table using  &2,&3;

--案例02：處理占位符的DML語句:create+insert_update

execute immediate insert_table using  &1,&2;

update_table:='update &update_tablename set &update_column_name=:new_value where &condition_column=:old_value ';

execute immediate update_table using &3 ,&4;

---or

create_table:='create table &建立的表名 (sid int, sno int)';

insert_table:='insert into &插入資料的表名 values (:sid, :sno)';

execute immediate insert_table using  &sid的值 ,&sno的值;

update_table:='update &被更新的表名 set &更新的列名=:new_value where &條件列=:old_value ';

execute immediate update_table using &新值 ,&條件列的值;

--案例03:處理占位符的DML語句:create+update+insert+delete

update_table:='update &update_tablename set &update_column_name=:new_value where &update_condition_column=:delete_condition_column_value ';

delete_table:='delete from &dele_table_name where &delete_condition_column=:delete_condition_column_value';

execute immediate delete_table using &5;

---執行過程：

SQL> declare

  2  create_table  varchar2(200);

  3  insert_table varchar2(200);

  4  update_table varchar2(200);

  5  delete_table varchar2(200);

  6  begin

  7  create_table:='create table &table_name (sid int, sno int)';

  8  execute  immediate create_table  ;

  9

 10  insert_table:='insert into &target_table_name values (:sid, :sno)';

 11  execute immediate insert_table using  &1,&2;

 12

 13  update_table:='update &update_tablename set &update_column_name=:new_value

where &update_condition_column=:delete_condition_column_value ';

 14  execute immediate update_table using &3 ,&4;

 15

 16  delete_table:='delete from &dele_table_name where &delete_condition_column=

:delete_condition_column_value';

 17  execute immediate delete_table using &5;

 18  end;

 19  /

輸入 table_name 的值:  sz

原值    7: create_table:='create table &table_name (sid int, sno int)';

新值    7: create_table:='create table sz (sid int, sno int)';

輸入 target_table_name 的值:  sz

原值   10: insert_table:='insert into &target_table_name values (:sid, :sno)';

新值   10: insert_table:='insert into sz values (:sid, :sno)';

輸入 1 的值:  101

輸入 2 的值:  201

原值   11: execute immediate insert_table using  &1,&2;

新值   11: execute immediate insert_table using  101,201;

輸入 update_tablename 的值:  sz

輸入 update_column_name 的值:  sid

輸入 update_condition_column 的值:  sno

原值   13: update_table:='update &update_tablename set &update_column_name=:new_

value where &update_condition_column=:delete_condition_column_value ';

新值   13: update_table:='update sz set sid=:new_value where sno=:delete_conditi

on_column_value ';

輸入 3 的值:  1001

輸入 4 的值:  201

原值   14: execute immediate update_table using &3 ,&4;

新值   14: execute immediate update_table using 1001 ,201;

輸入 dele_table_name 的值:  sz

輸入 delete_condition_column 的值:  sno

原值   16: delete_table:='delete from &dele_table_name where &delete_condition_c

olumn=:delete_condition_column_value';

新值   16: delete_table:='delete from sz where sno=:delete_condition_column_valu

e';

輸入 5 的值:  201

原值   17: execute immediate delete_table using &5;

新值   17: execute immediate delete_table using 201;

PL/SQL 過程已成功完成。

SQL> select * from sz;

未標明行

3.處理包含returning子句的DML語句

--案例01：處理包含returning子句的DML語句--create+insert+update

create_table varchar2(200);

res varchar2(100);

create_table:='create table &new_table_name (sid int, sno int)';

execute immediate create_table;

insert_table:='insert into &insert_target_table_name values (:sid, :sno)';

execute immediate insert_table using &1,&2 ;

update_table:='update &update_target_table_name set &udpate_column_name=:new_value where &condition_column_name=:old_value  returning &returning_name into :res';

execute immediate update_table using &3,&4

returning into res;

dbms_output.put_line('the result is : ' ||res);