今天做郵件群發系統開發,遇到這樣一個問題:更改數據庫一條記錄并返回它的id.
下面記錄我的思維過程:
方法一:
1.先select ...for update的方式找出這條記錄,同時將它鎖定;
2.再根據id update 該條記錄;
3.最后返回該條記錄的id;
這種方法的存儲過程如下:
CREATE OR REPLACE PROCEDURE backtrack_task_Id
(in_status_will_sending IN NUMBER,
in_status_now_sending IN NUMBER,
in_fixed_sending IN NUMBER,
out_taskId OUT NUMBER)
AS
temp_task_id NUMBER;
update_number NUMBER;
BEGIN
IF in_fixed_sending = 1 THEN
SELECT id
INTO temp_task_id
FROM tasks
WHERE status = in_status_will_sending
AND SYSDATE >= send_date
AND ROWNUM < 2 FOR UPDATE;
ELSE
SELECT id
INTO temp_task_id
FROM tasks
WHERE status = in_status_will_sending
AND ROWNUM < 2 FOR UPDATE;
END IF;
IF temp_task_id IS NOT NULL THEN
UPDATE tasks
SET status = in_status_now_sending
WHERE id = temp_task_id
AND status = in_status_will_sending;
update_number := SQL % ROWCOUNT;
END IF;
IF update_number = 1 THEN
out_taskId := temp_task_id;
ELSE
out_taskId := -1;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
out_taskId := -1;
END backtrack_task_Id;方法二:
update的同時返回該條記錄的信息(id)
這種方法的存儲過程如下:
CREATE OR REPLACE PROCEDURE backtrack_taskId
(in_status_will_sending IN NUMBER,
in_status_now_sending IN NUMBER,
in_fixed_sending IN NUMBER,
out_taskId OUT NUMBER)
AS
temp_task_id NUMBER;
BEGIN
IF in_fixed_sending = 1 THEN
UPDATE tasks
SET status = in_status_now_sending
WHERE status = in_status_will_sending
AND SYSDATE >= send_date
AND ROWNUM < 2
RETURNING id INTO temp_task_id;
ELSE
UPDATE tasks
SET status = in_status_now_sending
WHERE status = in_status_will_sending
AND ROWNUM < 2
RETURNING id INTO temp_task_id;
END IF;
out_taskId := temp_task_id;
END backtrack_taskId;
希望對自己和與我有一樣需求的朋友,都有所幫助,在此作下符號.