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
The requirements as follows: Create a folder called "SECURITY" on the server and upload all your project files to that folder. Please note, the "SECURITY" folder is NOT to be IN

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

what is the use of declare keyword

Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

Write a program to implement the inverted file shown in the slides (Simple Index file, LabelID file and Data file).  Use the Avail_List to point at the deleted Label IDs so that th

Manipulating Objects: You can use an object type in the CREATE TABLE statement to indicate the datatype of a column. When the table is created once, you can use the SQL statem

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

SQL Operators The PL/SQL uses all the SQL set, comparison, and row operators in the SQL statements. This part briefly describes some of these operators.  1. Comparison Opera

Keyword and Parameter Description: label_name: This is an undeclared identifier which optionally labels the PL/SQL block. When used, label_name should be enclosed by the do

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo