Oracle使用EXECUTE IMMEDIATE命令动态执行SQL语句
动态执行SQL语句指先把一个SQL命令保存到一个字符串中,然后通过execute immediate命令动态执行字符串中的SQL语句,以实现SQL语句的动态生成。
一、动态SQL语句的使用方式
DECLARE
字符串变量名 varchar2(n); --定义一个字符串类型的变量,用以保存SQL语句
BEGIN
字符串变量名:=SQL命令; --把SQL命令保存到字符串变量中
EXECUTE IMMEDIATE 字符串变量名
using 绑定参数列表
returning into 输出参数列表;; --执行字符串中的SQL语句
END;
/
说明:
(1)EXECUTE IMMEDIATE将不会提交一个DML事务执行,应该显式提交。
(2)如果通过EXECUTE IMMEDIATE处理DML命令,那么在完成以前需要显式提交,如果通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据;
(3)不支持返回多行的查询,这种交互可以用临时表来存储记录或者使用动态游标REF cursors;
(4)当执行SQL语句时,不要用分号,当执行PL/SQL块时,在其尾部用分号。
二、动态调用SQL语句举例
1、通过动态SQL执行DDL语句
创建一个存储过程sp_create_table,调用该存储过程创建一张数据表,代码如下:
SQL>
create or replace procedure sp_create_table
as
v_table_name varchar2(100);
v_sql_str varchar2(500);
begin
select 't'||to_char(sysdate,'yyyymmddhh24miss')
into v_table_name
from dual;
v_sql_str:='create table '||v_table_name||'('||'
id number(4) primary key,
name varchar2(50),
phone varchar2(20),
addr varchar2(200)'||
')';
execute immediate v_sql_str;
end;
17 /
Procedure created.
调用该存储过程,并查看结果:
SQL> call sp_create_table();
Call completed.
SQL> select * from tab where tname like 'T2020%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T20200217205344 TABLE
T20200217205804 TABLE
查看表结构:
SQL> desc T20200217205804;
Name Null? Type
-------------------------------- -------- ------------------------------------------------
ID NOT NULL NUMBER(4)
NAME VARCHAR2(50)
PHONE VARCHAR2(20)
ADDR VARCHAR2(200)
2、创建一个动态查询,根据输入的参数查询某个雇员的信息
存储过程的参数为雇员编号,根据传入的雇员编号查询该雇员的姓名和工资,代码如下:
SQL>
create or replace procedure sp_get_emp
(v_empno number)
as
v_ename varchar2(20);
v_sal number(6);
v_str varchar2(100);
begin
v_str:='select ename,sal from emp where empno=:1';
execute immediate v_str into v_ename,v_sal using v_empno;
dbms_output.put_line('姓名:'||v_ename||',工资:'||v_sal);
exception
when no_data_found then
dbms_output.put_line('没有此雇员!');
end;
15 /
Procedure created.
执行该存储过程:
SQL> call sp_get_emp(2222);
没有此雇员!
Call completed.
Elapsed: 00:00:00.01
SQL> call sp_get_emp(7788);
姓名:SCOTT,工资:3011
Call completed.