What is cursor in Oracle

 

Cursor In Oracle

 

What is cursor in Oracle:

Cursor in a Oracle is object or tool that allows you to retrieve and manipulate from the result set in a PLSQL block. This is used to process the individual rows returned by query.

Suppose you have different books and you want to go through each book and read the contents. I oracle cursor is a tool which helps to go through each row of data in a table just like reading through bunch of books.  

When you want to read a data from a table we can use the cursor.

In Oracle there are two types of Cursors

1.     Automatic Cursor or Implicit Cursor:

It just invisible cursor in Oracle that automatically used when we run a simple query. It retrieves one row at a time just like turning a page after completing it or reading it.

The implicit cursor is automatically created by Oracle when you execute any SQL query. We don’t need to declare implicit cursor by using PLSQL block, it is managed by database engine.

 

2.     Manual Cursor or Explicit Cursor:

Explicit cursors are created by user using PLSQL block. The name is given by user and also managed by user. Explicit cursors provide more flexibility and control over the result set. This helps you to fetch multiple rows, handle exceptions and perform operations on data.

 

Syntax:

Declare

-- Variable declaration

Cursor Cursor_Name IS

Select Column1, Column2

From Tablename

Where Condition;

--Declare variables to hold column values

          Begin

--Open cursor

Open Cursor_Name;

--fetch rows from the cursors into the variables

Loop

Fetch Cursor_Name INTO Variable1, Variable2;

Exit When Cursor_Name% NotFound;     --Exit Cursor

--Process the fetched values

End Loop;

--Close cursor

Close Cursor_Name;

End;

/

Syntax Explanation:

Cusror Cursor_Name IS:

This statement is used to declare explicit cursor. It is used to define SQL query that the cursor will execute.

Variable1, Variable2:

These are variable which hold the values returned by the cursor result set.

Open Cursor_Name:

This method opens the cursor to make ready to fetch the rows.

Loop:

This starts the loop to fetch the rows from the cursor.

Fetch Cursor_Name INTO Variable1,Variable2:

It helps to fetch the values from the cursor into the variables.

Exit When Cursor_Name%NOTFOUND:

This statements exit the loop when there is no rows to fetches into the variables

Close Cursor_Name:

Close the cursor after fetching all the rows

 

For Example;

DECLARE

   CURSOR book_cursor IS

    SELECT name, price

    from books;

    name books.name%type;

   price books.price%type;

   Begin

   open book_cursor;

   Loop

  fetch book_cursor into name, price;

   exit when book_cursor%Notfound;

   DBMS_OUTPUT.PUT_Line('Book Name: ' || Name  ||' ' || 'Book Price : ' || price);

   End Loop;

   close book_cursor;

   End;

   /