PL/SQL之七 动态SQL

1.为什么要使用动态SQL？

    原因：PL/SQL语句块，如存储过程，入参参数不固定，存储过程内部需要根据入参不同执行不同条件的SQL。此时需要用到动态SQL（依据入参拼接不同的SQL）。

2.怎么使用动态SQL？ 

2.1 基础语法

    EXECUTE IMMEDIATE dynamic_sql_string [into defined_variable1,defined_variable2,.....]
        [using [in|out|in out] bind_argument1,bind_argument2,....] 
        [{Returning|Return} field1,field2,...into bind_argument1,bind_argument2,....]

        说明：dynamic_sql_string:SQL语句或PL/SQL语句块。

              into子句：包含预定义变量的列表，保存select语句的返回值。

              using子句：包含绑定参数列表，参数值会传递到动态SQL语句或PL/SQL块，其中"in|out|in out"是绑定参数的模式，未指定时默认为IN模式。

              returning或return子句：包含绑定参数列表，存储动态SQL语句或PL/SQL块所返回的值，未指定模式绑定参数模式默认为IN模式。

        注意：Execute Immediate语句包含using子句和returning into子句时，using子句只能指定为IN模式。

2.2 Execute immediate SQL脚本

        示例：  

declare
      sql_stmt varchar2(100);
      v_zip varchar2(5):='11106';
begin
      sql_stmt:='create table my_student '||'as select * from student where zip='||v_zip;
      execute immediate sql_stmt;
end;

如果改写成这样: 

declare
      sql_stmt varchar2(100);
      v_zip varchar2(5):='11106';
begin
      sql_stmt:='create table my_student '||'as select * from student where zip=:1';
      execute immediate sql_stmt using v_zip; --此时会报错，提示ORA-01027 在数据定义操作中不允许有绑定变量。因为DDL语句不能接收任何绑定参数。
end

2.3 Execute immediate SQL脚本  into 执行结果存放区域  using 绑定变量

declare
      v_total_students number;
begin
      execute immediate 'select count(*) from my_student where student_id=:1' into v_total_students using 265;
      dbms_output.put_line('students added:'||v_total_students);    
end;

2.4 Execute immediate SQL脚本 using 绑定变量 returning into 返回参数

declare
      sql_stmt varchar2(100);
      v_new_zip varchar2(5);v_new_student_id number;
      v_student_id number:=151;
begin
      sql_stmt:=' update my_student set zip=11105 where student_id=:1'||'returning zip into :2';
      execute immediate sql_stmt using v_student_id returning into v_new_zip;
      dbms_output.put_line('更新的zipcode是:'||v_new_zip);   
      
      sql_stmt:='delete my_student where student_id=:1'||'returning student_id into :2';
      execute immediate sql_stmt using 151 returning into v_new_student_id;
      dbms_output.put_line('你刚刚删除的ID是：'||v_new_student_id);    
end;

3.注意事项

    3.1 动态SQL中的不能有绑定变量，否则会报错"ORA-01027 在数据定义操作中不允许有绑定变量"。

    3.2 动态SQL中不能将模式对象名称作为绑定参数传递给动态SQL语句。

    3.3 动态SQL要传入NULL值时，声明部分先定义一个字符型变量不赋值，默认为NULL，再通过using 此变量，给动态SQL传NULL值。

    3.4 除了EXECUTE IMMEDIATE方法外，还可以使用'DBMS_SQL'包达到类似功能。