Packaging cursors, PL-SQL Programming

Packaging Cursors 

You can split a cursor specification from its body for placement in a package. In that way, you can change the cursor body without changing the cursor specification. You can code the cursor specification in the package specification using the syntax as shown below:

CURSOR cursor_name [(parameter[, parameter]...)] RETURN return_type;

In the illustration below, you use the %ROWTYPE attribute to provide a record type which presents a row in the database table emp:

CREATE PACKAGE emp_actions AS

/* Declare cursor spec. */

CURSOR c1 RETURN emp%ROWTYPE;

...

END emp_actions;

CREATE PACKAGE BODY emp_actions AS

/* Define cursor body. */

CURSOR c1 RETURN emp%ROWTYPE IS

SELECT * FROM emp

WHERE sal > 3000;

...

END emp_actions;

The cursor specification has no SELECT statement as the RETURN clause defines the datatype of the result value. Though, the cursor body should have a SELECT statement and the similar RETURN clause as the cursor specification. Also, the number& datatypes of items in the SELECT list and the RETURN clause should match.

The Packaged cursors increase the flexibility. For illustration, you can change the cursor body in the last illustration, as shown, without having to change the cursor specification:

CREATE PACKAGE BODY emp_actions AS

/* Define cursor body. */

CURSOR c1 RETURN emp%ROWTYPE IS

SELECT * FROM emp

WHERE deptno = 20; -- new WHERE clause

...

END emp_actions;

Posted Date: 10/4/2012 3:57:06 AM | Location : United States







Related Discussions:- Packaging cursors, Assignment Help, Ask Question on Packaging cursors, Get Answer, Expert's Help, Packaging cursors Discussions

Write discussion on Packaging cursors
Your posts are moderated
Related Questions
Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

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

Use of Table Comparisons - SQL Table comparisons where it is noted that although table expressions cannot be compared, we have TABLE (t) to convert a table expression t into

Effects of NULL in Aggregate Operator - SQL Let aggop(x) be an invocation of some aggregate operator aggop in SQL, where x is an expression (usually an open expression) to be

THEORY OF SPONTANEOUS GENERATION - ABIOGENESIS OR AUTOGENESIS - According to this theory, the existing living communities have originated from non-living organic matter with

RETURN Statement The RETURN statement instantly completes the execution of a subprogram and returns control to the caller. The Execution then resumes with the statement below t

Manipulating Collections Within PL/SQL, the collections add procedural power and flexibility. The biggest benefit is that your program can compute subscripts to process the spec

Keyword and Parameter Description: label_name: This is an undeclared identifier which labels an executable statement or the PL/SQL block. You can use a GOTO statement to

%ROWTYPE: This attribute gives a record type which represents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresp

Data Types in SQL - Integer INTEGER or  synonymously INT, for integers within a certain range. SQL additionally has types SMALLINT and BIGINT for certain ranges of integers. T