源码-PL/SQL从入门到精通-第十六章-动态SQL语句-Part 2

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/52036974
--代码16.10 定义并打开动态SQL语句游标
DECLARE
   TYPE emp_cur_type IS REF CURSOR;      --定义游标类型
   emp_cur emp_cur_type;                 --定义游标变量
   v_deptno NUMBER(4) := '&deptno';      --定义部门编号绑定变量
   v_empno NUMBER(4);                                         
   v_ename VARCHAR2(25);
BEGIN
   OPEN emp_cur FOR                  --打开动态游标
      'SELECT empno, ename FROM emp '||
      'WHERE deptno = :1'
   USING v_deptno;
   NULL;
END;

--使用Fetch语句提取游标数据
DECLARE
   TYPE emp_cur_type IS REF CURSOR;      --定义游标类型
   emp_cur emp_cur_type;                 --定义游标变量
   v_deptno NUMBER(4) := '&deptno';      --定义部门编号绑定变量
   v_empno NUMBER(4);                                         
   v_ename VARCHAR2(25);
BEGIN
   OPEN emp_cur FOR                       --打开动态游标
      'SELECT empno, ename FROM emp '||
      'WHERE deptno = :1'
   USING v_deptno;
   LOOP
      FETCH emp_cur INTO v_empno, v_ename; --循环提取游标数据  
      EXIT WHEN emp_cur%NOTFOUND;          --没有数据时退出循环
      DBMS_OUTPUT.PUT_LINE ('员工编号: '||v_empno);
      DBMS_OUTPUT.PUT_LINE ('员工名称:  '||v_ename);
   END LOOP;
END;

--代码16.12 多行动态SQL语句执行完整示例（具有较完整逻辑）
DECLARE
   TYPE emp_cur_type IS REF CURSOR;      --定义游标类型
   emp_cur emp_cur_type;                 --定义游标变量
   v_deptno NUMBER(4) := '&deptno';      --定义部门编号绑定变量
   v_empno NUMBER(4);                                         
   v_ename VARCHAR2(25);
BEGIN
   OPEN emp_cur FOR                       --打开动态游标
      'SELECT empno, ename FROM emp '||
      'WHERE deptno = :1'
   USING v_deptno;
   LOOP
      FETCH emp_cur INTO v_empno, v_ename; --循环提取游标数据  
      EXIT WHEN emp_cur%NOTFOUND;          --没有数据时退出循环
      DBMS_OUTPUT.PUT_LINE ('员工编号: '||v_empno);
      DBMS_OUTPUT.PUT_LINE ('员工名称:  '||v_ename);
   END LOOP;
   CLOSE emp_cur;                          --关闭游标变量
EXCEPTION
   WHEN OTHERS THEN   
      IF emp_cur%FOUND THEN               --如果出现异常，游标变量未关闭
         CLOSE emp_cur;                   --关闭游标
      END IF;   
      DBMS_OUTPUT.PUT_LINE ('ERROR: '||
         SUBSTR(SQLERRM, 1, 200));         
END;

--代码16.13 在DML语句中使用BULK子句
DECLARE
   --定义索引表类型，用来保存从DML语句中返回的结果
   TYPE ename_table_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
   TYPE sal_table_type IS TABLE OF NUMBER(10,2) INDEX BY BINARY_INTEGER;   
   ename_tab ename_table_type;
   sal_tab sal_table_type;
   v_deptno NUMBER(4) :=20;                             --定义部门绑定变量
   v_percent NUMBER(4,2) := 0.12;                       --定义加薪比率绑定变量
   sql_stmt  VARCHAR2(500);                             --保存SQL语句的变量
BEGIN
   --定义更新emp表的sal字段值的动态SQL语句
   sql_stmt:='UPDATE emp SET sal=sal*(1+:percent) '
             ||' WHERE deptno=:deptno RETURNING ename,sal INTO :ename,:salary';
   EXECUTE IMMEDIATE sql_stmt USING v_percent, v_deptno
      RETURNING BULK COLLECT INTO ename_tab,sal_tab;   --使用RETURNING BULK COLLECT INTO子句获取返回值
   FOR i IN 1..ename_tab.COUNT LOOP                    --输出返回的结果值 
      DBMS_OUTPUT.put_line('员工'||ename_tab(i)||'调薪后的薪资：'||sal_tab(i));
   END LOOP;
END;


--代码16.14 使用Bulk子句处理多行查询
DECLARE
   TYPE ename_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
   TYPE empno_table_type IS TABLE OF NUMBER(24) INDEX BY BINARY_INTEGER; 
   ename_tab ename_table_type;              --定义保存多行返回值的索引表
   empno_tab empno_table_type;  
   v_deptno NUMBER(4) := '&deptno';          --定义部门编号绑定变量
   sql_stmt VARCHAR2(500);
BEGIN
   --定义多行查询的SQL语句
   sql_stmt:='SELECT empno, ename FROM emp '||'WHERE deptno = :1';
   EXECUTE IMMEDIATE sql_stmt 
   BULK COLLECT INTO empno_tab,ename_tab               --批量插入到索引表
   USING v_deptno;   
   FOR i IN 1..ename_tab.COUNT LOOP                    --输出返回的结果值 
      DBMS_OUTPUT.put_line('员工编号'||empno_tab(i)
                                         ||'员工名称：'||ename_tab(i));
   END LOOP;          
END;


--代码16.15 使用批量Fetch语句获取多行查询结果
DECLARE
   TYPE ename_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
   TYPE empno_table_type IS TABLE OF NUMBER(24) INDEX BY BINARY_INTEGER;
   TYPE emp_cur_type IS REF CURSOR;         --定义游标类型    
   ename_tab ename_table_type;              --定义保存多行返回值的索引表
   empno_tab empno_table_type;  
   emp_cur emp_cur_type;                    --定义游标变量
   v_deptno NUMBER(4) := '&deptno';         --定义部门编号绑定变量
BEGIN
   OPEN emp_cur FOR                         --打开动态游标
      'SELECT empno, ename FROM emp '||
      'WHERE deptno = :1'
   USING v_deptno;
   FETCH emp_cur BULK COLLECT INTO empno_tab, ename_tab; --批量提取游标数据  
   CLOSE emp_cur;                                        --关闭游标变量
   FOR i IN 1..ename_tab.COUNT LOOP                      --输出返回的结果值 
      DBMS_OUTPUT.put_line('员工编号'||empno_tab(i)
                                         ||'员工名称：'||ename_tab(i));
   END LOOP;       
END;


SELECT * FROM emp;
--代码16.16 使用Forall语句更新多个员工薪资（参数要手动输入？）
DECLARE
   --定义索引表类型，用来保存从DML语句中返回的结果
   TYPE ename_table_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
   TYPE sal_table_type IS TABLE OF NUMBER(10,2) INDEX BY BINARY_INTEGER;   
   TYPE empno_table_type IS TABLE OF NUMBER(4);         --定义嵌套表类型，用于批量输入员工编号  
   ename_tab ename_table_type;
   sal_tab sal_table_type;
   empno_tab empno_table_type;
   v_deptno NUMBER(4) :=20;                             --定义部门绑定变量
   v_percent NUMBER(4,2) := 0.12;                       --定义加薪比率绑定变量
   sql_stmt  VARCHAR2(500);                             --保存SQL语句的变量
BEGIN
   empno_tab:=empno_table_type(7369,7499,7521,7566,5093);    --初始化嵌套表
     --定义更新emp表的sal字段值的动态SQL语句
   sql_stmt:='UPDATE emp SET sal=sal*(1+:percent) '
             ||' WHERE empno=:empno RETURNING ename,sal INTO :ename,:salary';
   FORALL i IN 1..empno_tab.COUNT                        --使用FORALL语句批量输入参数
      EXECUTE IMMEDIATE sql_stmt USING v_percent, empno_tab(i)  --这里使用来自嵌套表的参数
      RETURNING BULK COLLECT INTO ename_tab,sal_tab;   --使用RETURNING BULK COLLECT INTO子句获取返回值
   FOR i IN 1..ename_tab.COUNT LOOP                    --输出返回的结果值 
      DBMS_OUTPUT.put_line('员工'||ename_tab(i)||'调薪后的薪资：'||sal_tab(i));
   END LOOP;
END;


--代码16.17 使用重复占位符示例（用PL/SQL替代SQL语句）（似乎是细枝末节？）
DECLARE
   col_in     VARCHAR2(10):='sal';    --列名
   start_in   DATE;        --起始日期
   end_in     DATE;        --结束日期
   val_in     NUMBER;      --输入参数值
   plsql_str    VARCHAR2 (32767)
      :=    '
         BEGIN
             UPDATE emp SET '
             || col_in
             || ' = :val
            WHERE hiredate BETWEEN :lodate AND :hidate
            AND :val IS NOT NULL;
        END;
        '; --动态PLSQL语句
BEGIN
   --执行动态SQL语句，为重复的val_in传入多次作为绑定变量
   EXECUTE IMMEDIATE dml_str
               USING val_in,start_in,end_in;
END;


--代码16.18 定义调用者权限（有点意思）
create table test(id int);

--定义一个删除任何数据库对象的通用的过程
CREATE OR REPLACE PROCEDURE drop_obj (kind IN VARCHAR2, NAME IN VARCHAR2)
AUTHID CURRENT_USER       --定义调用者权限
AS
BEGIN
   EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || NAME;
EXCEPTION
WHEN OTHERS THEN
   RAISE;   
END;

call scott.drop_obj('TABLE','test');

--代码16.5.4 传递Null参数
DECLARE
   v_null   CHAR (1);                      --在运行时该变量自动被设置为NULL值
BEGIN
   EXECUTE IMMEDIATE 'UPDATE emp SET comm=:x'
               USING v_null;                                     --传入NULL值
END;


--代码16.19 在执行动态SQL时使用异常处理机制
CREATE OR REPLACE PROCEDURE ddl_execution (ddl_string IN VARCHAR2)
   AUTHID CURRENT_USER IS            --使用调用者权限
BEGIN
   EXECUTE IMMEDIATE ddl_string;     --执行动态SQL语句
EXCEPTION
   WHEN OTHERS                       --捕捉错误  
   THEN
      DBMS_OUTPUT.PUT_LINE (      --显示错误消息
         '动态SQL语句错误：' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      DBMS_OUTPUT.PUT_LINE (      --显示当前执行的SQL语句
         '   执行的SQL语句为： "' || ddl_string || '"');
      RAISE;
END ddl_execution;



exec ddl_execution('alter table emp_test add emp_sal number NULL');