PLSQL

PL/SQL CURSORS

PL/SQL CURSORS

Cursors are said to be a pointer, in the context area. PL/SQL controls the context area through the cursor. Cursors holds the rows may be one or more that are returned by SQL statement. These set of rows; the cursor holds are referred to as the active set. There are two types of cursors:

1.Implicit Cursors
These cursors are automatically created by Oracle, whenever there is an no explicit cursor for the statement. Implicit cursors are not controlled by the programmers. When there is a DML statement i.e., INSERT, UPDATE, DELETE is issued, an implicit cursor is associated with the statement. There are different types of attributes available to the implicit cursors, they are:

  1. %FOUND – This attribute returns true if insert, delete and update statements are affected by one or more rows or if the select into statement returns one or more rows, else it returns false.
  2. %NOTFOUND – this attribute is opposite of %FOUND attribute, returns true if insert, delete and update statements are not affected by one or more rows or if the select into statement returns no rows, else it returns false.
  3. %ISOPEN – this attribute always returns false for the implicit cursors, since Oracle closes SQL Cursor automatically after completing the execution of its associated SQL statement. 
  4. %ROWCOUNT – this returns number of rows that are affected by INSERT, DELETE and UPDATES statements or returned by the SELECT INTO statement.

We can access any of the SQL cursor attribute by sql%attribute_name.

Example:
Refer to the table created in the functions section.
SELECT * from teachers;
DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE teachers 
   SET salary = salary + 100; 
   IF sql%notfound THEN 
      dbms_output.put_line('no teachers selected'); 
   ELSEIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' teachers selected '); 
   END IF;  
END; 
/

Output:

5 teachers selected  

PL/SQL procedure successfully completed. 

We can check the rows; they will be updated like below.

SELECT * from teachers.

ID

NAME

AGE

ADDRESS

SALARY

1

Ranjitha

34

Bangalore

2100

2

Sai

30

Hyderabad

2600

3

Shakthi

34

Pune

3100

4

Gayathri 

36

Bangalore

4100

5

Vignesh

34

Mumbai

4600

2.Explicit Cursors – These cursors are programmer-defined cursors for gaining control over the context area. These cursors are defined in the declaration section of the block. And this is created on a select statement that returns more than one row. 

Syntax:

CURSOR cursor_name IS select_statement;

When we are working with the Explicit cursors, following steps are included:

  1. Cursor should be declared for initializing the memory.
  2. Cursor should be opened for allocating the memory.
  3. Fetch the cursor to retrieve the data.
  4. And then close the cursor for releasing the allocated memory.

Declaring the cursor: This defines the cursors with a name and also with associated select statement.

 CURSOR t_teachers IS 

   SELECT id, name, address FROM teachers; 

 

Opening the cursor: This allocates the memory for the cursor and also makes it ready for fetching the rows.

   OPEN t_teachers;

Closing the cursors: It means releasing the allocated memory.

CLOSE t_teachers;

Fetching the cursors: This means accessing one row at a time.

FETCH t_teachers INTO t_id, t_name;

Example:

DECLARE 

t_id teachers.id%type; 

   t_name teachers.name%type; 

   t_addr teachers.address%type; 

   CURSOR t_teachers is 

      SELECT id, name FROM teachers; 
BEGIN 
   OPEN t_teachers; 
   LOOP 
   FETCH t_teachers into t_id, t_name; 
      EXIT WHEN t_teachers%notfound; 
      dbms_output.put_line(t_id || ' ' || t_name || ' ); 
   END LOOP; 
   CLOSE t_teachers; 
END; 
/

Output:

1 ranjitha

2 sai

3 shakthi

4 gayathri

5 vignesh

PL/SQL procedure successfully completed.