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;
把相同组织的消息合并起来的语句