学步园

declare
        cursor o is
                select  distinct art_grp_no
                from art_grp
                where art_grp_no between $art_grp1 and $art_grp2;
                  artgrp article.art_grp_no%type;
        cursor p is
                select
                        art_grp_no,
                        art_no,
                        sum(qty),
                        sum((amount+disc_amount)*(1+vat_perc/100))
                from
                        monthsale
                where
                        invoice_date between to_date('$time1','YYYYMMDD') and to_date('$time2','YYYYMMDD') and
                         art_grp_no=artgrp
        group by art_grp_no,
                       art_no
                order by sum((amount+disc_amount)*(1+vat_perc/100)) desc;


                d0 number(3);
                n number(5);
                d1 sz_art_sales.art_grp_no%type;
                d3 sz_art_sales.art_no%type;
                d2 sz_art_sales.qty%type;
                d4 sz_art_sales.amount%type;
begin
        n:=10;
        open o;
             loop
                fetch o into artgrp;
                 exit when o%notfound;
                    open p;
                       d0:=1;
                    for i in 1..n loop
                      fetch p into d1,d3,d2,d4;
                      exit when p%notfound;
                      insert into sz_art_sales values(d0,d1,d3,d2,d4);
                      commit;
                      d0:=d0+1;
                    end loop;
                   close p;
             end loop;
         close o;
end;
/

一个PL_SQL 循环套循环的PLSQL排名语句

create or replace procedure day_message1 is
begin
  declare
    cursor p is
      select distinct t1.org_code from send_user t1;
    v_org_code varchar(25);
  
    cursor o is
      select t2.org_name || ':' || substr(t3.oil_type, 0, 2) || ' 数量' ||
             round(sum(quantity), 1) || ' 均价' ||
             round(sum(amount) / sum(quantity), 0) || ' 金额' ||
             round(sum(amount) / 10000, 1) || '万' as message
        from MKT_SALE_ORDER T1, sys_org_short_name t2, sys_oil_short t3
       where t1.org_code = t2.org_code
         and t3.oil_id = t1.oil_id
         and t2.org_code = v_org_code
         and t1.order_date =
             to_date(to_char(sysdate, 'YYYYMMDD'), 'yyyymmdd') - 1
         and t1.order_status in ('审核通过', '提单完成', '提单执行')
       group by t2.org_name, t3.oil_type, t1.org_code
      having sum(quantity) <> 0
       order by 1;
  
    v_result  varchar2(1000);
    v_message varchar(200);
  
  begin
    open o;
    delete from tmp_message;
    open p;
    loop
      fetch p
        into v_org_code;
      exit when p%notfound;
    
      loop
        fetch o
          into v_message;
        exit when o%notfound;
        v_result := v_result || v_message || '    ' || chr(13);
      end loop;
    
      close o;
    
      insert into tmp_message values (v_org_code, v_result);
    end loop;
    commit;
  end;

end;

把相同组织的消息合并起来的语句