0261-0264

Previous Table of Contents Next

Page 261

The PROCEDURE statement begins the definition of the package application-interface routine get_order_total. Enclosed in parentheses are the parameters to be passed between the application and the order_total package. The semicolon marks the end of the procedure definition.

TIP

Modularity is the key to successful package design. If you limit the scope of subprograms, your code will be easier to design and debug.

Function Definition

Function definition is much the same as procedure definition, as illustrated by the following example:

 FUNCTION   calc_ship_charges (     in_merch_total    IN NUMBER   ) RETURN NUMBER; 

The FUNCTION statement begins the definition of the package function calc_ship_charges. Enclosed in parentheses are the parameters to be passed to the function for calculating shipping charges. The RETURN statement identifies the datatype of the calculated value to be returned. The semicolon marks the end of the function definition.

Subprogram Parameter Modes

You can define parameters as IN (the default parameter mode), IN OUT, or OUT, depending on the nature of the information to be passed. The first parameter, in_order_num, is defined as IN, which designates it as a value being passed to the subprogram. Defining a parameter as IN prevents it from being assigned a value in the routine.

The parameters out_status_code, out_msg, out_merch_total, and out_grand_total from the procedure definition example are defined as OUT ”values being returned to the caller. These parameters are uninitialized upon entry to the routine and are available for assignment of a value within the routine. Designating a parameter as OUT prevents it from being used in a subprogram expression.

The parameters out_shipping and out_taxes are defined as IN OUT, the last parameter mode. Parameters designated as IN OUT are initialized variables that are available for reassignment within the subprogram.

Page 262

Subprogram Specifications

After defining a subprogram and its parameters, you develop code for the packaged procedure subprogram. The following example illustrates a few basic constructs to be aware of while coding a subprogram:

 PROCEDURE   init_line_items IS   (local variables) BEGIN     (subprogram logic)     EXCEPTION       (exception handling) END init_line_items; 

In this example, the PROCEDURE name is init_line_items, with the local variables specified after the IS statement. The BEGIN statement is the actual start of the procedure (or function) where the subprogram code is developed along with any subprogram exception handling. The procedure is finished with the END name statement.

CAUTION

The procedure parameter list must exactly match the specification parameter list for the procedure being developed. This includes datatypes and parameter modes included in the specification.

Default Procedure Parameters

To add flexibility to procedure calls, you might specify default parameter values in your procedure definition. In this manner, you can call the procedure with all, one, or none of the specified parameters. Defaults are supplied for parameters that are not passed. The following example illustrates a procedure definition using default parameters:

 PROCEDURE   calc_ship_charges(     merch_total NUMBER DEFAULT 5.95) IS   ... 

References to the calc_ship_charges procedure can include a merch_total (or not). Calls without the merch_total parameter default to 5.95, as shown.

Stand-Alone Procedures

Procedures that are not part of a package are known as stand-alone because they are independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These types of procedures are not available for reference from other Oracle tools. Another limitation of stand-alone procedures is that they are compiled at runtime, which slows execution.

Page 263

Cursors

PL/SQL uses cursors for all database information access statements. The language supports the use of both implicit and explicit cursors. Implicit cursors are those established for which explicit cursors are not declared. You must use explicit cursors or cursor FOR loops in all queries that return multiple rows.

Declaring Cursors

You define cursors in the variable definition area of PL/SQL subprograms using the CURSOR name IS statement, as shown in the following example:

 CURSOR c_line_item IS (sql statement) 

The cursor SQL statement can be any valid query statement. Subsequent to cursor initialization, you are able to control cursor actions with the OPEN, FETCH, and CLOSE statements.

Cursor Control

To use a cursor for manipulating data, you must use the statement OPEN name to execute the query and identify all rows that meet the SELECT criteria. Subsequent retrieval of rows is accomplished with the FETCH statement. Once all information is processed , the CLOSE statement terminates all activity associated with the opened cursor. The following is an example of cursor control:

 OPEN c_line_item;   ...    FETCH c_line_item     INTO li_info;   ...   (retrieved row processing)   ... CLOSE c_line_item; 

The code opens the cursor c_line_item and processes the fetched rows. After it retrieves and processes all the information, the cursor closes . Retrieved-row processing is typically controlled by iterative loops, as discussed later in the chapter.

Explicit Cursor Attributes

There are four attributes associated with PL/SQL cursors:

• %NOTFOUND
• %FOUND
• %ROWCOUNT
• %ISOPEN

Page 264

All cursor attributes evaluate to TRUE, FALSE, or NULL, depending on the situation. The attribute %NOTFOUND evaluates to FALSE when a row is fetched, TRUE if the last FETCH did not return a row, and NULL if the cursor SELECT returned no data. The attribute %FOUND is the logical opposite of %NOTFOUND with respect to TRUE and FALSE, but still evaluates to NULL if the cursor FETCH returns no data.

You can use %ROWCOUNT to determine how many rows are selected at any point in the FETCH. This attribute increments upon successful selection of a row. In addition, %ROWCOUNT is at zero when the cursor first opens.

The final attribute, %ISOPEN, is either TRUE or FALSE, depending on whether the associated cursor is open. Before the cursor opens and after the cursor closes, %ISOPEN is FALSE. Otherwise, it evaluates to TRUE.

Cursor Parameters

You can specify parameters for cursors in the same way you do for subprograms. The following example illustrates the syntax for declaring parameter cursors:

 CURSOR c_line_item (order_num IN NUMBER) IS   SELECT merch_gross, recipient_num   FROM line_item   WHERE order_num = g_order_num; 

The parameter mode is always IN for cursor parameters, but the datatype can be any valid datatype. You can reference a cursor parameter, whose value is set when the cursor opens, only during the cursor's declared SQL query.

Flexibility within cursor parameters enables the developer to pass different numbers of parameters to a cursor by using the parameter default mechanism. This is illustrated in the following example:

 CURSOR c_line_item   (order_num INTEGER DEFAULT 100,     line_num INTEGER DEFAULT 1) IS ... 

By using the INTEGER DEFAULT declaration, you can pass all, one, or none of the parameters to this cursor depending on the logic flow of your code.

Creating Cursor Packages

A cursor package is similar to a procedure package in that you specify the cursor and its return attribute, %TYPE or %ROWTYPE, in the package specification area. You then specify the cursor "body" in the package body's specification area. Packaging a cursor in this manner gives you the flexibility of changing the cursor body without having to recompile applications that reference the packaged procedure. The following is a cursor package example:

 CREATE OR REPLACE PACKAGE order_total AS 

Previous Table of Contents Next