In this blog, we’ll discuss the cursors in PL/SQL. Oracle creates a memory area, referred to as the context area, for processing an SQL statement, which contains all the knowledge needed for processing the statement; for instance, the number of rows processed, etc.
A cursor may be a pointer to the present context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is mentioned because the active set.
You can name a cursor in order that it might be mentioned during a program to fetch and process the rows returned by the SQL statement, one at a time. There are two sorts of cursors
• Implicit cursors
• Explicit cursors
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there’s no explicit cursor for the statement. Programmers cannot control the implicit cursors and therefore the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is related to this statement. For INSERT operations, the cursor holds the info that must be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that might be affected.
In PL/SQL, you’ll ask the foremost recent implicit cursor because the SQL cursor, which always has attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed to be used with the FORALL statement. the subsequent table provides the outline of the foremost used attributes –

S.No	Attribute & Description
1	%FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a get INTO statement returned one or more rows. Otherwise, it returns FALSE.
2	%NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a get INTO statement returned no rows. Otherwise, it returns FALSE.
3	%ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
4	%ROWCOUNT Returns the amount of rows suffering from an INSERT, UPDATE, or DELETE statement, or returned by a get INTO statement.

Any SQL cursor attribute are going to be accessed as sql%attribute_name as shown below within the example.
Example
We will be using the purchasers table we had created and utilized in the previous chapters.
Select * from customers;

+—-+———-+—–+———–+———-+
| ID | NAME | AGE | ADDRESS | SALARY |
+—-+———-+—–+———–+———-+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+—-+———-+—–+———–+———-+
The following program will update the table and increase the salary of every customer by 500 and use the SQL%ROWCOUNT attribute to work out the amount of rows affected −

DECLARE 
total_rows number(2); 
BEGIN 
UPDATE customers 
SET salary = salary + 500; 
IF sql%notfound THEN 
dbms_output.put_line('no customers selected'); 
ELSIF sql%found THEN 
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected '); 
END IF; 
END; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
6 customers selected

PL/SQL procedure successfully completed.
If you check the records in customers table, you’ll find that the rows are updated −
Select * from customers;

+—-+———-+—–+———–+———-+
| ID | NAME | AGE | ADDRESS | SALARY |
+—-+———-+—–+———–+———-+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+—-+———-+—–+———–+———-+
Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context area. a particular cursor should be defined within the declaration section of the PL/SQL Block. it’s created on a get Statement which returns quite one row.
The syntax for creating a particular cursor is −
CURSOR cursor_name IS select_statement;
Working with a particular cursor includes the subsequent steps −
• Declaring the cursor for initializing the memory
• Opening the cursor for allocating the memory
• Fetching the cursor for retrieving the info
• Closing the cursor to release the allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a reputation and therefore the associated SELECT statement. for instance −
CURSOR c_customers IS
SELECT id, name, address FROM customers;
Opening the Cursor
Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. for instance , we’ll open the above defined cursor as follows −
OPEN c_customers;
Fetching the Cursor
Fetching the cursor involves accessing one row at a time. for instance , we’ll fetch rows from the above-opened cursor as follows −
FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor
Closing the cursor means releasing the allocated memory. for instance , we’ll close the above-opened cursor as follows −
CLOSE c_customers;
Example
Following may be a complete example for instance the concepts of explicit cursors &minua;

DECLARE 
c_id customers.id%type; 
c_name customer.name%type; 
c_addr customers.address%type; 
CURSOR c_customers is 
SELECT id, name, address FROM customers; 
BEGIN 
OPEN c_customers; 
LOOP 
FETCH c_customers into c_id, c_name, c_addr; 
EXIT WHEN c_customers%notfound; 
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
END LOOP; 
CLOSE c_customers; 
END; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.
So, this brings us to the end of blog. This Tecklearn ‘Cursors in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

492