Cursors

                Cursor is an active set area or a temporary working area. Which is used to retrieve the values.

A cursor is a temporary work area created in the system memory when a SQL statement is executed

Cursor attributes:

                %ISOPEN  :          Evaluates true when cursor is opened

                                                Evaluates false  when cursor is opened

                %FOUND:             Evaluates true when the rows are available.

                                                Evaluates false when the rows are available.

                %NOTFOUND :    It is opposite to the FOUND

                %ROWCOUNT:   It will gives the number of rows available

Structure of cursor:

                                                Create cursor

                                                           ↓

                                                Open cursor

                                                           ↓

                                                Fetch cursor values

                                                           ↓

            Close cursor

Access Types:

%type : which is used to access the single row of the same data type.

%rowtype:  which is used to access the multiple rows of the different data types.

Types of cursors:

                There are two types of cursors

1)      Implicit Cursors:  Which is used retrieve the single row.

2)      Explicit Cursors:  Which is used to retrieve the multiple rows.

Syntax:

            
            Create :   CURSOR  cur_name is SELECT statement

Open   :   OPEN Cus_name;

Fetch   :   FETCH cus_name into <variables>

Close   :   CLOSE cus_name

Implicit cursors

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed. 

An implicit cursor is a cursor which is internally created by Oracle. It is created by Oracle for each individual SQL. 

Example:

DECLARE  var_rows number(5);

BEGIN

  UPDATE employee  SET salary = salary + 1000;

  IF SQL%NOTFOUND THEN

         dbms_output.put_line('None of the salaries where updated');

  ELSIF SQL%FOUND THEN

         var_rows := SQL%ROWCOUNT;

         dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

  END IF;

END;

Explicit cursors:

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed. 

Example:

DECLARE 

         Cursor c1 is select * from emp;

V_emp c1%rowtpe;

BEGIN

  Open c1;

  IF  c1%isopend  THEN

         Loop

                             Fetch c1 into v_emp;

                             Exit  WHEN c1%notfound;

         End loop;

    END IF;

         CLOSE C1;

  END;

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;

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 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 FindCours ( 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%notfound then
     cnumber := 9999;

else
     DELETE FROM courses_tbl WHERE CURRENT OF c1;

    COMMIT;

end if;

close c1;

RETURN cnumber;

END;