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

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

Assignment Help:

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.

Related Discussions:- Using cursor attributes - bulk bind performance improvement

Create GUIs in PL/SQL, i NEED TO CREATE 3 guiS IN pl/sql sERVER PAGE FORM

i NEED TO CREATE 3 guiS IN pl/sql sERVER PAGE FORM

Using operator ref - manipulating objects pl sql, Using Operator REF: ...

Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

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

Is null operator-comparison operators, IS NULL Operator The IS NULL oper...

IS NULL Operator The IS NULL operator returns the Boolean value TRUE whenever its operand is null or FALSE if it is not null. The comparisons including the nulls always yield NU

Expression in assignment statement - pl sql, Expression: This is a ran...

Expression: This is a randomly complex combination of constants, variables, literals, operators, & function calls. The simplest expression consists of a single variable. If th

Expressions - syntax, Expressions An expression is a randomly comple...

Expressions An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl

Why use cursor variables, Why Use Cursor Variables ? Primarily, you use...

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

Boolean values-assignments in pl/sql, Boolean Values Only the values TRU...

Boolean Values Only the values TRUE, FALSE, & NULL can be assigned to a Boolean variable. For illustration, given the declaration DECLARE done BOOLEAN; the following statements

Using exists - collection methods, Using EXISTS The EXISTS(n) returns ...

Using EXISTS The EXISTS(n) returns TRUE if the nth element in a collection exist. Or else, EXISTS(n) returns FALSE. Primarily, you use EXISTS with DELETE to maintain the spars

Definition of from - sql, Definition of FROM - SQL Recall that the ope...

Definition of FROM - SQL Recall that the operand of FROM is denoted by a commalist, each element of that commalist being a table expression optionally accompanied by a range v

Write Your Message!

Name

Email id

Message

Verfication Code

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd