Packaging cursors, PL-SQL Programming

Assignment Help:

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;


Related Discussions:- Packaging cursors

Parameter modes - pl sql, Parameter Modes: You do not require to speci...

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Rowid - sql pseudocolumns, ROWID The ROWID returns the rowid (binary a...

ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il

Records - syntax, Records Records are the items of the type RECORD. The...

Records Records are the items of the type RECORD. The Records have exclusively named fields that can store the data values of various types. And hence, a record treat associate

Control structure, Control Structures The Control structures are the mo...

Control Structures The Control structures are the most important PL/SQL extension to the SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data

Sql is a database language, SQL Is a Database Language: The commands g...

SQL Is a Database Language: The commands given to a DBMS by an application are written in the database language of the DBMS. The term data sublanguage is sometimes used instea

Using exists - collection methods, Using EXISTS The EXISTS(n) returns ...

Using EXISTS The EXISTS(n) returns TRUE if the nth element in a collection exist. Or else, EXISTS(n) returns FALSE. Primarily, you use EXISTS with DELETE to maintain the spars

Tables within a table - sql, Tables within a Table - SQL Figure here ...

Tables within a Table - SQL Figure here is an exact copy of the one in the theory book and as before it is just an alternative way of representing some of the information con

Cursor variables in pl sql, Cursor Variables:   To execute the multi-...

Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

In out mode - parameter modes, IN OUT Mode An IN OUT parameter passes ...

IN OUT Mode An IN OUT parameter passes initial values to the subprogram being called and return efficient values to the caller. Within the subprogram, an IN OUT parameter acts

Procedure, 1. Create a procedure called TAX_COST_SP to accomplish the tax c...

1. Create a procedure called TAX_COST_SP to accomplish the tax calculation task. Keep in mind that the state and subtotal values are inputs into the procedure and the procedure is

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd