- 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;
- /
例子2
- 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;
例子3
- 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;