-
它是做什么的?
- 作用:该语句可以方便地在PL/SQL程序中执行DML(insert,upate,delete,单列select),DDL(create,alter,drop),DCL(GRANT,REVOKE)语句;
- 语法:
EXECUTE IMMEDIATE 动态SQL字符串 [[BULK COLLECT] INTO 自定义变量,.....| 记录类型] |
该语句由以下3个主要子句组成: |
注意:在使用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; |
-
示例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),然后将要更新的部门编号及部门名称分别保存在这两个嵌套表中,最后采用循环的方式利用 |
- 示例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_statement INTO 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 USING v_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=:eno RETURNING sal,job INTO :salary,:job' ; EXECUTE IMMEDIATE v_sql_statement USING 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=:eno RETURN sal,job INTO :salary,:job' ; EXECUTE IMMEDIATE v_sql_statement USING v_empno RETURN INTO v_salary,v_job ; |
-
示例2:删除数据,取得删除前的结果
| ||
分析:在进行数据删除时,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模式,所以可以接收部门增加后的部门编号数据 |