Fetching with a cursor, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Fetching with a cursor

Data types in sql, Data Types in SQL SQL's concept does not differ sig...

Data Types in SQL SQL's concept does not differ significantly from that defined in the theory book, apart from that business concerning NULL. However, the theory book equates

%rowcount, %ROWCOUNT When its cursor or cursor variable is opened, the...

%ROWCOUNT When its cursor or cursor variable is opened, the %ROWCOUNT is zeroed. Before the first fetch, the %ROWCOUNT yields 0. Afterward, it yields the number of rows fetche

%rowcount - implicit cursor attributes, %ROWCOUNT The %ROWCOUNT yields...

%ROWCOUNT The %ROWCOUNT yields the number of rows affected by the INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. The %ROWCOUNT yields zero when a

%notfound - implicit cursor attributes, %NOTFOUND The %NOTFOUND is the...

%NOTFOUND The %NOTFOUND is the logical opposite of the %FOUND. The %NOTFOUND yields TRUE when an INSERT, UPDATE, or DELETE statement affected no rows, or the SELECT INTO state

Row operators - sql operators, Row Operators The Row operators return ...

Row Operators The Row operators return or reference the particular rows. ALL retains the duplicate rows in the result of a query or in an aggregate expression. The DISTINCT el

Closest approximation to relational union - sql, Closest Approximation to R...

Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

Tautology - equivalences rules, Tautology - Equivalences Rules: If the...

Tautology - Equivalences Rules: If there Tautologies are not all the time as much easy to note as the one above so than we can use these truth tables to be definite that a sta

Assignments in pl/sql, Assignments in pl/sql The Variables and constants...

Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

Parameter and keyword description - procedures, Parameter and Keyword Descr...

Parameter and Keyword Description: procedure_name The user-defined procedure is declared by this construct. parameter_name: This identifies the formal parameter t

Ensuring backward compatibility, Ensuring Backward Compatibility   The...

Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd