db

declare
   i integer;
   type my_type is record
   (
    v_name varchar2(50),
    v_id   number
   );
   type my_table is table of my_type;
    
   v_person my_type;
   v_table my_table:=my_table();
    
   rowindex integer;
   prev_id number;--前一行ID
   c integer;
begin
   rowindex:=1;
   prev_id:=0;
   c:=0;
   for cc in(select first_name,department_id from employees t
   order by department_id)
   loop
      if rowindex>1 then --不是第一行
         if cc.department_id<>prev_id or
         (cc.department_id is null and prev_id is not null) or
         (cc.department_id is not null and prev_id is null)
         --不一样
         then
            --中间的小计
            --dbms_output.put_line(prev_id||'小计：'||'  '||c);
            v_table.extend();
            v_person.v_name:=prev_id||'小计：';
            v_person.v_id:=c;
            v_table(rowindex):=v_person;--行号从1开始
            rowindex:=rowindex+1;
            c:=0;
         end if;
      end if;   
      prev_id:=cc.department_id;
      c:=c+1;
      --真实的数据
      --dbms_output.put_line(cc.first_name||'  '||cc.department_id);
      v_table.extend();
      v_table(rowindex):=cc; 
      rowindex:=rowindex+1; 
   end loop;
   --最后的小计
   --dbms_output.put_line(prev_id||'小计：'||'  '||c);
   v_table.extend();
   v_person.v_name:=prev_id||'小计：';
   v_person.v_id:=c;
   v_table(rowindex):=v_person; 
     
   dbms_output.put_line('--------------------------------------------------------');
   rowindex:=v_table.first;
   loop
      exit when rowindex is null;
      dbms_output.put_line(rpad(v_table(rowindex).v_name,20,' ')||v_table(rowindex).v_id);
      rowindex:=v_table.next(rowindex);
   end loop;
end;