使用游标For循环更新表时，如何处理指定记录不存在的异常？

First, let's start with your existing code, formatted properly, and fix a critical issue that would break its functionality:

DECLARE
  CURSOR incr_cur IS 
    SELECT * FROM emp3 FOR UPDATE OF sal;
  v_job emp3.job%TYPE := '&ENTER_Job';
  v_cnt INTEGER := 0;
BEGIN
  -- Combine update, count, and output into one loop (fixes cursor closure issue)
  FOR r_l IN incr_cur LOOP
    IF v_job = r_l.job THEN  -- Use = instead of IN for single-value comparison
      UPDATE emp3 SET sal = sal + 100 WHERE CURRENT OF incr_cur;
      v_cnt := v_cnt + 1;
      DBMS_OUTPUT.PUT_LINE('The Salary of ' || r_l.ename || ' is Incremented by 100 and the Updated Salary is ' || (r_l.sal + 100));
    END IF;
  END LOOP;

  -- Handle case where no matching records exist
  IF v_cnt = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No employees found with job title: ' || v_job);
    -- Optional: Raise a custom error if this scenario should be treated as an exception
    RAISE_APPLICATION_ERROR(-20001, 'No employees exist for the specified job: ' || v_job);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error occurred: ' || SQLERRM);
    RAISE;  -- Re-raise the exception if you want it to propagate upstream
END;
/

Key Fixes & Improvements: #

• Removed redundant second cursor loop: A FOR loop over a cursor automatically closes the cursor when it finishes, so your original second loop would never execute. Combining all logic into one loop fixes this and boosts efficiency.
• Simplified condition: Replaced IN with = since v_job holds a single value—this makes the code more readable without changing functionality.
• Added immediate salary output: Calculated r_l.sal + 100 to show the updated salary directly in the output.

Handling Missing Records (No Matching Job) #

Oracle doesn’t throw a built-in exception when no records match your cursor’s filter—your loop just runs zero iterations. To handle this scenario, you have two practical approaches:

1. Check the Count Variable

As shown in the optimized code, use v_cnt to track how many records were updated. After the loop, if v_cnt equals 0, print a user-friendly message to inform the user no matches were found.

2. Raise a Custom Exception

If the absence of matching records should be treated as an error (e.g., in a batch process where this scenario is unexpected), use RAISE_APPLICATION_ERROR to throw a custom exception with a descriptive message. This allows upstream code to catch and handle the issue appropriately.

Additional Notes: #

• The FOR UPDATE OF sal clause locks the sal column of selected rows, preventing other sessions from modifying those rows until your transaction completes. Ensure this aligns with your application’s concurrency needs.
• Always include an EXCEPTION block to catch unexpected errors (like invalid input, table access issues, etc.). The WHEN OTHERS clause catches all unhandled exceptions, but re-raising it ensures the error isn’t silently swallowed.

内容的提问来源于stack exchange，提问作者P Prad