PL/SQL之七动态SQL

本文主要是介绍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脚本

示例：

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

如果改写成这样:

declaresql_stmt varchar2(100);v_zip varchar2(5):='11106';
beginsql_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 绑定变量

declarev_total_students number;
beginexecute 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 返回参数

declaresql_stmt varchar2(100);v_new_zip varchar2(5);v_new_student_id number;v_student_id number:=151;
beginsql_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;