Revision: 8379
Updated Code
at September 17, 2008 17:19 by stews
Updated Code
DECLARE
/* Search query using cursor, then update selected columns in the searched table(s)
*/
update_count PLS_INTEGER := 0;
CURSOR people_cur IS
SELECT zip_code
FROM addresses
WHERE zip_code = '00000'
FOR UPDATE OF zip_code;
BEGIN
FOR search_rec IN people_cur
LOOP
UPDATE addresses
SET zip_code = ''
WHERE CURRENT OF people_cur;
update_count := update_count + 1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Updated ' || update_count || ' addresses.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION - ' || SQLCODE || ': ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('Error stack at top level:');
DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
END;
Revision: 8378
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at September 17, 2008 17:16 by stews
Initial Code
DECLARE
/* Search query using cursor, then update selected columns in the searched table(s)
*/
update_count PLS_INTEGER := 0;
CURSOR people_cur IS
SELECT zip_code FROM addresses WHERE zip_code = '00000' FOR UPDATE OF zip_code;
BEGIN
FOR search_rec IN people_cur
LOOP
UPDATE addresses SET zip_code = '' WHERE CURRENT OF people_cur;
update_count := update_count + 1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Updated ' || update_count || ' addresses.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION - ' || SQLCODE || ': ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('Error stack at top level:');
DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
END;
Initial URL
Initial Description
I can never remember the syntax on the "FOR UPDATE OF" clause!
Initial Title
UPDATE using WHERE CURRENT OF clause
Initial Tags
update
Initial Language
PL/SQL