oracle递归函数学习笔记

1、描述：什么是递归？

start with...connect by prior用于实现递归查询树形结构。如果不好理解proir的向上向下，就记住如果想往上遍历就把上级节点放在prior后，如果想往下遍历反之将下级节点放在prior后。简单粗暴

2、语法：

 SELECT *
   FROM t_name
 {WHERE 条件一}
  START WITH 条件二
CONNECT BY {PRIOR PARENT=ID|PARENT=PRIOR ID} 

3、实例

3.1、实现每五分钟的字符串列表

select to_char((daybegin + (level - 1) * 5 / 1440), 'yyyy-mm-dd hh24:mi:ss') as newdaybegin
  from (select (trunc(sysdate, 'dd')) as daybegin from dual)
connect by level <= 288;


SELECT 
   last5time,
   CASE WHEN last5time = '23:55:00' THEN '24:00:00'
        ELSE this5time
   END this5time
FROM 
(select to_char((daybegin + (level - 1) * 5 / 1440) - 5 / 1440,
               'hh24:mi:ss') as last5time,
       to_char((daybegin + (level - 1) * 5 / 1440), 'hh24:mi:ss') as this5time
  from (select (trunc(sysdate, 'dd')) as daybegin from dual)
connect by level <= 288)
WHERE last5time >='09:30:00'
ORDER BY 2

3.2、oracle 递归写法实现病毒分裂算法

需求： 假如某个病毒能够自己复制自己，每1年可以复制出2个自己，然后就关闭了复制功能，不能够再进行复制，且每年都会死1个新生病毒，假如初始有5个病毒， 1年后总数是5+9,2年后总数是5+9+17，请用SQL求解（不使用显示的循环如While，For，Loop等，可以使用递归）列出N年中每一年的病毒数量。 200>=N>=1 时间要求：1秒内 

with vir_per_year
     (years,
      vir_cnt,
      total_vcnt) 
      as ( /*年份，每年病毒数，病毒总数*/
     select
        1 as years，
        5 as vir_cnt，
        5 as total_vcnt
     from dual
     union all
     select 
        years +1 as years,
        (vir_cnt*2) - 1 as vir_cnt,  /*病毒增长公式*/
        total_vcnt + (vir_cnt*2) - 1 as total_vcnt 
      from vir_per_year   /*递归调用 计算病毒数量*/
where years < 200  /*200年内*/

)
select 
    years,
    vir_cnt,
    total_vcnt
from vir_per_year;

3.3、递归实现当前日期向前追溯六个月每月月初及月末日期列表

   SELECT
    -- 当前日期
    SYSDATE AS Today,
    -- 当前月月初和月末
    TRUNC(SYSDATE, 'MM') AS Current_Month_Start,
    LAST_DAY(TRUNC(SYSDATE, 'MM')) AS Current_Month_End, 
    -- 近6个月每个月的月初和月末
    ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -LEVEL + 1) AS Month_Start,
    LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -LEVEL + 1)) AS Month_End
FROM
    DUAL
CONNECT BY
    LEVEL <= 6
ORDER BY
    Month_Start DESC;

3.4、获取A日期和B日期两个日期之间每个月月初及月末日期列表

WITH date_range (start_date,end_date,lvl)AS (
    SELECT 
        TRUNC(:a, 'MM') AS start_date, 
        LAST_DAY(:a) AS end_date, 
        1 AS lvl
    FROM 
        DUAL
    UNION ALL
    SELECT 
        ADD_MONTHS(start_date, 1)  AS start_date, 
        LAST_DAY(ADD_MONTHS(start_date, 1)) AS end_date, 
        lvl + 1 AS lvl
    FROM 
        date_range
    WHERE 
        ADD_MONTHS(start_date,1) <= LAST_DAY(:b)
)
SELECT 
    start_date AS Month_Start, 
    end_date AS Month_End,
    lvl AS lvl
FROM 
    date_range
ORDER BY 
    Month_Start;