Cursor in PL/SQL – PL/SQL Oracle Tutorial

Hola! Friends, I hope you guys are doing good. In this article, we will discuss the Cursors in PL/SQL How it works.

If you missed the previous article of function in PL/SQL.

The topics which will we are going to cover in this tutorial:

Introduction of Cursor in PL/SQL

A PL/SQL Cursor is a pointer of the context area ( Oracle creates a memory place is known as the context area). A context area contains all information about the statement which will use in the program. PL/SQL supervises the context area along with a cursor.

We can name a cursor so that we can call them whenever we need it. In simple words, we can say that by using the cursor, we can fetch multiple rows of data from a single query.

Two types of the cursor, are found in Oracle.

  • Implicit cursors
  • Explicit cursors
cursor in pl/sql

What is Implicit Cursor in PL/SQL?

An implicit cursor is formed by its self whenever we use the SELECT INTO, INSERT, UPDATE, DELETE then implicit cursor automatically created.

The implicit cursor is not effective when the query will return zero or multiple rows, then these exceptions will occur NO_DATA_FOUND or TOO_MANY_ROWS, ETC.

Oracle PL/SQL allows some attributes define as the Implicit cursor’s attributes to examine the status of DML commands as for example, %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.

AttributesDescriptions
%FOUNDThis return the value is TRUE if DML operation (INSERT, DELETE, and UPDATE effect at least one row or more rows or a SELECT INTO statement). Else, it returns FALSE.
%ISOPENThis keyword always gives back FALSE for implicit cursors because the SQL cursor is automatically closed after executing its associated SQL statements.
%NOTFOUNDThis attribute returns a value is TRUE if DML operations. Otherwise, it returns FALSE. It is just the opposite of %FOUND.
%ROWCOUNTThis function of SQL returns the number of rows affected by DML operations( INSERT, DELETE, and UPDATE or SELECT INTO).

Example:

DECLARE   
   totalnorows number(2);  
BEGIN  
   UPDATE  customers  
   SET vsalary = vsalary + 5000;  
   IF (sql%notfound) THEN  
      dbms_output.put_line('here,no customers rows updated');  
   ELSIF (sql%found) THEN  
      totalnorows := sql%rowcount;  
      dbms_output.put_line( totalnorows || ' customers rows updated ');  
   END IF;   
END;  
/

What is an Explicit Cursor in PL/SQL?

An Explicit cursor is programmer-friendly cursors for gathering more command over the context area. An explicit cursor should be declared or used in the declaration part of the PL/SQL Block. It is created on a SELECT query that will fetch and return more than one row.

The syntax of Explicit cursor is:

CURSOR cursor_name IS select_query; 

Syntax Explanation:

  • Here, cursor_name specify the name of the cursor, which is declared after the CURSOR keyword.
  • The select_query define a select query to fetch data after the IS keyword.
cursor in pl/sql

Declaration of the Explicit Cursor :

A programmer can control the explicit cursor through Open, Fetch, and Close.

Open the cursor:

The Open keyword is used to allocate some memory for the cursor, and through this, it easier to fetch the rows coming from the SQL query into it.

Syntax:

OPEN cursor_name;  

Fetch the cursor:

This is used to acquire one row at a time. We can fetch the rows from the opened cursor.

Syntax:
FETCH cursor_name INTO variable;  

Close the cursor:

By using this, a cursor be closed. Close has used the release the memory, which is captured in creating a time of cursor.

Syntax:

CLOSE cursor_name;

Explicit Cursor Example:

DECLARE  
   v_id customers.id%type;  
   v_name customers.name%type;  
   v_addr customers.address%type;  
   CURSOR v_customers is  
      SELECT id, name, address into custid, custname, custadd FROM customers;  
BEGIN  
   OPEN v_customers;  
   LOOP  
      FETCH v_customers into v_id, v_name, v_addr;  
      EXIT WHEN v_customers%notfound;  
      dbms_output.put_line(v_id || ' ' || v_name || ' ' || v_addr);  
   END LOOP;  
   CLOSE v_customers;  
END;  
/ 

FAQs:

What is actual work of a cursor?

The core concept of the cursor is fetching no of rows in sequence.

What are the basic use of MERGE?

The MERGE is used to merging with multiple DML statements into one statement.

What is a Candidate Key in PL/SQL?

The columns in a table Which can act as a Primary Key are defined as the Candidate Key.


In the above, we have discussed cursor in PL/SQL, their types and also how to use with suitable example. For more, please Keep in touch,

Thank You 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *