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
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

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

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a

Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty

%ISOPEN The Oracle closes the SQL cursor automatically after executing its related SQL statement. As a result, the %ISOPEN forever yields FALSE.

Relational Operators and Logical Operators It prepares the ground for subsequent sections in which each specific relational operator is paired with its logical counterpart, su

Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

THEORY OF BIOGENESIS - This theory explains that the existing living organisms originated from pre-existing living beings not from non living entities. This concept of or

EXECUTE IMMEDIATE Statement   The EXECUTE IMMEDIATE statement prepare (parses) and instantly executes a dynamic SQL statement or an anonymous PL/SQL block. Syntax:

Using a join on 3 tables, select 5 columns and 10 rows from the 3 tables without the use of a Cartesian product Query: SELECT E.LAST_NAME, E.FIRST_NAME, S.BUILDING, S.BRAN