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

Using limit - collection method, Using LIMIT For nested tables, that h...

Using LIMIT For nested tables, that have no maximum size, the LIMIT returns NULL. For varrays, the LIMIT returns the maximum number of elements that a varray can have (that yo

Wrapping and unwrapping in sql, Wrapping and unwrapping in SQL Operato...

Wrapping and unwrapping in SQL Operators WRAP and UNWRAP in connection with attributes whose declared types are tuple types. Example shows how extension and projection can be

Use the pls_integer datatype - performance of application, Use the PLS_INTE...

Use the PLS_INTEGER Datatype When you require to declare an integer variable, use the datatype PLS_INTEGER that is the most efficient numeric type. That is as the PLS_INTEGER

Using host arrays - bulk bind performance improvement, Using Host Arrays ...

Using Host Arrays The Client-side programs can use anonymous PL/SQL blocks to bulk-bind input and output host arrays. However, this is the well-organized way to pass the colle

Components of an object type - attributes in pl/sql, Attributes: Just ...

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

Example of alternative formulation as a table constraint, Example of Altern...

Example of Alternative formulation as a table constraint Example: Alternative formulation as a table constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_

Table literals - sql, Table Literals - SQL One might expect SQL to sup...

Table Literals - SQL One might expect SQL to support table literals in the manner illustrated in Example 2.2, but in fact that is not a legal SQL expression. Example: Not a

Biconditional - sql, Biconditional - SQL The biconditional p ↔ q can b...

Biconditional - SQL The biconditional p ↔ q can be expressed in Tutorial D by p = q and the same is true of SQL. The question then arises as to whether, in SQL, p = q is equiv

Iteration schemes- iterative control, Iteration Schemes The bounds of a ...

Iteration Schemes The bounds of a loop range can be variables, literals, variables, or expressions but must compute to integers. Below are some of the examples. As you can see t

Tautologies, Tautologies: Above given table allows us to read the trut...

Tautologies: Above given table allows us to read the truth of the connectives in the next manner. Just expect we are looking at row three. It means this says that, if there P

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