36.动态SQL

一、引言

       前面介绍的所有内容，所操作的数据库表必须存在，否则执行子程序时就会出现问题，这种方法叫做静态SQL。PLSQL中有一种能够定义程序时不指定具体的操作对象，在执行时动态传入对象的技术，叫做动态SQL。

二、动态的创建表格并返回表格行数

       下面的代码，我们定义了一个名叫get_table_count_fun的function，该函数接收一个字符串格式的表名作为参数，首先在user_tables中检查存不存在该表，若不存在就创建表，然后获取表中数据行的个数。

    

create or replace function get_table_count_fun(p_table_name varchar2) return number
as
 v_sql varchar2(200);
 v_count number;
begin
 select count(*) into v_count from user_tables where table_name=upper(p_table_name);
 if v_count=0 then
   v_sql:='create table '||p_table_name||'(empno number,ename varchar2(20) )';
   execute immediate v_sql;
 end if;
 v_sql:='select count(*) from '||p_table_Name;
 execute immediate v_sql into v_count;
 return v_count;
end;
/

        可以看到，我们创建表和查询表的方式都是通过动态sql的方式进行的，v_sql是我们创建表和查询表的sql语句，而execute immediate 后面加v_sql就是执行动态sql的方式，如果是DDL语句或者DML语句，直接执行即可；如果是select语句，需要将插叙结果into给你个变量。定义完成上述函数，我们可以在在plsql代码块中执行调用：

declare
 v_count number;
begin
  v_count:=get_table_count_fun('myemp');
  dbms_output.put_line(v_count);
end;
/

三、设置占位符

        使用PLSQL时，可以在sql语句中设置占位符，设置的方式是使用“:占位符名称”，如下面的例子，我们在数据表myemp中插入一条数据：

declare
  v_empno myemp.empno%type :=1;
  v_ename myemp.ename%type :='hyman';
  v_sql varchar2(100);
begin
  v_sql:='insert into myemp values(:eno,:enm)';
  execute immediate v_sql using v_empno,v_ename;
end;
/

       在为占位符指定数据时，我们只需要在execute immediate语句中使用using关键字添加变量列表即可，执行结果如下：

四、接收DML更新的字段

        动态sql中可以使用returning into来获取DML语句影响的数据字段，如下面的代码，我们修改myemp表中empno为1的姓名，然后获取修改后的姓名并打印。

declare
 v_sql varchar2(100);
 v_name myemp.ename%type;
 v_no myemp.empno%type;
begin
 v_no:=1;
 v_sql:='update myemp set ename=''Liuzhihui'' where empno=:eno returning ename into :enm';
 execute immediate v_sql using v_no returning into v_name;
 dbms_output.put_line(v_name);
end;
/

        可以看出，再使用returning into时需要分别在sql语句和execute immediate中使用，在sql语句中指定需要接受的字段以及变量的占位符，在execute immediate中指定接受的变量，需要特别注意的是在sql语句中的表示字符串时单引号需要用两个单引号表示。

        结果如下：

五、批量接收更新的字段

        在前面的文章中我们使用bulk collection into接收过select的多行数据，在动态sql中，我们也可以使用bulk collection into 接收多行更新的字段，我们先在myemp表中再插入一条数据，之后表中的数据如下：

        我们要实现的效果是，删除表中所有的数据，但是要使用索引表批量接收ename并打印出来：

declare
  type ename_index is table of myemp.ename%type index by pls_integer;
  v_ename ename_index;
  v_sql varchar2(100);
begin
  v_sql:='delete from myemp returning ename into :ena';
  execute immediate v_sql returning bulk collect into v_ename;
  for x in 1..v_ename.count loop
    dbms_output.put_line(v_ename(x));
  end loop;
end;
/

        我们发现同接收单个更新的字段相比，批量接收的唯一区别在于execute immediate子句中需要使用bulk collect into，且接收的对象应该是索引表或者数组。这是批量接收删除的字段，更新和插入类似。接下来我们再来看一个批量接收查询字段的例子。

       首先我们需要再次插入刚才删除的数据，然后使用动态sql进行查询操作并接收查询到的人员的ename：

declare
  type ename_varray is varray(100) of myemp.ename%type;
  v_ename ename_varray;
  v_sql varchar2(100);
begin
  v_sql:='select ename from myemp';
  execute immediate v_sql bulk collect into v_ename;
  for x in 1 .. v_ename.count loop
    dbms_output.put_line(v_ename(x));
  end loop;
end;
/

        从上面的代码可以看出，使用动态sql进行select并接收查询结果时，不再需要returning关键字，因为查询操作本身就会返回一个结果集，而不用使用returning显式的去返回一个结果集。

       上面两段代码的执行结果如下：

六、游标操作中使用动态SQL

        

        游标操作中同样可以使用动态SQL，如下面的代码，我们利用动态SQL的游标操作查询empno为1的雇员信息并打印出来：

declare
  cur_emp sys_refcursor;
  v_emprow myemp%rowtype;
  v_empno myemp.empno%type:=1;
begin
  open cur_emp for 'select * from myemp where empno = :eno' using v_empno;
  loop
     fetch cur_emp into v_emprow;
     exit when cur_emp%notfound;
     dbms_output.put_line(v_emprow.empno||' '||v_emprow.ename);
  end loop;
  close cur_emp;
end;
/

        如果需要批量接收数据，还是利用bulk collect into：

declare
  type emp_table is table of myemp%rowtype index by pls_integer;
  cur_emp sys_refcursor;
  v_emps emp_table;
begin
  open cur_emp for 'select * from myemp';
  fetch cur_emp bulk collect into v_emps;
  close cur_emp;
  for x in 1..v_emps.count loop
    dbms_output.put_line(v_emps(x).empno||' '||v_emps(x).ename);
  end loop;
end;
/