Fetching from a cursor variable, PL-SQL Programming

Assignment Help:

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 {cursor_variable_name | :host_cursor_variable_name}

INTO {variable_name[, variable_name]... | record_name};

In the next example, you fetch rows from the cursor variable emp_cv into the

user-defined record emp_rec:

LOOP

/* Fetch from cursor variable. */

FETCH emp_cv INTO emp_rec;

EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched

-- process data record

END LOOP;

Any variables in the related query are evaluated only when the cursor variable is opened. To change the product set or the values of variables in the query, you should reopen the cursor variable with the variables set to their new values. Though, you can use a different INTO clause on separate fetches with similar cursor variable. Each fetch retrieve another row from the similar result set.

The PL/SQL ensures the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each of the column value returned by the query related with the cursor variable, there should be a parallel, type-compatible field or variable in the INTO clause. The number of fields or variables should also equal the number of column values. Or else, you get an error.

The error occurs at the compile time, when the cursor variable is strongly typed or at run time, if it is weakly typed. At run time, the PL/SQL raises the predefined exception ROWTYPE_MISMATCH before the first fetch. Therefore, if you trap the error and execute the FETCH statement using a different INTO clause, then no rows are lost.


Related Discussions:- Fetching from a cursor variable

Updating by insertion, Updating by insertion Syntax : INSERT IN...

Updating by insertion Syntax : INSERT INTO ENROLMENT VALUES (SID ('S4'), 'Devinder', CID ('C1'));

Pl/sql engine, Architecture The PL/SQL run-time system and compilation ...

Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL

Write an anonymous block that contains a pl/sql procedure, Write an anonymo...

Write an anonymous block that contains a PL/SQL procedure. The procedure takes two input parameter: oldZip and newZip, and it updates the zipcodes table by replacing all oldZip wit

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

Manipulating local collections - pl/sql, Manipulating Local Collections ...

Manipulating Local Collections Within PL/SQL, to manipulate the local collection, by using the  TABLE and CAST operators . The operands of CAST are a collection declared locally

Anatomy of a command, Anatomy of a Command Figure, showing a simple S...

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Definition of cross join - sql, Definition of CROSS JOIN - SQL Let s ...

Definition of CROSS JOIN - SQL Let s = t1 CROSS JOIN t2, where t1 and t2 are table expressions optionally accompanied by range variables. Then: Note: Here T denotes Table

Recursive subprograms, Recursive Subprograms The recursive subprogram ...

Recursive Subprograms The recursive subprogram is the one that calls itself. Think of a recursive call as a call to a few other subprograms that does the similar task as your

Records - syntax, Records Records are the items of the type RECORD. The...

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

How bulk bind helps improvement in performance?, How Bulk Binds Improve Per...

How Bulk Binds Improve Performance The assigning of values to the PL/SQL variables in SQL statements is known as binding. The binding of the whole collection at once is know

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