CURRENT OF & FOR UPDATE

Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE

oracle_pl/sql_where_current_of_&_for_update

The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.

The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted. We must declare the cursor with the FOR UPDATE clause to use this feature.

Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

Syntax :

WHERE [CURRENT OF cursor_name | search_condition]

Example :

CREATE OR REPLACE function findCourse ( name_in IN VARCHAR2 )
RETURN NUMBER
IS
    cnumber NUMBER;
    CURSOR c1
    IS
        SELECT course_number
        FROM courses_tbl
        WHERE course_name = name_in
        FOR UPDATE of instructor;
    BEGIN
        OPEN c1;
        FETCH c1 INTO cnumber;
        IF c1%NOFOUND THEN
            cnumber := 9999;
        ELSE
            UPDATE courses_tbl
            SET instructor = 'SMITH'
            WHERE CURRENT OF c1;
            
            COMMIT;
        END IF;
        CLOSE c1;
     RETURN cnumber;
    END;


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.