郁闷的事 过程跑了很久 最后不见了！死不见尸体活不见人

Question

过程用游标获得年周 然后循环统计每周的数据。
跑了4.5个小时候，没有写过程日志，而且数据表中没数据。
单独拿出来确时可以的
create or replace procedure P_Market_test_UserDataTotal iscursor cur_yearWEEK is
   select distinct udf_weekofyear(F_RegTime,'YYYYIW') as F_YearWeek
   from T_Base_New_Reg_User u
   where  to_char(f_regtime,'yyyyiw')>'200744' and  f_regtime < TRUNC(SYSDATE,'D')+1
   order by F_YearWeek asc;
 v_YearWEEK char(6);   begin
        open cur_yearWeek;
           loop fetch cur_yearWeek into v_Yearweek;
           exit when  cur_yearWeek%notfound;            insert into  T_Market_WEEK_UserDataTotal
            select  a.f_yearmonth,rgnum,efnum,ntnum,RgTotalNum,EffTotalNum,TaredTotalNum,sysdate
                        from
                        (
                            select v_Yearweek  as F_YearMonth,count(distinct f_username) RgNum
                            from T_Base_New_Reg_User Ne
                            where  udf_weekofyear(f_regtime,'YYYYIW')=v_Yearweek
            
                        ) a left join
                        (
                            select  v_Yearweek  as F_YearMonth,count(distinct f_username) efNum
                            from T_Base_New_Effective_User ef
                            where udf_weekofyear(F_FIRSTFILLMONEYDATE,'YYYYIW')=v_Yearweek
            
                        )b on a.f_yearmonth=b.f_yearmonth
                        left join
                        (
                            select v_Yearweek  as F_YearMonth,count(distinct f_username) Ntnum
                            from T_Base_New_Tared_User Nu
                            where   udf_weekofyear(F_FirstTraedTime,'YYYYIW')=v_Yearweek
            
                        )c on a.f_yearmonth=c.f_yearmonth
                         inner join
                        (
                            select  v_Yearweek  as F_YearMonth,count(distinct f_username) RgTotalNum
                            from  T_Base_New_Reg_User Ne
                            where udf_weekofyear(f_regtime,'YYYYIW')<=v_Yearweek
                        )d on a.f_yearmonth=d.f_yearmonth
                        inner join
                       (
                            select  v_Yearweek  as F_YearMonth,count(distinct f_username) EffTotalNum
                            from T_Base_New_Effective_User Ne
                            where udf_weekofyear(F_FIRSTFILLMONEYDATE,'YYYYIW')<=v_Yearweek
                        )e on a.f_yearmonth=e.f_yearmonth
                        inner join
                        (
                            select  v_Yearweek  as F_YearMonth,count(distinct f_username) TaredTotalNum
                            from T_Base_New_Tared_User Ne
                            where  udf_weekofyear(F_FirstTraedTime,'YYYYIW')<=v_Yearweek
                        )f on a.f_yearmonth=f.f_yearmonth;
                        commit;
   end loop;
   close cur_yearWeek;
--  write to log
   end; 

Answer 1

这一大片取出来的就是一条记录吧，分别取出来放变量里再insert就可以了，不用做连接from 
                        ( 
                            select v_Yearweek  as F_YearMonth,count(distinct f_username) RgNum 
                            from T_Base_New_Reg_User Ne 
                            where  udf_weekofyear(f_regtime,'YYYYIW')=v_Yearweek 
            
                        ) a left join 
                        ( 
                            select  v_Yearweek  as F_YearMonth,count(distinct f_username) efNum 
                            from T_Base_New_Effective_User ef 
                            where udf_weekofyear(F_FIRSTFILLMONEYDATE,'YYYYIW')=v_Yearweek 
            
                        )b on a.f_yearmonth=b.f_yearmonth 
                        left join 
                        ( 
                            select v_Yearweek  as F_YearMonth,count(distinct f_username) Ntnum 
                            from T_Base_New_Tared_User Nu 
                            where  udf_weekofyear(F_FirstTraedTime,'YYYYIW')=v_Yearweek 
            
                        )c on a.f_yearmonth=c.f_yearmonth 
                        inner join 
                        ( 
                            select  v_Yearweek  as F_YearMonth,count(distinct f_username) RgTotalNum 
                            from  T_Base_New_Reg_User Ne 
                            where udf_weekofyear(f_regtime,'YYYYIW') <=v_Yearweek 
                        )d on a.f_yearmonth=d.f_yearmonth 
                        inner join 
                      ( 
                            select  v_Yearweek  as F_YearMonth,count(distinct f_username) EffTotalNum 
                            from T_Base_New_Effective_User Ne 
                            where udf_weekofyear(F_FIRSTFILLMONEYDATE,'YYYYIW') <=v_Yearweek 
                        )e on a.f_yearmonth=e.f_yearmonth 
                        inner join 
                        ( 
                            select  v_Yearweek  as F_YearMonth,count(distinct f_username) TaredTotalNum 
                            from T_Base_New_Tared_User Ne 
                            where  udf_weekofyear(F_FirstTraedTime,'YYYYIW') <=v_Yearweek 
                        )f on a.f_yearmonth=f.f_yearmonth; 

Answer 2

select * from T_Market_WEEK_UserDataTotal where f_yearweek >'200744'
order by f_yearweek asc 
35 200827 5849 1241 1242 1317161 272040 236578 2009-06-23 12:18:02
36 200828 6853 1640 1536 1324014 273680 238114 2009-06-23 12:20:57
37 200829 6534 1589 1432 1330548 275269 239546 2009-06-23 12:24:03
38 200830 6573 1636 1473 1337121 276905 241019 2009-06-23 12:26:54
39 200831 7975 2086 1827 1345096 278991 242846 2009-06-23 12:29:43

Answer 3

我的意思是
select v_Yearweek  as F_YearMonth,count(distinct f_username) RgNum 
                            from T_Base_New_Reg_User Ne 
                            where  udf_weekofyear(f_regtime,'YYYYIW')=v_Yearweek 
像这样的sql目的就是取他数字吧，
效果等同于 
select count(distinct f_username) RgNum into 一个变量
                            from T_Base_New_Reg_User Ne 
                            where  udf_weekofyear(f_regtime,'YYYYIW')=v_Yearweek 
同样其他几个sql也可以分别取出各表的记录数存到变量里
然后insert into T_Market_WEEK_UserDataTotal values (v_Yearweek ,变量1,变量2,...,sysdate)

Answer 4

你的意思是没有报错，执行也没有效果！
差不多是这样的
                 commit; 
  end loop; 才把部分结果写到表里

Answer 5

cursor cur_yearWEEK is 
  select distinct udf_weekofyear(F_RegTime,'YYYYIW') as F_YearWeek 
  from T_Base_New_Reg_User u 
  where  to_char(f_regtime,'yyyyiw')>'200744' and  f_regtime < TRUNC(SYSDATE,'D')+1 
  order by F_YearWeek asc; 
v_YearWEEK char(6); 200745
200746
200747
200748
200749
200750
200751
200752
200801
200802
200803
200804
200805
200806
200807
200808
200809
200810
200811
200812
200813
200814
200815
200816
200817
200818
200819
200820
200821
200822
200823
200824
200825
200826
200827
200828
200829
200830
200831
200832
200833
200834
200835
200836
200837
200838
200839
200840
200841
200842
200843
200844
200845
200846
200847
200848
200849
200850
200851
200852
200853
200901
200902
200903
200904
200905
200906
200907
200908
200909
200910
200911
200912
200913
200914
200915
200916
200917
200918
200919
200920
200921
200922
200923
200924
200925

Answer 6

还有一个问题，你的过程里面用的是left join 也就是说当表a查询不出数据的时候，其他几个即使有数据，也会返回一个空