PL/SQL Cursor Update
PL/SQL stands for Procedural Language/Structured Query Language. It has block structure programming features. In Oracle PL/SQL, cursors play a vital role in managing and processing query results. Among the various types of cursors, updatable cursors stand out for their ability to fetch data and modify rows directly.
In this article, we will cover the PL/SQL Cursors, with a particular focus on Updatable Cursors.
What are PL/SQL Cursors?
Context area is a special memory that is used by Oracle for Storing and retrieving information from the database. It contains all the details related to the database. The cursor is a virtual pointer to the context area, in the database. The cursor helps to process through the rows one by one. It can be used implicitly or explicitly. Implicit cursor is used without declaring the cursor with a cursor name in the declaration section.
The maximum number of characters allowed in a cursor name is 128. The cursor name should be unique. An explicit cursor is declared with a cursor name and the SELECT statement. There are two types of cursor, read-only and updatable.
Read-Only Cursor
The read-only cursor cannot modify the data. It is declared with the FOR READ ONLY keyword in the SELECT statement. Usually FOR READ ONLY keyword is optional as the SELECT statement performs read-only operations.
Updatable Cursor
Updatable cursors are used to perform manipulation operations such as deleting, inserting, and updating rows. FOR UPDATE keyword is used in cursor declaration. Updation can be performed with FOR UPDATE and FOR UPDATE OF keyword. FOR UPDATE locks the selected row so another transaction can't access it.FOR UPDATE OF is used to provide the column for which the row should be locked.
Syntax:
SET SERVEROUTPUT ON; DECLARE --declare variables and cursor; CURSOR cursor_name IS SELECT statement FROM FOR UPDATE; BEGIN OPEN cursor_name; FETCH cursor_name; -- to update UPDATE table_name CLOSE cursor_name; --to commit the changes COMMIT; END; /
Examples of PL/SQL Updatable Cursors
Example 1: Updating Rows in a Table Using FOR UPDATE
Update the score from the Geeks table using FOR UPDATE.
Query:
SET SERVEROUTPUT ON; DECLARE CURSOR cursor_geek IS SELECT Id, Name, Score FROM Geeks FOR UPDATE; variable_id NUMBER; variable_name VARCHAR2(20); variable_score NUMBER; BEGIN -- Table before Updates DBMS_OUTPUT.PUT_LINE('TABLE BEFORE UPDATES'); FOR element IN (SELECT * FROM Geeks) LOOP DBMS_OUTPUT.PUT_LINE(element.Id || ' ' || element.Name || ' ' || element.Score); END LOOP; DBMS_OUTPUT.NEW_LINE; OPEN cursor_geek; LOOP FETCH cursor_geek INTO variable_id, variable_name, variable_score; EXIT WHEN cursor_geek%NOTFOUND; -- update UPDATE Geeks SET Score = variable_score + 100 WHERE CURRENT OF cursor_geek; END LOOP; CLOSE cursor_geek; COMMIT; --Print the updated table DBMS_OUTPUT.PUT_LINE('TABLE AFTER UPDATES'); FOR element IN (SELECT * FROM Geeks) LOOP DBMS_OUTPUT.PUT_LINE(element.Id || ' ' || element.Name || ' ' || element.Score); END LOOP; END; /
Explanation:
- The cursor cursor_geek is declared with the FOR UPDATE clause, locking the rows to be updated.
- The loop fetches each row and updates the Score column by adding 100.
- WHERE CURRENT OF cursor_geek ensures that the update targets the current row
Output:
The output contains the table before the update and the table after updation.

Example 2: Using FOR UPDATE OF to Lock Specific Columns
To update only specific columns in the table, you can use the FOR UPDATE OF clause.
Syntax:
SET SERVEROUTPUT ON; DECLARE --declare variables and cursor; CURSOR cursor_name IS SELECT statement FROM FOR UPDATE OF column_name; -- column to be updated. BEGIN OPEN cursor_name; FETCH cursor_name ; -- to update UPDATE table_name CLOSE cursor_name; --to commit the changes COMMIT; END; /
Query:
SET SERVEROUTPUT ON; DECLARE CURSOR geek_cursor IS SELECT Id, Name, Score FROM Geeks FOR UPDATE OF Score; -- column to be updated variable_id NUMBER; variable_name VARCHAR2(20); variable_score NUMBER; BEGIN -- Table before Updates DBMS_OUTPUT.PUT_LINE('TABLE BEFORE UPDATES'); FOR element IN (SELECT * FROM Geeks) LOOP DBMS_OUTPUT.PUT_LINE(element.Id || ' ' || element.Name || ' ' || element.Score); END LOOP; DBMS_OUTPUT.NEW_LINE; OPEN geek_cursor; LOOP FETCH geek_cursor INTO variable_id, variable_name, variable_score; EXIT WHEN geek_cursor%NOTFOUND; -- Update UPDATE Geeks SET Score = variable_score+ 100 WHERE CURRENT OF geek_cursor; END LOOP; CLOSE geek_cursor; -- Committing changes COMMIT; -- Print the updated table DBMS_OUTPUT.PUT_LINE('TABLE AFTER UPDATES'); FOR element IN (SELECT * FROM Geeks) LOOP DBMS_OUTPUT.PUT_LINE(element.Id || ' ' || element.Name || ' ' || element.Score); END LOOP; END; /
Output:

Explanation:
- The FOR UPDATE OF Score clause locks only the Score column for update operations, allowing more granular control.
- This helps avoid potential conflicts with other processes that might modify other columns in the same rows.
Advantages of Using Updatable Cursors
- It is used to insert, update, and delete rows from the table.
- It provides a more efficient way than SQL statements.
- It performs updation in less number of round trips between the database and the application.
Disadvantages of Using Updatable Cursors
- It may increase complexity while handling multiple tables from the database.
- It may not support updates on aggregations or joins.
- It may face concurrency issues if another process is going on.
Conclusion
PL/SQL Updatable Cursors provide a powerful way to update multiple rows. It helps to increases the performance. It helps to make an efficient and effective Database Management System. By familiarizing yourself with the syntax, examining real-world examples, and considering the advantages and disadvantages, developers can use updateable cursors wisely, improving the performance and functionality of PL/SQL applications.