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

来源：互联网发布：太阳能热水器知乎编辑：程序博客网时间：2024/05/22 21:42
--代码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       --定义调用者权限ASBEGIN   EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || NAME;EXCEPTIONWHEN 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');
0 0