今天做邮件群发系统开发,遇到这样一个问题:更改数据库一条记录并返回它的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;
希望对自己和与我有一样需求的朋友,都有所帮助,在此作下符号.