批量操作数据方法(实例)

存储过程中有两种数据输出方法:
（一）
一种是利用EXECUTE IMMEDIATE SQL using....执行动态sql，一次批量将数据输入到临时表中，然后利用游标进行输出
     execute immediate SQL bulk collect into (record/table) 执行动态sql
(1) PROCEDURE PRO_SASCOLLECTMONEY(PARAMETERS IN varchar2, re_cursor OUT t_cursor) IS
    sel := 'INSERT INTO EPCISLOGTMP.TMP_SAS_COLLECT_MONEY_REPORT
            (DEPARTMENT_CODE,
             GROUP_CODE,
             g_health_net_collect)
             SELECT * FROM (
                  SELECT se.DEPARTMENT_CODE,
                   se.last_group_code GROUP_CODE,
                   SUM(DECODE(T.CPP, ''2H'', T.NET_COLLECT, 0)) g_health_net_collect
              FROM (SELECT  /*+INDEX(t2 IX_SAS_C_C_EMPLOYEE_CODE)  */
                           T2.EMPLOYEE_CODE,
                           T2.PERSON_GROUP_FLAG ||DECODE(T2.PLAN_CLASS_CODE,''A'',''A'',''B'',''A'', ''C'', ''C'', ''K'',''H'',''J'',''J'',T2.PLAN_CLASS_CODE) CPP,
                           SUM(T2.COLLECT_AMOUNT) COLLECT_AMOUNT,
                           SUM(T2.STANDARD_AMOUNT) STANDARD_AMOUNT,
                           SUM(T2.NET_COLLECT) NET_COLLECT
                      FROM SAS_COLLECT_CHANNEL    T2,
                           DEPARTMENT_RELATION    T3,
                           EPCISBASE.SAS_EMPLOYEE T4
                     WHERE T4.DEPARTMENT_CODE = T3.CHILD_DEPARTMENT_CODE
                       AND T3.PARENT_DEPARTMENT_CODE = :v_departmentCode_epcis
                       AND T2.YEAR = :v_queryYear_epcis
                       AND T2.MONTH BETWEEN :v_queryBeginMouth_epcis AND :v_queryEndMouth_epcis
                       AND T2.EMPLOYEE_CODE = T4.EMPLOYEE_CODE '
             || v_filter ||
                    'AND (NVL(T4.TYPE, ''x'') != ''03'' OR (T4.TYPE = ''03'' AND (T4.AGENT_CODE = ''0'' OR NOT EXISTS
                         (SELECT 1
                              FROM SAS_AGENT T
                             WHERE T.AGENT_CODE = T4.AGENT_CODE
                               AND T.AGENT_MARK = ''1''))))
                    GROUP BY T2.EMPLOYEE_CODE,
                              T2.PERSON_GROUP_FLAG,
                              DECODE(T2.PLAN_CLASS_CODE,''A'',''A'',''B'',''A'', ''C'', ''C'', ''K'',''H'',''J'',''J'',T2.PLAN_CLASS_CODE)) T,
                     EPCISBASE.SAS_EMPLOYEE  se
              WHERE t.EMPLOYEE_CODE = se.employee_code
              GROUP BY se.last_group_code,se.EMPLOYEE_CHANNEL,se.EMPLOYEE_NAME,se.DEPARTMENT_CODE,se.EMPLOYEE_CODE )
              WHERE ROWNUM < :counter_limit  ';

   EXECUTE IMMEDIATE sel
     USING v_departmentCode_epcis, v_queryYear_epcis,v_queryBeginMouth_epcis,v_queryEndMouth_epcis,counter_limit;

   OPEN  re_cursor FOR
   SELECT T.DEPARTMENT_CODE  机构,
          (SELECT tt.group_code||'||'||tt.group_name
          FROM EPCISBASE.Sas_Group tt
         WHERE t.group_code = tt.group_code
           AND ROWNUM = 1) 团队代码名称,
          t.g_health_net_collect 团体健康险净实收保费
      FROM EPCISLOGTMP.TMP_SAS_COLLECT_MONEY_REPORT T ;

  END PRO_SASCOLLECTMONEY;
 
 
 
(2)
execute immediate 'Select t.department_code,t.DEPARTMENT_CHINESE_NAME,t.SALE_GROUP_CODE,t.SALE_AGENT_CODE,t.SALE_AGENT_NAME,t.POLICY_NO,' ||
                 'sum(T_P),sum(P_B),sum(P_E) from (' ||
                 sql_statment || sql_statment1 ||
                 ') t group by department_code,DEPARTMENT_CHINESE_NAME,SALE_GROUP_CODE,SALE_AGENT_CODE,SALE_AGENT_NAME,POLICY_NO' bulk
                 collect
            into v_tmp_records; 

（二）利用DBMS
  presql_statement := sel || filter || ' union ' || newsel || newfilter ||
                        ' union ' || newlastsel || newlastfilter;
 
    open c_select_partition;
    loop
      fetch c_select_partition
        into v_partition_no;
      exit when c_select_partition%notfound;
   
      sql_statement := replace(presql_statement,
                               ':partition_no',
                               'part' || to_char(v_partition_no));
         
      cid := DBMS_SQL.open_cursor;
      DBMS_SQL.parse(cid, sql_statement, DBMS_SQL.v7);
      --定义输出变量
      DBMS_SQL.define_column(cid, 1, v_policy_no, 20);
      DBMS_SQL.define_column(cid, 2, v_endorse_no, 20);
      nrows := DBMS_SQL.execute(cid);
   
     --绑定变量(必录)
      DBMS_SQL.bind_variable(cid,
                           ':rightdepartmentCode',
                           p_right_department_code);
                          
      loop
        <<l_select_next>>
     
        if DBMS_SQL.fetch_rows(cid) = 0 then
          exit;
        end if;
     
        -- 将结果放入PL/SQL变量
        DBMS_SQL.column_value(cid, 1, v_policy_no);
        DBMS_SQL.column_value(cid, 2, v_endorse_no);
       
     
        ---2009-4-47新增平台到帐日期、平台交易号信息
        begin
          select circ_serial, gain_date
            into v_circ_serial, v_gain_date
            from odsdata.circ_tran_info t
           where t.document_no = v_endorse_apply_no
             and t.document_type = '03'
             and rownum = 1;
        exception
          when others then
            v_circ_serial := '';
            v_gain_date   := '';
        end;
             
       
     
        --过滤收付途径
        if p_payment_path is not null then
          if v_payment_path <> p_payment_path then
            goto l_select_next;
          end if;
        end if;
        begin
          v_PAY_MODE := TO_NUMBER(v_PAY_MODE);
        exception
          when others then
            v_PAY_MODE := '';
        end;
     
        --将记录插入到表tmp_policy_vehicle_report_bj
        insert into tmp_policy_vehicle_report_bj
          (POLICY_NO,
           endorse_No)
        values
          (V_POLICY_NO,
           v_endorse_No);
     
        counter := counter + 1;
     
        if (counter >= counter_limit) then
          DBMS_SQL.close_cursor(cid);
          goto l_select_result;
        end if;
      end loop;
   
      DBMS_SQL.close_cursor(cid);
    end loop;
   
    再对tmp_policy_vehicle_report_bj表的数据一条条做输出处理