Cursor variables in pl sql, PL-SQL Programming

Cursor Variables:

 To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names the work region, to access the information. Or, you can use the cursor variable that points to the work region. While a cursor always refers to similar query work region, a cursor variable can refer to various work regions. To create the cursor variables, you can define a REF CURSOR type, and then declare the cursor variables of that type.

The Cursor variables are such as C or Pascal pointers that hold the memory location (address) of a little item rather of the item itself. Therefore, declaring a cursor variable build a pointer, not an item.

1597_constant variable.png

Keyword and Parameter Description:

type_name:

This is a user-defined type specifier that is used in the subsequent declarations of the PL/SQL cursor variables.

REF CURSOR:

In PL/SQL, the pointers have datatype REF X, where REF is short form of REFERENCE and

X stands for the class of objects. And hence, the cursor variables have datatype REF CURSOR.

RETURN:

This keyword introduces the RETURN clause that specifies the datatype of the cursor variable result value. You can use the %ROWTYPE attribute in the RETURN clause to give a record type which presents a row in a database table or a row returned by a cursor or strongly typed cursor variable. You can also use the %TYPE attribute to give the datatype of a earlier declared record.

cursor_name:

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

cursor_variable_name:

These identify a PL/SQL cursor variable formerly declared within the present scope.

record_name:

These identify a user-defined record formerly declared within the present scope.

record_type_name:

These identify a RECORD type formerly defined within the present scope.

db_table_name:

These identify a database table (or view) that shuld be accessible when the declaration is elaborated.

%ROWTYPE:

These attributes provides a record type that presents a row in the database table or a row fetched from the cursor or strongly typed cursor variable. The Fields in the record and equivalent columns in the row have similar names and datatypes.

%TYPE:

These attributes provides the datatype of a formerly declared user-defined record.

 

Posted Date: 10/6/2012 9:14:45 AM | Location : United States







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

Write discussion on Cursor variables in pl sql
Your posts are moderated
Related Questions
Using FOR UPDATE If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

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

LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)

1. Create a procedure called TAX_COST_SP to accomplish the tax calculation task. Keep in mind that the state and subtotal values are inputs into the procedure and the procedure is

Example of WRAP Operator - SQL The effect of Example can be obtained in SQL but note that one needs to write down not only the names of the columns being wrapped but also the

Multiple Assignment- SQL SQL supports multiple assignment to local variables and also applies multiple assignment semantics in SET clauses of UPDATE statements, but does not

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Remote Operations: As the illustration shows below, the PL/SQL subprograms can execute the dynamic SQL statements which refer to the objects on a remote database: PROCEDURE