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

Using first and last - collection method, Using FIRST and LAST FIRST a... Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

Cursors, What is Cursors how to use it in Real time application ? What is Cursors how to use it in Real time application ?

Group and having query, Using a join on 2 tables, select all columns and 10... Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_

Update statement - syntax, UPDATE Statement   The UPDATE statement tra... UPDATE Statement   The UPDATE statement transforms the values of the specified columns in one or more rows in the table or view. Syntax:

In operator-comparison operators, IN Operator The operator IN tests the ... IN Operator The operator IN tests the set membership. This means "equal to any member of." The set may have nulls, but they are ignored. For illustration, the statement below do

Miller-urey''s experimental procedure, MILLER-UREY' S EXPERIMENTAL PROCEDU... MILLER-UREY' S EXPERIMENTAL PROCEDURES - They recreated the probable conditions on the primitive earth in the laboratory. An atmosphere containing hydrogen, ammonia, me

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

Effects of null in table literal, Effects of NULL in Table Literal Whe... Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Calculate the total shopper spending, Many of the reports generated from th... Many of the reports generated from the system calculate the total dollars in purchases for a shopper. Complete the following steps to create a function named TOT_PURCH_SF that acce

Ssrs report writing, SSRS Report Writing Project Description: This re... SSRS Report Writing Project Description: This report is part of a larger project to make a SQL Server Reporting Service (SSRS) based reporting solution. There can be more rep