PL/SQL动态SQL

动态SQL是一种在PL/SQL运行时生成和执行SQL语句的编程方法，为PL/SQL编程提供了很大的灵活性。主要应用于一些静态SQL无法支持的操作场景，例如DDL，又或者是一些需要传入参数的SQL语句。 动态 SQL 使用 EXECUTE IMMEDIATE 语句处理大多数动态 SQL 语句。如果动态 SQL 语句是返回多行的 SELECT 语句，将 EXECUTE IMMEDIATE 语句与 BULK COLLECT INTO 子句一起使用。动态SQL传入参数使用USING子句。 示例： 1、使用USING字句给动态SQL传入参数

CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);


create or replace procedure p_sql is
    v_sql varchar(500);
    vret number;
    vin number:=1111;
begin
    perform dbms_output.serveroutput('t');
    v_sql:='select sal from emp where empno=:id';
    execute immediate v_sql into vret using 7521;
    perform dbms_output.put_line(vret);
   -- raise notice 'vret=%',vret;
end;
/
call p_sql();

2、SELECT返回多条记录时，使用 BULK COLLECT INTO 字句。

create table ascii_t(id int, c1 varchar2(100), c2 char(100), c3 nchar(100), c4 nvarchar2(100), c5 clob, c6 nclob, c7 number, c8 smallint, c9 date, c10 timestamp);
insert into ascii_t values(1, 'abc', 'eqw', chr(20)||'da', chr(30)||'==', '而我却', chr(465)||'饿我去的', 465.89, -100, '2021-07-06 10:15:12', to_timestamp('2021-07-06 14:38:48.680297', 'yyyy-mm-dd HH24:mi:ss.ff'));
insert into ascii_t values(2, '中国', '大苏打', chr(125)||'ddsaa', chr(120)||'==', 'daewq', chr(879)||'大苏打ew', 1.89, 128,'2020-08-06 10:15:12', to_timestamp('2021-07-06 14:38:48.680297', 'yyyy-mm-dd HH24:mi:ss.ff'));
insert into ascii_t values(3, '四届', 'eqw', chr(458)||'打算', chr(654)||'==', '打算而我却', chr(135)||'而我打算eqw', 0.89, 0, '2020-08-06 10:15:12', to_timestamp('2021-07-08 14:38:48', 'yyyy-mm-dd HH24:mi:ss'));
insert into ascii_t values(4, 'abc', '而且我给v的', chr(20)||'da', chr(445)||'==', '和梵蒂冈', chr(135)||'aeq4556大', -895.89, 11, '2021-08-06 10:15:12', to_timestamp('2021-08-08 14:38:48', 'yyyy-mm-dd HH24:mi:ss'));
insert into ascii_t values(5, 'abewqe', 'eqw', chr(34)||'恶趣味', chr(102)||'==', '日期', chr(798)||'321', -7895.89, 22, '2020-09-06 10:15:12', to_timestamp('2021-07-09 14:38:48', 'yyyy-mm-dd HH24:mi:ss'));
insert into ascii_t values(6, '打算', 'dasdas', chr(38)||'大师的人情味', chr(128)||'==', '发', chr(4565)||'', 7851.89, -56, '2020-10-06 10:15:12', to_timestamp('2021-10-08 14:38:48', 'yyyy-mm-dd HH24:mi:ss'));
-- 插入空值
insert into ascii_t values(7, '', 'dasdas','', chr(128)||'==', '发', chr(4565)||'', '', 127, '', '');
insert into ascii_t values(8, '打算', '', chr(38)||'大师的人情味', '' ,'发', '', 7895.89, '', SYSDATE, '');
insert into ascii_t values(9, '', 'dasdas', '', '', '', chr(4565)||'', 7895.89, '', '', to_timestamp('2021-10-08 14:38:48', 'yyyy-mm-dd HH24:mi:ss'));

create or replace procedure ascii_pro(col varchar) is
  type ascii_into is table of number;
  var_c1 ascii_into;
  v_sql varchar2(200);
begin
    v_sql := 'select ascii('||col||') from ascii_t order by id;';
    execute immediate v_sql bulk collect into var_c1;
    /*输出雇员信息*/
    for v_index in var_c1.first .. var_c1.last loop
      raise notice '%','ascii:'||var_c1[v_index];
    end loop;
end;
/
call ascii_pro('c2');

3、动态执行CREATE TABLE

create table emp1 as select * from emp;
create table dept_tmp (deptno number(2),dname varchar2(14),loc varchar2(13));
create or replace package pkg1 is
procedure raise_salary(v1 number,v2 number);
end;
/
create or replace package body pkg1 is
procedure raise_salary(v1 number,v2 number) is
begin
   update emp1 set sal=sal+v2 where empno=v1;
end;
end;
/
DECLARE
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   salary      NUMBER(7,2);
   dept_id     NUMBER(2) := 50;
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE using_t (id NUMBER, amt NUMBER)';
   sql_stmt := 'INSERT INTO dept_tmp VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
  -- plsql_block := 'BEGIN pkg1.raise_salary(:id, :amt); END;';
   plsql_block:='call pkg1.raise_salary(:id, :amt)';
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;
  -- execute immediate v_sql using in vin, in vret;
--    sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
--       RETURNING sal INTO :2';
--    EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
   EXECUTE IMMEDIATE 'DELETE FROM dept_tmp WHERE deptno = :num'
      USING dept_id;
   EXECUTE IMMEDIATE 'ALTER table dept_tmp add  c1 number';
END;
/