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
I need to write one function and one procedure to query a Oracle 10.1 DB using PL SQL. I have the schema and exact queries...along with work Ive started and a template to put the a

CLOSE Statement The CLOSE statement allows the resources held by a cursor variable or open cursor to be reused. No more rows can be fetched from the cursor variable or closed

Count Operator in SQL Example: Counting the students who have scored more than 50 in some exam (SELECT COUNT (*) FROM (SELECT DISTINCT StudentId FROM EXAM_MARK WHE

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

DELETE Command - SQL Loosely speaking, DELETE removes some existing rows from its target table. Suppose the university decides that course C3 is to be withdrawn. Example shows

what is the use of declare keyword

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Variable Declaration - SQL SQL's support for variables is very similar to Tutorial D's, except that the syntax for creating persistent  variables-base tables-is quite differen