Advanced Search

UPDATE using WHERE CURRENT OF clause

/ Published in: PL/SQL
Save to your folder(s)
I can never remember the syntax on the "FOR UPDATE OF" clause!

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DECLARE
  2.     /* Search query using cursor, then update selected columns in the searched table(s)
  3.     */
  4.  
  5.     update_count PLS_INTEGER := 0;
  6.  
  7.     CURSOR people_cur IS
  8.         SELECT zip_code 
  9.           FROM addresses 
  10.          WHERE zip_code = '00000' 
  11.     FOR UPDATE OF zip_code;
  12. BEGIN
  13.     FOR search_rec IN people_cur
  14.     LOOP
  15.         UPDATE addresses 
  16.            SET zip_code = '' 
  17.         WHERE CURRENT OF people_cur;
  18.  
  19.         update_count := update_count + 1;
  20.     END LOOP;
  21.  
  22.     COMMIT;
  23.     DBMS_OUTPUT.PUT_LINE('Updated ' || update_count || ' addresses.');
  24.  
  25. EXCEPTION
  26.     WHEN OTHERS THEN
  27.         DBMS_OUTPUT.PUT_LINE('EXCEPTION - ' || SQLCODE || ': ' || SQLERRM);
  28.         DBMS_OUTPUT.PUT_LINE('Error stack at top level:');
  29.         DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_backtrace);
  30. END;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.