CREATEORREPLACEPROCEDURE backtrack_task_Id (in_status_will_sending INNUMBER, in_status_now_sending INNUMBER, in_fixed_sending INNUMBER, out_taskId OUT NUMBER) AS temp_task_id NUMBER; update_number NUMBER; BEGIN IF in_fixed_sending =1THEN SELECT id INTO temp_task_id FROM tasks WHERE status = in_status_will_sending AND SYSDATE >= send_date AND ROWNUM <2FORUPDATE; ELSE SELECT id INTO temp_task_id FROM tasks WHERE status = in_status_will_sending AND ROWNUM <2FORUPDATE; ENDIF; IF temp_task_id ISNOTNULLTHEN UPDATE tasks SET status = in_status_now_sending WHERE id = temp_task_id AND status = in_status_will_sending; update_number := SQL %ROWCOUNT; ENDIF; IF update_number =1THEN out_taskId := temp_task_id; ELSE out_taskId :=-1; ENDIF; EXCEPTION WHEN NO_DATA_FOUND THEN out_taskId :=-1; END backtrack_task_Id;
方法二: update的同时返回该条记录的信息(id) 这种方法的存储过程如下:
CREATEORREPLACEPROCEDURE backtrack_taskId (in_status_will_sending INNUMBER, in_status_now_sending INNUMBER, in_fixed_sending INNUMBER, out_taskId OUT NUMBER) AS temp_task_id NUMBER; BEGIN IF in_fixed_sending =1THEN 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; ENDIF; out_taskId := temp_task_id; END backtrack_taskId;