存储过程游标循环

create or replace procedure T_SELL_VOLUME is
  --vn_qty        NUMBER;
  --vn_insert_qty NUMBER;

BEGIN
          --TRUNCATE TABLE T_TASK_VOLUME;   --删除表中所有的数据
  --基本信息
  for sin1 in (

        select lin6.qid,
               lin6.qname,
               lin6.sid,
               lin6.sname,
               lin6.cid,
               lin6.cname,
               lin5.sales_dist_id,
               lin5.sales_offi_id,
               lin5.sales_group_id,
               lin5.target_id,
               lin5.target_name,
               lin5.brand_id,
               lin5.brand_name,
               lin5.prod_modl_id,
               lin5.prod_modl_name,
               lin5.plan_month,
               lin5.lever_code,
               lin5.mobil_chnl_type
          from (

                select *
                  from (select *
                           from (select level lv, --体现此节点在树中的层数
                                        connect_by_root(tso.id) qid, --体现此节点是否叶节点
                                        connect_by_root(tso.name) qname,
                                        --体现自己的根节点
                                        tso.id   sid,
                                        tso.name sname
                                   from t_sales_orga tso
                                 --where connect_by_root(tso.name)!=tso.name
                                 connect by prior id = tso.parent_id
                                 --连接关系
                                  start with tso.type_code = 'SaleDist'
                                 --以哪个节点为根节点展开
                                  order by 4) lin
                          where lv = '2') lin4
                 inner join (select sidd, cid, cname
                               from (

                                     select level lv, --体现此节点在树中的层数
                                             connect_by_root(tso.id) sidd, --体现此节点是否叶节点
                                             connect_by_root(tso.name) sname,
                                             --体现自己的根节点
                                             tso.id   cid,
                                             tso.name cname
                                       from t_sales_orga tso
                                      where connect_by_root(tso.name) != tso.name
                                     connect by prior id = tso.parent_id
                                     --连接关系
                                      start with tso.type_code = 'SaleOffi'
                                     --以哪个节点为根节点展开
                                      order by 4

                                     ) lin2) lin3 on lin3.sidd = lin4.sid

                -- where (lin4.qid in (?) or ? is null)
                 --  and (lin4.sid in (?) or ? is null)
                --   and (lin3.cid in (?) or ? is null)

                 order by nlssort(lin4.sname, 'NLS_SORT=SCHINESE_PINYIN_M'),
                           nlssort(lin3.cname, 'NLS_SORT=SCHINESE_PINYIN_M')

                ) lin6
          left join (select tde.sales_dist_id,
                            tde.sales_offi_id,
                            tde.sales_group_id,
                            tsil.target_id,
                            tsil.target_name,
                            tsil.brand_id,
                            tsil.brand_name,
                            tsil.prod_modl_id,
                            tsil.prod_modl_name,
                            tsi.plan_month,
                            tde.lever_code,
                            tde.mobil_chnl_type
                       from t_sell_in1 tsi
                      inner join t_sell_in_line1 tsil on tsi.id = tsil.sell_in_id
                      inner join t_dealer tde on tde.id = tsil.target_id
                      where tsi.resource_type = '2'
                       /*
                        and tsi.plan_month = ?
                        and (tde.sales_dist_id in (?) or ? is null)
                        and (tde.sales_offi_id in (?) or (?) is null)
                        and (tde.sales_group_id in (?) or ? is null)
                        and (tde.mobil_chnl_type in (?) or ? is null)
                        and (tsil.target_name like ? or ? is null)
                        and (tde.lever_code in (?) or ? is null)
                        and (tsil.brand_id in (?) or ? is null)
                        and (tsil.prod_modl_id in (?) or ? is null)
                        */
                      group by tde.sales_dist_id,
                               tde.sales_offi_id,
                               tde.sales_group_id,
                               tsil.target_id,
                               tsil.target_name,
                               tsil.brand_id,
                               tsil.brand_name,
                               tsil.prod_modl_id,
                               tsil.prod_modl_name,
                               tsi.plan_month,
                               tde.lever_code,
                               tde.mobil_chnl_type
                      order by nlssort(tsil.target_name,
                                       'NLS_SORT=SCHINESE_PINYIN_M')) lin5 on lin5.sales_dist_id =
                                                                              lin6.qid
                                                                          and lin5.sales_offi_id =
                                                                              lin6.sid
                                                                          and lin5.sales_group_id =
                                                                              lin6.cid

         order by nlssort(lin6.qname, 'NLS_SORT=SCHINESE_PINYIN_M'),
                  nlssort(lin6.sname, 'NLS_SORT=SCHINESE_PINYIN_M'),
                  nlssort(lin6.cname, 'NLS_SORT=SCHINESE_PINYIN_M'),
                  nlssort(lin5.target_name, 'NLS_SORT=SCHINESE_PINYIN_M')


  )LOOP
    -- vn_qty := xx.sl;
    --- dbms_output.put_line('A'||vn_qty);

    for sin2 in (
               --SELL-IN 计划量
                select tde.sales_dist_id,
                       tde.sales_offi_id,
                       tde.sales_group_id,
                       tsil.target_id,
                       tsil.target_name,
                       tsil.brand_id,
                       tsil.brand_name,
                       tsil.prod_modl_id,
                       tsil.prod_modl_name,
                       tsi.plan_month,
                       tde.lever_code,
                       tde.mobil_chnl_type,
                       sum(tsil.resource_qty) sjfp
                  from t_sell_in1 tsi
                 inner join t_sell_in_line1 tsil on tsi.id = tsil.sell_in_id
                 inner join t_dealer tde on tde.id = tsil.target_id
                 where tsi.resource_type = '2'
                   and tsi.plan_month = sin1.plan_month
                   and (tde.sales_dist_id =sin1.sales_dist_id)
                   and (tde.sales_offi_id =sin1.sales_offi_id)
                   and (tde.sales_group_id =sin1.sales_group_id)
                  and (tsil.target_id=sin1.target_id)
                  and (tsil.brand_id = sin1.brand_id)
                   and (tsil.prod_modl_id = sin1.prod_modl_id)

                 group by  tde.sales_dist_id,
                       tde.sales_offi_id,
                       tde.sales_group_id,
                       tsil.target_id,
                       tsil.target_name,
                       tsil.brand_id,
                       tsil.brand_name,
                       tsil.prod_modl_id,
                       tsil.prod_modl_name,
                       tsi.plan_month,
                       tde.lever_code,
                       tde.mobil_chnl_type

     )LOOP



   for sin3 in(
        --铺货量
             select  tpm.id prod_modl_id,
                     tpm.name prod_modl_name,
                     tpm.brand_id,
                     td.mobil_chnl_type,
                     td.sales_dist_id,
                     td.sales_offi_id,
                     td.sales_group_id,
                     td.id dname,
                     td.name,
                     td.lever_code,
                     to_char(tcs.audit_date,'yyyy-mm') audit_data,
                     tcs.audit_date,
                     td.mobil_chnl_type,
                     sum(tcsl.checkout_sum) checkout_sum
                from t_cover_settlement tcs
               inner join t_cover_settlement_line tcsl on tcs.form_no = tcsl.form_no
               inner join t_product tp on tcsl.prod_code = tp.prod_code
               inner join t_prod_modl tpm on tp.prod_modl_id = tpm.id
               inner join t_dealer td on td.dealer_code = tcs.deal_code
               where
                tcs.audit_date >=to_date(to_char(trunc(to_date(sin1.plan_month,'yyyy-MM'),'MONTH'),'yyyy-MM-dd'),'yyyy-MM-dd')
                and tcs.audit_date <=to_date(to_char(LAST_DAY(to_date(sin1.plan_month,'yyyy-mm')),'yyyy-MM-dd'),'yyyy-MM-dd')
                and  (tpm.id =sin1.prod_modl_id)
                and tp.prod_modl_id in
                     (select distinct (tsil.prod_modl_id)
                        from t_sell_in1 tsi
                       inner join t_sell_in_line1 tsil on tsi.id = tsil.sell_in_id
                        where tsi.resource_type = '0'
                       and (tsi.plan_month =sin1.plan_month)
                       and (tsil.prod_modl_id =sin1.prod_modl_id)
                         )
               group by  tpm.id,
                         tpm.name,
                         tpm.brand_id,
                         td.mobil_chnl_type,
                         td.sales_dist_id,
                         td.sales_offi_id,
                         td.sales_group_id,
                         td.id,
                         td.name,
                         tcs.audit_date,
                         td.lever_code,
                         td.mobil_chnl_type

     )LOOP
      for sin4 in(
            --SELL-IN出货量
                      select tpm.id prod_modl_id,
                             tpm.name,
                             tpm.brand_id,
                             td.mobil_chnl_type,
                             td.sales_dist_id,
                             td.sales_offi_id,
                             td.sales_group_id,
                             td.id dname,
                             td.abbr,
                             td.lever_code,
                             to_char( tsf.audit_date,'yyyy-mm') audit_data,
                             tsf.audit_date,
                             td.mobil_chnl_type,
                             sum(tsfl.ship_qty) ship_qty
                        from t_shipment_form tsf
                       inner join t_ship_form_line tsfl on tsf.form_no = tsfl.form_no
                       inner join t_product tp on tsfl.prod_code = tp.prod_code
                       inner join t_prod_modl tpm on tp.prod_modl_id = tpm.id
                       inner join t_dealer td on td.dealer_code = tsf.dealer_code
                       where
                           tsf.audit_date >=to_date(to_char(trunc(to_date(sin1.plan_month,'yyyy-MM'),'MONTH'),'yyyy-MM-dd'),'yyyy-MM-dd')
                           and tsf.audit_date <=to_date(to_char(LAST_DAY(to_date(sin1.prod_modl_id,'yyyy-mm')),'yyyy-MM-dd'),'yyyy-MM-dd')
                          and (tpm.id =sin1.prod_modl_id)
                         and  tp.prod_modl_id in
                             (select distinct (tsil.prod_modl_id)
                                from t_sell_in1 tsi
                               inner join t_sell_in_line1 tsil on tsi.id = tsil.sell_in_id
                               inner join t_dealer tde on tde.id = tsil.target_id
                               where tsi.resource_type = '0'
                                and (tsi.plan_month =sin1.plan_month)
                                and (tsil.prod_modl_id =sin1.prod_modl_id)
                              )
                       group by tpm.id,
                             tpm.name,
                             tpm.brand_id,
                             td.mobil_chnl_type,
                             td.sales_dist_id,
                             td.sales_offi_id,
                             td.sales_group_id,
                             td.id,
                             td.abbr,
                             tsf.audit_date,
                             td.lever_code,
                             td.mobil_chnl_type
       )LOOP
           for sin5 in(
                    --SELL-IN退货量
                    select tpm.id prod_modl_id,
                           tpm.name,
                           tpm.brand_id,
                           td.mobil_chnl_type,
                           td.mobil_chnl_type,
                           td.mobil_chnl_type,
                           td.sales_dist_id,
                           td.sales_offi_id,
                           td.sales_group_id,
                           td.id dname,
                           td.abbr,
                           to_char( trf.audit_date,'yyyy-mm') audit_data,
                           trf.audit_date,
                           td.lever_code,
                           td.mobil_chnl_type,
                           sum(trfl.return_qty) return_qty
                      from t_return_form trf
                     inner join t_return_form_line trfl on trf.form_no = trfl.form_no
                     inner join t_product tp on trfl.prod_code = tp.prod_code
                     inner join t_prod_modl tpm on tp.prod_modl_id = tpm.id
                     inner join t_dealer td on td.dealer_code = trf.dealer_code
                     where (tpm.id =sin1.prod_modl_id)
                        and trf.audit_date >= to_date(to_char(trunc(to_date(sin1.plan_month,'yyyy-MM'),'MONTH'),'yyyy-MM-dd'),'yyyy-MM-dd')
                        and trf.audit_date <= to_date(to_char(LAST_DAY(to_date(sin1.plan_month,'yyyy-mm')),'yyyy-MM-dd'),'yyyy-MM-dd')
                        and
                        tp.prod_modl_id in
                           (select distinct (tsil.prod_modl_id)
                              from t_sell_in1 tsi
                             inner join t_sell_in_line1 tsil on tsi.id = tsil.sell_in_id
                             inner join t_dealer tde on tde.id = tsil.target_id
                             where tsi.resource_type = '0'
                              and (tsi.plan_month =sin1.plan_month)
                              and (tsil.prod_modl_id =sin1.prod_modl_id)

                            )
             group by  tpm.id ,
                       tpm.name,
                       tpm.brand_id,
                       td.mobil_chnl_type,
                       td.mobil_chnl_type,
                       td.mobil_chnl_type,
                       td.sales_dist_id,
                       td.sales_offi_id,
                       trf.audit_date,
                       td.sales_group_id,
                       td.id,
                       td.abbr,
                       td.lever_code,
                       td.mobil_chnl_type
      )LOOP
       for sin6 in(
              --SELL-IN铺退量
                select tpm.id prod_modl_id,
                       tpm.name,
                       tpm.brand_id,
                       td.sales_dist_id,
                       td.sales_offi_id,
                       td.sales_group_id,
                       td.id dname,
                       td.abbr,
                       td.lever_code,
                       to_char( tcr.audit_date,'yyyy-mm') audit_data,
                       tcr.audit_date,
                       td.mobil_chnl_type,
                       sum(tcrl.checkout_sum) checkout_sum
                  from t_cover_return tcr
                 inner join t_coverv_return_line tcrl on tcr.form_no = tcrl.form_no
                 inner join t_product tp on tcrl.prod_code = tp.prod_code
                 inner join t_prod_modl tpm on tp.prod_modl_id = tpm.id
                 inner join t_dealer td on td.dealer_code = tcr.deal_code
                 where (tpm.id =sin1.prod_modl_id)
                  and tcr.audit_date >= to_date(to_char(trunc(to_date(sin1.plan_month,'yyyy-MM'),'MONTH'),'yyyy-MM-dd'),'yyyy-MM-dd')
                  and tcr.audit_date <= to_date(to_char(LAST_DAY(to_date(sin1.plan_month,'yyyy-mm')),'yyyy-MM-dd'),'yyyy-MM-dd')
                  and  tp.prod_modl_id in
                       (select distinct (tsil.prod_modl_id)
                          from t_sell_in1 tsi
                         inner join t_sell_in_line1 tsil on tsi.id = tsil.sell_in_id
                         inner join t_dealer tde on tde.id = tsil.target_id
                         where tsi.resource_type = '0'
                          and (tsi.plan_month =sin1.plan_month)
                          and (tsil.prod_modl_id=sin1.prod_modl_id)
                        )
                 group by tpm.id,
                       tpm.name,
                       tpm.brand_id,
                       td.sales_dist_id,
                       td.sales_offi_id,
                       td.sales_group_id,
                       td.id,
                       tcr.audit_date,
                       td.abbr,
                       td.lever_code,
                       td.mobil_chnl_type

            )LOOP
         for sout in(
                --SELL-OUT销售量
                      SELECT ttso.sales_orga_id,
                             ttso.sales_offi_id,
                             ttso.sales_group_id,
                             ttso.dealer_id,
                             ttso.dealer_abbr,
                             tde.lever_code,
                             tde.mobil_chnl_type,
                             ttl.brand_id,
                             ttl.brand_name,
                             ttl.prod_modl_id,
                             to_char(ttl.created_date,'yyyy-mm') created_data,
                             ttl.created_date,
                             ttl.prod_modl_name,
                             count(ttl.serial_no) xssl
                        FROM T_TERMINAL_SALES_ORDER ttso
                       inner join T_TSO_LINE ttl on ttl.terminal_sales_order_id = ttso.id
                       inner join t_dealer tde on tde.id = ttso.dealer_id
                       where ttl.prod_model_old_id in
                             (select distinct (tsil.prod_modl_id)
                                from t_sell_out tsi
                               inner join t_sell_out_line tsil on tsi.id = tsil.sell_out_id
                               where tsi.resource_type = '0'
                                and tsi.plan_month =sin1.plan_month
                                and (tsil.target_id =sin1.target_id)
                              )

                        and ttl.created_date >=to_timestamp(to_char(trunc(to_date(sin1.plan_month,'yyyy-MM'),'MONTH'),'yyyy-MM-dd'),'yyyy-mm-dd hh24:mi:ss')
                        and ttl.created_date <=to_timestamp(to_char(LAST_DAY(to_date(sin1.plan_month,'yyyy-mm')),'yyyy-MM-dd') || '23:59:59.999999999','yyyy-mm-dd hh24:mi:ss.ff9')
                        and (ttso.sales_offi_id= sin1.sales_offi_id)
                        and ttl.serialstatus = '0'
                       group by ttso.sales_orga_id,
                                ttso.sales_offi_id,
                                ttso.sales_group_id,
                                ttso.dealer_id,
                                ttso.dealer_abbr,
                                tde.lever_code,
                                tde.mobil_chnl_type,
                                ttl.brand_id,
                                ttl.created_date,
                                ttl.brand_name,
                                ttl.prod_modl_id,
                                ttl.prod_modl_name

            )LOOP
             INSERT INTO T_TASK_VOLUME
                    (ID,
                     SALES_DIST_ID,
                     SALES_DIST_NAME,
                     SALES_OFFI_ID,
                     SALES_OFFI_NAME,
                     SALES_GROUP_ID,
                     SALES_GROUP_NAME,
                     TARGET_ID,
                     TARGET_NAME,
                     BRAND_ID,
                     BRAND_NAME,
                     PROD_MODL_ID,
                     PROD_MODL_NAME,
                     PLAN_MONTH,
                     AUDIT_DATE,
                     CREATED_DATE,
                     RESOURCE_QTY,
                     CHECKOUT_SUM,
                     SHIP_QTY,
                     RETURN_QTY,
                     CHECKOUTE_SUM,
                     SALES_QTY)
                  VALUES
                    (TTV_ID.NEXTVAL,
                     sin1.sales_dist_id,
                     sin1.qname,
                     sin1.sales_offi_id,
                     sin1.sname,
                     sin1.sales_group_id,
                     sin1.cname,
                     sin1.target_id,
                     sin1.target_name,
                     sin1.brand_id,
                     sin1.brand_name,
                     sin1.prod_modl_id,
                     sin1.prod_modl_name,
                     sin1.plan_month,
                     sin3.audit_date,
                     sout.created_date,
                     sin2.sjfp,
                     sin3.checkout_sum,
                     sin4.ship_qty,
                     sin5.return_qty,
                     sin6.checkout_sum,
                     sout.xssl
                   );
                END LOOP;

              END LOOP;

           END LOOP;

         END LOOP;

      END LOOP;

    END LOOP;
 
  END LOOP;
       commit;

end T_SELL_VOLUME;

sin1的循环会出现问题，把sin1做为后面几个游标里查询的条件，那位朋友能帮看看吗？

建议先看看<提问的技巧>