动态SQL¶

OSCAR编译PLOSCAR程序块分为两种：前期编联，即SQL语句在程序编译期间就已经确定，大多数的编译情况属于这种类型；另外一种是后期编联，即SQL语句只有在运行阶段才能建立。例如当查询条件为用户输入时，此时OSCAR的SQL引擎无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。OSCAR中的静态SQL采用前期编联方式，动态SQL采用后期编联方式。

动态SQL是通过EXECUTE IMMEDIATE语句来执行的不确定的SQL语句。

动态SQL用USING命令绑定参数列表，用RETURNING INTO命令输出参数列表。

当动态SQL返回多行结果时，且此时不使用USING或RETURNING INTO命令输出。PLOSCAR默认将多行结果输出。

说明：

动态SQL是指DDL和不确定的DML（即带参数的DML）
USING语句绑定参数列表为输入参数列表，在运行时刻与动态SQL语句中以占位符形式出现的参数进行绑定。
输出参数列表为动态SQL语句执行后返回的参数列表。

由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，会损失一些系统性能来换取其灵活性。

在编写动态SQL语句时，尽量使用类似SQL的语句，这样OSCAR可以通过本身的缓存池进行缓存，在下一次运行类似的语句的时候就可以直接调用缓存池中也解析过的语句，以此来提高效率。

当涉及到集合单元的时候，尽量使用批编联，比如FORALL语句、BULK COLLECT子句等。

示例1:返回单结果集¶

--  清理环境
DROP TABLE tab CASCADE;
DROP PROCEDURE proc;

create table tab
(
    a int,
    b varchar(20),
    c date
);
insert into tab values(1,'wang','2010-03-23');
insert into tab values(2,'wang','2010-03-23');
insert into tab values(3,'wang','2010-03-23');

create or replace procedure proc(a1 int) as
    v1 int;
    v2 varchar(20);
    v3 date := '1986-07-29';
begin
    execute immediate 'update tab set c = :1 
        where a = :2 Returning a,b into :3,:4'
        using v3,a1 returning into v1,v2;
    dbms_output.put_line(v1 || ' ' || v2 || ' ' || v3 || ' return' || v1 || ' ' || v2);
end;

/
exec proc(1);

--删除过程
DROP PROCEDURE proc;
DROP TABLE tab CASCADE;

示例2:返回多结果集¶

动态SQL返回多行结果

--  清理环境
DROP TABLE tab CASCADE;
DROP PROCEDURE proc;

create table tab
(
    a int,
    b varchar(20),
    c date
);
insert into tab values(1,'wang','2010-03-23');
insert into tab values(2,'wang','2010-03-23');
insert into tab values(3,'wang','2010-03-23');

create or replace procedure proc as
declare   
   type type1 is record (a int, b varchar(20), c date);   
   type type2 is table of type1;   
   tr1 type2; 
begin   
   execute immediate 'select * from tab order by a, b, c' bulk collect into tr1;
   dbms_output.put_line(tr1(1).a || ' ' || tr1(1).b || ' ' || tr1(1).c);
   dbms_output.put_line(tr1(2).a || ' ' || tr1(2).b || ' ' || tr1(2).c);
   dbms_output.put_line(tr1(3).a || ' ' || tr1(3).b || ' ' || tr1(3).c);
end;

/
exec proc;
1 wang 2010-03-23
2 wang 2010-03-23
3 wang 2010-03-23

--删除过程
DROP PROCEDURE proc;
DROP TABLE tab CASCADE;