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
Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

Selecting Objects: Suppose that you have run the SQL*Plus script below that creates object type Person and object table persons, and that you have settled the table: CREATE

Aggregate Operators SQL Supports all of the aggregate operators mentioned in the theory book and many more besides. The syntax, however, involves an unusual trick that SQL cal

Keyword & Parameter Description: WHEN: This keyword introduces the exception handler. You can have many exceptions execute the similar sequence of the statements by follo

Declaring Exceptions The Exceptions can be declared only in the declarative part of the PL/SQL subprogram, block, or package. By introducing its name, you can declare an excep

Use Native Dynamic SQL A few programs (a normal-purpose report writer for illustration) should build and process a variety of SQL statements at run time. Therefore, their full

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Manipulating Collections Within PL/SQL, the collections add procedural power and flexibility. The biggest benefit is that your program can compute subscripts to process the spec

Managing Cursors The PL/SQL uses 2 types of cursors: implicit and explicit. The PL/SQL declares a cursor implicitly for all the SQL data manipulation statements, including th

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ