天天看点

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);