Fetching with a cursor, PL-SQL Programming

Fetching with a Cursor

The FETCH statements retrieve the rows in the result set one at a time. After each and every fetch, the cursor advance to the next row in the result set. An illustration is a shown:

FETCH c1 INTO my_empno, my_ename, my_deptno;

For each column the value returned by the query related with the cursor, there should be a parallel, type-compatible variable in the INTO list. Normally, you use the FETCH statement in the following way:

LOOP

FETCH c1 INTO my_record;

EXIT WHEN c1%NOTFOUND;

-- process data record

END LOOP;

The query can reference the PL/SQL variables within its scope. Though, any variables in the query are evaluated only when the cursor is opened. In the illustration, each retrieved the salary is multiplied by 2, even though the factor is incremented after each fetch:

DECLARE

my_sal emp.sal%TYPE;

my_job emp.job%TYPE;

factor INTEGER := 2;

CURSOR c1 IS SELECT factor*sal FROM emp WHERE job = my_job;

BEGIN

...

OPEN c1; -- here factor equals 2

LOOP

FETCH c1 INTO my_sal;

EXIT WHEN c1%NOTFOUND;

factor := factor + 1; -- does not affect FETCH

END LOOP;

END;

To change the result set or the values of the variables in the query, you should close and reopen the cursor with the input variables set to their new values.

Though, you can use a different INTO list on separate fetches with similar cursor. Each fetch retrieves another row and assigns values to the target variables, as the illustration shows:

DECLARE

CURSOR c1 IS SELECT ename FROM emp;

name1 emp.ename%TYPE;

name2 emp.ename%TYPE;

name3 emp.ename%TYPE;

BEGIN

OPEN c1;

FETCH c1 INTO name1; -- this fetches first row

FETCH c1 INTO name2; -- this fetches second row

FETCH c1 INTO name3; -- this fetches third row

...

CLOSE c1;

END;

Posted Date: 10/4/2012 3:54:01 AM | Location : United States







Related Discussions:- Fetching with a cursor, Assignment Help, Ask Question on Fetching with a cursor, Get Answer, Expert's Help, Fetching with a cursor Discussions

Write discussion on Fetching with a cursor
Your posts are moderated
Related Questions
Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Understanding Varrays The Items of type VARRAY are termed as the varrays. They permit you to relate a single identifier with the whole collection. This relationship lets you man

COMMIT Statement The COMMIT statement explicitly makes everlasting changes to the database during the present transaction. The Changes made to the database are not considered e

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

Manipulating Objects: You can use an object type in the CREATE TABLE statement to indicate the datatype of a column. When the table is created once, you can use the SQL statem

Substitution and Instantiation - SQL It shows how NULL might appear in substitution for a parameter of a predicate and how it might thus participate in instantiation of that p

Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

Fetching from a Cursor Variable The FETCH statement retrieve rows one at a time from the product set of a multi-row query. The syntax for the same is as shown: FETCH {curso

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca