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
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

GOTO Statement   The GOTO statement branches categorically to a block label or statement label. The label should be exclusive within its scope and should precede a PL/SQL bloc

This task involves developing some functions that extract data from an SQL database. The scenario is that a company which owns an online vehicle search website wants to generate so

I need SQL , WP SQL Expert Project Description: Expert required to modify WP SQL query. Skills required are MySQL, SQL, PHP, Wordpress

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Using the BULK COLLECT Clause The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these ke

Create a Oracle procedure to produce vertical output format when selecting rows from a database table.

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

Records Records are the items of the type RECORD. The Records have exclusively named fields that can store the data values of various types. And hence, a record treat associate

a. Write an anonymous block that contains a PL/SQL function. Given an order number orderNo, the function will calculate the total number of the parts in the order. Then the anonym