假如要实现以下的一个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)使用。