达梦数据库-使用PL/SQL实现动态SQL

        动态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语句可显著转移所有开发努力的关注点。可以将任何分解成一组较小的子任务，然后一次解决一个子任务，而不是试图找到一个通用的解决方案。虽然付出了代价，但是获得了很大的灵活性，而且后者往往是更重要的。

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台