Summary: in this tutorial, you will learn how to use the Oracle updatable cursor to update data in a table.
Introduction to Oracle Cursor
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;
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
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;
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
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; /
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.
In this tutorial, you have learned how to use the Oracle updatable cursor to update data in a table.