RDBMS

What is a Cursor?

Oracle uses a work area to execute SQL commands and store processing information; a cursor is a name through which PL/SQL accesses this area.

• Implicit cursor – declared by PL/SQL for every DML command and every query that returns a single row. Its name is SQL; no declaration is needed.
• Explicit cursor – declared by the programmer for multi-row queries.

A SELECT … INTO succeeds only when it returns exactly one row: no row raises NO_DATA_FOUND, more than one raises TOO_MANY_ROWS, because many values cannot be copied into one variable. An explicit cursor stores the whole set of rows and lets the program fetch one row at a time.

Handling an Explicit Cursor

1. Declare the cursor in the declare section: CURSOR name [(parameters)] IS select [FOR UPDATE [OF columns]];
2. OPEN it – executes the SELECT and places the rows in the cursor.
3. FETCH one row at a time INTO variables (one variable per column, matching types), inside a loop.
4. CLOSE it – releases the resources.

declare
   cursor course_det is
      select ccode, name, fee from courses;
   v_ccode courses.ccode%type;
   v_name  courses.name%type;
   v_fee   courses.fee%type;
begin
   open course_det;
   loop
      fetch course_det into v_ccode, v_name, v_fee;
      exit when course_det%notfound;          -- previous FETCH failed: end of cursor
      dbms_output.put_line(v_ccode || ' ' || v_fee);
   end loop;
   close course_det;
end;

Cursor FOR Loop

The cursor FOR loop automates opening the cursor, fetching each row, terminating at the end and closing the cursor. Its record variable is declared automatically as cursor%ROWTYPE, is available only inside the loop, and its fields are accessed as rec.column.

declare
   cursor courses_cursor is
      select b.ccode, count(*) cnt
      from batches b, students s
      where b.bcode = s.bcode
      group by b.ccode;
   v_per number(5,2);
begin
   for rec in courses_cursor loop          -- rec declared automatically
      if rec.cnt > 20 then
         v_per := 1.15;                    -- +15%
      elsif rec.cnt > 10 then
         v_per := 1.10;                    -- +10%
      else
         v_per := 0.95;                    -- -5%
      end if;
      update courses set fee = fee * v_per where ccode = rec.ccode;
   end loop;
end;

Implicit Cursor and Cursor Attributes

OPEN, FETCH and CLOSE cannot be used with the implicit cursor SQL, but its attributes describe the most recent SQL statement. Syntax: cursor_name%attribute.

begin
   update courses set fee = fee * 1.1 where duration > 25;
   if sql%rowcount > 5 then        -- more than 5 rows changed
      rollback;
   else
      commit;
   end if;
end;

Input Arguments (Parameterised Cursors)

Parameters are declared with the cursor and values are passed when it is opened, so the same cursor can represent different sets of rows. Give only the datatype, without size (VARCHAR2, not VARCHAR2(10)).

declare
   cursor batch_cursor (pccode varchar2) is
      select * from batches where ccode = pccode;
begin
   open batch_cursor('ora');   -- batches of Oracle
   close batch_cursor;
   open batch_cursor('vb');    -- reopened for another course
   close batch_cursor;

   for rec in batch_cursor('ora') loop    -- argument in a cursor FOR loop
      null;
   end loop;
end;

FOR UPDATE and WHERE CURRENT OF

Oracle locks rows automatically while changing them. FOR UPDATE in the cursor’s SELECT locks all the cursor’s rows when the cursor is opened, before they are updated or deleted. WHERE CURRENT OF cursor in UPDATE/DELETE refers to the row last fetched; it requires FOR UPDATE.

declare
   cursor course_details is
      select ccode, name, fee from courses
      for update of fee;
begin
   for rec in course_details loop
      update courses set fee = rec.fee * 1.05
      where current of course_details;
   end loop;
end;

Chapter 18 Notes (PDF)

Question / Answer (15 questions)

Moderate-level questions based on the Chapter 18 notes (PDF). Click a question to see the answer.

MCQ Test : Check Your Understanding (5 questions)

Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.

Bibliography/References

The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.

1. Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
2. Oracle Corporation. (n.d.). Oracle Database PL/SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/
3. Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL Programming (6th ed.). O’Reilly Media.