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

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

Second step at defining type sid in sql, Second Step at defining type SID i...

Second Step at defining type SID in SQL CREATE TYPE SID AS VARCHAR(5) ; Explanation: TYPE SID announces that a type named SID is being defined to the system.

Using savepoints, Using Savepoints The scope of the savepoint is a tra...

Using Savepoints The scope of the savepoint is a transaction in which it is defined. The Savepoints defined in the major transaction are not related to the savepoints defined

Using operator value in pl sql, Using Operator VALUE: As you may expec...

Using Operator VALUE: As you may expect, the operator VALUE returns the value of an object. The VALUE takes its argument a correlation variable.  For illustration, to return a

Example of not exists in sql, Example of NOT EXISTS in SQL Example: Us...

Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

Do you know anyone that can do this type of coding or not?, Task 2 [12 mark...

Task 2 [12 marks] Write the package body for the following package specification (the detailed description of each function and procedure is provided in the appendix below). Place

When are constraints checked - sql, When Are Constraints Checked Unde...

When Are Constraints Checked Under the model constraints are conceptually checked at all statement boundaries (and only at statement boundaries). By default the same is true

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

Exception handling, Exception handling In the PL/SQL, a warning or erro...

Exception handling In the PL/SQL, a warning or error condition is known as an exception. The Exceptions can be internally defined (by the run-time system) or user defined. The

Authorisations - privileges, Authorisations - Privileges As relational...

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

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