1．静态SQL与动态SQL
Oracle编译PL/SQL程序块分为两个种：一种是，SQL语句在程序编译期间就已经确定，大多数的编译情况属于这种类型，如：静态SQL；另外一种是SQL语句只有在运行阶段才能建立，例如当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。如：动态SQL。
本文主要就动态SQL的基础做总结。
2．使用EXECUTE IMMEDIATE语句处理相关语句：
动态SQL是一种”不确定”的SQL，那其执行就有其相应的特点。Oracle中提供了Execute immediate语句来执行动态SQL，语法如下：
Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;
- USING 子句给动态语句传值
例如：
declare
l_str1 varchar2(20) := 'hello';
l_str2 varchar2(10) := 'world';
begin
execute immediate 'insert into t_str values (:1, :2, :3)'
using 50, l_str1 , l_str2 ;
commit; -----一定要显示提交
end;
4.INTO子句从动态语句检索值
例如：
declare
v_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp' into v_cnt ;
dbms_output.put_line(v_cnt );
end;
5.传递并检索值.INTO子句用在USING子句前
例如：
declare
v_empno pls_integer := 20;
v_ename varchar2(20);
v_esex varchar2(20);
begin
execute immediate 'select ename, esex from emp where empno = :1'
into v_ename,v_esex
using v_empno ;
end;
6.输出参数returning into 子句用在USING子句后
例如：
create or replace procedure update_data(stuid varchar2, age number)
as
strSQL varchar2(1000);
strID varchar2(50);
strName varchar2(50);
strSex varchar2(50);
begin
strSQL := 'update tb_student set age=:a where id=:b returning id, name, sex into :c, :d, :e';
execute immediate strSQL using age, stuid returning into strID, strName, strSex;
execute immediate 'commit'; -- 这样也是可以的
dbms_output.put_line('ID:' || strID || ' ;Name:' || strName || ' ;Sex:' || strSex);
end;
在上面的代码中，:a、:b、:c、:d和:e都是占位符，占位符必须以冒号开始，名字无所谓。使用了占位符以后，就需要在execute immediate语句后面使用using将参数传递进去，参数将与占位符一一对应。但是有一点需要谨记，绑定参数不能是表名、列名、数据类型等，绑定参数只能是值、变量或者表达式。用DDL语句动态创建对象时，应该使用连接运算符||，最好不要使用绑定参数。
另外上述代码中还使用了一个returning into的关键语句，returning into语句的主要作用是：
delete操作：returning返回的是delete之前的结果
insert操作：returning返回的是insert之后的结果
update操作：returning语句是返回update之后的结果
7．通过游标实现多行查询的SELECT语句
例如：
declare
type ref_cur is ref cursor;
rc ref_cur;
seriesrow t_Md_Vehicle_Series%rowtype;
v_sql varchar2(500):='select * from t_Md_Vehicle_Series m where m.vehicle_make_id=:makeid';
v_sql2 varchar2(500);
type tb_model_type is table of t_md_vehicle_model%rowtype;
model_array tb_model_type;
begin
DBMS_OUTPUT.ENABLE(100000);
open rc for v_sql using 'CN001';
loop
FETCH rc INTO seriesrow;
EXIT WHEN rc%NOTFOUND;
dbms_output.put_line('name:'||seriesrow.vehicle_series_name||'--------------> id:'||seriesrow.vehicle_series_id);
v_sql2:='select * from t_md_vehicle_model m where m.vehicle_series_id=:seriesid and m.valid_flag=1';
execute immediate v_sql2 bulk collect into model_array using seriesrow.vehicle_series_id;
for i in model_array.first .. model_array.last loop
dbms_output.put_line('ID:' || model_array(i).vehicle_sub_model_id
|| '--------------> Name:' || model_array(i).vehicle_sub_model_name );
end loop;
end loop;
CLOSE rc;
end;
输出结果：
image.png
上述语句中，用带有子句bulk collect into的execute immediate语句。采用bulk collect into可以将查询结果一次性地加载到集合中，可以在select into、fetch into、returning into语句中使用bulk collect into；但是需要特别注意的是，在使用bulk collect into时，所有的into变量都必须是集合类型。
网友评论