Using Cursor Attributes

To process the SQL data manipulation statements, the SQL engine must opens an implicit cursor named SQL. This cursor's attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) return all the useful information about the most recently executed SQL data manipulation statement.

The SQL cursor has only one composite attribute, like %BULK_ROWCOUNT that has the semantics of an index-by table. The ith element stores the number of rows processed by the ith execution of the SQL statement. If the ith execution affects no rows, %BULK_ ROWCOUNT(i) returns zero. An illustration is shown below:

DECLARE

TYPE NumList IS TABLE OF NUMBER;

depts NumList := NumList(10, 20, 50);

BEGIN

FORALL j IN depts.FIRST..depts.LAST

UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j);

IF SQL%BULK_ROWCOUNT(3) = 0 THEN

...

END IF

END;

The %BULK_ROWCOUNT and the FORALL statement use the similar subscripts. For illustration, if the FORALL statement uses the range -5...10, so does %BULK_ROWCOUNT.

Posted Date: 10/4/2012 3:32:57 AM | Location : United States

Ask an Expert

Related Discussions:- Using cursor attributes - bulk bind performance improvement, Assignment Help, Ask Question on Using cursor attributes - bulk bind performance improvement, Get Answer, Expert's Help, Using cursor attributes - bulk bind performance improvement Discussions

Write discussion on Using cursor attributes - bulk bind performance improvement
Your posts are moderated

Write your message here..

Related Questions

Package standard in pl/sql, Package STANDARD package named STANDARD d... Package STANDARD package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available a

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

Creating a table, Creating a Table Syantax: CREATE TABLE ENROL... Creating a Table Syantax: CREATE TABLE ENROLMENT (StudentId SID, Name   VARCHAR (30) NOT NULL, CourseId CID, PRIMARY KEY (StudentId, CourseId)) ; Explan

Using subqueries, Using Subqueries A subquery is a query (typically ... Using Subqueries A subquery is a query (typically enclosed by parentheses) that appears within another SQL data manipulation statement. If evaluated, the subquery gives a va

Query optimization, 1.( /5 marks) Suppose that a B+-tree index with the sea... 1.( /5 marks) Suppose that a B+-tree index with the search key (dept_name, building) is available on relation department. What would be the best way to handle the following selecti

Difference between implicit and explicit cursor, Implicit Cursor is declare... Implicit Cursor is declared and used by the oracle environment internally. while the explicit cursor is declared and used by the external user. more over implicitly cursors are no

Calculate the number of tuples, Question: (a) The objective of query opti... Question: (a) The objective of query optimization is to choose the most efficient strategy for implementing a given relational query, thereby improving the system performance. On

When or then key constraints, WHEN or THEN Key Constraints Suppose a t... WHEN or THEN Key Constraints Suppose a table has two columns representing a period of time throughout which the information conveyed by the other columns is recorded as having

Features of pl/sql, Main features of PL/SQL A good way to get familiar ... Main features of PL/SQL A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a

What is a record, What Is a Record  ? A record is a group of related... What Is a Record  ? A record is a group of related data items that stored in the fields, each with its own name and datatype. Assume that you have different data about an em