达梦数据库-用PLSQL合并集合

        简单地加载、卸载集合外，对它们进行操作也是非常必要。考虑到很长一段时间以来，SQL开发一直包含UNION/INTERSECT/MINUS子句，通过用户定义的集合来进行类似的操作。

        MULTISET命令为性能调优提供了可能性。

案例背景：

        常常会遇到对一张表，筛选不同类别用户，通过union的方式合并查询结果。随着应用模块上线，产品功能模块增加，从最初只有4个union最终变成14个union。以此往复，安全结构变得更加复杂，不同子查询的过滤条件开始以奇怪的方式叠加。

        

测试案例：

        #如果用户是总经办，查询返回自己的相关信息；如果用户是人事，查询返回除自己以外其他员工的相关信息。

        #如果用户是总经办或者行政，查询返回其他部门员工的相关信息。

        满足以上两个条件的人员列表可以重叠。由于这个原因，需要由 MULTISET UNION DISTINCT 在最后把一起都合并在一起。

过程测试：

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;

2、创建函数：

create or replace FUNCTION f_attention_ot(i_empno number) RETURN emp_search_nt IS
	v_emp_rec DMHR.EMPLOYEE%ROWTYPE;
	v_out_nt emp_search_nt:=emp_search_nt();
	v_sub_nt emp_search_nt:=emp_search_nt();
	v_comm_nt emp_search_nt:=emp_search_nt();
BEGIN	
	--load information about the logged user
	select * into v_emp_rec from DMHR.EMPLOYEE  where EMPLOYEE_ID=i_empno; 
	--get subordinates
	if v_emp_rec.job_id = '11' then --总经办，查看自己信息
		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 v_sub_nt
		from DMHR.EMPLOYEE 
		where EMPLOYEE_ID=i_empno;
	ELSEIF v_emp_rec.job_id = '72' then --人事，查看除自己以外的信息
		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 v_sub_nt
		FROM DMHR.EMPLOYEE 
		where EMPLOYEE_ID!=i_empno;
	end if;
	
	if v_emp_rec.job_id in ('11','22') then  --总经办和行政，查看其它部门的信息
		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 v_comm_nt
		FROM DMHR.EMPLOYEE 
		where DEPARTMENT_ID != v_emp_rec.DEPARTMENT_ID;
	end if;
	--merge information
	dbms_output.put_line('Merging: '||v_sub_nt.count||'+'||v_comm_nt.count);
	v_out_nt:=v_sub_nt MULTISET UNION DISTINCT v_comm_nt;
	RETURN v_out_nt;
end;

3、结果测试：

select * from table(f_attention_ot(7001));--总经办
select * from table(f_attention_ot(7448));--人事
select * from table(f_attention_ot(7109));--行政

        结果没有什么特别，但所提出的方法清晰地分离了检查当前用户的特权和处理每个条件的核心步骤。Merging输出证明两个集合为了输出而合并在一起。

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