open  cur_ply for
     select ply_list.* from cust_renew_ply_list ply_list
     left join
     (
      select  level4.ply_no as level4_ply_no
        from cust_ply_data_new data_new
                  inner join cust_temp_ply_base base
                       on data_new.ply_no = base.c_ply_no
                  left join cust_temp_ply_base parents
                       on base.c_ply_no = parents.c_ply_no
                  left join cust_ply_data_new level2
                       on data_new.last_ply_no = level2.ply_no
                  left join cust_ply_data_new level3
                       on level2.last_ply_no = level3.ply_no
                  left join cust_ply_data_new level4
                       on level3.last_ply_no = level4.ply_no
                where base.c_orig_flg = 1
                and sysdate >= base.t_insrnc_bgn_tm
                and sysdate <= add_months(base.t_insrnc_end_tm,3)
                and level4.ply_no is not null
           ) vx
           on ply_list.renew_ply_no = vx.level4_ply_no;
    
      loop
          -- 每次处理指定行数的记录
          fetch cur_ply bulk collect
            into table_renew limit v_rows_count;
          exit when table_renew.count = 0;          for i in 1 .. table_renew.count loop
             item_renew := table_renew(i);
             --处理单个客户逻辑
             v_level_4_ply := item_renew.renew_ply_no;
             v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
             
             while nvl(v_level_4_ply,'error') <> 'error'
                loop
                    insert into cust_renew_ply_list
                     (
                          start_ply_no,
                          renew_ply_no,
                          prod_params,
                          user_id
                     )
                     values
                     (
                          item_renew.start_ply_no,
                          v_level_4_ply,
                          item_renew.prod_params,
                          item_renew.user_id
                     );
                v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);    
             end loop;
             commit;
       end loop;
     end loop;
   close cur_ply;
求高人指点 此段 代码 什么含义必有重谢!!!

解决方案 »

  1.   

    BEGIN
      --打开动态游标cur_ply
      OPEN cur_ply FOR
        SELECT ply_list.*
          FROM cust_renew_ply_list ply_list
          LEFT JOIN (SELECT level4.ply_no AS level4_ply_no
                       FROM cust_ply_data_new data_new
                      INNER JOIN cust_temp_ply_base base
                         ON data_new.ply_no = base.c_ply_no
                       LEFT JOIN cust_temp_ply_base parents
                         ON base.c_ply_no = parents.c_ply_no
                       LEFT JOIN cust_ply_data_new level2
                         ON data_new.last_ply_no = level2.ply_no
                       LEFT JOIN cust_ply_data_new level3
                         ON level2.last_ply_no = level3.ply_no
                       LEFT JOIN cust_ply_data_new level4
                         ON level3.last_ply_no = level4.ply_no
                      WHERE base.c_orig_flg = 1
                        AND SYSDATE >= base.t_insrnc_bgn_tm
                        AND SYSDATE <= add_months(base.t_insrnc_end_tm, 3)
                        AND level4.ply_no IS NOT NULL) vx
            ON ply_list.renew_ply_no = vx.level4_ply_no;  LOOP
        --将游标查询到的数据集存入嵌套表table_renew,取行数v_rows_count
        FETCH cur_ply BULK COLLECT
          INTO table_renew LIMIT v_rows_count;
        EXIT WHEN table_renew.count = 0;
      
        --循环处理嵌套表的中的记录
        FOR i IN 1 .. table_renew.count LOOP
          item_renew := table_renew(i);
          --处理单个客户逻辑
          v_level_4_ply := item_renew.renew_ply_no;
          v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
        
          WHILE nvl(v_level_4_ply, 'error') <> 'error' LOOP
            INSERT INTO cust_renew_ply_list
              (start_ply_no, renew_ply_no, prod_params, user_id)
            VALUES
              (item_renew.start_ply_no, v_level_4_ply, item_renew.prod_params,
               item_renew.user_id);
            v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
          END LOOP;
          COMMIT;
        END LOOP; --结束嵌套表循环
      END LOOP; --结束游标循环
      CLOSE cur_ply; --关闭游标
    END;
      

  2.   

    先看一下这本书
    精通Oracle 10g PL/SQL编程(http://download.csdn.net/source/552111)
      

  3.   

    BEGIN
      --打开动态游标cur_ply
      OPEN cur_ply FOR
        SELECT ply_list.*
          FROM cust_renew_ply_list ply_list
          LEFT JOIN (SELECT level4.ply_no AS level4_ply_no
                       FROM cust_ply_data_new data_new
                      INNER JOIN cust_temp_ply_base base
                         ON data_new.ply_no = base.c_ply_no
                       LEFT JOIN cust_temp_ply_base parents
                         ON base.c_ply_no = parents.c_ply_no
                       LEFT JOIN cust_ply_data_new level2
                         ON data_new.last_ply_no = level2.ply_no
                       LEFT JOIN cust_ply_data_new level3
                         ON level2.last_ply_no = level3.ply_no
                       LEFT JOIN cust_ply_data_new level4
                         ON level3.last_ply_no = level4.ply_no
                      WHERE base.c_orig_flg = 1
                        AND SYSDATE >= base.t_insrnc_bgn_tm
                        AND SYSDATE <= add_months(base.t_insrnc_end_tm, 3)
                        AND level4.ply_no IS NOT NULL) vx
            ON ply_list.renew_ply_no = vx.level4_ply_no;  LOOP
        --将游标查询到的数据集存入嵌套表table_renew,取行数v_rows_count
        FETCH cur_ply BULK COLLECT
          INTO table_renew LIMIT v_rows_count;
        EXIT WHEN table_renew.count = 0;
      
        --循环处理嵌套表的中的记录
        FOR i IN 1 .. table_renew.count LOOP
          item_renew := table_renew(i);
          --处理单个客户逻辑
          v_level_4_ply := item_renew.renew_ply_no;
          v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
        
          WHILE nvl(v_level_4_ply, 'error') <> 'error' LOOP
            INSERT INTO cust_renew_ply_list
              (start_ply_no, renew_ply_no, prod_params, user_id)
            VALUES
              (item_renew.start_ply_no, v_level_4_ply, item_renew.prod_params,
               item_renew.user_id);
            v_level_4_ply := Get_Clear_Ply_No(v_level_4_ply);
          END LOOP;
          COMMIT;
        END LOOP; --结束嵌套表循环
      END LOOP; --结束游标循环
      CLOSE cur_ply; --关闭游标
    END;