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!