Cursor is a pointer to a memory area called context area. This we have already learnt with all the other details in the previous tutorial. Today in this blog we will learn how to create an explicit database cursor.
As we have already learnt that whenever we execute a DML statement, the oracle server creates an implicit cursor in the background. As these cursors are created by oracle server itself thus user does not have much programmatic control on them. In case if you want to control your own DMLs then you need to write an explicit cursor.
So let’s quickly see how you can create your own database cursor in oracle database.
SET SERVEROUTPUT ON; DECLARE v_name VARCHAR2(30); --Declare Cursor CURSOR cur_RebellionRider IS SELECT first_name FROM EMPLOYEES WHERE employee_id < 105; BEGIN OPEN cur_RebellionRider; LOOP FETCH cur_RebellionRider INTO v_name; DBMS_OUTPUT.PUT_LINE (v_name); EXIT WHEN cur_RebellionRider%NOTFOUND; END LOOP;--Simple Loop End CLOSE cur_RebellionRider; END; /
We used EMPLOYEE table of HR sample Schema for creating the above explicit cursor. You can watch my Video Tutorial on The Same Topic for line by line explanation of the above code.
Declaration of your cursor can only be done in the “Declaration” section of the PL/SQL block and the rest of the steps of explicit cursor creation cycle must be done in the execution section of a PL/SQL block.
This is a quick tutorial on how to create an explicit cursor in oracle database. Hope you liked it. Do make sure to share this blog on your Facebook and on your twitter. Thanks for reading & have a great day!
[…] previous PL/SQL tutorial we saw how to create a simple explicit cursor. No doubt that explicit cursor has certain advantages […]
[…] As it is obvious that in order to declare a Cursor Based Record we need to have a Cursor over which we can design our Record variable. For the demonstration let’s have the same cursor which we have discussed in PL/SQL tutorial 27 on “How to Create Simple Explicit Cursor.” […]
[…] In this blog you will learn how to use Bulk Collect clause with FETCH-INTO statement of an explicit cursor. As FETCH statements are part of cursor life cycle thus a working knowledge of explicit cursor is required for the better understanding of this tutorial. For the same you can check out the tutorial on how to create an explicit cursor in Oracle database. […]