oracle存储过程带游标例子

a8cz893p50

create or replace procedure sum_storage
is
plant  g_containerinv.plant%type;
sloc   g_containerinv.sloc%type;
part   g_containerinv.partno%type;
qty    g_containerinv.qty%type;
cursor c_sumqty
is
select plantid,whid,partno,sum(qtyperuom) as totalqty from g_container group by plantid,whid,partno;
vr_intoqty   c_sumqty%rowtype;            
begin
open c_sumqty;
loop
fetch c_sumqty   into vr_intoqty;
exit when c_sumqty%notfound;
plant:  = vr_intoqty.plantid;
sloc:  = vr_intoqty.whid;
part:  = vr_intoqty.partno;
qty:  = vr_intoqty.totalqty;
insert into g_containerinv(timekey, plant, sloc, partno, qty, editdate, operater)
values (to_char(sysdate,'yyyymmddhh24miss'),plant,sloc,part,qty,sysdate,'wms');
end loop;
close c_sumqty;
commit;
end sum_storage;
/
---------

CREATE OR REPLACE PROCEDURE add_sup_temp IS
v_group_id  sup_temp.group_id % TYPE;
v_item_id   sup_temp.item_id % TYPE;
v_item_name sup_temp.item_name % TYPE;
v_cnt_group NUMBER(4);
CURSOR c_product_group IS
SELECT group_id, item_id, lang, item_name, classify_id, parent_item
FROM viewA;
CURSOR c_groupCursor(p_group_id sup_acl_product_group.group_id % TYPE) IS
SELECT group_id FROM tableB;
BEGIN
DELETE FROM sup_acl_product_group;
OPEN c_product_group;
LOOP
FETCH c_product_group
INTO v_group_id, v_item_id, v_item_name;
EXIT WHEN c_product_group%NOTFOUND;
OPEN c_groupCursor(v_group_id);
LOOP
FETCH c_groupCursor
INTO v_c_group_id;
EXIT WHEN c_groupCursor%NOTFOUND;
SELECT COUNT(group_id)
INTO v_cnt_group
FROM sup_acl_product_group pg
WHERE pg.group_id = v_c_group_id
AND pg.item_id = v_item_id;
IF v_cnt_group <> 0 THEN
UPDATE sup_acl_product_group pg SET pg.last_update_time = sysdate;
ELSE
INSERT INTO sup_acl_product_group
(group_id, item_id, item_name, last_update_time)
VALUES
(v_c_group_id, v_item_id, v_item_name, sysdate);
END IF;
END LOOP;
CLOSE c_groupCursor;
END LOOP;
CLOSE c_product_group;
COMMIT;
END;



create or replace procedure p_tmp
begin
declare
cursor c_cur is
select  st.business_id            as business_id,
st.userid       as userid,
st.end_date        as end_date,
st.creation_date  as creation_date
from tableH st
where 1=1;
v_row                c_cur%rowtype;
v_business_id_t      varchar2(200);
begin
open c_cur;
loop
fetch c_cur
into v_row;
exit when c_cur%notfound;
select count(n.business_id)
into v_business_id_t
from tableM n
where n.business_id = v_row.business_id
and n.userid = v_row.userid;
if v_business_id_t = 0 then
insert into tableN
(business_id, userid, end_date, creation_date)
values
(v_row.business_id,
v_row.userid,
v_row.end_date,
v_row.creation_date);
end if;
if v_business_id_t > 0 then
update tableN t
set t.end_date = v_row.end_date
where t.business_id = v_row.business_id
and t.userid = v_row.userid;
end if;
end loop;
close c_cur;
commit;
end;
exception
when others
then
rollback;
dbms_output.put_line('异常啦');
end;