oracle 游标

---------create
-----如果项目阶段到了9自动释放
-----------------------------------
PROCEDURE release_project IS
v_step_name NUMBER := 1; ------阶段
v_pick_person_id NUMBER;
v_picking_id NUMBER;
l_iface cop_project_picking_his%ROWTYPE;
CURSOR cur_data IS
SELECT h.fstep_name, h.fsys_sq
FROM cop_cop_or_header_v h
WHERE h.fstep_name = '9';
BEGIN
SAVEPOINT step_1;

FOR rec IN cur_data
LOOP
BEGIN
BEGIN
SELECT cpp.pick_person_id, cpp.picking_id
INTO v_pick_person_id, v_picking_id
FROM cop_project_picking cpp
WHERE cpp.project_id = rec.fsys_sq
AND rownum = 1;
EXCEPTION
WHEN OTHERS THEN
v_pick_person_id := 0;
END;
IF v_pick_person_id != 0
THEN
--删除COP_PROJECT_PICKING表信息
DELETE FROM cop_project_picking pick
WHERE pick.project_id = rec.fsys_sq;

--将传入的信息插入到COP_PROJECT_PICKING_HIS历史表中
l_iface.picking_id := v_picking_id;
l_iface.project_id := rec.fsys_sq;
l_iface.pick_person_id := v_pick_person_id;
l_iface.pool_id := -1;
l_iface.action_code := 'UNPICK';
l_iface.remark := '项目阶段到9自动释放';
l_iface.creation_date := SYSDATE;
l_iface.created_by := -1;
l_iface.last_update_date := SYSDATE;
l_iface.last_updated_by := -1;
INSERT INTO cop_project_picking_his VALUES l_iface;

END IF;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO step_1;
--dbms_output.put_line('锁定回滚原因: '|| SQLERRm);
END release_project;