Examples For PLSQL Cursors - Explicit, Implicit And Ref Cursors

时间:2022-05-22 15:07:39
A cursor acts logically as a pointer into a result set. You can move the cursor through the result set, processing each row, until you determine you are at the end of the result set. There are three types of syntax associated with cursors: creating the cursor, fetching with the cursor, and closing the cursor. In addition, there are a number of attributes of a cursor you can use in your logical comparisons. The following are the types of Cursors in Oracle:

Explicit Cursors

Explicit Cursors are cursors that you declare and use.

Implicit Cursors

PL/SQL allows you to include SQL statements, including SELECT statements, as a part of your code without declaring a cursor, that is called an implicit cursor.

Ref Cursors

A cursor references a result set. The REF CURSOR allows you to pass a cursor reference from one PL/SQL program unit to another. In other words, it allows you to create a variable that will receive a cursor and enable access to its result set, but in this blog I am giving examples for only Explicit and Implicit Cursors, I will give example for Ref Cursors and Dynamic Cursor in another blog.
 
An example of Explicit Cursor:
 
DECLARE
   nemployeeid   NUMBER;
   dstartdate    DATE;
   denddate      DATE;
   sjobid        VARCHAR2 (20);
 
   -- declare cursor
   CURSOR curjob
   IS
      SELECT employee_id,
             start_date,
             end_date,
             job_id
        FROM hr.job_history;
BEGIN
   OPEN curjob;
 
   LOOP
      FETCH curjob
      INTO nemployeeid, dstartdate, denddate, sjobid;
 
      EXIT WHEN curjob%NOTFOUND;
      DBMS_OUTPUT.put_line(   'Employee '
                           || nemployeeid
                           || 'had job '
                           || sjobid
                           || ' for '
                           || (denddate - dstartdate)
                           || ' days.');
   END LOOP;
 
   CLOSE curjob;
END;
/
Same example is given below for explicit cursor but with For Loop, the For Loop cursors are more smart as there is no need to declare variables to fetch values in them and no need to open or close or to check whether the pointer is at end of the cursor. Here is the example:
 
DECLARE
   CURSOR curjob
   IS
      SELECT employee_id,
             start_date,
             end_date,
             job_id
        FROM hr.job_history;
BEGIN
   FOR jh_rec IN curjob
   LOOP
      DBMS_OUTPUT.put_line(   '‘Employee '
                           || jh_rec.employee_id
                           || ' had job '
                           || jh_rec.job_id
                           || ' for '
                           || (  jh_rec.end_date
                               - jh_rec.start_date
                               || ' days.'));
   END LOOP;
END;
/
 
An Implicit Cursor example:
 
DECLARE
   nempno   NUMBER;
 
   CURSOR curjob
   IS
      SELECT employee_id,
             start_date,
             end_date,
             job_id
        FROM hr.job_history;
BEGIN
  -- below sql query is the type of Implicit Cursor
   SELECT COUNT ( * ) INTO nempno FROM hr.job_history;
 
   DBMS_OUTPUT.put_line (
      'There are ' || nempno || ' employee history records.');
 
   FOR jh_rec IN curjob
   LOOP
      DBMS_OUTPUT.put_line(   '‘Employee '
                           || jh_rec.employee_id
                           || ' had job '
                           || jh_rec.job_id
                           || ' for '
                           || (  jh_rec.end_date
                               - jh_rec.start_date
                               || ' days.'));
   END LOOP;
END;
/
Examples For PLSQL Cursors - Explicit, Implicit And Ref Cursors