{"id":2865,"date":"2019-08-09T03:54:51","date_gmt":"2019-08-09T10:54:51","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=2865"},"modified":"2025-05-25T03:47:12","modified_gmt":"2025-05-25T10:47:12","slug":"oracle-cursor-for-update","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/oracle-cursor-for-update\/","title":{"rendered":"Oracle CURSOR FOR UPDATE"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn how to use the Oracle updatable cursor to update data in a table.<\/p>\n\n\n\n

Introduction to Oracle Cursor FOR UPDATE #<\/a><\/h2>\n\n\n\n

Sometimes, you want to lock a set of rows before you can update<\/a> them in your program. Oracle provides the FOR UPDATE<\/code> clause of the SELECT<\/a><\/code> statement in an updatable cursor to perform this kind of locking mechanism.<\/p>\n\n\n\n

Here’s the syntax for declaring an updatable cursor:<\/p>\n\n\n

CURSOR<\/span> cursor_name IS<\/span>\n    SELECT<\/span> select_clause\n    FROM<\/span> from_clause\n    WHERE<\/span> where_clause\n    FOR<\/span> UPDATE<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The new syntax here is the FOR UPDATE<\/code> keywords.<\/p>\n\n\n\n
Once you open the cursor, Oracle will lock all rows selected by the SELECT ... FOR UPDATE<\/code> statement in the tables specified in the FROM<\/code> clause. These rows will remain locked until the cursor is closed or the transaction is completed with either COMMIT<\/code> or ROLLBACK<\/code>.<\/p>\n\n\n\n
If you have a specific column that you want to update, you can list it in the FOR UPDATE<\/code> clause as follows:<\/p>\n\n\n
CURSOR<\/span> cursor_name IS<\/span>\n    SELECT<\/span> select_clause\n    FROM<\/span> from_clause\n    WHERE<\/span> where_clause\n    FOR<\/span> UPDATE<\/span> OF<\/span> column_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, Oracle only locks rows of the table that has the column name listed in the FOR UPDATE OF<\/code> clause.<\/p>\n\n\n\n
Note that if you use only FOR UPDATE<\/code> clause and do not include one or more column after the OF<\/code> keyword, Oracle will then lock all selected rows across all tables listed in the FROM<\/code> clause.<\/p>\n\n\n\n
Oracle Cursor FOR UPDATE example #<\/a><\/h2>\n\n\n\n
We’ll use the customers<\/code> and orders<\/code> from sample database<\/a>:<\/p>\n\n\n\n
\"Customers<\/figure>\n\n\n\n
The following example uses a cursor FOR UPDATE<\/code> to update credit limit for customers:<\/p>\n\n\n
DECLARE<\/span>\n    -- customer cursor<\/span>\n    CURSOR<\/span> c_customers IS<\/span> \n        SELECT<\/span> \n            customer_id, \n            name<\/span>, \n            credit_limit\n        FROM<\/span> \n            customers\n        WHERE<\/span> \n            credit_limit > 0<\/span> \n        FOR<\/span> UPDATE<\/span> OF<\/span> credit_limit;\n    -- local variables<\/span>\n    l_order_count PLS_INTEGER := 0<\/span>;\n    l_increment   PLS_INTEGER := 0<\/span>;\n    \nBEGIN<\/span>\n    FOR<\/span> r_customer IN<\/span> c_customers\n    LOOP<\/span>\n        -- get the number of orders of the customer<\/span>\n        SELECT<\/span> COUNT(*)\n        INTO<\/span> l_order_count\n        FROM<\/span> orders\n        WHERE<\/span> customer_id = r_customer.customer_id;\n        -- <\/span>\n        IF<\/span> l_order_count >= 5<\/span> THEN<\/span>\n            l_increment := 5<\/span>;\n        ELSIF<\/span> l_order_count < 5<\/span> AND<\/span> l_order_count >=2<\/span> THEN<\/span>\n            l_increment := 2<\/span>;\n        ELSIF<\/span> l_increment = 1<\/span> THEN<\/span>\n            l_increment := 1<\/span>;\n        ELSE<\/span> \n            l_increment := 0<\/span>;\n        END<\/span> IF<\/span>;\n        \n        IF<\/span> l_increment > 0<\/span> THEN<\/span>\n            -- update the credit limit<\/span>\n            UPDATE<\/span> \n                customers\n            SET<\/span> \n                credit_limit = credit_limit * ( 1<\/span> +  l_increment\/ 100<\/span>)\n            WHERE<\/span> \n                customer_id = r_customer.customer_id;\n            \n            -- show the customers whose credits are increased<\/span>\n            dbms_output.put_line('Increase credit for customer '<\/span> \n                || r_customer.NAME || ' by '<\/span> \n                || l_increment || '%'<\/span> );\n        END<\/span> IF<\/span>;\n    END<\/span> LOOP<\/span>;\n    \n    EXCEPTION<\/span>\n        WHEN<\/span> OTHERS THEN<\/span>\n            dbms_output.put_line('Error code:'<\/span> || SQLCODE);\n            dbms_output.put_line('Error message:'<\/span> || sqlerrm<\/span>);\n            RAISE<\/span>;\n            \nEND<\/span>;\n\/\n<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Increase credit for customer Supervalu by 5%\nIncrease credit for customer NextEra Energy by 5%\nIncrease credit for customer Raytheon by 2%\nIncrease credit for customer Plains GP Holdings by 2%\n...<\/code><\/span><\/pre>\n\n\n
How it works:<\/p>\n\n\n\n
Since Oracle locks all rows returned by the SELECT ... FOR UPDATE<\/code> during the update, you should have a WHERE<\/a><\/code> clause to select only necessary rows to be locked.<\/p>\n\n\n\n