Oracle存储函数--递归查询介绍
背景说明
根据业务需要,项目组长要求在Oracle数据库中对一系列数据进行查找,前提是要用到日期(yyyy-MM-dd)查询条件,如果查询到数据的数据为空,则日期减1天继续查找,反复循环...直到查询到数据为止。SELECT tb.DATA1,tb.DATA2 FROM TABLE tb WHERE tb.DATE='2018-12-07'
很显然,上述SQL代码段只能查询对应日期的数据,即使查询到的数据为空
初步思路
假设所查询的日期刚好有数据,则不需要进行一系列麻烦的操作,可直接进行SQL查询,如果你的运气不是那么好,那么用刚才的SQL会徒劳无功既然日期条件会被多次用到,为何不写一个存储函数供多次调用呢
解决方案
1、存储函数Fun_zjdt的建立
--创建存储函数,判断传入的条件(日期和subId)所查询到的总数是否大于0
create or replace function Fun_zjdt(daily In varchar2,subId In varchar2) return varchar2 is userfulDate varchar2(100);
--创建Fun_zjdt存储函数,并声明接收数据的变量名称(userfulDate )和类型(varchar2(100))
Begin
Select (
SELECT
CASE
WHEN COUNT (days.weteam_subid) = 1
THEN daily
ELSE Fun_zjdt(to_char(to_date(daily,'yyyy-MM-dd')-1,'yyyy-MM-dd'),subId)
END
--如果查询到的总数大于0就返回当前日期,否则日期-1递归执行该存储函数
FROM drcd_aac_yerg_sd days
LEFT JOIN ccd_cse_sop_drant ccsd ON ccsd.cogd_id=days.word_pide
WHERE
days.bsfee='0' AND
to_char(days.daily_date,'yyyy-MM-dd') = daily and
days.weram_subid LIKE concat(subId,'%')
)Into userfulDate --有数据的日期
From dual;
Return(userfulDate); --返回有数据日期
End Fun_zjdt; --结束函数
上述就是存储函数的创建
接下来测试存储函数是否可用(说明:执行速度取决于当前查询日期与有数据日期间隔的长短,间隔越大则运行速度越慢,反之越快)
select Fun_zjdt('2302-06-01','C1') from dual;
执行结果
2、SQL对存储函数的调用
SELECT
COUNT (days.weteam_subid) TOTAL,
COUNT (days.weteam_subid)-COUNT (CASE WHEN ccsd.coodqe LIKE '01%' THEN 1 ELSE NULL END) DY,
COUNT (CASE WHEN ccsd.coodqe LIKE '04%' THEN 1 ELSE NULL END) BA,
COUNT (CASE WHEN ccsd.coodqe LIKE '06%' THEN 1 ELSE NULL END) ZJ,
COUNT (CASE WHEN ccsd.coodqe LIKE '05%' THEN 1 ELSE NULL END) WJ,
COUNT (CASE WHEN ccsd.coodqe LIKE '07%' THEN 1 ELSE NULL END) CZ,
COUNT (CASE WHEN ccsd.coodqe LIKE '02%' THEN 1 ELSE NULL END) FZ,
COUNT (CASE WHEN ccsd.coodqe LIKE '03%' THEN 1 ELSE NULL END) SG,
COUNT (CASE WHEN ccsd.coodqe LIKE '01%' THEN 1 ELSE NULL END) TG,
COUNT (CASE WHEN ccsd.coodqe LIKE '09%' THEN 1 ELSE NULL END) TC,
COUNT (CASE WHEN ccsd.coodqe LIKE '10%' THEN 1 ELSE NULL END) QT,
FROM drill_dy_days days
LEFT JOIN ccd_cse_sop_drant ccsd ON ccsd.cogd_id=days.word_pide
WHERE
to_char(days.daily_date,'yyyy-MM-dd') = (select Fun_zjdt('2302-05-11','C1') from dual) and
days.weteam_subid LIKE 'C1%'
ORDER BY days.weteam_subid
OK,就这样吧!