Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / Oracle CURSOR FOR UPDATE

Oracle CURSOR FOR UPDATE

Summary: in this tutorial, you will learn how to use the Oracle updatable cursor to update data in a table.

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;

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;

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; /

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.

  • Was this tutorial helpful?
  • YesNo
Previous PL/SQL Cursor Variables with REF CURSOR
Next PL/SQL Procedure

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.