--判断当前登录人是否属于(事业部会计查询、事业部查询)
--校验结果语句
--传参 username (例如:username='ymling')
SELECT COUNT(1)
FROM sn_ucodecfg.wbcode w, sn_ucodecfg.users u
WHERE w.wcode = u.wcode
AND (w.gwcodes = 'N9XYGG00000183' OR w.gwcodes = 'N9XYGG00000184')
AND u.username='zwfeng'
--校验结果记录>0,取所有法人组织
SELECT DISTINCT b.bcode AS corpbcode
FROM bcode b, sn_ucodecfg.mbcode m
WHERE b.bcode = m.bcode
AND m.dtype = '02';
--校验结果记录=0,按原逻辑取对应权限下法人组织
--传参 username (例如:username='ymling')
SELECT bcode
FROM sn_ucodecfg.xydatalimsum
WHERE dtype = '02'
AND username = 'zwfeng'
需求如上:通过执行验证语句,然后再判断放回哪个sql的数据
方式一: with as
with..as关键字,是以‘with’关键字开头的sql语句,在实际工作中,我们经常会遇到同一个查询sql会同时查询多个相同的结果集,即sql一模一样,这时候我们可以将这些相同的sql抽取出来,使用with..as定义。with..as相当于一张中间表,可以简单理解为sql片段(类似java复用代码)。可认为在真正进行查询之前预先构造了一个临时表,之后便可多次使用它做进一步的分析和处理。
简单例子:
with temp as (select a from tableA)
select * from temp;
temp就相当于 select a from tableA
使用with as,实现 就是把后面的两个结果集union,然后把验证的sql结果当作查询条件
with temp as (SELECT decode(COUNT(1),0,0,1) as num
FROM sn_ucodecfg.wbcode w, sn_ucodecfg.users u
WHERE w.wcode = u.wcode
AND (w.gwcodes = 'N9XYGG00000183' OR w.gwcodes = 'N9XYGG00000184')
AND u.username='zwfeng'--传入用户名
)
select x.corpbcode from (SELECT DISTINCT b.bcode AS corpbcode,1 as num
FROM bcode b, sn_ucodecfg.mbcode m
WHERE b.bcode = m.bcode
AND m.dtype = '02'
union
SELECT bcode as corpbcode,0 as num
FROM sn_ucodecfg.xydatalimsum z,temp t
WHERE dtype = '02'
AND username = 'zwfeng'--传入用户名
) x,temp y where x.num = y.num --y.num 就是验证sql的结果
第二种方式:
select* from (SELECT DISTINCT b.bcode AS corpbcode
FROM bcode b, sn_ucodecfg.mbcode m
WHERE b.bcode = m.bcode
AND m.dtype = '02'
and 1 = (SELECT decode(count(1),0,0,1) from sn_ucodecfg.wbcode w, sn_ucodecfg.users u WHERE w.wcode = u.wcode
AND (w.gwcodes = 'N9XYGG00000183' OR w.gwcodes = 'N9XYGG00000184') and u.username ='zwfeng')
union
SELECT bcode
FROM sn_ucodecfg.xydatalimsum x
WHERE dtype = '02' and x.username = 'zwfeng' and 0 = (SELECT decode(count(1),0,0,1) from sn_ucodecfg.wbcode w, sn_ucodecfg.users u WHERE w.wcode = u.wcode
AND (w.gwcodes = 'N9XYGG00000183' OR w.gwcodes = 'N9XYGG00000184') and u.username = 'zwfeng'))
第三种方式:
使用 oracle pipelined 返回一个结果集;
create or replace type MsgType as table of varchar2(4000);
create or replace function f_pipeline_test return MsgType
PIPELINED as
begin
for i in 1 .. 10 loop
pipe row(i);
end loop;
pipe row('All done!');
return;
end;
查询结果:select * from table(f_pipeline_test())