Posted By

stews on 09/17/08


Tagged

update where of current


Versions (?)

UPDATE using WHERE CURRENT OF clause


 / Published in: PL/SQL
 

I can never remember the syntax on the "FOR UPDATE OF" clause!

  1. /* Search query using cursor, then update selected columns in the searched table(s)
  2.   */
  3.  
  4. update_count PLS_INTEGER := 0;
  5.  
  6. CURSOR people_cur IS
  7. SELECT zip_code
  8. FROM addresses
  9. WHERE zip_code = '00000'
  10. FOR UPDATE OF zip_code;
  11. FOR search_rec IN people_cur
  12. UPDATE addresses
  13. SET zip_code = ''
  14. WHERE CURRENT OF people_cur;
  15.  
  16. update_count := update_count + 1;
  17.  
  18. DBMS_OUTPUT.PUT_LINE('Updated ' || update_count || ' addresses.');
  19.  
  20. DBMS_OUTPUT.PUT_LINE('EXCEPTION - ' || SQLCODE || ': ' || SQLERRM);
  21. DBMS_OUTPUT.PUT_LINE('Error stack at top level:');
  22. DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_backtrace);

Report this snippet  

You need to login to post a comment.