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
Use of COUNT in SQL It describes and discusses various general methods of expressing constraints, eventually noting that support for "=" with relation operands is sufficient f

Using INNER JOIN INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows ar

LAWS / RULES - Dollo's Law                     :           Living organisms do exhibit evolutionary irreversibility or evolution is irreversible. Williston's Law

GOTO Statement   The GOTO statement branches categorically to a block label or statement label. The label should be exclusive within its scope and should precede a PL/SQL bloc

Perform the following queries on the Hospital1.DB using SQL Anywhere (START EARLY!). a. Which patients have purchased the drug "Tylenol"? List the names and addresses. Arrange the

Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e

Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th

Functions The function is a subprogram which can take parameters and be invoked. Normally, you can use a function to calculate a value. The function has 2 sections: the specifi

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Challenge 1 You are required to do the project and write a test plan for it. Demo 4 is a check writer program for employees. In it, the user enters all information about the