Cursor in PLSQL(Basic)-Part 1

What is cursor in PLSQL?

       A cursor is a temporary work area created in the system memory when a SQL statement is executed in PLSQL block. A cursor contains information on a select statement and the data retrieved by the select statement.

Types of Cursor:

There are 2 types of cursor.

1.     Implicit cursor
2.     Explicit cursor

            1) Implicit/SQL cursor:
 

      Implicit cursor  also called as sql cursor is a temporary work area which is created and

 Handled automatically by the system/plsql. It can hold only one row.

We can track the information about the execution of implicit cursors through its attributes.

There are mainly 4 cursor attributes.

·         %FOUND

If a DML Statement changed a row then the %FOUND attribute returns True

·         %ISOPEN

If a cursor is open %ISOPEN attribute returns True. But for implicit cursor it is always false

·         %NOTFOUND

If a DML Statement is not changed any row then the %NOTFOUND attribute returns True

·         %ROWCOUNT

It returns the number of rows affected from DML operation.

Note: Cursor has another attribute called  %BULK_ROWCOUNT which is designed for use with FORALL statement.

    2) Explicit Cursor

Explicit  cursor is a temporary work area which is created and  Handled by the developer. It can hold  multiple rows.

Steps to follow to handle explicit cursor:

Step 1: Declaring a cursor

          Explicit cursor need to declare at declarative part of any pl/sql block, subprogram or packages.

 

Step 2: Opening a cursor

Opening a cursor executes the statement which is in the  declared cursor.

  

Step 3: Fetching with a cursor

After open the cursor you need to fetch the rows using LOOP.

 

Step 4: Closing a cursor

Once all the rows are fetched the control will come out from the loop and you need to close the cursor.

Example: 

Cursor with Parameter:

You can declare a cursor with parameter and you  can use this parameter in the query.

While opening the cursor you have to pass the parameter to the cursor. 

Example: