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

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/52036377
学到后面几章，发现实用性大大提高。
心得：
1. 代码尽量放到集成开发环境（PL/SQL）中查看，效率要比在书本上看高多了。
2. 卖油翁言“我亦无他，唯手熟尔”，软件开发不是纸上谈兵，必须在开发环境中不断实践才行！
--第16章开始
--代码16.1 动态SQL语句使用示例  
CREATE OR REPLACE FUNCTION get_tablecount (table_name IN VARCHAR2)
   RETURN PLS_INTEGER
IS
   --定义动态SQL语句
   sql_query   VARCHAR2 (32767) := 'SELECT COUNT(*) FROM ' || table_name;
   l_return    PLS_INTEGER;              --保存返回值的变量
BEGIN
   EXECUTE IMMEDIATE sql_query
                INTO l_return;           --动态执行SQL并返回结果值
   RETURN l_return;                      --返回函数结果
END;


--调用函数，执行动态SQL
DECLARE
   v_count PLS_INTEGER;
BEGIN
   v_count:=get_tablecount('emp');
   DBMS_OUTPUT.put_line('emp表的行数：'||v_count);
   v_count:=get_tablecount('dept');
   DBMS_OUTPUT.put_line('dept表的行数：'||v_count);   
END;   

--代码16.2 使用动态SQL语句执行DDL语句
DECLARE
   v_counter   NUMBER;
BEGIN
   ---查询要创建的表是否存在
   SELECT COUNT (*) INTO v_counter FROM user_tables
            WHERE table_name = 'EMP_TESTING';
   ---如果存在则删除该表
   IF v_counter > 0 THEN
      DBMS_OUTPUT.put_line ('表存在不创建');
   ELSE
      DBMS_OUTPUT.put_line ('表不存在');
      --如果不使用动态SQL，在这里会出现错误
     EXECUTE IMMEDIATE 'CREATE TABLE emp_testing  (
       emp_name            VARCHAR2(18)                    not null,
       hire_date           DATE                            not null,
       status              NUMBER(2),
       constraint PK_ENTRY_MODIFYSTATUS primary key (emp_name, hire_date)
    )';
      --实际上前面的表根本没有创建成功，该INSERT不能成功执行
     EXECUTE IMMEDIATE 'INSERT INTO emp_testing VALUES(''李进平'',TRUNC(SYSDATE)-5,1)';
     COMMIT;
   END IF;
   v_counter :=0;
END;

drop table emp_testing;
SELECT * FROM emp_testing;

--代码16.3 使用动态SQL执行DDL和DML语句
DECLARE
   sql_statement   VARCHAR2 (100);
BEGIN
   --定义一个DDL语句，用来创建一个表
   sql_statement := 'CREATE TABLE ddl_demo(id NUMBER,amt NUMBER)';
   --执行动态SQL语句
   EXECUTE IMMEDIATE sql_statement;
   --定义一个DML语句，用来向表中插入一条记录
   sql_statement := 'INSERT INTO ddl_demo VALUES(1,100)';
   --执行动态SQL语句
   EXECUTE IMMEDIATE sql_statement;
END;

select * from ddl_demo;

--代码16.4 执行动态PL/SQL语句
DECLARE
   plsql_block   VARCHAR2 (500);     --定义一个变量用来保存PL/SQL语句
BEGIN
   plsql_block:=                       --为动态PL/SQL语句赋值
       'DECLARE
          I  INTEGER:=10;
        BEGIN
          EXECUTE IMMEDIATE ''TRUNCATE TABLE ddl_demo'';
          FOR j IN 1..I LOOP
             INSERT INTO ddl_demo VALUES(j,j*100);
          END LOOP;
        END;';                       --语句结束时添加分号
    EXECUTE IMMEDIATE plsql_block;   --执行动态PL/SQL语句
    COMMIT;                          --提交事务
END;

DROP TABLE emp_name_tab;

--代码16.5 绑定变量使用示例
DECLARE
   sql_stmt  VARCHAR2(200);                               --保存SQL语句的变量
   TYPE id_table IS TABLE OF INTEGER;                     --定义2个嵌套表类型
   TYPE name_table IS TABLE OF VARCHAR2(8);
   t_empno id_table:=id_table(9001,9002,9003,9004,9005);  --定义嵌套表变量并进行初始化
   t_empname name_table:=name_table('张三','李四','王五','赵六','何七');
   v_deptno  NUMBER(2):=30;
   v_loc VARCHAR(20):='浦东';
   emp_rec emp%ROWTYPE;
BEGIN 
   --为记录类型赋值，记录类型作为绑定变量将失败
   emp_rec.empno:=9001;
   emp_rec.ename:='西蒙';
   emp_rec.hiredate:=TRUNC(SYSDATE);
   emp_rec.sal:=5000;
   --使用普通的变量作为绑定变量
   sql_stmt:='UPDATE dept SET loc=:1 WHERE deptno=:2';
   EXECUTE IMMEDIATE sql_stmt USING v_loc,v_deptno;
   --创建一个测试用的数据表
   sql_stmt:='CREATE TABLE emp_name_tab(empno NUMBER,empname VARCHAR(20))';
   EXECUTE IMMEDIATE sql_stmt;
   --使用嵌套表变量的值作为绑定变量
   sql_stmt:='INSERT INTO emp_name_tab VALUES(:1,:2)';
   FOR i IN t_empno.FIRST..t_empno.LAST LOOP
      EXECUTE IMMEDIATE sql_stmt USING t_empno(i),t_empname(i);
   END LOOP;
   --使用记录类型提示失败
   --sql_stmt:='INSERT INTO emp VALUES :1';
   --EXECUTE IMMEDIATE sql_stmt USING emp_rec;
END;

select * from dept;
drop table emp_name_tab;
select * from emp_name_tab;

--代码10.6 使用字符串拼接设置方案对象
--创建一个清除表内容的过程
CREATE OR REPLACE PROCEDURE trunc_table(table_name IN VARCHAR2)
IS
  sql_stmt VARCHAR2(100);
BEGIN
   sql_stmt:='TRUNCATE TABLE '||table_name;        --使用拼接设置方案对象
   EXECUTE IMMEDIATE sql_stmt;                     --动态执行SQL语句
END; 

BEGIN
   trunc_table('emp_name_tab');                  
END;


SELECT * FROM emp WHERE empno=7369;

--代码16.7 使用Returning into子句
DECLARE
   v_empno NUMBER(4) :=7369;                      --定义员工绑定变量
   v_percent NUMBER(4,2) := 0.12;                 --定义加薪比率绑定变量
   v_salary  NUMBER(10,2);                        --返回变量
   sql_stmt  VARCHAR2(500);                       --保存SQL语句的变量
BEGIN
   --定义更新emp表的sal字段值的动态SQL语句
   sql_stmt:='UPDATE emp SET sal=sal*(1+:percent) '
             ||' WHERE empno=:empno RETURNING sal INTO :salary';
   EXECUTE IMMEDIATE sql_stmt USING v_percent, v_empno
      RETURNING INTO v_salary;                    --使用RETURNING INTO子句获取返回值
   DBMS_OUTPUT.put_line('调整后的工资为：'||v_salary);
END;


--代码16.8 执行单行查询
DECLARE
   sql_stmt  VARCHAR2(100);           --保存动态SQL语句的变量
   v_deptno NUMBER(4) :=20;           --部门编号，用于绑定变量
   v_empno NUMBER(4):=7369;           --员工编号，用于绑定变量
   v_dname  VARCHAR2(20);             --部门名称，获取查询结果
   v_loc  VARCHAR2(20);               --部门位置，获取查询结果
   emp_row emp%ROWTYPE;               --保存结果的记录类型
BEGIN
   --查询dept表的动态SQL语句
   sql_stmt:='SELECT dname,loc FROM dept WHERE deptno=:deptno';
   --执行动态SQL语句并记录查询结果
   EXECUTE IMMEDIATE sql_stmt INTO v_dname,v_loc USING v_deptno ;
   --查询emp表的特定员工编号的记录
   sql_stmt:='SELECT * FROM emp WHERE empno=:empno';
   --将emp表中的特定行内容写入emp_row记录中
   EXECUTE IMMEDIATE sql_stmt INTO emp_row USING v_empno;
   DBMS_OUTPUT.put_line('查询的部门名称为：'||v_dname);
   DBMS_OUTPUT.put_line('查询的员工编号为：'||emp_row.ename);   
END;

--代码16.9 指定参数的模式
--创建过程
CREATE OR REPLACE PROCEDURE create_dept(
deptno IN OUT NUMBER,             --IN OUT变量，用来获取或输出deptno值
dname IN VARCHAR2,                --部门名称
loc IN VARCHAR2                   --部门地址
)AS
BEGIN
  --如果deptno没有指定值
  IF deptno IS NULL THEN
     --从序列中取一个值
     SELECT deptno_seq.NEXTVAL INTO deptno FROM DUAL;
  END IF;
  --向dept表中插入记录
  INSERT INTO dept VALUES(deptno,dname,loc);
END;

--查看编译错误
SELECT   name,line, POSITION, text
     FROM user_errors
    WHERE NAME = 'CREATE_DEPT'
 ORDER BY SEQUENCE;
 
--创建序列
CREATE SEQUENCE deptno_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 9999999
NOCYCLE NOCACHE;

SELECT deptno_seq.NEXTVAL FROM dual;

--代码16.9 指定参数的模式
DECLARE
   plsql_block   VARCHAR2 (500);
   v_deptno      NUMBER (2);
   v_dname       VARCHAR2 (14)  := '网络部';
   v_loc         VARCHAR2 (13)  := '也门';
BEGIN
   plsql_block := 'BEGIN create_dept(:a,:b,:c);END;';
   --在这里指定过程需要的IN OUT参数模式
   EXECUTE IMMEDIATE plsql_block
               USING IN OUT v_deptno, v_dname, v_loc;
   DBMS_OUTPUT.put_line ('新建部门的编号为：' || v_deptno);
END;

select * from dept;