Cursors in PL/SQL

Hi Everyone, Today's topic is Cursors

When word "Cursor" occurs, It means there is a Pointer which point out something.

When an SQL statement is processed, Oracle creates a memory area known as context area.

A cursor is a pointer to this context area.

It contains all information needed for processing the statement.

In PL/SQL, the context area is controlled by Cursor.

A cursor contains information on a select statement and the rows of data accessed by it

Types of Cursor in PL/SQL:

1. Implicit Cursors

2. Explicit Cursors

1. Implicit Cursors:

Whenever Oracle executes an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE, it automatically creates an implicit cursor.

Orcale provides some attributes known as Implicit cursor's attributes to check the status of DML operations.

Some of them are:

%FOUND : It return TRUE when the DML statement Or SELECT affect at least one row.

%NOTFOUND : It is opposite of %FOUND.

%ROWCOUNT : It will return Number of row affected or return after SQL statement execution

%ISOPEN : It always returns FALSE for implicit cursors, because the SQL cursor is automatically closed after executing its associated SQL statements.

Explicit Cursor:

The Explicit cursors are defined by the programmers to gain more control over the context area. These cursors should be defined in the declaration section of the PL/SQL block.

It is created on a SELECT statement which returns more than one row.

Here is Life Cycle of Explicit Cursor:

1. Declare Cursor:

It defines the cursor with a name and the associated SELECT statement.

Syntax:

Before using an explicit cursor, you must declare it in the declaration section of a block or package as follows:

CURSOR name IS 
 SELECT statement;  

2. Open Cursor:

Syntax:

OPEN cursor_name;  

3. Fetch the cursor:

Syntax:

FETCH cursor_name INTO variable_list;  

4. Close Cursor:

Syntax:

Close cursor_name;  

Example:

DECLARE  
   s_id students.student_id%type;   
   s_name students.sname%type;  
   s_addr students.student_city%type;  
   CURSOR s_students is   --Declare Cursor
      SELECT student_id, sname, student_city FROM students;  -- Query
BEGIN  
   OPEN s_students;  -- Open cursor
   LOOP  
      FETCH s_students into s_id, s_name, s_addr;  -- Fetch Cursor
      EXIT WHEN s_students%notfound;  -- %NOTFOUND attribute
      dbms_output.put_line(s_id || ' ' || s_name || ' ' || s_addr);  
   END LOOP;  
   CLOSE s_students;  --Close Cursor
END;  

Output:

This is the basic of cursor. Some advance example will be updated later. Keep Connect.

1. Cursor with For loop

2. Cursor with Parameters

Thank you for reading.

Ask your Doubt or Query in Comment Sections.