PL/SQL Cursor Update

PL/SQL stands for Procedural Language/ Structured Query Language. It has block structure programming features.PL/SQL supports SQL queries. It also supports the declaration of the variables, control statements, Functions, Cursor, Procedure, and Triggers.PL/SQL contains declaration section, execution section, exception section, and end. Declare and exception blocks are optional.

In this article, we will cover the PL/SQL Cursors, with a particular focus on Updatable Cursors. Cursors in PL/SQL are pivotal for handling result sets, and there are two main types: read-only and updatable. Read-only cursors allow data retrieval but prohibit modifications, while updatable cursors extend this functionality, permitting both fetching and updating of data.

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.

The Read-Only

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 Geeks Table Scores Using For UPDATE in PL/SQL

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 with FOR UPDATE keyword is declared in the declaration section. It is opened and fetched in the execution section. The Fetch operation is performed in loop to update each row UPDATE keyword is used to update the table.” WHERE CURRENT OF cursor_geek” is used to point to the current row. “cursor_geek%NOTFOUND” is used as can condition till there are rows in the table. The cursor is closed after the updation.

Output:

The output contains the table before the update and the table after updation.

PL/SQL UPDATABLE CURSOR

Example 2: Updating Geeks Table Scores Using For UPDATE OF in PL/SQL

Update the score from the Geeks table using FOR UPDATE OF.

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:

PL/SQL UPDATABLE CURSOR

Explanation: FOR UPDATE OF keyword is used to lock the score column from the geeks table.

Advantages and Disadvantages of the Updatable Cursors

Advantages

• 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

• 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.