【数据库】Oracle存储函数--递归查询介绍

背景说明

       根据业务需要，项目组长要求在Oracle数据库中对一系列数据进行查找，前提是要用到日期（yyyy-MM-dd）查询条件，如果查询到数据的数据为空，则日期减1天继续查找，反复循环...直到查询到数据为止。PS:必须纯SQL逻辑查询，不得涉及其他程序代码

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
usefulDate VARCHAR2(100);  
--创建Fun_zjdt存储函数，并声明接收数据的变量名称（usefulDate ）和类型（VARCHAR2(100)）
BEGIN
   SELECT (
       SELECT 
          CASE 
              WHEN COUNT (days.weteam_subid) > 0
              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 usefulDate   --有数据的日期装入到usefulDate中
   FROM DUAL;
   RETURN (usefulDate);  --返回有数据日期
End Fun_zjdt;    --结束函数

      上述就是存储函数的创建，接下来测试存储函数是否可用
（说明：执行速度取决于当前查询日期与有数据日期间隔的长短，间隔越大则运行速度越慢，反之越快）

 SELECT  Fun_zjdt('2302-06-01','C1')  FROM  DUAL;

      执行结果
    

2、SQL对存储函数的调用

SELECT 
   COUNT (days.weteam_subid) TOTAL, 
   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 drcd_aac_yerg_sd 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

      另外，递归查询时如果递归的次数超过50时，就会报错；提示（ORA-00036:超过递归SQL级别的最大值50 ）如下图!

      真是印证了那句话：长江后浪推前浪，一浪更比一浪强（难） ：-（
最终经过网上大量资料查询，发现好多前辈们也遇到了此类问题，还是没有比较好的解决方案。持续关注…
继续关注此问题：
      以上递归太过繁琐，并且递归次数超过50时就会报错。
所以说，现在找到了更好的办法：查出小于条件日期的最大的日期作为查询条件

 SELECT MAX(tt.date) AS maxDate FROM tables tt WHERE to_char(tt.date,'yyyy-MM-dd') <= '2018-12-22' 

即可解决以上问题