用sys_context 和Native Dynamic SQL实现变量绑定

来源：互联网发布：ipad怎么清除缓存数据编辑：程序博客网时间：2024/06/05 14:21

假如要实现以下的一个Procedure, 这个procedure动态生成一个where clause,然后运行合成后的查询而获得一些查询信息。create or replace procedure do_query(cname_in    dbms_sql.varchar2_table,
                                     operator_in dbms_sql.Varchar2_Table,
                                     value_in    dbms_sql.Varchar2_Table) 。

有两种方法可以实现： 1)dbms_sql 2)natvie dynamic sql
1>如果用dbms_sql, 优点：可以用bind variable, 很少的soft parse.如果此程序被多人多用户使用，可极大的挺高性能。缺点：多去额外的开销。比native dynamic sql多出很多辅助程序。实现如下：

create or replace procedure do_query(cname_in    dbms_sql.varchar2_table,
                                     operator_in dbms_sql.Varchar2_Table,
                                     value_in    dbms_sql.Varchar2_Table) is
v_cursor   int default dbms_sql.open_cursor;
v_query    long;
v_sep      varchar2(20) default 'where ';
v_colvalue varchar2(4000);
v_status   integer;
begin
v_query := 'select empno,ename,sal from emp ';

For i in 1..cname_in.count loop
    v_query := v_query || v_sep || cname_in(i) || ' ' || operator_in(i) || ' ' ||
               ':bv' || i;
    v_sep   := ' and ';
end loop;

dbms_sql.parse(v_cursor, v_query, dbms_sql.native);

For i in 1..cname_in.count loop
dbms_sql.bind_variable(v_cursor, ':bv' || i, value_in(i));
end loop;

for i in 1 .. 3 loop
dbms_sql.define_column(v_cursor, i, v_colvalue, 4000);
end loop;

v_status := dbms_sql.execute(v_cursor);

while (dbms_sql.fetch_rows(v_cursor) > 0) loop
    for i in 1 .. 3 loop
      dbms_sql.column_value(v_cursor, i, v_colvalue);
      dbms_output.put(v_colvalue || '--------');
    end loop;
    dbms_output.new_line;
end loop;
end;

16:45:37 lab@ORCL>declare
17:06:26   2 v_cname dbms_sql.varchar2_table;
17:06:26   3 v_operator dbms_sql.varchar2_table;
17:06:26   4 v_value dbms_sql.varchar2_table;
17:06:26   5 begin
17:06:26   6 v_cname(1) :='ename';
17:06:26   7 v_cname(2) :='deptno';
17:06:26   8
17:06:26   9 v_operator(1) :=' like ';
17:06:26 10 v_operator(2) :=' = ';
17:06:26 11
17:06:26 12 v_value(1) := '%A%';
17:06:26 13 v_value(2) := '40';
17:06:26 14
17:06:26 15 do_query(v_cname,v_operator,v_value);
17:06:27 16 end;
17:06:27 17 /
7599--------ALLEN--------1700--------
7621--------WARD--------1350--------
7754--------MARTIN--------1350--------
7798--------BLAKE--------2950--------
8000--------JAMES--------1050--------

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.03

2>如果用Native Dynamic SQL则有相反的优缺点。不过最大的问题的是： Native Dynamic SQL只能以实际值来拼成一个可执行的sql, 不能使用绑定变量。 Oracle介绍了特性可以帮助解决这一难点： Sys_context;
那么首先来了解一下Sys_context的用法。

17:53:02 sys@ORCL>grant create any context to lab;

授权成功。

已用时间: 00: 00: 00.01
17:53:38 sys@ORCL>conn lab/lab;
已连接。
17:53:49 lab@ORCL>create or replace context bv_context using dyn_demo;

上下文已创建。

已用时间: 00: 00: 00.06

create or replace package dyn_demo
is
procedure do_query2(cname_in    dbms_sql.varchar2_table,
                                      operator_in dbms_sql.Varchar2_Table,
                                      value_in    dbms_sql.Varchar2_Table);
end dyn_demo;

create or replace package body dyn_demo
is
procedure do_query2(cname_in    dbms_sql.varchar2_table,
                                      operator_in dbms_sql.Varchar2_Table,
                                      value_in    dbms_sql.Varchar2_Table) is
type rc is ref cursor;
v_cursor rc;

v_query long;
v_sep varchar2(20) default 'where ';
v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
v_query := 'select empno,ename,sal from emp ';

For i in 1 .. cname_in.count loop
    v_query := v_query || v_sep || cname_in(i) || ' ' || operator_in(i) || ' ' ||
               'sys_context(''BV_CONTEXT'',''' || cname_in(i) || ''')';
    dbms_session.set_context('bv_context',cname_in(i),value_in(i));
    v_sep   := ' and ';
end loop;

open v_cursor for v_query;
loop
    fetch v_cursor
      into v_empno, v_ename, v_sal;
    exit when v_cursor%notfound;
    dbms_output.put_line(v_empno || '--------' || v_ename || '--------' ||
                         v_sal || '--------');
end loop;
close v_cursor;
end do_query2;

begin
null;
end dyn_demo;

--sample data
18:05:37 lab@ORCL>declare
18:09:05   2 v_cname dbms_sql.varchar2_table;
18:09:05   3 v_operator dbms_sql.varchar2_table;
18:09:05   4 v_value dbms_sql.varchar2_table;
18:09:05   5 begin
18:09:05   6 v_cname(1) :='ename';
18:09:05   7 v_cname(2) :='deptno';
18:09:05   8
18:09:05   9 v_operator(1) :=' like ';
18:09:05 10 v_operator(2) :=' = ';
18:09:05 11
18:09:05 12 v_value(1) := '%A%';
18:09:05 13 v_value(2) := '40';
18:09:05 14
18:09:05 15 dyn_demo.do_query2(v_cname,v_operator,v_value);
18:09:05 16 end;
18:09:05 17 /
7599--------ALLEN--------1700--------
7621--------WARD--------1350--------
7754--------MARTIN--------1350--------
7798--------BLAKE--------2950--------
8000--------JAMES--------1050--------

PL/SQL ????????

????: 00: 00: 00.01

综上所述, dynamic SQL相对于静态sql来讲提供更多的扩展性。但是效率相对低。此外还有三大缺点：

破坏了对象之间的依赖关系；
使程序变得更脆弱；
使程序更难被调试。

那么dbms_sql和NDS(Native Dynamic SQL)相比：

dbms_sql使用绑定变量，适用于被经常或大量使用的程序。此时效率高；但代码量更大。在绑定变量个数未知，输出变量个数未知的情况下应用dbms_sql.
NDS代码量小，在使用次数少的时候能减少工作量. 此时运行速度快。有时要搭档open for(ref cursor)使用。