据两个例子,
第一个数层级关系的递归,用循环
begin for orgId in (select org_id from DWSDATA.T_AGENT_ORG_ID group by agent_id ) loop insert into ken.all_agent(agent_id,all_child_agent ) select orgId.org_id,t.org_id from CDMDATA.agent_list t start with t.agent_id=orgId.agent_id connect by prior agent_id= parent_agent_id ; commit; end loop; end;
第二个,累计值统计时,不能进行group by 所以不能多个日期同时运行,故可以定义一个循环,在多个日期中跑时,进行每个日期循环。例如下面是一个统计30天内无交易的人数
begin for RTrade_Date in (select distinct statistic_date from ADSDATA.XS_DATA_INDEX_DAILY where statistic_date>= trunc(sysdate-1) and statistic_date< trunc(sysdate) ) -- 所有需要统计的日期 loop insert into ADSDATA.XS_DATA_INDEX_DAILY ( statistic_date ,od_category ,statistic_value ,value_comment ,data_timestamp ) select trunc(max(LAST_TRADE_DATE)) as statistic_date ,‘交易统计‘ as od_category ,count(0) statistic_value ,‘连续30天无交易商户数‘ as value_comment ,sysdate as data_timestamp from ( select max(Trade_Date) as LAST_TRADE_DATE,shop_no from XS.TRADE_DETAIL_HIS where Trade_Date < RTrade_Date.Statistic_Date+1 group by shop_no --每个用户交易的最早日期 ) where LAST_TRADE_DATE+30 >RTrade_Date.Statistic_Date+1; commit; end loop; end;