源码-Oracle数据库管理-第十七章-动态SQL语句-Part 2（使用EXECUTE IMMEDIATE）

EXECUTE IMMEDIATE是PL/SQL中执行动态SQL语句的首选，它比DBMS_SQL包简单，而且功能同样出色。它可以支持绑定变量、批处理，可以执行各种SQL语句，也可以动态地执行一段PL/SQL代码，而且在性能上，也要优越于DBMS_SQL。

--20170108--17.2 使用Execute Immediate--17.2.1 Execute Immediate语法--17.2.2 执行SQL语句和PL/SQL语句块--代码17.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;drop table ddl_demo;--代码17.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;--17.2.3 使用绑定变量--代码17.5 在动态SQL语句中使用绑定变量DECLARE  --定义变量，用来为绑定变量提供值  v_empno    NUMBER := 8998;  v_ename    emp.ename%TYPE := '吕四娘';  v_job      emp.job%TYPE := '剑客';  v_deptno   emp.deptno%TYPE := 20;  v_tbl_name VARCHAR2(50) := 'emp';  v_sql_str  VARCHAR2(1000);BEGIN  --定义一个SQL查询字符串，它的插入值使用了4个绑定变量  v_sql_str := 'INSERT INTO ' || v_tbl_name ||               '(empno,ename,job,deptno) VALUES(:1,:2,:3,:4)';  --执行动态SQL语句，按绑定变量占位符的位置为绑定变量赋值  EXECUTE IMMEDIATE v_sql_str    USING v_empno, v_ename, v_job, v_deptno;END;SELECT * FROM emp WHERE empno=8998;--代码17.6 不同类型的绑定变量使用示例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;drop table emp_name_tab;select * from emp_name_tab;select * from dept where deptno=30;--代码17.7 使用字符串拼接来指定表名DECLARE  sql_stmt VARCHAR2(100);  table_name VARCHAR2(20):='emp_history_temp';          --定义一个表名变量BEGIN   sql_stmt:='TRUNCATE TABLE '||table_name;          --使用字符串拼接表名   EXECUTE IMMEDIATE sql_stmt;                    --执行动态SQL语句END;--练习：自己写的第一个含有动态SQL语句的PL/SQL程序块，cheers!:)DECLARE   sql_statement   VARCHAR2 (100);   v_count number(10);BEGIN   --定义一个DDL语句，用来删除一个表   sql_statement := 'drop table emp_history_temp';   --执行动态SQL语句   EXECUTE IMMEDIATE sql_statement;   --定义一个DDL语句，用来创建一个表   sql_statement := 'create table emp_history_temp (id number(2))';   EXECUTE IMMEDIATE sql_statement;   --定义一个DML语句，用来向表中插入一条记录   sql_statement := 'INSERT INTO emp_history_temp VALUES(7)';   EXECUTE IMMEDIATE sql_statement;   --定义一个DQL语句，用来查询记录   sql_statement := 'select count(*) from emp_history_temp';   EXECUTE IMMEDIATE sql_statement into v_count;   DBMS_OUTPUT.put_line('查询到'||v_count||'条记录');END;drop table emp_history_temp;select * from emp_history_temp;--17.2.4 使用Returning into子句--代码17.8 使用Returning into子句DECLARE   v_empno NUMBER(4) :=5093;                      --定义员工绑定变量   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;select sal from emp where empno=5093;select * from emp_history;--17.2.5 使用Into子句--代码17.9 在单行查询中使用into子句DECLARE   sql_stmt  VARCHAR2(100);            --保存动态SQL语句的变量   v_deptno NUMBER(4) :=20;             --部门编号，用于绑定变量   v_empno NUMBER(4):=5093;           --员工编号，用于绑定变量   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;update emp set ename='双双' where empno=5093;update emp set empno=1126 where empno=8012;update emp set ename='斌斌' where empno=1126;select * from emp;--17.2.6 指定参数模式--代码17.10 使用In out模式的参数CREATE OR REPLACE PROCEDURE create_dept(deptno IN OUT NUMBER,             --IN OUT变量，用来获取或输出deptno值dname IN VARCHAR2,                --部门名称loc IN VARCHAR2                    --部门地址)ASBEGIN  --如果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;/--下面的代码调用create_dept，并且返回部门的编号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||'，新建部门位于：' || v_loc);END;/ SELECT deptno_seq.NEXTVAL FROM DUAL;