Defining ref cursor types, PL-SQL Programming

Defining REF CURSOR Types

To make cursor variables, you take 2 steps. At first, you define a REF CURSOR type, and then declare the cursor variables of that type. You can define the REF CURSOR types in any PL/SQL subprogram, block, or package using the syntax as shown:

TYPE ref_type_name IS REF CURSOR RETURN return_type;

Where ref_type_name is a type specifier used in the subsequent declarations of the cursor variables and return_type should present a record or a row in a database table. In the illustration below, you specify a return type that presents a row in the database table dept:

DECLARE

TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

REF CURSOR types can be weak (nonrestrictive) or strong (restrictive). 

Posted Date: 10/4/2012 4:00:23 AM | Location : United States







Related Discussions:- Defining ref cursor types, Assignment Help, Ask Question on Defining ref cursor types, Get Answer, Expert's Help, Defining ref cursor types Discussions

Write discussion on Defining ref cursor types
Your posts are moderated
Related Questions
UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

Example of Shorthand for a row constraint Example: Shorthand for a row constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Mark_in_range CHECK (Mark BETWEEN 0 AND 100);

Dynamic Ranges The PL/SQL lets you determine the loop range dynamically at run time, as the example below shows: SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_cou

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

Rephrase Conditional Control Statements When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops evaluating the expression as s

Example of EXCEPT Operator - SQL Example, like its counterpart in the theory book, illustrates the convenience of allowing any table expression to be the source for an INSERT

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Using a join on 3 tables, select 5 columns and 10 rows from the 3 tables without the use of a Cartesian product Query: SELECT E.LAST_NAME, E.FIRST_NAME, S.BUILDING, S.BRAN

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff