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

Package - pl/sql programming, What Is a Package? The package is a sch...

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

Dynamic ranges- iterative control, Dynamic Ranges The PL/SQL lets you det...

Dynamic Ranges The PL/SQL lets you determine the loop range dynamically at run time, as the example below shows: SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_cou

Example of except operator - sql, Example of EXCEPT Operator - SQL Ex...

Example of EXCEPT Operator - SQL Example, like its counterpart in the theory book, illustrates the convenience of allowing any table expression to be the source for an INSERT

I need sql data base, I need SQL Data Base Project Description: Netwo...

I need SQL Data Base Project Description: Network SQL database and SQL Setup two 4 workstation Skills required are Data Entry, MySQL, SQL

Using set transaction, Using SET TRANSACTION You use the SET TRANSACTI...

Using SET TRANSACTION You use the SET TRANSACTION statement to begin the read-only or read-write transaction, start an isolation level, or assign your present transaction to a

Case sensitivity-naming conventions, Case Sensitivity Similar to all the...

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

Effects of null in aggregate operator - sql, Effects of NULL in Aggregate O...

Effects of NULL in Aggregate Operator - SQL Let aggop(x) be an invocation of some aggregate operator aggop in SQL, where x is an expression (usually an open expression) to be

In mode - parameter modes, IN Mode An IN parameter pass the values to ...

IN Mode An IN parameter pass the values to the subprogram being called. Within the subprogram, an IN parameter acts like a constant. And hence, it cannot be assigned a value.

Theory of catastrophism or catalysm - origin of life, THEO R Y OF CATASTR...

THEO R Y OF CATASTROPHISM OR CATALYSM (CUVIER 1769-1832) - The world has passed thorugh several stages and at the end of each stage there was a catastrophe killing all the

Multiple assignment - sql, Multiple Assignment- SQL SQL supports mult...

Multiple Assignment- SQL SQL supports multiple assignment to local variables and also applies multiple assignment semantics in SET clauses of UPDATE statements, but does not

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