What’s up Internet? Welcome back once again.
I am Manish from RebellionRider.com. Today in this PL/SQL tutorial we will learn about
cursors in oracle database. Database cursors is the most demanded series
on my channel. Also similar to triggers which we have already covered in the previous tutorials.
Cursor is an important topic from the Job Interview as well as from the oracle certification
perspective. So in this tutorial you will get the answers
of all the questions on database cursors which will help you getting good marks in your exams
as well as ace your Job interview. Do make sure to give this video a big thumbs
up and share it with your friends on your Facebook and twitter or by any means you find
convenient. So without wasting much of your time let’s get started.
So what is a cursor in oracle database? Cursor is a pointer to a memory area called
context area. This context area is a memory region inside the Process Global Area or PGA
assigned to hold the information about the processing of a SELECT statement or DML Statement
such as INSERT, DELETE, UPDATE or MERGE. Let’s take a deep look at the context area.
So What is a context area?
As said above the context area is a memory region inside the Process Global Area or PGA
which helps oracle server in processing an SQL statement by holding the important information
about that statement. This information includes:
• Rows returned by a query. • Number of rows processed by a query.
• A pointer to the parsed query in the shared pool.
Using cursor you can control the context area as it is a pointer to the same. Types of cursors in oracle database:
There are two types of cursors in oracle database. 1. Implicit cursor and
2. Explicit cursor. Implicit cursor in oracle database
As the name suggests these are the cursors which are automatically created by the oracle
server every time an SQL DML statement is executed. User cannot control the behavior
of these cursors. Oracle server creates an implicit cursor in the background for any
PL/SQL block which executes an SQL statement as long as an explicit cursor does not exists
for that SQL statement. Explicit Cursor in oracle database
In contrast to implicit cursors, we have explicit cursors. Explicit cursors are user defined
cursors which means user has to create these cursors for any statement which returns more
than one row of data. Unlike implicit cursor user has full control of explicit cursor.
Steps for creating an Explicit Cursor To create an explicit cursor you need to follow
4 steps. These 4 steps are • Declare
• Open • Fetch and,
• Close In case of implicit cursors oracle server
performs all these steps automatically for you. So now let’s take a quick look at all
these steps one by one. Declare
Declaring a cursor means initializing a cursor into memory. You define explicit cursor in
declaration section of your PL/SQL block and associate it with the SELECT statement.
Syntax of declaring a cursor is pretty simple. CURSOR cursor_name IS select_statement;
Declaration starts with the keyword CURSOR followed by the cursor name, which could be
anything you want. Then we have another keyword IS which is followed by a valid SELECT statement.
After the declaration we have to ‘Open’ the cursor.
In order to put that cursor to work we have to open it first. When you open a cursor the
memory will be allotted to it and it’s all set for the next step which is fetching the
data from it. Here is how you can open a cursor.
OPEN cursor_name; To open an already declared cursor, you simply
have to write a statement which starts with the keyword OPEN followed by the name of your
cursor which you want to open or have declared. You generally write this statement into the
execution section of your PL/SQL block. Next step is fetch. The process of retrieving
the data from the cursor is called fetching. Once the cursor is declared and opened then
you can retrieve the data from it. Let’s see how. FETCH cursor_name INTO PL/SQL variable;
Or FETCH cursor_name INTO PL/SQL record;
You can retrieve the data from the cursor either into a PL/SQL variable which we have
discussed in PL/SQL tutorial 2 or into a PL/SQL record which we will see in the future video.
The only step which is left in the cycle is Close or closing the cursor. Once you are done working with your cursor
it’s advisable to close it. As soon as the server comes across the closing statement
of a cursor it will relinquish all the resources associated with it. In order to close the cursor you simply have
to write the keyword CLOSE followed by the name of the cursor.
CLOSE cursor_name; That’s all you have to do. And Here is the basic programming structure
of the cursor in oracle database. DECLARE
CURSOR cursor_name IS select_statement; BEGIN
OPEN cursor_name; FETCH cursor_name INTO PL/SQL variable;
Or FETCH cursor_name INTO PL/SQL record;
CLOSE cursor_name; END;/
I highly suggest you to read the blog on rebellionrider.com on the same topic for detailed knowledge on
cursors in database. Link is in the description. That’s it for this tutorial on cursors in
oracle database. Hope you learnt something and enjoyed watching. Stay tuned as in the
next tutorial we will see how to create cursors in oracle. Till then take care and make sure
to give this video a big thumbs up and share it with your friends also do subscribe.
Thanks for watching this is Manish from rebellion rider.com