动态SQL允许动态地建立和执行SQL和PL/SQL。也可以将对象集合和动态SQL联合起来使用。结合这些特征意味着搜索的结果可以表示为对象集合的输出,并且动态生成搜索查询可表达特定的搜索标准。
针对每一种情况创建定制的SQL语句,而不是建立单一的适用于所有可能的搜索排列的通用SQL语句,会更加有效。
1、创建查询输出格式:
假设需要根据员工姓名,员工ID,员工身份过滤员工。
create type emp_search_ot as object (empno_nr number,
empno_dsp varchar2(256),
comp_nr number);
create type emp_search_nt is table of emp_search_ot;
使用create type语句将emp_search_nt定义成SQL类型。SQL对象集合类型可以通过使用内置的table函数转换成一种规则的SQL集合。
2、创建函数
create or replace function f_search_tt
(i_empno number:=NULL, i_ename_tx varchar2:=NULL, i_loc_tx number:=NULL,
i_limit_nr number:=50)
return emp_search_nt is
v_out_tt emp_search_nt:=emp_search_nt();
v_from_tx varchar2(32767):='dmhr.employee';
v_where_tx varchar2(32767):='rownum<=v_limit_nr';
v_plsql_tx varchar2(32767);
begin
if i_empno is not null then
v_where_tx:=v_where_tx||chr(10)||'and dmhr.employee.employee_id=v_empno_nr';
end if;
if i_ename_tx is not null then
v_where_tx:=v_where_tx||chr(10)||'and dmhr.employee.employee_name like ''%''||v_ename_tx||''%''';
end if;
if i_loc_tx is not null then
v_from_tx:=v_from_tx||chr(10)||'join dmhr.department on (dmhr.employee.department_id=dmhr.department.department_id)';
v_where_tx:=v_where_tx||chr(10)||'and dmhr.department.location_id=v_loc_tx';
end if;
v_plsql_tx:=
'declare '||chr(10)||
'v_limit_nr number:=:1;'||chr(10)||
'v_empno_nr number:=:2;'||chr(10)||
'v_ename_tx varchar2(256):=:3;'||chr(10)||
'v_loc_tx number:=:4;'||chr(10)||
'begin '||chr(10)||
'select emp_search_ot('||
'dmhr.employee.employee_id, dmhr.employee.employee_name||''(''||dmhr.employee.job_id||'')'','||
'dmhr.employee.salary+nvl(dmhr.employee.commission_pct,0))'||chr(10)||
'bulk collect into :5'||chr(10)||
'from '||v_from_tx||chr(10)||
'where '||v_where_tx||';'||chr(10)||
'end;';
dbms_output.put_line('<<Script that was executed>>'||chr(10)||v_plsql_tx);
execute immediate v_plsql_tx using
in i_limit_nr, in i_empno, in i_ename_tx, in i_loc_tx, out v_out_tt;
return v_out_tt;
end;
说明:
a、表employee一直在使用,而表department只有在指定工作位置location_id时才会连接使用。
b、emp_search_ot构造函数必须包含在查询中,因为输出结果是对象集合而不是记录。
3、测试基本搜索
SQL> select * from table(f_search_tt(1001));
<<Script that was executed>>
declare
v_limit_nr number:=:1;
v_empno_nr number:=:2;
v_ename_tx varchar2(256):=:3;
v_loc_tx number:=:4;
begin
select emp_search_ot(dmhr.employee.employee_id, dmhr.employee.employee_name||'('||dmhr.employee.job_id||')',dmhr.employee.salary+nvl(dmhr.employee.commission_pct,0))
bulk collect into :5
from dmhr.employee
where rownum<=v_limit_nr
and dmhr.employee.employee_id=v_empno_nr;
end;
行号 EMPNO_NR EMPNO_DSP COMP_NR
---------- -------- ---------- -------
1 1001 马学铭(11) 30000
已用时间: 5.803(毫秒). 执行号:18413.
SQL> select * from table(f_search_tt(NULL,'张',3));
<<Script that was executed>>
declare
v_limit_nr number:=:1;
v_empno_nr number:=:2;
v_ename_tx varchar2(256):=:3;
v_loc_tx number:=:4;
begin
select emp_search_ot(dmhr.employee.employee_id, dmhr.employee.employee_name||'('||dmhr.employee.job_id||')',dmhr.employee.salary+nvl(dmhr.employee.commission_pct,0))
bulk collect into :5
from dmhr.employee
join dmhr.department on (dmhr.employee.department_id=dmhr.department.department_id)
where rownum<=v_limit_nr
and dmhr.employee.employee_name like '%'||v_ename_tx||'%'
and dmhr.department.location_id=v_loc_tx;
end;
行号 EMPNO_NR EMPNO_DSP COMP_NR
---------- -------- ---------- -------
1 3104 张强升(12) 9151
2 3114 张礼榕(22) 4354
3 3139 张千桐(32) 16614
4 3143 张立军(32) 16653
5 3163 张传英(42) 4862
6 3172 张日云(52) 10954
7 3173 张赵丽(52) 10964
8 3175 张静怡(52) 10984
9 3176 张浩(52) 10999
10 3181 张珍(52) 11143
11 3183 张先林(52) 11162
行号 EMPNO_NR EMPNO_DSP COMP_NR
---------- -------- ---------- -------
12 3213 张君洪(42) 5998
12 rows got
已用时间: 4.374(毫秒). 执行号:18414.
SQL> select * from table(f_search_tt(NULL,'张',3,2));
<<Script that was executed>>
declare
v_limit_nr number:=:1;
v_empno_nr number:=:2;
v_ename_tx varchar2(256):=:3;
v_loc_tx number:=:4;
begin
select emp_search_ot(dmhr.employee.employee_id, dmhr.employee.employee_name||'('||dmhr.employee.job_id||')',dmhr.employee.salary+nvl(dmhr.employee.commission_pct,0))
bulk collect into :5
from dmhr.employee
join dmhr.department on (dmhr.employee.department_id=dmhr.department.department_id)
where rownum<=v_limit_nr
and dmhr.employee.employee_name like '%'||v_ename_tx||'%'
and dmhr.department.location_id=v_loc_tx;
end;
行号 EMPNO_NR EMPNO_DSP COMP_NR
---------- -------- ---------- -------
1 3104 张强升(12) 9151
2 3114 张礼榕(22) 4354
已用时间: 4.039(毫秒). 执行号:18415.
连接是动态创建,即只有在需要的时候才严格按照要求创建。这个例子说明一个概念,最好的调优方法是什么都不做,除非某个行动是不可避免的。它还指出,动态地构建SQL语句可显著转移所有开发努力的关注点。可以将任何分解成一组较小的子任务,然后一次解决一个子任务,而不是试图找到一个通用的解决方案。虽然付出了代价,但是获得了很大的灵活性,而且后者往往是更重要的。