如何在plsql的begin和end之间使用type is record、is table of声明的collect集合呢？

青旅半醒 2023-08-17 15:20 68阅读 0赞

declare里进行声明一个类型type is record，这里必须写多列，否则在bulk collect里就不能使用is table of 的类型的对象了。

举例：

declare
        type type_number is record (
            center_name varchar2(128),
            amounts number(20, 3)
            );
        type type_list_amount is table of type_number;
        v_list type_list_amount;
        v_amount_sum number(20,3);
    begin
        select distinct podt.center_code||':'||accc.center_name,sum(abs(ppjl.AMOUNT)) bulk collect
        into v_list
        from podt,
             codt,
             accc,
             PPJL
        where codt.policy_no = podt.policy_no
          and podt.center_code = accc.center_code
          and ppjl.POLICY_NO = podt.POLICY_NO
          and codt.coverage_no = '1'
          and (podt.po_status_code in ('A', 'L', 'M', 'P', 'R', 'U', 'W', 'Z') or
               (podt.po_status_code in ('T', 'S', 'D', 'J', 'Y', 'Z')))
          and (codt.co_status_code in ('P', 'R', 'A', 'L', 'M', 'U', 'W') or
               (codt.co_status_code in ('T', 'E', 'S', 'D', 'J', 'Y', 'Z')))
        and exists(select * from ppjl where ACCOUNT_CODE like '4101%')
        and ppjl.TRAN_DATE >= to_date('2018/01/01', 'YYYY/MM/DD')
        and ppjl.TRAN_DATE <= to_date('2018/12/31', 'YYYY/MM/DD')
        group by podt.center_code, accc.center_name;
    
        v_amount_sum := 0;
    
        /**
          所有机构的有效保单号的累计缴额
         */
        for i in 1.. v_list.COUNT loop
            v_amount_sum := v_amount_sum + v_list(i).amounts;
        end loop;
    
        DBMS_OUTPUT.put_line('保单保费总算：' || v_amount_sum);
    
    end;

但是可以这样用：

declare
        type type_varchar2s is table of varchar2(20);
        v_list type_varchar2s;
    begin
        select POLICY_NO bulk collect
        into v_list
        from PODT;
    
        for i in 1..v_list.COUNT loop
            DBMS_OUTPUT.put_line(v_list(i));
            end loop;
    end;