RSS

9.1 Cursor Manipulation

08 May

For Oracle to process a SQL statement, it needs to create an area of memory known as the context area; this will have the information needed to process the statement. This information includes the number of rows processed by the statement and a pointer to the parsed representation of the statement. (Parsing a SQL statement is the process whereby information is transferred to the server, at which point the SQL statement is evaluated as being valid.) In a query, the active set refers to the rows that are returned. A cursor is a handle, or pointer, to the context area. Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed. Cursors have two important features:

  • Cursors allow you to fetch and process rows returned by a SELECT statement one row at a time.
  • A cursor is named so that it can be referenced.

 

 

TYPES OF CURSORS

There are two types of cursors:

  • Oracle automatically declares an  implicit cursor every time a SQL statement is executed. The user is unaware of this and cannot control or process the information in an implicit cursor.
  • The program defines an explicit cursor for any query that returns more than one row of data. This means that the programmer has declared the cursor within the PL/SQL code block. This declaration allows the application to sequentially process each row of data as the cursor returns it.

 

 

IMPLICIT CURSOR

To better understand the capabilities of an explicit cursor, you first need to understand the process of an implicit cursor:

  • Any given PL/SQL block issues an implicit cursor whenever a SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.
  • A cursor is automatically associated with every DML (data manipulation) statement (UPDATE, DELETE, INSERT).
  • All UPDATE and DELETE statements have cursors that identify the set of rows that will be affected by the operation.
  • An INSERT statement needs a place to receive the data that is to be inserted into the database; the implicit cursor fulfills this need.
  • The most recently opened cursor is called the SQL cursor.

 

The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. During the processing of an implicit cursor, Oracle automatically performs the OPEN, FETCH, and CLOSE operations.

 

An implicit cursor can tell you how many rows were affected by an update. Cursors have attributes such as ROWCOUNT. SQL%ROWCOUNT returns the number of rows updated. It can be used as follows:

 

SET SERVEROUTPUT ON

BEGIN

UPDATE student

SET first_name = ‘B’

WHERE first_name LIKE ‘B%’;

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

END;

 

 

SET SERVEROUTPUT ON;

DECLARE

v_first_name VARCHAR2(35);

v_last_name VARCHAR2(35);

BEGIN

SELECT first_name, last_name

INTO v_first_name, v_last_name

FROM student

WHERE student_id = 123;

DBMS_OUTPUT.PUT_LINE (‘Student name: ‘||v_first_name||‘ ‘||v_last_name);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘There is no student with student ID 123’);

END;

Oracle automatically associates an implicit cursor with the SELECT INTO statement and fetches the values for the variables v_first_name and v_last_name. After the SELECT INTO statement completes, Oracle closes the implicit cursor.

 

EXPLICIT CURSOR

The only means of generating an explicit cursor is to name the cursor in the DECLARE section of the PL/SQL block.

The advantage of declaring an explicit cursor over an indirect implicit cursor is that the explicit cursor gives the programmer more programmatic control. Also, implicit cursors are less efficient than explicit cursors, so it is harder to trap data errors.

The process of working with an explicit cursor consists of the following steps:

1. Declaring the cursor. This initializes the cursor into memory.

2. Opening the cursor. The declared cursor is opened, and memory is allotted.

3. Fetching the cursor. The declared and opened cursor can now retrieve data.

4. Closing the cursor. The declared, opened, and fetched cursor must be closed to release the memory allocation.

 

DECLARING A CURSOR

Declaring a cursor defines the cursor’s name and associates it with a SELECT statement. You declare a cursor using the following syntax:

CURSOR c_cursor_name IS select statement

 

You can’t use a cursor unless the complete cycle of declaring, opening, fetching, and closing has been performed.

 

Cursor names follow the same rules of scope and visibility that apply to the PL/SQL identifiers. Because the cursor name is a PL/SQL identifier, it must be declared before it is referenced. Any valid select statement can be used to define a cursor, including joins and statements with the UNION or MINUS clause.

 

RECORD TYPES

A record is a composite data structure, which means that it is composed of one or more elements. Records are very much like a row of a database table, but each element of the record does not stand on its own. PL/SQL supports three kinds of records: table-based, cursor-based, and programmer-defined. A table-based record is one whose structure is drawn from the list of columns in the table. A cursor-based record is one whose structure matches the elements of a predefined cursor. To create a table-based or cursor-based record, use the %ROWTYPE attribute:

record_name table_name or cursor_name%ROWTYPE

 

SET SERVEROUTPUT ON

DECLARE

vr_student student%ROWTYPE;

BEGIN

SELECT *

INTO vr_student

FROM student

WHERE student_id = 156;

DBMS_OUTPUT.PUT_LINE(vr_student.first_name ||   || vr_student.last_name || ‘ has an ID of 156’);

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-2001, ‘The Student is not in the database’);

END;

The variable  vr_student is a record type of the existing database table student. In other words, it has the same components as a row in the student table. A cursor-based record is much the same, except that it is drawn from the select list of an explicitly declared cursor. When referencing elements of the record, you use the same syntax that you use with tables:

record_name.item_name

Advertisements
 
Leave a comment

Posted by on 05/08/2010 in ORACLE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: