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

Mysql developer/programmer, Project Description: I want somebody who can...

Project Description: I want somebody who can help me with an idea that I have been working on for a few months now. The person will require extensive knowledge of warcraft 3 PvP

Exception_init pragma - pl/sql, EXCEPTION_INIT Pragma The pragma EXCEPT...

EXCEPTION_INIT Pragma The pragma EXCEPTION_INIT relates an exception name with an Oracle error number. Which allow you to refer to any internal exception by the name and to wri

Example of foreign key constraint - sql, Example of Foreign Key Constraint ...

Example of Foreign Key Constraint Example: Alternative formulation for 6.3 as a foreign key constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_take_exam

Loop statements, LOOP Statements The LOOP statements execute a series o...

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

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

Pl/sql assignment, 1. a. Write a trigger that fires when a part's price...

1. a. Write a trigger that fires when a part's price is updated. The trigger will write a record into a table called PriceUpdates. The record should contain the information of

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

Parameter and keyword description - open-for statement, Parameter and Keywo...

Parameter and Keyword Description: cursor_variable_name: This identifies a cursor variable or the parameter formerly declared within the present scope. host_cursor_va

Data types in sql - xml, Data Types in SQL - XML, Array, Row ...

Data Types in SQL - XML, Array, Row BINARY LARGE OBJECT for arbitrarily large bit strings. XML for XML documents and fragments. ARRAY types for arrays.

Fetching with a cursor, Fetching with a Cursor The FETCH statements re...

Fetching with a Cursor The FETCH statements retrieve the rows in the result set one at a time. After each and every fetch, the cursor advance to the next row in the result set

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