Oracle CURSOR FOR UPDATE

Introduction to Oracle Cursor FOR UPDATE

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

Here is the syntax for declaring an updatable cursor:

CURSOR cursor_name IS
    SELECT select_clause
    FROM from_clause
    WHERE where_clause
    FOR UPDATE;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The new syntax here is the FOR UPDATE keywords.

Once you open the cursor, Oracle will lock all rows selected by the SELECT ... FOR UPDATE statement in the tables specified in the FROM clause. And these rows will remain locked until the cursor is closed or the transaction is completed with either COMMIT or ROLLBACK.

Note that Oracle locks all rows returned by the SELECT ... FOR UPDATE during the update, therefore, you should have a WHERE clause to select only necessary rows to be locked.

If you have a specific column that you want to update, you can list it in the FOR UPDATE clause as follows:

CURSOR cursor_name IS
    SELECT select_clause
    FROM from_clause
    WHERE where_clause
    FOR UPDATE OF column_name;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this case, Oracle only locks rows of the table that has the column name listed in the FOR UPDATE OF clause.

Note that if you use only FOR UPDATE clause and do not include one or more column after the OF keyword, Oracle will then lock all selected rows across all tables listed in the FROM clause.

Oracle Cursor FOR UPDATE example

Consider the following example.

DECLARE
    -- customer cursor
    CURSOR c_customers IS 
        SELECT 
            customer_id, 
            name, 
            credit_limit
        FROM 
            customers
        WHERE 
            credit_limit > 0 
        FOR UPDATE OF credit_limit;
    -- local variables
    l_order_count PLS_INTEGER := 0;
    l_increment   PLS_INTEGER := 0;
    
BEGIN
    FOR r_customer IN c_customers
    LOOP
        -- get the number of orders of the customer
        SELECT COUNT(*)
        INTO l_order_count
        FROM orders
        WHERE customer_id = r_customer.customer_id;
        -- 
        IF l_order_count >= 5 THEN
            l_increment := 5;
        ELSIF l_order_count < 5 AND l_order_count >=2 THEN
            l_increment := 2;
        ELSIF l_increment = 1 THEN
            l_increment := 1;
        ELSE 
            l_increment := 0;
        END IF;
        
        IF l_increment > 0 THEN
            -- update the credit limit
            UPDATE 
                customers
            SET 
                credit_limit = credit_limit * ( 1 +  l_increment/ 100)
            WHERE 
                customer_id = r_customer.customer_id;
            
            -- show the customers whose credits are increased
            dbms_output.put_line('Increase credit for customer ' 
                || r_customer.NAME || ' by ' 
                || l_increment || '%' );
        END IF;
    END LOOP;
    
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Error code:' || SQLCODE);
            dbms_output.put_line('Error message:' || sqlerrm);
            RAISE;
            
END;
/
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works.

• First, declare an updatable cursor that updates credits of the customers whose credits are greater than zero.
• Next, loop over the rows in the cursors.
• Then, get the number of orders for each customer.
• After that, assign the credit increment based on the order count.
• Finally, update the credit of the customer.