动态游标（例如表名作为参数）以及动态SQL分析
表名作为参数的动态游标
DECLARE
v_table_name VARCHAR2(30) := 'CUX_MES_WIP_BARCODE_MAP';
--l_rec SYS_REFCURSOR;
TYPE t_data IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE t_cur IS REF CURSOR;
l_data t_data;
l_rec t_cur;
l_cur VARCHAR2(4000);
v_fm_barcode VARCHAR2(30) := 'cxy-p-001';
v_to_barcode VARCHAR2(30) := 'cxy-p-002';
v_barcode VARCHAR2(30);
BEGIN
l_cur := 'select barcode from ' || v_table_name || ' where barcode >= ' ||
''''||v_fm_barcode||'''' || ' and barcode <= ' ||
''''||v_to_barcode || '''';
dbms_output.put_line(l_cur);
OPEN l_rec FOR l_cur;
LOOP
EXIT WHEN l_rec%NOTFOUND;
FETCH l_rec BULK COLLECT
INTO l_data;
FOR i IN 1 .. l_data.count LOOP
dbms_output.put_line(l_data(i));
END LOOP;
END LOOP;
END;
动态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 declare alter_table varchar2(200); begin alter_table:='alter table &target_table_name modify &column_name varchar2(10)'; execute immediate alter_table ; end; --案例03：使用execute immediate处理DDL语句---drop declare drop_table varchar2(200); begin drop_table:='drop table ' ||'&target_table_name'; execute immediate drop_table ; end; --案例04：使用execute immediate处理DDL语句--drop table create or replace procedure drop_table (table_name varchar2) is sql_sta varchar2(200); begin sql_sta:='drop table ' ||table_name; execute immediate sql_sta; end; --调用方法： exec drop_table('accp'); --案例05：使用execute immediate处理DDL语句--create+select declare select_sta varchar2(200); emp_rec emp%rowtype; begin 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; /*使用占位符时，这个占位符是在引号内使用的*/ end; --------------------------------------------------------- 1.2 使用execute immediate处理DCL语句 ---------------------------------------------------------- --案例01:使用execute immediate处理DCL语句--grant create or replace procedure grant_priv(priv varchar2, username varchar2) is priv_stat varchar2(200); begin priv_stat:=' grant '|| priv || ' to ' || username; --注意字符串和连接符之间的空格 execute immediate priv_stat; end; --调用方法 exec grant_priv('create session', 'scott'); --案例02:使用execute immediate处理DCL语句--revoke create or replace procedure revoke_priv(priv varchar2, username varchar2) is priv_stat varchar2(200); begin priv_stat:=' revoke '|| priv || ' from ' || username; --注意字符串和连接符之间的空格 execute immediate priv_stat; end; --调用方法 exec revoke_priv('create session', 'scott'); ---------------------------------------------------------------- 1.3.使用execute immediate处理DML语句 ---------------------------------------------------------------- 1.处理无占位符和return子句的DML语句 --案例01：查询子句:select declare sql_stat varchar2(100); begin sql_stat:='select * from emp'; execute immediate sql_stat; end; --案例02：处理无占位符和return子句的DML语句--insert declare insert_table varchar2(200); begin insert_table:='insert into &table_name values (&sid, &sno)'; execute immediate insert_table; end; --案例03：处理无占位符和return子句的DML语句--update declare update_table varchar2(200); begin update_table:='update &table_name set &column_name=&new_value '; execute immediate update_table; end; --案例06:处理无占位符和return子句的DML语句--delete declare delete_table varchar2(200); begin delete_table:='delete from &table_name '; execute immediate delete_table; end; --案例05：新建一个表然后插入数据--create+insert declare create_table varchar2(200); begin create_table:='create table &table_name(sid int, sno int)'; execute immediate create_table ; end; declare insert_table varchar2(200); begin insert_table:='insert into &table_name values (&sid, &sno)'; execute immediate insert_table; end; --案例06：同时实现新建一个表并插入数据--create+insert declare create_table varchar2(200); insert_table varchar2(200); begin create_table:='create table &table_name (sid int, sno int)'; execute immediate create_table ; insert_table:='insert into &table_name values (&sid, &sno)'; execute immediate insert_table; end; --案例07：同时实现新建一个表并插入数据--create+insert+update /* 这里也可以分开写 */ declare create_table varchar2(200); insert_table varchar2(200); update_table varchar2(200); begin create_table:='create table &table_name (sid int, sno int)'; execute immediate create_table ; insert_table:='insert into &table_name values (&sid, &sno)'; execute immediate insert_table; update_table:='update &table_name set &column_name=&new_value where sid=&old_value'; execute immediate update_table; end; --案例08：同时实现新建一个表并插入数据--create+insert+update+delete declare create_table varchar2(200); insert_table varchar2(200); update_table varchar2(200); delete_table varchar2(200); begin create_table:='create table &table_name (sid int, sno int)'; execute immediate create_table ; insert_table:='insert into &table_name values (&sid, &sno)'; execute immediate insert_table; update_table:='update &table_name set &column_name=&new_value where sid=&old_value'; execute immediate update_table; delete_table:='delete from &delete_tablename'; execute immediate delete_table; end; --案例08：同时实现新建一个表并插入数据--create+insert+update+delete+insert declare create_table varchar2(200); insert_table varchar2(200); update_table varchar2(200); delete_table varchar2(200); re_insert_table varchar2(200); begin create_table:='create table &table_name (sid int, sno int)'; execute immediate create_table ; insert_table:='insert into &table_name values (&sid, &sno)'; execute immediate insert_table; update_table:='update &table_name set &column_name=&new_value where sid=&old_value'; execute immediate update_table; delete_table:='delete from &delete_tablename'; execute immediate delete_table; re_insert_table:='insert into &re_table_name values (&new_sid, &new_sno)'; execute immediate re_insert_table; end; 2.处理占位符的DML语句 --案例01：处理占位符的DML语句:create+insert declare create_table varchar2(200); insert_table varchar2(200); begin create_table:='create table &table_name (sid int, sno int)'; execute immediate create_table ; insert_table:='insert into &target_table_name values (:sid, :sno)'; execute immediate insert_table using &2,&3; end; --案例02：处理占位符的DML语句:create+insert_update declare create_table varchar2(200); insert_table varchar2(200); update_table varchar2(200); begin create_table:='create table &table_name (sid int, sno int)'; execute immediate create_table ; insert_table:='insert into &target_table_name values (:sid, :sno)'; 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; end; ---or declare create_table varchar2(200); insert_table varchar2(200); update_table varchar2(200); begin create_table:='create table &新建的表名 (sid int, sno int)'; execute immediate create_table ; 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 &新值 ,&条件列的值; end; --案例03:处理占位符的DML语句:create+update+insert+delete declare create_table varchar2(200); insert_table varchar2(200); update_table varchar2(200); delete_table varchar2(200); begin create_table:='create table &table_name (sid int, sno int)'; execute immediate create_table ; insert_table:='insert into &target_table_name values (:sid, :sno)'; execute immediate insert_table using &1,&2; update_table:='update &update_tablename set &update_column_name=:new_value where &update_condition_column=:delete_condition_column_value '; execute immediate update_table using &3 ,&4; delete_table:='delete from &dele_table_name where &delete_condition_column=:delete_condition_column_value'; execute immediate delete_table using &5; end; ---执行过程： 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 declare create_table varchar2(200); insert_table varchar2(200); update_table varchar2(200); res varchar2(100); begin 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); end;
