RSS

9.1.2 Process an Explicit Cursor

10 May

To use a cursor, you must make use of the complete cycle of declaring, opening, fetching, and closing the cursor.To help you learn these four steps, this lab covers them one at a time.

 

A) Write the declaration section of a PL/SQL block. It should define a cursor named c_student based on the student table,with last_name and first_name concatenated into one item called name.

 

DECLARE

CURSOR c_student IS

SELECT first_name ||     || last_name name

FROM student;

vr_student c_student%ROWTYPE;

The syntax for opening a cursor is

OPEN cursor_name;

B) Add the necessary lines to the PL/SQL block that you just wrote to open the cursor.

BEGIN

OPEN c_student;

FETCHING ROWS IN A CURSOR

After the cursor has been declared and opened, you can retrieve data from the cursor.The process of getting data from the cursor is called fetching the cursor.There are two ways to fetch a cursor:

FETCH cursor_name INTO PL/SQL variables;

or

FETCH cursor_name INTO PL/SQL record;

When the cursor is fetched, the following occurs:

1. The FETCH command is used to retrieve one row at a time from the active set.This is generally done inside a loop.The values of each row in the active set can then be stored in the corresponding variables or PL/SQL record one at a time, performing operations on each one successively.

2. After each FETCH, the active set pointer is moved forward to the next row.Thus, each FETCH returns successive rows of the active set, until the entire set is returned.The last FETCH does not assign values to the output variables; they still contain their prior values.

 

DECLARE

CURSOR c_zip IS

SELECT *

FROM zipcode;

vr_zip c_zip%ROWTYPE;

BEGIN

OPEN c_zip;

LOOP

FETCH c_zip INTO vr_zip;

EXIT WHEN c_zip%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vr_zip.zip ||   || vr_zip.city ||   || vr_zip.state);

END LOOP;

END;

C) For the PL/SQL block that you have been writing, add a loop. Inside the loop, fetch the cursor into the record. Include a DBMS_OUTPUT line inside the loop so that each time the loop iterates, all the information in the record is displayed in a SQL*Plus session.

LOOP

FETCH c_student INTO vr_student;

DBMS_OUTPUT.PUT_LINE(vr_student.name);

CLOSING A CURSOR

As soon as all the rows in the cursor have been processed (retrieved), the cursor should be closed.This tells the PL/SQL engine that the program is finished with the cursor, and the resources associated with it can be freed.The syntax for closing the cursor is

CLOSE cursor_name;

After a cursor is closed, you no longer can fetch from it. Likewise, it is not possible to close an already closed cursor.Trying to perform either of these actions would result in an Oracle error.

 

D) Continue with the code you have developed by adding a CLOSE statement to the cursor.

CLOSE c_student;

The code is not complete because there is not a proper way to exit the loop.

DECLARE

CURSOR c_student_name IS

SELECT first_name, last_name

FROM student

WHERE rownum <= 5;

vr_student_name c_student_name%ROWTYPE;

BEGIN

OPEN c_student_name;

LOOP

FETCH c_student_name INTO vr_student_name;

EXIT WHEN c_student_name%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(‘Student name: ‘ || vr_student_name.first_name ||     || vr_student_name.last_name);

END LOOP;

CLOSE c_student_name;

— Print Last Record

— DBMS_OUTPUT.PUT_LINE(‘Student name: ‘ || vr_student_name.first_name || ‘    ‘ || vr_student_name.last_name);

END;

A user-defined record is based on the record type defined by a programmer. First you declare a record type, and then you declare a record variable based on the record type defined in the preceding step:

TYPE type_name IS RECORD

(field_name 1 DATATYPE 1,

field_name 2 DATATYPE 2,

field_name N DATATYPE N);

record_name TYPE_NAME%ROWTYPE;

 

Consider the following code fragment:

SET SERVEROUTPUT ON;

DECLARE

— declare user-defined type

TYPE instructor_info IS RECORD

(instructor_id instructor.instructor_id%TYPE,

first_name instructor.first_name%TYPE,

last_name instructor.last_name%TYPE,

sections NUMBER(1));

— declare a record based on the type defined above

rv_instructor instructor_info;

SET SERVEROUTPUT ON;

DECLARE

TYPE instructor_info IS RECORD

(first_name instructor.first_name%TYPE,

last_name instructor.last_name%TYPE,

sections NUMBER);

rv_instructor instructor_info;

BEGIN

SELECT RTRIM(i.first_name),

RTRIM(i.last_name), COUNT(*)

INTO rv_instructor

FROM instructor i, section s

WHERE i.instructor_id = s.instructor_id

AND i.instructor_id = 102

GROUP BY i.first_name, i.last_name;

DBMS_OUTPUT.PUT_LINE(‘Instructor, ‘||    rv_instructor.first_name||    ‘ ‘||rv_instructor.last_name||    ‘, teaches ‘||rv_instructor.sections||    ‘ section(s)’);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE (‘There is no such instructor’);

END;

Advertisements
 
Leave a comment

Posted by on 05/10/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: