Cursors Database Management System

Cursors Database Management SystemManagement System

CURSORS

Every SQL statement executed by the Oracle Server has an individual cursor associated with it:

  • Implicit cursors. Declared for all DML and PL/SQL SELECT Statements
  • Explicit cursors. Declared and named by the programmer

 

Implicit and Explicit Cursors

CURSORS Database Management System : CThe Oracle server uses work areas, called private SOL areas, to execute SQL statements and to Store processing information. You can use PL/SQL Cursors to name a private SQL area and access its Stored information.

 

Explicit Cursors

Use explicit cursors to individually process each row returned by a multiple-row SELECT Statement. The set of rows returned by a multiple-row query is called the active set. Its size is the number of rows that meet your search criteria. The diagram on the side shows how an explicit cursor “points” to the Current row in the active set. This allows your program to process the rows one at a time.

A PL/SQL program opens a Cursor, processes rows returned by a query, and then closes the Cursor. The cursor marks the current position in the active set.

 CURSORS Database Management System

Explicit cursor functions:

  • Can process beyond the first row returned by the query, row by row
  • Keep track of which row is currently being processed
  • Allow the programmer to manually control explicit cursors in the PL/SQL block

Now that you have a conceptual understanding of cursors, review the steps to use them. The syntax for each step can be found on the following pages.

Controlling Explicit Cursors

  1. Declare the cursorbynaming it and defining the structure of the query to be performed within it.
  2. Open the cursor. The OPEN statement executes the query and binds any variables that are referenced. Rows identified by the query are called the active set and are now available for fetching.
  3. Fetch data from the cursor. In the flow diagram shown on the slide, after each fetch you test the Cursor for any existing row. If there are no more rows to process, then you must close the Cursor.
  4. Close the Cursor. The CLOSEstatement releases the active set of rows. It is now possible to reopen the Cursor to establish a fresh active Set.

 

Declaring the Cursor

Syntax:

  • Do not include the INTO clause in the cursor declaration.
  • If processing rows in a specific sequence is required, use the ORDER BY clause in the query.

CURSOR cursor_name IS

select_statement;

Declaring the Cursor

Use the CURSOR Statement to declare an explicit cursor. You can reference variables within the query, but you must declare them before the CURSOR Statement.

In the syntax:

Cursor_name is a PL/SQL identifier.

select_statement is a SELECT statement without an INTO clause.

Note

  • Do not include the INTO clause in the cursor declaration because it appears later in the FETCH – Statement.
  • The cursor can be any valid ANSI SELECT statement, to include joins, and so on.

Example:

DECLARE

CURSOR emp_cursor IS

SELECT emp_id, last name

FROM emp;

CURSOR dept_cursor IS

SELECT *

FROM departments

WHERE location_id=170;

BEGIN

Declaring the Cursor (continued)

In the example on the slide, the cursor emp_cursor is declared to retrieve the EMPID and LASTNAME Columns from the EMP table. Similarly, the cursor DEPT_CURSOR is declared to retrieve all the details for the department with the LOCATION_ID 170.

DECLARE

Var_empno emp.emp id%TYPE;

Var_ename emp.last_name%TYPE;

CURSOR emp_cursor IS

SELECT emp_id, last_name

FROM emp;

BEGIN

Fetching the values retrieved by the cursor into the variables declared in the DECLARE Section is Covered later in this lesson.

OPEN Statement

The OPEN statement executes the query associated with the cursor, identifies the result set, and positions the cursor before the first row.

In the Syntax:

Cursor_name is the name of the previously declared cursor.

OPEN is an executable statement that performs the following operations:

  1. Dynamically allocates memory for a context area that eventually contains crucial processing information.
  2. Parses the SELECT Statement.
  3. Binds the input variables-sets the value for the input variables by obtaining their memory addresses.
  4. Identifies the active set-the set of rows that satisfy the search criteria, Rows in the active set are not retrieved into variables when the OPEN statement is executed. Rather, the FETCH statement retrieves the rows.

 

  1. Positions the pointer just before the first row in the active set. For cursors declared using the FOR UPDATE clause, the OPEN statement also locks those rows. The FOR

 

UPDATE clause is discussed in a later lesson.

Note: If the query returns no rows when the cursor is opened, PL/SQL does not raise an exception. However, you can test the status of the cursor after a fetch using the SQL%ROWCOUNT cursor attribute.

FETCH Statement

The FETCH statement retrieves the rows in the active set one at a time. After each fetch, the cursor advances to the next row in the active set

In the Syntax:

cursor_name is the name of the previously declared Cursor.

Variable is an output variable to Store the results.

record_name is the name of the record in which the retrieved data is stored (The record variable can be declared using the %ROWTYPE attribute).

Guidelines:

  • Include the same number of variables in the INTO clause of the FETCH Statement as Columns in the SELECT statement, and be sure that the data types are compatible.
  • Match each variable to correspond to the columns positionally.
  • Alternatively, define a record for the cursor and reference the record in the FETCH INTO clause.
  • Test to see whether the cursor contains rows. If a fetch acquires no values, there are no rows left to process in the active set and no error is recorded.

Note: The FETCH statement performs the following operations:

  1. Reads the data for the current row into the output PL/SQL
  2. 2. Advances the pointer to the next row in the identified set.

Fetching Data from the Cursor

Example:

LOOP

FETCH emp_cursor INTO var_empno, var_ename;

EXIT WHEN …;

– Process the retrieved data

END LOOP:

FETCH Statement (continued)

You use the FETCH statement to retrieve the current row values into output variables. After the fetch, you can manipulate the data in the variables. For each column value returned by the query associated with the Cursor, there must be a corresponding variable in the INTO list. Also, their data types must be Compatible.

Retrieve the first 10 emp one by one.

SET SERVEROUTPUT ON

DECLARE

var_empno emp.emp_id%TYPE,

var_ename emp.last_name%TYPE;

CURSOR emp_cursor IS

SELECT emp_id, last_name

FROM emp;

BEGIN

OPEN emp cursor;
FOR iiN 1…10 LOOP
FETCHemp cursor INTO war empno, var_ename;
DBMS_OUTPUT.PUT_LINE (TO CHAR(var empno)
||’ ‘|| varename);
END LOOP:
END;

CLOSE Statement

The CLOSE statement disables the cursor, and the active set becomes undefined. Close the cursor after completing the processing of the SELECT statement. This step allows the cursor to be reopened, if required. Therefore, you can establish an active set several times.

In the syntax:

cursor_name is the name of the previously declared cursor.

Do not attempt to fetch data from a cursor after it has been closed, or the INVALID_CURSOR exception will be raised.

There is a maximum limit to the number of open cursors per user, which is determined by the
OPEN CURSORS parameter in the database parameter file. OPEN CURSORS = 50 by default.

OPEN emp cursor
FOR i IN 1…10 LOOP
FETCH emp_cursor INTO var_empno, var_ename;

END LOOP;
CLOSE emp_cursor;
END;

Explicit Cursor Attributes
Obtain status information about a cursor.

 

Cursors Database Management System

The %SOPEN Attribute

  • Fetch rows only when the cursor is open.
    Use the %ISOPEN cursor attribute to determine whether the cursor is open.

Example:
IF NOT emp_cursor%ISOPEN THEN
OPEN emp cursor;
END IF;
LOOP
FETCH emp_cursor…
The %SOPEN Attribute

You can fetch rows only when the cursor is open. Use the %SOPEN cursor attribute to determine whether the cursor is open.

  • Fetch rows in a loop. Use cursor attributes to determine when to exit the loop.
  • Use the %ROWCOUNT cursor attribute for the following:

– To retrieve an exact number of rows

– Fetch the rows in a numeric FOR loop

– Fetch the rows in a simple loop and determine when to exit the loop.

Note:  %ISOPEN returns the status of the cursor: TRUE if open and FALSE if not.

Controlling Multiple Fetches

  • process several rows from an explicit cursor using a loop
  • fetch a row with each iteration.
  • Use the explicit cursor attributes to test the success of each fetch

Controlling Mutiple Fetches from Explicit Cursors

To process several rows from an explicit cursor, you typically define a loop to perform a fetch on each iteration. Eventually a rows in the active set are processed, and an unsuccessful fetch sets the 96 NOT FOUND attribute to TRUE. Use the explicit cursor attributes to test the success of each fetch. before any further references are made to the cursor. If you omit an exit criterion, an infinite loop results.

The %NOTFOUND and %ROWCOUNT Attributes

%NOTFOUND

%NOTFOUND is the logical opposite of %FOUND.9%NOT FOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row. In the following example, you use %NOT FOUND to exit a loop when FETCH fails to return a row:

LOOP

FETCH c1 INTO my_ename, my_sal, my hiredate;

EXIT WHEN c1%NOTFOUND;

END LOOP;

Before the first fetch, % NOT FOUND evaluates to NULL. So, if FETCH never executes successfully, the loop is never exited. That is because the EXIT WHEN statement executes only if its WHEN condition is true. To be safe, use the following EXIT statement instead:

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

If a cursor is not open, referencing it with % NOT FOUND raises INVALID_CURSOR.

The %NOTFOUND and %ROWCOUNT Attributes (continued)

%ROWCOUNT

When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. In the next example, you use %ROWCOUNT to take action if more than ten rows have been fetched;

LOOP

FETCH c1. INTO my_ename, my_deptno;

IF c1%ROWCOUNT > 10 THEN

END IF;

END LOOP;

If a Cursor is not open, referencing it with %ROWCOUNT raises INVALID_CURSOR.

Example

DECARE

var _empno emp.emp id%TYPE;

var_ename emp.last_name%TYPE;

CURSOR emp_cursor IS

SELECT emp_id, last_name

FROM emp;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO var_empno, var_ename;

EXIT WHEN emp_cursor%ROWCOUNT > 10 OR

Emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE (TO_CHAR(var_empno)

||’ ||’ var_ename);

END LOOP;

CLOSE emp_cursor;

END;

Example of Cursor using a PL/SQL RECORD.

DECLARE

CURSOR emp_cursor IS

SELECT emp_id, last_name

FROM emp;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp _cursor INTO emp_record;

Cursors and Records

You have already seen that you can define records that have the structure of columns in a table. You can also define a record based on the selected list of Columns in an explicit cursor. This is convenient for processing the rows of the active set, because you can simply fetch into the record. Therefore, the values of the row are loaded directly into the corresponding fields of the record.

Example

Use a cursor to retrieve emp numbers and names and populate a database table, TEMP_LIST,

with this information.

DECLARE

CURSOR emp_cursor IS

SELECT emp_id, last_name

FROM emp;

emp_record emp_cursor %ROWTYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO emp_record;

 

EXIT WHEN emp_cursor%NOTFOUND;

INSERT INTO temp_list (empid, empname)

VALUES (emp_record.emp_id, emp_record.last_name);

END LOOP;

COMMIT;

CLOSE emp_cursor;

END;

 

Cursor FOR Loops

Syntax:

  • The cursor FOR loop is a shortcut to process explicit cursors.
  • Implicit open, fetch, exit, and close occur.
  • The record is implicitly declared.

Cursor FOR loops

FOR record_name IN cursor_name LOOP

statement1;

statement:2;

END LOOP;

Example

Retrieve emp one by one and print out a list of those emp currently working in the Sales department (DEPARTMENT_ID = 80). The example from the slide is completed below :

SET SERVEROUTPUT ON

DECLARE

CURSOR emp_cursor IS

SELECT last_name, department_id

FROM emp;

BEGIN

FOR emp_record IN emp_cursor LOOP

-implicit open and implicit fetch occur

IF emp_record.department_id = 80 THEN

DBMS_OUTPUT.PUT_LINE (“Emp ‘ || emp_record.last_name

||’works in the Sales Dept.’);

END IF;

END LOOP;-implicit close and implicit loop exit

END;

/