EXPLAIN TYPES OF CURSORS ?

Cursors are classified depending on the circumstances under which they are opened. If the Oracle engine opened a cursor for its internal processing it is known as an Implicit Cursor. A cursor can also be opened for processing data through a PL/SQL block, on demand. Such a user-defined cursor is known as an Explicit Cursor.

GENERAL CURSOR ATTRIBUTES

When the Oracle engine creates an Implicit or Explicit cursor, cursor control variables are also created to control the execution of the cursor. These are a set of four system variables, which keep track of the Current status of a cursor. These cursor variables can be accessed and used in a PL/SQL code block.

Both Implicit and Explicit cursors have four attributes. They are described below:

Attribute Name	Description
%ISOPEN	Returns TRUE if cursor is open, FALSE otherwise.
%FOUND	Returns TRUE if record was fetched successfully, FALSE otherwise.
%NOTFOUND	Returns TRUE if record was not fetched successfully, FALSE otherwise.
%ROWCOUNT	Returns number of records processed from the cursor.

Implicit Cursor

The Oracle engine implicitly opens a cursor on the Server to process each SQL statement. Since the implicit cursor is opened and managed by the Oracle engine internally, the function of reserving an area in memory, populating this area with appropriate data, processing the data in the memory area, releasing the memory area when the processing is complete is taken care of by the Oracle engine. The resultant data is then passed to the client machine via the network. A cursor is then opened in memory on the client machine to hold the rows returned by the Oracle engine. The number of rows held in the cursor on the client is managed by the client's operating system and it's swap area.

Implicit cursor attributes can be used to access information about the status of the last insert, update, delete or single-row select statements. This can be done by preceding the implicit cursor attribute with the cursor name (i.e. SQL). The values of the cursor attributes always refer to the most recently executed SQL statement, wherever the statement appears. If an attribute value is to be saved for later use, it must be assigned to a (boolean) memory variable.

Implicit Cursor Processing in Client Server Environment

Attribute Name	Description
%ISOPEN	The Oracle engine automatically opens and closes the SQL cursor after executing its associated select, insert, update or delete SQL statement has been processed in case of implicit cursors. Thus the SQL%ISOPEN attribute of an implicit cursor cannot be referenced outside of its SQL statement. As a result, SQL%ISOPEN always evaluates to FALSE.
%FOUND	Evaluates to TRUE, if an insert, update or delete affected one or more rows, or a single-row select returned one or more rows. Otherwise, it evaluates to FALSE. The syntax for accessing this attribute is SQL%FOUND
%NOTFOUND	Is the logical opposite of %FOUND. It evaluates to TRUE, if an insert, update or delete affected no rows, or a single-row select returns no rows. Otherwise, it evaluates to FALSE. The syntax for accessing this attribute is SQL%NOTFOUND
%ROWCOUNT	Returns the number of rows affected by an insert, update or delete, or select into statement. The syntax for accessing this attribute is SQL%ROWCOUNT

Example:

The bank manager has decided to transfer employees across branches. Write a PL/SQL block to accept an employee number and the branch number followed by updating the branch number of that employee to which he belongs appropriately. Display an appropriate message using SQL%FOUND based on the existence of the record in the EMP_MSTR table. Display .an appropriate message using SQL%NOTFOUND based on the non-existence of the record in the EMP_MSTR table.

BEGIN

UPDATE EMP_MSTR SET BRANCH_NO = &BRANCH_NO

WHERE EMP_NO = &EMP_NO;

IF SQL%FOUND THEN

dbms_output.put_line('Employee Successfully Transferred');

END IF;

IF SQL%NOTFOUND THEN

dbms_output.put_line(‘Employee Number does not Exist’);

END IF;

END;

Output:

Enter value for branch_no: 'B4'

OLD  :  UPDATE EMP_MSTR SET BRANCH_NO = &BRANCH_NO

NEW :  UPDATE EMP_MSTR SET BRANCH_NO = 'B4'

Enter value for emp_no: 'El'

OLD :  WHERE EMP_NO = &EMP_NO;

NEW : WHERE EMP_NO = 'El';

Employee Successfully Transferred

PL/SQL procedure successfully completed.

Both SQL%FOUND and SQL%NOTFOUND attributes evaluate to NULL until they are set by an implicit or explicit cursor operation.

Example:

For SQL%ROWCOUNT

The bank manager of Junagadh branch decides to activate all those accounts, which were previously marked as inactive for performing no transactions in last 365 days. Write a PL/SQL block to update the status of accounts. Display an appropriate message based on the number of rows affected by the update fired.

DECLARE

Rows_Affected char(4);

BEGIN

UPDATE ACCT_MSTR SET STATUS = 'A' WHERE STATUS = 'S' AND BRANCH_NO

IN(SELECT BRANCH_NO FROM BRANCH_MSTR

WHERE NAME = ‘Junagadh’);

Rows_Affected := TO_CHAR(SQL%ROWCOUNT);

IF SQL%ROWCOUNT > 0 THEN

dbms_output.put_line(Rows_Affected ||' Account(s) Activated Successfully');

ELSE

dbms_output.put_line('Currently No Inactive Accounts in the Junagadh Branch');

END IF;

END;

Output:

2 Account (s) Activated Successfully

PL/SQL procedure successfully completed.

Explicit Cursor

When individual records in a table have to be processed inside a PL/SQL code block a cursor is used. This cursor will be declared and mapped to an SQL query in the Declare Section of the PL/SQL block and used within its Executable Section. A cursor thus created and used is known as an Explicit Cursor.

Explicit Cursor Management

The steps involved in using an explicit cursor and manipulating data in its active set are:

Ø  Declare a cursor mapped to a SQL select statement that retrieves data for processing

Ø  Open the cursor

Ø  Fetch data from the cursor one row at a time into memory variables

Ø  Process the data held in the memory variables as required using a loop

Ø  Exit from the loop after processing is complete

Ø  Close the cursor

Cursor Declaration

A cursor is defined in the declarative part of a PL/SQL block. This is done by naming the cursor and mapping it to a query. When a cursor is declared, the Oracle engine is informed that a cursor of the said name needs to be opened. The declaration is only an intimation. There is no memory allocation at this point in time. The three commands used to control the cursor subsequently are open, fetch and close.

The Functionality of Open, Fetch And Close Commands

Initialization of a cursor takes place via the open statement, this:

Ø  Defines a private SQL area named after the cursor name

Ø  Executes a query associated with the cursor

Ø  Retrieves table data and populates the named private SQL area in memory i.e. creates the Active Data Set

Ø  Sets the cursor row pointer in the Active Data Set to the first record

A fetch statement then moves the data held in the Active Data Set into memory variables. Data held in the memory variables can be processed as desired.

The fetch statement is placed inside a Loop ... End Loop construct, which causes the data to be fetched into the memory variables and processed until all the rows in the Active Data Set are processed. The fetch loop then exits. The exiting of the fetch loop is user controlled.

After the fetch loop exits, the cursor must be closed with the close statement. This will release the memory occupied by the cursor and its Active Data Set. A PL/SQL block is necessary to declare a cursor and create an Active Data Set. The cursor name is used to reference the Active Data Set held within the cursor.

Syntax:

CURSOR Cursor_Name IS SELECT statement;

Opening A Cursor

Opening a cursor executes the query and creates the active set that contains all rows, which meet the query search criteria. An open statement retrieves records from a database table and places the records in the cursor (i.e. named private SQL area in memory). A cursor is opened in the Server's memory.

Syntax:

OPEN CursorName;

The working of the Client Tool and Oracle Engine when an explicit cursor is opened using the OPEN command is represented diagrammatically below:

Diagram : Processing of OPEN CURSOR command in the Oracle Engine

Fetching A Record From The Cursor

The fetch statement retrieves the rows from the active set opened in the Server into memory variables declared in the PL/SQL code block on the client one row at a time. The memory variables are opened on the client machine. Each time a fetch is executed, the cursor pointer is advanced to the next row in the Active Data Set.

A standard loop structure (Loop-End Loop) is used to fetch records from the cursor into memory variables one row at a time.

Syntax:

FETCH CursorName INTO Variable 1, Variable2, ..;

There must be a memory variable for each column value of the Active Data Set. Data types must match. These variables will be declared in the DECLARE section of the PL/SQL block.

Closing A Cursor

The close statement disables the cursor and the active set becomes undefined. This will release the memory occupied by the cursor and its Data Set both on the Client and on the Server.

Syntax:

CLOSE CursorName;

Once a cursor is closed, the reopen statement causes the cursor to be reopened.

Explicit Cursor Attributes

Similar to the cursor attributes in case of implicit cursors, four attributes are associated with explicit cursors. The attributes can be used in a PL/SQL code block for processing of data or exiting. The cursor name is appended to the attribute name when referencing the attribute.

Attribute Name	Description
%FOUND	Evaluates to TRUE, if the last fetch succeeded because a row was available, or to FALSE, if the last fetch failed because no more rows were available. The syntax for accessing this attribute is CursorName%FOUND.
%NOTFOUND	Is the logical opposite of %FOUND. It evaluates to TRUE, if the last fetch has failed because no more rows were available; or to FALSE, if the last fetch returned a row. The syntax for accessing this attribute is CursorName%NOTFOUND.
%ISOPEN	Evaluates to TRUE, if an explicit cursor is open; or to FALSE, if it is closed. The syntax for accessing this attribute is CursorName%ISOPEN.
%ROWCOUNT	Returns the number of rows fetched from the active set. It is set to zero when the cursor is opened. The syntax for accessing this attribute is CursorName%ROWCOUNT.

Example:

The bank manager has decided to mark all those accounts as inactive (I) on which there are no transactions performed in the last 365 days. Whenever any such update takes place, a record for the same is maintained in the INACTV_ACCT_MSTR table comprising of the account number, the opening date and the type of account. Write a PL/SQL block to do the same.

Table Name: INACTV_ACCT_MSTR

ACCT_NO	OPNDT	TYPE

Create the table INACTV_ACCT_MSTR as:

CREATE TABLE INACTV_ACCT_MSTR (

ACCT_NO VARCHAR2(10), OPNDT DATE, TYPE VARCHAR2(2));

DECLARE

CURSOR Crsr_NoTrans IS

SELECT ACCT_NO, STATUS, OPNDT, TYPE FROM ACCT_MSTR

WHERE ACCT_NO IN (SELECT ACCT_NO FROM TRANS_MSTR

GROUP BY ACCT_NO HAVING MAX(SYSDATE  - DT) >365);

str_ACCT_NO ACCT_MSTR.ACCT_NO%type;

str_STATUS ACCT_MSTR.STATUS%type;

dt_OPNDT ACCT_MSTR.OPNDT%type;

str_TYPE ACCT_MSTR.TYPE%type;

BEGIN

OPEN Crsr_NoTrans;

IF Crsr_NoTrans%ISOPEN THEN

   LOOP

      FETCH Crsr_NoTrans INTO str_ACCT_NO, str_STATUS, dt_OPNDT, str_TYPE;

      EXIT WHEN Crsr_NoTrans%NOTFOUND;

      IF Crsr_NoTrans%FOUND THEN

UPDATE ACCT_MSTR SET STATUS = 'S'

WHERE ACCT_NO = str_ACCT_NO;

INSERT INTO INACTV_ACCT_MSTR

VALUES(str_ACCT_NO, dt_OPNDT, str_TYPE);

        END IF;

   END LOOP;      .

  COMMIT;

ELSE

dbms_output.put_line ('Unable to open Cursor');

END IF;

CLOSE Crsr_NoTrans;

END;

The above PL/SQL code marks all those accounts as inactive (S) on which there are no transactions performed in the last 365 days. To do this an Explicit cursor named Crsr_NoTrans is declared using the following query:

SELECT ACCT_NO, STATUS, OPNDT, TYPE FROM ACCT_MSTR

WHERE ACCT_NO IN (SELECT ACCT_NO FROM TRANS_MSTR

GROUP BY ACCT_NO HAVING MAX(SYSDATE - DT) >365);

The above query will retrieve account numbers, their status, the date on which the account was opened and the type of account. This information will be retrieved based on the following query:

SELECT ACCT_NO FROM TRANS_MSTR

GROUP BY ACCT_NO HAVING MAX(SYSDATE - DT) >365;

This query is responsible to retrieve only those account numbers from the TRANS_MSTR table where the difference between the current date and the date on which the last transaction was performed is more than 365 days i.e. one year. This means a concept of Subquery is applied here.

Since the above query will retrieve more than one value the following variables are declared to hold them.

str_ACCT_NO ACCT_MSTR.ACCT_NO%type;

str_STATUS ACCT_MSTR.STATUS%type;

dt_OPNDT ACCT_MSTR.OPNDT%type;

str_TYPE ACCT_MSTR.TYPE%type;

The %type here means that the data type of these variables will be as that from the table.

Since the above query may return more than one row a loop is created which is responsible to:

Ø  Fetch the data retrieved by the cursor into the variable

Ø  Check if any data is retrieved

·        Update the STATUS field of the ACCT_MSTR table to reflect the inactivity

·        Insert a record in the INACTV_ACCT_MSTR table to reflect the updation

Ø  Repeat the above steps until the data retrieval process is complete.

Finally a COMMIT is fired to make the changes permanent.