Cursor attributes in pl sql, PL-SQL Programming

Cursor Attributes

 The Cursors and cursor variables have 4 attributes which give you helpful information about the execution of a data manipulation statement.

Syntax:

1586_Cursor Attributes.png

Keyword and Parameter Description:

cursor_name:

These identify an explicit cursor formerly declared within the present scope.

cursor_variable_name:

These identify the PL/SQL cursor variable (or parameter) formerly declared within the present scope.

host_cursor_variable_name:

These identify a cursor variable declared in the PL/SQL host atmosphere and passed to the PL/SQL as a bind variable. The host cursor variable datatype is well-suited with the return type of any PL/SQL cursor variable. The host variables should be prefixed with a colon.

SQL:

These are the name of the implicit SQL cursor.

%FOUND:

This is a cursor attribute that can be appended to the name of the cursor or cursor variable. Previous to the first fetch from an open cursor, the cursor_name%FOUND results NULL. Afterward, it results TRUE if the final fetch returned a row, or FALSE if the final fetch failed to return a row. Until the SQL statement is executed, the SQL%FOUND results NULL. Afterward, it results TRUE if the statement affects any rows, or FALSE if it affects no rows.

%ISOPEN:

This is a cursor attribute that can be appended to the name of the cursor or cursor variable. If a cursor is open, the cursor_name%ISOPEN results TRUE; Or else, it results FALSE. The Oracle automatically closes the implicit SQL cursor after executing its related SQL statement, Therefore the SQL%ISOPEN always results FALSE.

%NOTFOUND:

This is a cursor attribute that can be appended to the name of the cursor or cursor variable. Previous to the first fetch from an open cursor, the cursor_name%NOTFOUND results NULL. Afterward, it results FALSE if the last fetch returned a row, or TRUE if the last fetch unsuccessful to return a row.

Until a SQL statement is executed, the SQL%NOTFOUND results NULL. Afterward, it results FALSE if the statement affects any rows, or TRUE if it affects no rows.

%ROWCOUNT:

This is a cursor attribute that can be appended to the name of the cursor or cursor variable. If a cursor is opened, the %ROWCOUNT is zeroed. Before the first fetch, the cursor_name%ROWCOUNT yields to 0. Afterward, it results the number of rows fetch so far. The number is incremented if the newest fetch returned a row. Until the SQL statement is executed, the SQL%ROWCOUNT results NULL. Afterward, it results the number of rows affected by the statement. The SQL%ROWCOUNT results 0 if the statement affect no rows.

Posted Date: 10/6/2012 9:12:17 AM | Location : United States







Related Discussions:- Cursor attributes in pl sql, Assignment Help, Ask Question on Cursor attributes in pl sql, Get Answer, Expert's Help, Cursor attributes in pl sql Discussions

Write discussion on Cursor attributes in pl sql
Your posts are moderated
Related Questions
Collections:   The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subsc

Using SAVEPOINT The SAVEPOINT names and marks the present point in the processing of a transaction. Used with the ROLLBACK TO statement, the savepoints undo parts of a transac

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

Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

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

Question: Consider the following relations (primary keys are underlined): AUTHOR (ANo, aname, address, speciality) PUBLISHER (PNo, pname, Location) BOOK (BNo, Title, ISBN,

Second Step at defining type SID in SQL CREATE TYPE SID AS VARCHAR(5) ; Explanation: TYPE SID announces that a type named SID is being defined to the system.

Exceptions An exception is the runtime error or warning condition that can be predefined or user-defined. The Predefined exceptions are raised implicitly through runtime system

Majority of Differences among 9i, 10G, 11G :- These are some combine feature which has differences among others. Automatic Workload Repository (AWR) Drop database' s

MERGE and TRUNCATE in SQL SQL has two more table update operators, MERGE and TRUNCATE. MERGE, like INSERT, takes a source table s and uses it to update a target table t. Brief