i my procedure i use 3 table as select for update. after getting select in cursor o update values in tables. and i have my values in cursor. my goal is to return values in cursor as SYS_REFCURSOR.
/* Formatted on 10/12/2022 12:13:44 (QP5 v5.388) */
CREATE OR REPLACE PACKAGE BODY sh1.test IS
PROCEDURE sms2 (p_return_code OUT INTEGER, v_out OUT SYS_REFCURSOR) IS
v_run_type VARCHAR (20) := 'SMS';
v_batch_size INTEGER;
v_threshold_size INTEGER;
v_retry_time INTEGER;
v_exception_list VARCHAR (1);
v_q_day VARCHAR (10);
BEGIN
DECLARE
CURSOR c_cur IS
SELECT p.outbound_event_id,
cl.seq_id,
-- select is long
p.bill_account_code
FROM sh1.publish p, sh1.contact_list cl, sh1.actions_cfg ac
WHERE
AND ROWNUM <= v_batch_size
AND ac.das_service = v_run_type
AND p.action_id = ac.action_id
AND p.retry_count <= ac.das_retry_count
AND p.outbound_event_id = cl.outbound_event_id
ORDER BY p.creation_date DESC
FOR UPDATE OF
p.action_status,
cl.status,
p.mode_date,
cl.mode_date
SKIP LOCKED;
c_cur_count c_cur%ROWTYPE;
BEGIN
OPEN c_cur;
FETCH c_cur INTO c_cur_count;
IF c_cur%NOTFOUND THEN
p_return_code := 2;
RETURN;
END IF;
CLOSE c_cur;
FOR cur_rec IN c_cur LOOP
UPDATE sh1.publish p
SET p.action_status = '1', p.mode_date = SYSDATE
WHERE p.outbound_event_id = cur_rec.outbound_event_id;
UPDATE sh1.contact_list cl
SET cl.status = 1, cl.mode_date = SYSDATE
WHERE cl.outbound_event_id = cur_rec.outbound_event_id;
END LOOP;
COMMIT;
p_return_code := 0;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Error code:' || SQLCODE);
DBMS_OUTPUT.put_line ('Error message:' || SQLERRM);
p_return_code := 5;
END;
END sms2;
END test;
i try OPEN v_output FOR SELECT *** option bu i can not use FOR UPDATE option.
1条答案
按热度按时间6jygbczu1#
使用返回SYS_REFCURSOR并在后面处理游标的过程的最简单选项基本上如下所示: