Cursors

Q1. What is a cursor? Why Cursor is required?

Answer. Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows. Cursors are unidirectional only.

Q2. What are cursor attributes? Explain them.

Answer. Following are the cursor attributes:
• %Found – to check whether cursor has fetched any row. True if rows are fetched.
• %NotFound – to check whether cursor has fetched any row. True if no rows are fetched.
• %IsOpen – to check whether cursor is open or not
• %RowCount – number of rows fetched/updated/deleted.

These attributes are preceded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

Q3. Types of Cursor.

Answer. Following are the 2 types of cursors:

1. Implicit cursors: are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
2. Explicit cursors: are created by programmers by declaring them in PL/SQL block. Though the cursor stores all records, only one record can be processed at a time, which is known as CURRENT row. When a row is fetched, the current row position moves to next row. There are four steps in using an Explicit Cursor.

• DECLARE the cursor in the declaration section
• OPEN the cursor in the Execution Section.
• FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
• CLOSE the cursor in the Execution Section before you end the PL/SQL Block.
• Above steps can be skipped using For Cursor Loop.

Q4. Can we have parameterized Cursors?

Answer. Yes.

DECLARE
  CURSOR c1(p_DeptNo Number := 1) IS /*default value for parameter*/
    SELECT First_Name|| ' '|| Last_Name as name
      FROM Employees E
     INNER JOIN Dept D USING (Dept_Id);

    v_rec c1%ROWTYPE;
BEGIN
    OPEN c1(10);
    LOOP
        FETCH c1 INTO v_rec;
        EXIT WHEN cur_revenue%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_rec.name);
    END LOOP;
    CLOSE c1;
END;

Q5. Explain the usage of WHERE CURRENT OF clause in cursors?

Answer. WHERE CURRENT OF clause in an UPDATE, DELETE statement refers to the latest row fetched from a cursor.

Q6. What is a cursor for loop?

Answer. Cursor for loop is shortcut process for Explicit Cursors because the Cursor is Declared & Opened, Rows are fetched once for each iteration and the cursor is closed automatically when all the rows have been processed.

Q7. What is REF Cursor & Sys_RefCursor?

Answer. A REF Cursor is a datatype that holds a cursor data. Sys_RefCursor is weakly typed, so you can return pointers to queries. REF Cursor can be opened on the server and passed to the client as a unit containing all the rows at a time. It also can be assigned & passed as parameter to other program units. REF Cursors are opened using OPEN FOR statement. They behave like normal cursors.

REF CURSOR and SYS_REFCURSOR type is used interchangeably in PL/SQL program. You need to create type of REF CURSOR and its variable in the program unit, however SYS_REFCURSOR is predefined REF CURSOR defined in standard package of Oracle located at following location in windows:
%ORACLE_HOME%/rdbms/admin/stdspec.sql

Example:
CREATE OR REPLACE FUNCTION sf_cusrsor RETURN SYS_REFCURSOR AS
  C1 SYS_REFCURSOR;
BEGIN
  OPEN c FOR select emp_name from emp;
  RETURN c;
END sf_cusrsor;
 
set serveroutput on
       
DECLARE
  type cur_ref_type is ref cursor;
  C2 cur_ref_type;
  V_emp_name emp.emp_name%Type;
BEGIN
  C2 := sf_cusrsor(); --Get ref cursor from function
  LOOP 
    FETCH C2 into V_emp_name;
    EXIT WHEN C2%NOTFOUND;
    dbms_output.put_line('Value from cursor: '|| V_emp_name);
  END LOOP;
END; 

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.