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
ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity BEGIN INSERT INTO bb_basketitem VALUES (88,8,10.8,21,16,2,3); END; Brewbean’s wants to add a check

Authorize and fetch data from Instagram Project Description: Incorporate Instagram feed on mobile site platform: c#, ms sql, jquerymobile, jquery Web admin Author

Using DEFAULT You can use the keyword DEFAULT rather than that of the assignment operator to initialize the variables. For e.g. the declaration blood_type CHAR := ’O’; it can b

Using Cursor Attributes: Every cursor has 4 attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT. If appended to the cursor name, they return the helpful information about

%TYPE: This attribute gives the datatype of a formerly declared collection, cursor variable, object, field, record, database column, or variable. Datatype: This is simply

Need to change mysql query to PDO Project Description: I want someone to convert me 1 .php file that includes some sql/mysql stuff to PDO is a very small file. Skills requ

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

I need a query for PL/SQL, selecting names with cursor, goes down the list, assigns usernames (initials001) based on initials in the name. If two names have same initials the user

Delimiters A delimiter is a simple or compound symbol which has a special meaning to PL/SQL. For example, you use delimiters to symbolize an arithmetic operation like additio

PL SQL Code Review HEADER ELEMENTS File Name Clear, meaningful and descriptive about main objective of the file. Multiple words are joined using underscores which adh