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

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

青旅半醒 2023-08-17 15:20 89阅读 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;