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
Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server

Scope and Visibility The References to an identifier are resolved according to its visibility and scope. The scope of an identifier is that area of a program unit (subprogram, b

Running the PL/SQL Wrapper To run the PL/SQL Wrapper, go through the wrap command at your operating system prompt by using the syntax as shown: wrap iname=input_file [oname=

Order of Evaluation When you do not use the parentheses to specify the order of evaluation, the operator precedence determine the order. Now compare the expressions below: NOT

Transactions in SQL BEGIN TRANSACTION, COMMIT, and ROLLBACK, SQL has the same syntax except for START in place of BEGIN. However, START TRANSACTION is used only for outermost

THEO R Y OF ETERNITY OF LIFE (PRAYER - 1880) - The theory of eternity of life, also called the steady-state theory , states that life has ever been in existence as at presen

Keyword and Parameter Description: boolean_expression: This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statement

Example of UNWRAP Operator - SQL Example here shows how unwrapping can be done in longhand in SQL. Example: Unwrapping in SQL Letting CONTACT_INFO_WRAPPED denote the res

Enrolment was split - SQL Example shows how relvars IS_CALLED and IS_ENROLLED_ON can be derived from the original ENROLMENT relvar, using projection in the initial assignment

Use Native Dynamic SQL A few programs (a normal-purpose report writer for illustration) should build and process a variety of SQL statements at run time. Therefore, their full