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

Initializing objects in pl sql, Initializing Objects: Till you initiali...

Initializing Objects: Till you initialize an object by calling the constructor for its object type, the object is automatically null. That is, the object itself is null, not me

Defining records, Defining and Declaring Records To create records, yo...

Defining and Declaring Records To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative

Block structure in pl/sql, Block Structure The PL/SQL is a block-struct...

Block Structure The PL/SQL is a block-structured language. That is, the fundamental units (procedures, anonymous blocks, and functions) that make up a PL/SQL program are logi

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

Third step at defining type sid in sql, Third Step at defining type SID in ...

Third Step at defining type SID in SQL CREATE DOMAIN SID AS VARCHAR(5) CHECK ( VALUE IS NOT NULL AND SUBSTRING(VALUE FROM 1 FOR 1) = 'S' AND CAST('+'||SUBSTRING(VALUE

Heap sort algorithm in pl sql, I want to implement heap sort algorithm in p...

I want to implement heap sort algorithm in pl sql please share the source code for guidance

Example of using aggregation on nested tables, Example of Using Aggregation...

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM

Calculating a Shopper''s Total Spending, Many of the reports generated from...

Many of the reports generated from the system calculate the total dollars in a shopper''s purchases. Follow these steps to create a function named TOT_PURCH_SF that accepts a shopp

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

Procedural constraint enforcement (triggers) , Procedural Constraint Enforc...

Procedural Constraint Enforcement (Triggers) SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures th

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