Tell me everything about cursors?

Cursor is a  private work areas to execute SQL statements and to store processing information.
It is an Temporary buffer used to hold the   
transnational data for manipulation purpose.
It is not stored in database.
It is not Re-usable.
It is valid in Pl/sql Block only.
It is created in the Logical memory only.
Every SQL DML statement processed by PL/SQL has  an associated CURSOR.

Oracle server uses some private work areas to execute SQL statement and to store processing information.

By using PLSQL Cursors these private SQL areas can be named and the stored information can be accessed.

Cursor contains two parts
    Header
    Body

Header includes cursor name, any parameters and the type of data being loaded.

Body includes the select statement.
Ex: Cursor c(dno in number) return dept%rowtype is select *from dept;

In the above
Header – cursor c(dno in number) return dept%rowtype
Body – select *from dept

By using PL/SQL cursors these private SQL areas can be named and the stored information can be accessed.

Disadvantages:

Cursors allow row by row processing of record set. For every row, a network round trip is made unlike in a Select query where there is just one network round trip. Cursors need more I/O and temp storage resources, thus it is slower.

Two Types of CURSORS

1.  IMPLICIT
All INSERT statements
All UPDATE statements
All DELETE statements

Single row SELECT….INTO Statements

Declared for all DML and pl/sql statements. By default it selects one row only.

Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL select statements, including queries that return only one row.

Oracle Server implicitly opens a cursor to process each SQL statement not associated with on explicitly declared cursor.

The most recent implicit cursor can be returned as the SQL cursor.
ORACLE implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor.PL/SQL lets you refer to the most recent implicit cursor as the SQL” cursor. So, although you cannot use the OPEN,FETCH, and CLOSE statements to control an implicit cursor, you can still use cursor attributes to access information about the most recently executed SQL statement.

2.  EXPLICIT. 

               Multiple row SELECT  STATEMENTS

Declared and named by the developer. Use explicit cursor to individually process each row returned by a multiple statements, is called ACTIVE SET.

For queries that return more than one row, explicit cursors are declared and named by the programe  and manipulated through specific statements in the block’s executable actions.

Explicit cursors are used to individually process each row returned by a multiple-row SELECT statement.

The set of rows returned by a multiple – row query is called as active set. 

The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly define a cursor to process the rows. You use three commands to control the cursor:

Allows the programmer to manually control explicit cursor in the pl/sql block

Declare: create a named sql area

CURSOR STAGES

    Open : identify the active set.

    Fetch : load the current row in to variables.

    Close : release the active set.

Explicit Cursors : 3 Steps

 I. Declaring Cursor

   II. Cursor Operations

   III. Cursor Attributes

I)Declaring Cursor

 Syntax:  cursor <cursor name> is <select  stmt>;

        ex:  cursor c1 is select * from emp;

II). Cursor Operations:

  i> open <cursor name>;

    used to open the cursor.

   memory will be alloted to cursor after opening it.

iii)Fetch <cursor name> into < Variables >;

    Used to retrieve data from cursor to pl/sql variables.         

At a time it can retrieve only one row into variables. Generally Fetch will be placed in loop .

iiii)> close <cursor name>;

      used to close the cursor.memory allotted will be De allocated .

3. PARAMETERIZED CURSORS

Parameters can be passed to the cursor in a cursor for loop.

It allow to open and close an explicit cursor several times in a block, returning a different active set on each occasion for each execution, the previous cursor is closed and reopened with a new set of parameters.

Sizes should not be mentioned for the data types of parameters the parameters names are for references in the query expression of the cursor. 

POINTS TO REMEMBER

Cursor name can be up to 30 characters in length.

Cursors declared in anonymous blocks or subprograms closes automatically when that block terminates execution.

%bulk_rowcount and %bulk_exceptions can be used only with for all construct.

Cursor declarations may have expressions with column aliases.

These expressions are called virtual columns or calculated columns.

Parameters allow values to be passed to a cursor when it is opened and to be used in the query when it executes Different active set each time based on a parameter value that is passed to a cursor.

Syntax:

CURSOR cursor_name(para_name1  datatype,

                                       para_name2  datatype,…) IS  select_statement;

Example:

DECLARE

   CURSOR par_cur (p_deptno NUMBER, p_job VARCHAR2)

   IS

      SELECT empno, 

ename

      FROM emp

      WHERE deptno = p_deptno 

  AND UPPER (job) = UPPER (p_job);

  

   v_deptno   emp.deptno%TYPE   := &dno;

   v_job      emp.job%TYPE      := '&job';

BEGIN

   FOR emp_record IN par_cur (v_deptno, v_job)

   LOOP

      DBMS_OUTPUT.put_line('Empno      :' || emp_record.empno);

      DBMS_OUTPUT.put_line('Ename       :' || emp_record.ename);

   END LOOP;

END;

Cursor Attributes:

Attribute Type Description

% is open Boolean Returns True if cursor is opened successfully.evaluates                                                                            to true if the cursor is open.

% not found Boolean Returns True if Fetch statement fails to retrieve the data                                                                          into Pl/sql variables.evaluates to true if the most recent                                                                            fetch does not return a row

% found Boolean Returns True if Fetch statement successfully                                                                                            retrieves the data into pl/sql variables.evaluates to true                                                                            if the most recent fetch returns a row.

% Row Count Number Returns the No.of rows successfully retrieved from                                                                                  cursor so far. Initially it holds 0. After every successful                                                                            "Fetch" it is incremented by 1.evaluates to the total                                                                                  number of rows returned to far.

%ROWCOUNT:

If cursor is open, returns TRUE. If cursor is closed, returns FALSE.

%ROWCOUNT Example

LOOP 

FETCH my_cursor INTO my_ename , my_sal ;

EXIT WHEN ( my_cursor%NOTFOUND )

OR  ( my_cursor%ROWCOUNT > 10 ) ;

--     process data here 

END LOOP 

%NOTFOUND:

    If cursor is closed, or declared, but not open, returns INVALID_CURSOR.

    If a row was not returned, returns TRUE.

    If a fetch was successful, returns FALSE.

    If cursor is open, but fetch was not successfully executed, returns NULL.

%NOTFOUND Example 

LOOP 

FETCH my_cursor INTO my_ename , my_sal ;

EXIT WHEN my_cursor%NOTFOUND ;

   --  process data here

END LOOP ;

%FOUND

    If cursor is closed, or declared, but not open, returns INVALID_CURSOR.

    If a row was not returned, returns FALSE.

    If a fetch was successful, returns TRUE.

    If cursor is open, but fetch was not successfully executed, returns NULL.

%FOUND Example 

FETCH my_cursor INTO my_ename ,my_sal ;

WHILE my_cursor INTO my_ename , my_sal ;

                       --  process data here 

                     FETCH my_cursor INTO my_ename ,my_sal ;

        END LOOP ; 

%ISOPEN:

    If cursor is closed, or declared, but not open, returns INVALID_CURSOR.

    Returns amount of rows that were fetched.

  %ISOPEN Example 

IF my_cursor%ISOPEN THEN 

FETCH my_cursor INTO my_ename , my_sal ;

ELSE 

OPEN my_cursor ;

END IF ;

cursor variable:

In case of a cursor, Oracle opens an anonymous work area that stores processing information. This 

area can be accessed by cursor variable which points to this area.

One must define a REF CURSOR type, and then declare cursor variables of that type to do so.

E.g : /* Create the cursor type. */

TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;

/* Declare a cursor variable of that type. */

company_curvar company_curtype;

Cursor For Loop:

used to perform cursor operations automatically.

Improves performance.

cursor for loop is a short cut to process explicit cursors

it has higher performance

cursor for loop requires only the declaration of the cursor, remaining things like opening, fetching and close are automatically take by the cursor for loop

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, 

fetches rows of values from active set into fields in the record and closes when all the records have been processed.

Syntax:  

 For < variable > in <cursor name> loop

 < Exec stmts >;

 End loop;

Example:

          Declare

            Cursor emp_cursor is

                   Select empno,ename

                            From emp;

          Begin

            For emp_record in emp_cursor loop

              Dbms_output.putline(emp_record.empno);

               Dbms_output.putline(emp_record.ename) 

           End loop       

         End;

Advantage: 

 1. open cursor

 2. fetch rows

 3. check for end of rows

 4. close cursor

 5. declare variables

Difference b/w implicit cursor and explicit cursor?

Implicit cursors are automatically created by oracle for all its DML stmts. 

Examples of implicit cursors: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN; 

Explicit cursors are created by the users for multi row select stmts.

Ref cursor

Ref cursors are used for dynamic query building.

A REF CURSOR is basically a data type.  

A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time. The ref cursor returns the result set from a table to a cursor variable.  

A ref cursor variable can be returned to a client

Whereas ref cursors can be passed as parameters in procedures/functions 

whereas ref cursors can be defined at run time.

In ref cursor, we cannot define them OUTSIDE of a procedure / function and also we only open the ref cursor, the oracle server implicitly close the ref cursor.

Ex: using %Type
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  en emp.ename%type;
begin
  open c_emp for select ename from emp;
  loop
      fetch c_emp into en;
      exit when c_emp%notfound;
      dbms_output.put_line(en);
  end loop;
  close c_emp;
end;

Ex: using %rowtype
declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  er emp%rowtype;
begin
  open c_emp for select * from emp;
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.ename || ' - ' || er.sal);
  end loop;
  close c_emp;
end;

Diffrence Between Cursor and For Loop

Diffrence Between Cursor and REF Cursor