oracle12C--EXECUTE IMMEDIATE语句(61)

• 它是做什么的？
  • 作用：该语句可以方便地在PL/SQL程序中执行DML（insert,upate,delete，单列select），DDL（create,alter,drop），DCL（GRANT,REVOKE）语句；
  • 语法：

EXECUTE IMMEDIATE 动态SQL字符串 [[BULK COLLECT] INTO自定义变量,.....| 记录类型]
[USING [IN | OUT | IN OUT] 绑定参数,.....]
[[RETURNING | RETURN][BULK COLLECT] INTO 绑定参数,....];

该语句由以下3个主要子句组成：
INTO ：保存动态SQL执行结果，如果返回多行记录，则可以通过BULK COLLECT设置批量保存；
USING：用来为动态SQL设置占位符设置内容；
RETURNING | RETURN：两者使用效果一样，是取得更新表记录被影响的数据，通过BULK COLLECT来设置批量绑定

注意：在使用USING或RETURNING语句时都可以设置参数模式（IN,OUT,IN OUT），其中对USING子句主要是使用变量定义的内容，所以默认的模式是IN模式。使用RETURNING子句时不需要设置内容，只需要接收返回内容，所以其模式为OUT

• 执行动态SQL
  • 示例1：使用动态SQL创建表和PL/SQL块

DECLARE

v_sql_statement VARCHAR2(200) ;

v_count NUMBER ; --保存查找结果

BEGIN

SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name='MLDN_TAB' ;

IF v_count = 0 THEN --数据表不存在

v_sql_statement := 'CREATE TABLE mldn_tab(

id NUMBER PRIMARY KEY ,

url VARCHAR2(50) NOT NULL)' ; --定义动态SQL

EXECUTE IMMEDIATE v_sql_statement ;

ELSE --数据表存在

v_sql_statement := 'TRUNCATE TABLE mldn_tab' ;

EXECUTE IMMEDIATE v_sql_statement ;

END IF ;

v_sql_statement := 'BEGIN

FOR x IN 1 .. 10 LOOP

INSERT INTO mldn_tab(id,url) VALUES (x , ''www .mldnjava.cn - '' || x) ;

END LOOP ;

END ;' ;

EXECUTE IMMEDIATE v_sql_statement ;

COMMIT ; --提交事务

END ;

/

本程序首先判断要操作的数据表（mldn_tab）是否存在，如果数据表不存在，则使用动态SQL创建一张mldn_tab的数据表，如果存在，则执行TRUNCATE TABLE命令，将mldn_tab数据表中的数据清楚干净，之后继续利用动态SQL执行一个PL/SQL程序块，向mldn_tab数据表中增加10条记录，运行流程图如下：

• 设置绑定变量
  • 定义：使用动态SQL时，可以在定义SQL字符串里采用占位符的方式设置绑定变量，所设置的绑定变量需要在程序运行时动态地使用USING语句设置占位符内容，而设置绑定变量的方式采用":占位符名称"的方式表示；
  • 示例1：使用绑定变量

DECLARE

v_sql_statement VARCHAR2(200) ;

v_deptno dept.deptno%TYPE := 60 ;

v_dname dept.dname%TYPE := 'MLDN' ;

v_loc dept.loc%TYPE := '北京' ;

BEGIN

v_sql_statement := 'INSERT INTO dept(deptno,dname,loc) VALUES (:dno , :dna , :dl)' ;

EXECUTE IMMEDIATE v_sql_statement USING v_deptno,v_dname,v_loc ;

COMMIT ;

END ;

/

此程序不能直接绑定NULL，需要通过变量设置。以下的设置是错误的：

EXECUTE IMMEDIATE v_sql_statement USING v_deptno,v_dname,NULL;
如果希望loc的内容为NULL,可以将v_loc变量设置为NULL，其他执行部分不做改变；

• 示例2：利用集合更新多条记录

DECLARE

v_sql_statement VARCHAR2(200) ;

TYPE deptno_nested IS TABLE OF dept.deptno%TYPE NOT NULL ;

TYPE dname_nested IS TABLE OF dept.dname%TYPE NOT NULL ;

v_deptno deptno_nested := deptno_nested(10,20,30,40) ;

v_dname dname_nested := dname_nested('财务部','研发部','销售部','操作部') ;

BEGIN

v_sql_statement := 'UPDATE dept SET dname=:dna WHERE deptno=:dno' ;

FOR x IN 1 .. v_deptno.COUNT LOOP

EXECUTE IMMEDIATE v_sql_statement USING v_dname(x),v_deptno(x) ;

END LOOP ;

COMMIT ;

END ;

/

本程序定义了两个嵌套表类型（deptno_nested,dname_nested），然后将要更新的部门编号及部门名称分别保存在这两个嵌套表中，最后采用循环的方式利用
EXECUTE IMMEDIATE动态地执行更新操作

• 示例3：查询数据

DECLARE

v_sql_statement VARCHAR2(200) ;

v_empno emp.empno%TYPE := 7369 ;

v_emprow emp%ROWTYPE ;

BEGIN

v_sql_statement := 'SELECT * FROM emp WHERE empno=:eno' ;

EXECUTE IMMEDIATE v_sql_statementINTO v_emprow USING v_empno ;

DBMS_OUTPUT.put_line('雇员编号：' || v_emprow.empno || '，姓名：' || v_emprow.ename || '，职位：' || v_emprow.job) ;

END ;

/

本程序在查询语句中使用了绑定变量，由于此时需要返回结果，所以通过EXECUTE IMMEDIATE中的INTO子句将查询结果保存在v_emprow变量中，同时还需要使用USING设置占位符数据

• 示例4：上面的示例中，绑定变量的代码都只是针对基本的数据类型，例如字符串，数字等。这种方式无法针对DDL操作；
  • 在创建表时使用绑定变量

DECLARE

v_sql_statement VARCHAR2(200) ;

v_table_name VARCHAR2(200) := 'mldn' ;

v_id_column VARCHAR2(200) := 'id' ;

BEGIN

v_sql_statement := 'CREATE TABLE:tn (:ci NUMBER PRIMARY KEY)' ;

EXECUTE IMMEDIATE v_sql_statement USINGv_table_name,v_id_column ;

END ;

/

运行结果：

错误报告:

ORA-00903: 表名无效

ORA-06512: 在 line 7

• 此时CREATE是DDL操作命令，无法使用绑定变量设置表名称，同理，对于删除表，截断表操作也一样无法使用，如果要使用，可以采用拼接字符串的方式完成，例如：
  • 正确的代码

DECLARE

v_sql_statement VARCHAR2(200) ;

v_table_name VARCHAR2(200) := 'mldn' ;

v_id_column VARCHAR2(200) := 'id' ;

BEGIN

v_sql_statement := 'CREATE TABLE ' || v_table_name ||' (' || v_id_column ||' NUMBER PRIMARY KEY)' ;

EXECUTE IMMEDIATE v_sql_statement ;

END ;

/

• 接收DML更新行数
  • 当用户使用DML更新操作后，可以利用RETURNING INTO子句接收更新后被影响的数据行的详细信息；
  • 示例1：更新数据，取得更新后的结果

DECLARE

v_sql_statement VARCHAR2(200) ; --定义SQL操作语句

v_empno emp.empno%TYPE := 7369 ; --要更新的雇员编号

v_salary emp.sal%TYPE ; --保存更新后的sal内容

v_job emp.job%TYPE ; --保存更新后的job内容

BEGIN

v_sql_statement := 'UPDATE emp SET sal=sal*1.2,job=''开发'' ' ||

' WHERE empno=:enoRETURNING sal,job INTO :salary,:job' ;

EXECUTE IMMEDIATE v_sql_statementUSING v_empno RETURNING INTO v_salary,v_job;

DBMS_OUTPUT.put_line('调整后的工资：' || v_salary || '，新的职位：' || v_job) ;

END ;

/

提示：也可以使用RETURN接收影响数据行的数据，代码片段如下：

v_sql_statement := 'UPDATE emp SET sal=sal*1.2,job=''开发'' ' ||

' WHERE empno=:enoRETURN sal,job INTO :salary,:job' ;

EXECUTE IMMEDIATE v_sql_statementUSING v_empno RETURN INTO v_salary,v_job;

• 示例2：删除数据，取得删除前的结果

 

DECLARE

v_sql_statement VARCHAR2(200) ; --定义SQL操作语句

v_emprow emp%ROWTYPE ; --保存emp类型

v_empno emp.empno%TYPE := 7369 ; --删除的雇员编号

v_ename emp.ename%TYPE ; --删除的雇员姓名

v_sal emp.sal%TYPE ; --删除的雇员工资

BEGIN

v_sql_statement := 'DELETE FROM emp WHERE empno=:enoRETURNING ename,sal INTO :name,:sal' ;

EXECUTE IMMEDIATE v_sql_statement USING v_empnoRETURNING INTO v_ename,v_sal ;

DBMS_OUTPUT.put_line('删除的雇员编号：' || v_empno || '，姓名：' || v_ename || '，工资：' || v_sal) ;

END ;

/

运行结果：
删除的雇员编号：7369，姓名：，工资：

分析：在进行数据删除时，SQL语句使用RETURNING INTO将要删除雇员的姓名及职位赋值给两个绑定变量(:name,:sal)，然后在使用EXECUTE IMMEDIATE时通过RETURNING INTO将已删除雇员的姓名及工资赋值给v_ename和v_sal这两个变量

• 示例3：编写部门增加过程

CREATE OR REPLACE PROCEDURE dept_insert_proc(

p_deptno IN OUT dept.deptno%TYPE , --此处可以将p_deptno的内容回传

p_dname dept.dname%TYPE, --默认为IN模式

p_loc dept.loc%TYPE) AS --默认为IN模式

BEGIN

SELECT MAX(deptno) INTO p_deptno FROM dept ; --取得最大的deptno内容

p_deptno := p_deptno + 1 ; --让最大值部门编号加1，此处不考虑超过2位数字情况

INSERT INTO dept(deptno,dname,loc) VALUES (p_deptno,p_dname,p_loc) ;

END ;

/

本过程要传递3个参数，其中对于部门编号（p_deptno）采用了IN OUT模式，所以此值可以传回到调用处，在过程里首先将查询最大的部门编号。之后依次部门编号为基础进行加1的增长，这样就可以产生一个新的部门编号，并将此部门编号保存在dept表中

• 接上例：编写PL/SQL块，调用过程

DECLARE

v_sql_statement VARCHAR2(200) ;

v_deptno dept.deptno%TYPE ;

v_dname dept.dname%TYPE := 'MLDN' ;

v_loc dept.loc%TYPE := '北京' ;

BEGIN

v_sql_statement := 'BEGIN

dept_insert_proc(:dno , :dna , :dl) ;

END ;' ; --定义PL/SQL块

EXECUTE IMMEDIATE v_sql_statement USING IN OUT v_deptno , IN v_dname , v_loc ;

DBMS_OUTPUT.put_line('新增部门编号为：' || v_deptno) ;

END ;

/

此操作使用USING传递被绑定的参数，由于在dept_insert_proc()过程中的第一个参数采用了IN OUT模式，所以可以接收部门增加后的部门编号数据