Oracle PL/SQL: Advanced Cursors

A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement. This tutorial is a continuity of Oracle PL/SQL Cursors which discuses some basic functionalities of PL/SQL Cursors.

Oracle PL/SQL: Cursor Attributes

While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

Below is an example of how you might use the %NOTFOUND attribute.

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

open c1;
fetch c1 into cnumber;
if c1%notfound then
     cnumber := 9999;
end if;

close c1;

RETURN cnumber;

END;

Oracle PL/SQL: SELECT FOR UPDATE Statement

The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

The syntax for the Select For Update is:

CURSOR cursor_name
IS
   select_statement
   FOR UPDATE [of column_list] [NOWAIT];

For example, you could use the Select For Update statement as follows:

CURSOR c1
IS
   SELECT course_number, instructor
   from courses_tbl
   FOR UPDATE of instructor;

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement which is discussed below.

Oracle PL/SQL: WHERE CURRENT OF Statement

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

The syntax for the Where Current Of statement is either:

UPDATE table_name
    SET set_clause
    WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name
WHERE CURRENT OF cursor_name;

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the Where Current Of Statement:

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number, instructor
        from courses_tbl
        where course_name = name_in
        FOR UPDATE of instructor;
BEGIN

open c1;
fetch c1 into cnumber;
if c1%notfound then
     cnumber := 9999;

else
     UPDATE courses_tbl
        SET instructor = ‘SMITH’
        WHERE CURRENT OF c1;

    COMMIT;

end if;
close c1;

RETURN cnumber;

END;

Deleting using the WHERE CURRENT OF Statement

Here is an example where we are deleting records using the Where Current Of Statement:

CREATE OR REPLACE Function FindCourse
   ( name_in IN varchar2 )
   RETURN number
IS
    cnumber number;

    CURSOR c1
    IS
       SELECT course_number, instructor
        from courses_tbl
        where course_name = name_in
        FOR UPDATE of instructor;
BEGIN

open c1;
fetch c1 into cnumber;
if c1%notfound then
     cnumber := 9999;

else
     DELETE FROM courses_tbl
        WHERE CURRENT OF c1;

    COMMIT;

end if;
close c1;

RETURN cnumber;

END;

Related Posts

1. Oracle PL/SQL: Cursors
2. Oracle PL/SQL: Loops and Conditional Statements
3. Oracle PL/SQL: Is Null / Is Not Null
4. Oracle PL/SQL: Transactions
5. Inbuilt Functions in Oracle

Tags: oracle plsql, oracle tutorial, plsql, plsql tutorial