Using cursor attributes - bulk bind performance improvement, PL-SQL Programming

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







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
Related Questions
Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

Using Aliases The Select-list items fetched from a cursor related with the %ROWTYPE should have simple names or, if they are expressions, should have aliases. In the example bel

Package STANDARD The package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available

Creating a SQL file 1. Open a new file in Notepad++ and save it to the location c:\mysql\bin, with the name lab8script.sql (the file extension should be .sql ). Add a MySQL co

Need to change mysql query to PDO Project Description: I want someone to convert me 1 .php file that includes some sql/mysql stuff to PDO is a very small file. Skills requ

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

Primary Key - SQL A PRIMARY KEY specification carries an implicit NOT NULL constraint on each column of the specified key. When more than one key constraint is required, the k

COMMIT Statement The COMMIT statement explicitly makes everlasting changes to the database during the present transaction. The Changes made to the database are not considered e

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh