|PL/SQLSQL Procedural Language Extension|
|Subprogram Parameter Modes|
|Default Procedure Parameters|
|Explicit Cursor Attributes|
|Creating Cursor Packages|
|Variable Declaration and Assignment|
|Numeric FOR Loops|
|Cursor FOR Loops|
|Referencing Stored Procedures|
|Stored Procedure States|
|Referencing Internal Functions|
|Scope of Reference|
This chapter discusses constructs of PL/SQL that enable software developers to use this procedural language interface to the Oracle RDBMS. Constructs within PL/SQL are similar to those found in a 3GL and C and provide a flexible method to manipulate
database information. PL/SQL is a mainstay of SQL*Forms, but writing database procedures outside forms gives you access to common database manipulation routines by other Oracle development tools while limiting the size of forms applications.
PL/SQL enables you to group all related programming in one database object called a package. A complete set of PL/SQL routines that accomplish a certain programming task is called a packaged procedure. Packages also help you develop an
applications interface separate from internal procedure code. This concept is discussed in more detail later in this chapter.
Before reading about the various aspects of the PL/SQL language, examine the syntax for creating a procedure and building a script for ease of maintenance as changes occur. This code is the first step in developing a sample package for calculating
dollar totals in a merchandise orderan example that is completed within this chapter. The following example illustrates some commands for creating this PL/SQL packaged procedure and script.
set echo on spool order_total CREATE OR REPLACE PACKAGE order_total AS (package specifications) END order_total CREATE OR REPLACE PACKAGE BODY order_total AS (package body specifications) END order_total; DROP PUBLIC SYNONYM order_total; CREATE PUBLIC SYNONYM order_total for order_total; GRANT EXECUTE ON order_total TO PUBLIC; spool off SELECT * FROM user_errors WHERE name='ORDER_TOTAL' ;
The first command in this script, SET ECHO ON, displays a listing of the package to the screen as it is being compiled. ECHO combined with the SPOOL name command creates a list file (order_total.lst) for debug purposes. This file will contain the
compilation of the procedure, including errors, complete with line numbers.
CREATE OR REPLACE PACKAGE name is the command that starts the procedure build in the database. Declarations of objects and subroutines within the package area are visible to your applications. Think of this area as the application interface to
your PL/SQL code; at the very least, you must define the procedure entry routine here. Modifications to any specifications in this area require rebuilding your applications. The END statement signifies the end of the package specification area.
Next is the CREATE OR REPLACE PACKAGE BODY name statement that begins the specification area for declarations of PL/SQL objects and subroutines that only the procedure can "see." This area is invisible to your application but is not
required in designing package procedures. However, designing procedures in this manner enables you to modify package body specifications without altering the application interface. As a result, applications do not require recompilation when these internal
specifications change. Once again, the END statement marks the end of package body specifications.
The next three statements work in conjunction to enable all users to access procedures defined in your package. First, any existing public synonym is dropped and subsequently recreated. The GRANT statement provides "public" access to the
At the end of the script is the statement SPOOL OFF name that terminates output to the listing file. This is followed by a SELECT statement that displays any compilation errors to the terminal where the script was invoked. The name field
in this SELECT statement identifies the name of the package being created and must be in uppercase.
Once you create it, you can run the script using a SQL*Plus command as follows:
sqlplus (username/password) @ot
The login specified must be a privileged account. After the @ is the name of the script that contains the package creation text. In this case, the script name is ot.sql, and because SQL is the default file extension for SQL scripts, it need not be
included on the SQLPLUS command line.
Creating subprograms within a package is the next step in developing a packaged procedure. You must decide which routines will be application-interface routines and which routines will be available only within the package. This determines where the
subprogram specification will residein the package or in the package body. There are two types of subprograms in PL/SQL, procedures and functions.
To define a procedure, you must specify a routine name and the parameters to be passed in and out of the routine. In the order_total example, the following code defines the application-interface routine and resides in the package specification area:
PROCEDURE get_order_total ( in_order_num IN NUMBER, out status_code OUT VARCHAR2, out_msg OUT VARCHAR2, out_merch_total OUT NUMBER, out_shipping IN OUT NUMBER, out_taxes IN OUT NUMBER, out_grand_total OUT NUMBER );
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
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 data type of the
calculated value to be returned. The semicolon marks the end of the function definition.
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.
Parameters out_status_code, out_msg, out_merch_total, and out_grand_total from the procedure definition example are defined as OUTvalues 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.
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.
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 subprogram code is developed along with any subprogram
exception handling. The procedure is finished with the END name statement.
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.
Procedures that are not part of a package are known as stand-alone because they 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 run time, which slows execution.
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.
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.
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.
There are four attributes associated with PL/SQL cursors.
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. 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 have been 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.
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 data type can be any valid data type. 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.
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 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 CURSOR c_line_item RETURN line_item.merch_gross%TYPE; ... END order_total; CREATE OR REPLACE PACKAGE BODY order_total AS CURSOR c_line_item RETURN line_item.merch_gross%TYPE SELECT merch_gross FROM line_item WHERE order_num = g_order_num; ... END order_total;
In this example, the RETURN variable is the same as the line_item.item_merch_gross column. You can use the %ROWTYPE attribute to specify a RETURN record that mirrors a row in a database table.
The most important feature of any language is how to define variables. Once you've defined the variables, PL/SQL enables you to use them in SQL statements as well as language statements. Definition of constants within PL/SQL follow the same rules. Also,
you can define variables and constants as local to one subprogram or global to the entire package you are creating.
Any PL/SQL or SQL data type is valid for variable definitions. The most commonly used data types are VARCHAR2, DATE, NUMBER (SQL data types), BOOLEAN, and BINARY_INTEGER (PL/SQL data types). PL/SQL scalar and composite data types are discussed in more
detail later in this chapter.
Assume you want to declare two local variables named merch_gross and recip_count. The first, merch_gross, is to hold a ten-digit, floating-point number rounded to two decimal places; recip_count will hold an integer counter. Declare these variables as
merch_gross NUMBER; recip_count BINARY_INTEGER;
You can use two methods to assign values to variables. The first is using an assignment operator as follows:
merch_gross := 10.50;
The second method is to use a SQL SELECT or FETCH statement that assigns a database value as follows:
SELECT merch_gross INTO merch_gross FROM line_item WHERE order_num = g_order_num;
Constant declaration is similar to variable declaration except that the CONSTANT keyword must follow the variable name. You must immediately assign a value to the CONSTANT.
tax_rate CONSTANT NUMBER := 0.03;
Global variables are defined in the same manner as local variables, but they are defined outside of all procedure definitions. Suppose you want to define variables g_order_num and g_recip_counter to be available to all package subprograms. The
following is an example of the syntax:
CREATE OR REPLACE PACKAGE BODY order_total AS ... g_order_num NUMBER; g_recip_counter BINARY_INTEGER; ... PROCEDURE ...
Notice that these global variables are defined in the package body specification area so as not to be "seen" by applications that call the order_total packaged procedure.
The DEFAULT keyword enables you to initialize variables without using the assignment operator as in the following example:
merch_gross NUMBER DEFAULT 10.50;
You can also use the DEFAULT keyword to initialize a subprogram's cursor parameters and fields in user-defined records.
The two attributes of PL/SQL variables and constants are %TYPE and %ROWTYPE. The %TYPE attribute enables you to declare variables similar to database columns without knowing the data type of the column. You can define merch_gross from the previous
example as follows:
Defining a variable in this manner enables you to put database changes in effect on the next compilation of a PL/SQL procedure without changing the code.
The %ROWTYPE attribute enables you to represent a row in a table with a record type that masks the database columns. Consider the sample database information in Table 5.1.
You can define a cursor inside your procedure (see "Declaring Cursors" earlier in the chapter) to pull information from the LINE_ITEM table. Along with the cursor, define a ROWTYPE variable to store the fields in this row as follows:
CURSOR c_line_item IS SELECT merch_gross, recipient_num FROM line_item WHERE order_num = g_ordnum; li_info c_line_item%ROWTYPE;
To retrieve the data, issue a FETCH.
FETCH c_line_item INTO li_info;
After the FETCH, use dot notation to access the information pulled from the database.
g_order_merch_total := g_order_merch_total + li_info.merch_gross;
PL/SQL supports a wide range of scalar data types for defining variables and constants. Unlike composite data types, scalar data types have no accessible components. These data types fall into one of the following categories:
Now, take a closer look at the data types in each category.
The BOOLEAN data type, which takes no parameters, is used to store a binary value, TRUE or FALSE. This data type can also store the non-value NULL. You cannot insert or retrieve data from an Oracle database using this data type.
The data type DATE, which takes no parameters, is used to store date values. These DATE values include time when stored in a database column. Dates can range from 1/1/4712 B.C. to 12/31/4712 A.D. Defaults for the DATE data type are as follows:
|Date: first day of current month
Character data types include CHAR, VARCHAR2, LONG, RAW, and LONG RAW. CHAR is for fixed-length character data, and VARCHAR2 stores variable-length character data. LONG stores variable-length character strings; RAW and LONG RAW store binary data or byte
strings. The CHAR, VARCHAR2, and RAW data types take an optional parameter for specifying length.
This length parameter, max_len, must be an integer literal, not a constant or variable. Table 5.2 shows maximum lengths and database column widths of character data types.
Maximum Database Column Width
From this table, you can see the constraint on inserting CHAR, VARCHAR2, and RAW data into database columns of the same type. The limit is the column width. However, you can insert LONG and LONG RAW data of any length into similar columns because the
column width is much greater.
There are two data types in the number data type category: BINARY_INTEGER and NUMBER. BINARY_INTEGER stores signed integers with a range of -231 to 231-1. The most common use for this data type is an index for PL/SQL tables.
Storage for fixed or floating-point numbers of any size is available using the NUMBER data type. For floating-point numbers, you can specify precision and scale in the following format:
A variable declared in this manner has a maximum of ten digits, and rounding occurs to two decimal places. The precision default is the maximum integer supported by your system, and 0 is the default for scale. The range for precision is 1 to 38 whereas
the scale range is -84 to 127.
The two composite data types in PL/SQL are TABLE and RECORD. The TABLE data type enables the user to define a PL/SQL table to be used for array processing. The RECORD data type enables the user to go beyond the %ROWTYPE variable attribute; with it, you
specify user-defined fields and field data types.
The TABLE composite data type provides the developer a mechanism for array processing. Although it's limited to one column of information per PL/SQL table, you can store any number of rows for that column. The word from Oracle is that future versions of
PL/SQL will provide more flexibility in the use of tables.
In the order_total example, define a PL/SQL table named g_recip_list (the information will be used globally). The following is an illustration of this concept:
TYPE RecipientTabTyp IS TABLE OF NUMBER(22) INDEX BY BINARY_INTEGER; ... g_recip_list RecipientTabTyp;
To initialize an array, you must first define an array name or TYPE, which in this example is RecipientTabTyp. This TABLE column is defined as NUMBER with a maximum of 22 digits. You can define the column as any valid PL/SQL data type; however, the
primary key, or INDEX, must be of type BINARY_INTEGER. After defining the array structure, you can make reference for variable definition as shown with g_recip_list defined as an array of TYPE RecipientTabTyp.
Arrays are available as information stores subsequent to initialization of the array. To store information in the array g_recip_list that was defined in the last example, you simply reference the array with a numeric value. This is shown in the
g_recip_list(j) := g_recipient_num(i)
In this example, i and j are counters with values 1. . .n. Once information is stored in an array, you can access it, also with numeric values, as shown in the example. In this case, rows of g_recipient_num are referenced for storage in
The RECORD composite data type provides the developer a mechanism for record processing as described previously. Although you cannot initialize TABLEs at the time of declaration, you can with RECORDs, as illustrated in the following example:
TYPE LineRecTyp IS RECORD (merch_gross NUMBER := 0, recip_num NUMBER := 0 ); ... li_info LineRecTyp;
Defining a RECORD of TYPE LineRecTyp allows declarations such as li_info of that TYPE as shown. You can use this method of RECORD declaration in place of the li_info declaration in the previous %ROWTYPE example. As with %ROWTYPE, references to
RECORD information is accomplished with dot notation.
g_order_merch_total := g_order_merch_total + li_info.merch_gross;
You can use one of three methods to assign values to records. First, you can assign a value to a record field as you would assign any variable.
li_info.merch_gross := 10.50;
A second method is to assign all fields at once by using two records that are declared with the same data type. Assume a second LineRecTyp is defined as new_li_info.
new_li_info := li_info;
This statement assigns all fields of new_li_info the values from the same fields of li_info.
A third method of assigning values to fields of a record is through SQL SELECT or FETCH statements.
OPEN c_line_item; ... FETCH c_line_item INTO li_info;
In this case, all fields of li_info are assigned values from the information retrieved by the FETCH of cursor c_line_item.
Every procedural language has control structures that provide processing of information in a logical manner by controlling the flow of information. Available structures within PL/SQL include IF-THEN-ELSE, LOOP, and EXIT-WHEN. These structures provide
flexibility in manipulating database information.
Use of the LOOP statement provides iterative processing based on logical choices. The basic construct for PL/SQL LOOPs is shown in the following example:
<<loop_name>> LOOP (repetitive processing) END LOOP loop_name;
To break out of a loop such as this, you must issue an EXIT or GOTO statement based on some processing condition. If you raise a user-defined exception, the LOOP also terminates. Now, examine three types of PL/SQL loops that expressly define LOOP
The WHILE loop checks the status of any PL/SQL expression that evaluates to TRUE, FALSE, or NULL at the start of each processing cycle. The following is an example of the use of WHILE loops:
WHILE (expression) LOOP (loop processing) END LOOP;
As stated, the program evaluates the expression at the start of each loop cycle. The program performs the loop processing if the expression evaluates to TRUE. A FALSE or NULL evaluation terminates the loop. Iterations through the
loop are exclusively determined by the evaluation of the expression.
You can control loop iterations with the use of numeric FOR loops. This mechanism enables the developer to establish a range of integers for which the loop will cycle. The following example from the order_total package illustrates numeric FOR loops:
<<recip_list>> FOR i in 1..g_line_counter LOOP (loop processing) END LOOP recip_list;
In this example, loop processing cycles over the range of integers 1 through the value of g_line_counter. The value of the loop index i is checked at the start of the loop and incremented at the end of the loop. When i is one greater than
g_line_counter, the loop terminates.
Cursor FOR loops combine cursor control and conditional control for manipulation of database information. The loop index, cursor OPEN, cursor FETCH, and cursor CLOSE are all implicit when using cursor FOR loops. Consider the following example:
CURSOR c_line_item IS (sql statement) BEGIN FOR li_info IN c_line_item LOOP (retrieved record processing) END LOOP; END;
As shown, the program explicitly declares the c_line_item cursor before its reference in the FOR loop. When the program enters the FOR loop, the code implicitly opens c_line_item and implicitly creates the li_info record as if the following declaration
Once inside the loop, the program can reference the fields of the li_info record that are assigned values by the implicit FETCH inside the FOR loop. Fields of li_info mirror the row retrieved by the c_line_item cursor.
When data is exhausted for the FETCH, c_line_item is implicitly closed.
The IF-THEN-ELSE structure provides alternative processing paths that depend on certain conditions. For example, consider merchandise orders with multiple-line items where a list of recipients is built. Using conditional and iterative control to build
the recipient list, the code is as follows:
PROCEDURE init_recip_list IS recipient_num NUMBER; i BINARY_INTEGER; j BINARY_INTEGER := 1; k BINARY_INTEGER; BEGIN g_out_msg := 'init_recip_list'; <<recip_list>> FOR i in 1..g_line_counter LOOP IF i = 1 THEN g_recip_list(j) := g_recipient_num(i); j := j + 1; g_recip_list(j) := 0; ELSE FOR k in 1..j LOOP IF g_recipient_num(i) = g_recip_list(k) THEN exit; ELSIF k = j THEN g_recip_list(j) := g_recipient_num(i); j := j + 1; g_recip_list(j) := 0; end IF; end LOOP; end IF; end LOOP recip_list; END;
In the order_total example, the subprogram init_recip_list builds a list of unique recipient numbers for calculating additional shipping charges. There is a controlling FOR loop that cycles through each recipient number found on a particular order. The
g_recip_list array is initialized with the first recipient number, and subsequent numbers are checked against all unique numbers in g_recip_list until a unique list of all recipients is compiled.
Also illustrated in this example is the IF-THEN-ELSE extension ELSIF. This statement provides further conditional control with additional constraint checks within the IF-THEN-ELSE structure. Use of ELSIF also requires a THEN statement in executing logic
Another example of iterative control is the use of the EXIT-WHEN statement that allows completion of a LOOP once certain conditions are met. Consider the example of exiting a cursor fetch loop:
open c_line_item; loop fetch c_line_item into li_info; EXIT WHEN (c_line_item%NOTFOUND) or (c_line_item%NOTFOUND is NULL);
In this example, the LOOP is terminated when no more data is found to satisfy the select statement of cursor c_line_item.
PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination. The decision to
enable a procedure to continue after an error condition is one you have to make in development as you consider possible errors that could arise.
You must define the exception handler within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in
which the handler was defined. If there are no more executable statements in the block, control returns to the caller.
PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. You accomplish this by naming an exception as in the following example:
In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:
EXCEPTION when OT_FAILURE then out_status_code := g_out_status_code; out_msg := g_out_msg;
This exception is defined in the order_total example to capture status and associated data for any NO_DATA_FOUND exceptions encountered in a subprogram. The following is an example of a subprogram exception:
EXCEPTION when NO_DATA_FOUND then g_out_status_code := 'FAIL'; RAISE ot_failure;
Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.
Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND from the previous example is a system-defined exception. Table 5.3 is a complete list of internal exceptions.
In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling has not been established. This exception is illustrated in the following example:
when OTHERS then out_status_code := 'FAIL'; out_msg := g_out_msg || ' ' || SUBSTR(SQLERRM, 1, 60);
This technique is used in the order_total sample procedure to trap all procedure errors other than NO_DATA_FOUND. The information passed back to the caller in out_msg is the subprogram name contained in g_out_msg concatenated with the first 60
characters returned from the SQLERRM function by the SUBSTR function.
SQLERRM only returns a valid message when called inside an exception handler unless an argument is passed to the function that is a valid SQL error number. The Oracle error code is the first part of the message returned from SQLERRM. Next is the text
associated with that Oracle error code.
In this manner, all errors encountered during procedure execution are trapped and passed back to the application for debug purposes. The following is a sample return error from the order_total procedure:
FAIL: init_line_items ORA-01001: invalid cursor
This error message (formatted by the application) reveals an illegal cursor operation in the subprogram init_line_items. The portion of the message returned from SQLERRM begins with the ORA-01001 SQL error code. Another error message is illustrated in
the following example:
In this case, the subprogram calc_ship_charges had a NO_DATA_FOUND error. This is determined by the fact that no SQL error messages are concatenated with the message text.
Although some people think commenting code is unnecessary, there are two methods you can use to place comments within your PL/SQL procedures. The first is for commenting single lines, and the syntax is shown in the following example:
--*************** CREATE PACKAGE ORDER_TOTALING ***************
A double dash at the start of the line marks the line as a comment. The second method is used to place a sequence of comment statements in a PL/SQL package.
/* The following code generates a list of unique recipient numbers from all recipient numbers for a particular order */
A comment block such as this begins with the /* and ends with the */. You can place single-line and multiple-line comments in any portion of PL/SQL code.
You can store PL/SQL code in the Oracle database with the RDBMS Procedural Database Extension. Advantages of using stored procedures include easier maintenance, decreased application size, increased execution speed, and greater memory savings, to name a
few. With this in mind, explore the various techniques for accessing stored procedures in the following sections.
Another big advantage to using stored procedures is the capability to reference the procedure from many different Oracle applications. You can make reference to stored procedures with other stored procedures, database triggers, applications built with
Oracle Precompilers, or Oracle tools such as SQL*Forms. The following example calls the order_total procedure from another procedure:
order_total.get_order_total (order_num, status_code, message, merch_gross, shipping, taxes, grand_total);
The following example shows the same order_total procedure referenced from PRO*C, an Oracle Precompiler application.
EXEC SQL BEGIN order_total.get_order_total ( :order_num, :status_code, :message, :merch_gross, :shipping, :taxes, :grand_total); END; END-EXEC;
All parameters in this example to the order_total procedure are Oracle bind variables that you must declare before the reference to the package. The final example illustrates a call to the order_total package from a SQL*Forms application.
BEGIN ... order_total.get_order_total ( order_num, status_code, message, merch_gross, shipping, taxes, grand_total); ... END;
Once again, you must declare all variables passed as parameters before calling the procedure.
After compilation, a stored procedure exists in either a valid or invalid state. If you haven't made any changes to the procedure, it is considered valid and may be referenced. If any subprogram or object referenced within a procedure
changes, its state becomes invalid. Only procedures in a valid state are available for reference.
Referencing a procedure that is invalid causes Oracle to recompile any and all objects called by the referenced procedure. If the recompilation does not succeed, Oracle returns a run-time error to the caller, and the procedure remains in an invalid
state. Otherwise, Oracle recompiles the referenced procedure, and if the recompilation is successful, execution continues.
The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the referenced name, however, to resolve a procedure or function call. The count and data types
of formal parameters are also considered.
PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal functions. To further ensure calling the proper procedure, you can use the dot notation as illustrated by
previous examples on application references to stored procedures. Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference.
The COMMIT statement is available to PL/SQL procedures unless you are calling the procedure from a SQL*Forms application. To enable commits within a procedure called by a SQL*Forms application, you must issue the DDL statement ALTER SESSION ENABLE
COMMIT IN PROCEDURE before you invoke the PL/SQL object. Because you cannot issue this command from SQL*Forms, you must create a user exit from which you can issue the ALTER SESSION statement and subsequently call the procedure. The following is an example
of calling the order_total procedure from SQL*Forms through a user exit:
In this case, the order_totl routine of the SQL*Forms user exit references the order_total packaged procedure.
PL/SQL provides various tools in a package named STANDARD for use by developers. These tools include internal functions and internal exceptions. I previously discussed exception handling and two internal functions, SQLCODE and SQLERRM, that provide
information for exception reporting and are only valid in exception handlers.
Internal PL/SQL functions exemplify the concept of overloading with respect to naming procedures and functions. Remember that PL/SQL resolves a procedure or function call by matching the number and data types of formal parameters in the reference and
not just by reference name. Consider the two internal functions named TO_NUMBER in the following example:
function TO_NUMBER (str CHAR [, fmt VARCHAR2, [, nlsparms] ]) return NUMBER function TO_NUMBER (str VARCHAR2 [, fmt VARCHAR2 [, nlsparms] ]) return NUMBER
Both functions are named TO_NUMBER, but the data type of the first parameter is CHAR in the first definition and VARCHAR2 in the second. Optional parameters are the same in both cases. PL/SQL resolves a call to the TO_NUMBER function by looking at the
data type of the first parameter.
You might also have a user-defined procedure or function named TO_NUMBER. In this case, the local definition takes precedence over the internal function definition. You can still access the internal function, however, by using the dot notation as
As shown, prefacing the TO_NUMBER function call with the name of the PL/SQL package STANDARD references the internal function.
The function TO_NUMBER is one example of a PL/SQL internal function. Table 5.4 shows a complete list of PL/SQL internal function categories along with default return values.
Common Return Value
Although most character functions return a VARCHAR2, some functions return other values. Table 5.5 lists available character functions along with a brief description, argument list, and return value if other than the most likely return value for the set
of functions. Optional arguments are enclosed in square brackets. All internal character functions take the following form:
function ASCII (char VARCHAR2) return VARCHAR2
Returns standard collating code for character.
Returns character for collating code.
Returns str2 appended to str1.
str1 VARCHAR2, str2 VARCHAR2
Returns str1 with the first letter of each word in uppercase and all others in lowercase.
Returns starting position of str2 in str1. Search begins at pos for the nth occurrence. If pos is negative, the search is performed backwards. Both pos and n default to 1. The function returns 0 if str2 is not found.
Similar to INSTR except pos is a byte position.
Returns character count in str and for data type CHAR; length includes trailing blanks.
str CHAR or
Similar to LENGTH; returns byte count of str including trailing blanks for CHAR.
str CHAR or
Returns str with all letters in lowercase.
str CHAR or
Left pads str to length len with characters in pad, which defaults to a single blank. Returns first len characters in str if str is longer than len.
Returns str with characters removed up to first character not in set; set defaults to a single blank.
Similar to INITCAP except a sort sequence is specified by nlsparms.
Similar to LOWER except a sort sequence is specified by nlsparms.
Similar to UPPER except a sort sequence is specified by nlsparms.
Returns str in sort sequence specified by nlsparms.
Returns str1 with all occurrences of str2 replaced by str3. If str3 is not specified, all occurrences of str2 are removed.
Similar to LPAD except str is right padded with len sequence of characters in pad.
Similar to LTRIM except trailing characters are removed from str after the first character not in set.
Returns phonetic representation of str.
Returns substring of str starting at pos for length len or to the end of str if len is omitted. For pos < 0, SUBSTR counts backward from the end of str.
Similar to SUBSTR except works on bytes, not characters.
Replaces all occurrences of set1 with set2 characters in str.
Returns all letters in uppercase.
str CHAR or
Table 5.6 lists available conversion functions along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal conversion functions are of the following form:
function CHARTOROWID (str VARCHAR2) return ROWID
Converts str to type ROWID.
str CHAR or
Converts str from character set1 to character set2. Character set1 and set2 can be a character set name or database column.
Converts str from CHAR or VARCHAR2 to RAW.
str CHAR or
Opposite of HEXTORAW.
Converts bin from ROWID to 18-byte hex string.
Converts dte to VARCHAR2 based on fmt. You can specify a language for date conversion in nlsparms.
Converts num to VARCHAR2 based on fmt. You can specify the following format elements in nlsparms: decimal character, group separator, and a symbol for local or international currency.
Converts MLSLABEL type to VARCHAR2 based on fmt.
Converts str or num to DATE value based on fmt. The fmt argument is not optional when converting a number. You can specify a language for date conversion in nlsparms.
str VARCHAR2 or
Converts str to MLSLABEL data type. If fmt is omitted, str must be in default label format. TO_LABEL is a Trusted Oracle function.
str CHAR or
Converts single-byte str to multi-byte equivalent, if it exists.
Converts str to NUMBER value according to fmt. You can specify format elements in nlsparms as described in the TO_CHAR function.
Opposite of TO_MULTI_BYTE.
All date functions return a DATE value unless otherwise specified in Table 5.7, which lists available date functions along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal date
functions are of the following form:
function ADD_MONTHS (dte DATE, num NUMBER) return DATE
Returns dte plus or minus num months.
Returns last day of the month for dte.
Returns month count between dte1 and dte2. NUMBER is < 0 if dte1 is earlier than dte2.
Returns date and time in zon2 based on dte date and time in time zone zon1.
Returns first day of the week for day that is later than dte.
Returns dte rounded to specified unit in fmt. If no fmt is specified, dte is rounded to the the nearest day.
Returns current system date and time.
Returns dte with the time of day truncated as specified by fmt.
Table 5.8 lists miscellaneous functions along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets.
Returns internal representation of expr based on one of the following fmt specifications:
expr DATE or
[, fmt BINARY_INTEGER
[, pos BINARY_INTEGER
[, len BINARY_INTEGER]]]
17=single character Arguments pos and len specify the portion of the representation to return.
Returns greatest value of list of exprn. All expressions must be data-type compatible with expr1.
expr1, expr2, expr3
Returns greatest lower bound from list of labels. Each label must be of type MLSLABEL. GREATEST_LB is a Trusted Oracle function.
label [, label] . . .
Returns least value from list of exprn. All expressions must be data-type compatible with expr1.
expr1, expr2, expr3 . . .
Returns least upper bound from list of of labels. Each label must be of type MLSLABEL. LEAST_UB is a Trusted Oracle function.
label [, label] . . .
Returns value of not null arg1 or value of arg2. arg1 and arg2 must be of the same data type.
Data type of arg1 and arg2
Returns unique ID number of current Oracle user.
Returns username of current Oracle user.
Returns current session information based on str, which can be one of the following:
audit entry identifier
language, territory, and database character set
auditing session identifier
session terminal type
Returns number of bytes in expr.
expr DATE or
All number functions return a NUMBER value unless otherwise specified in Table 5.9, which lists available number functions along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal
number functions are of the following form:
function ABS (n NUMBER) return NUMBER
Returns absolute value of n.
Returns smallest integer >= n.
Returns cosine of a. Angle a must be in radians.
Returns hyperbolic cosine of n.
Returns value of en.
Returns largest integer <= n.
Returns natural log of n where n > 0.
Returns base-m log of n where m > 1 and n > 0.
Returns remainder of m/n.
Returns value of mn.
Returns m rounded to n places.
Returns -1 for n < 0, 0 for n=0 and 1 for n > 0.
Returns sine of a. Angle a must be in radians.
Returns hyperbolic sine of n.
Returns square root of n.
Returns tangent of a. Angle a must be in radians.
Returns hyperbolic tangent of n.
Returns m truncated to n places.
Use of the DECLARE statement is limited to the creation of sub-blocks within PL/SQL blocks, as shown in the following example:
BEGIN ... <<inner>> DECLARE ... BEGIN ... END inner; ... END;
This code uses DECLARE to declare cursors, variables, and constants local to the sub-block labeled inner.
PL/SQL enables you to reference all defined objects (such as variables, cursors, packages, and so on) using simple references, qualified references, remote references, or a combination of qualified and remote references. Case is irrelevant in all object
references. A simple reference to the order_total package interface takes the following form:
get_order_total( ... );
A qualified reference to the same package looks as follows:
order_total.get_order_total( ... );
A remote reference to this package is shown in the following example:
get_order_total@concepts( ... );
Finally, using a qualified and remote reference, you reference the order_total package as follows:
order_total.get_order_total@concepts( ... );
The first two instances reference the order_total procedure on the local machine. The last two instances show a remote access to the order_total procedure using the concepts database link.
To further simplify the reference to a procedure, you can use a synonym. You cannot use synonyms to reference PL/SQL objects contained in subprograms or packages, however. An example of synonym creation is provided in the section "Package
Creation," which shows a sample script to build the order_total packaged procedure.
Another naming convention worth mentioning is scope of reference. This refers to the range over which you can reference a PL/SQL identifier (such as a variable or subprogram). In simple terms, the hierarchy of scope is block, local, global, and
application, with respect to the range of reference. The following example illustrates this point.
CREATE OR REPLACE PACKAGE order_total AS PROCEDURE get_order_total ( ... ); END order_total CREATE OR REPLACE PACKAGE BODY order_total AS ot_failure EXCEPTION; ... PROCEDURE init_line_items IS i BINARY INTEGER := 0; ... BEGIN ... <<inner>> DECLARE j BINARY_INTEGER := 0; BEGIN j = i; ... EXCEPTION ... raise ot_failure; END inner; ... END; END order_total;
In this example, the scope of reference for variable j is the inner sub-block where it is defined. Variable i, however, is defined local to the init_line_items procedure. You can reference it in the inner sub-block as shown. The defined
exception, ot_failure, is global to the package body and may be referenced by all subprograms but not by the caller. Finally, the get_order_total interface routine and associated variables are available at the application level and throughout the package.
A final note on scope of reference: You can define local identifiers in a sub-block that use the same name as global identifiers. You must then make reference to the global identifier with a qualified name that can be an enclosing block or
subprogram name. You then make the qualified reference using the dot notation.
PL/SQL supports both explicit and implicit data type conversions of specified values. Explicit conversions work through the use of an internal function, such as TO_NUMBER described previously. Implicit conversions happen at compile time where one data
type is supplied and a different data type is expected. This PL/SQL feature enables you to rely on the compiler instead of using explicit conversion routines. Consider the following SQL statement:
SELECT SUM(grand_total)FROM order WHERE order_date < '10-SEP-95';
In this case, the order_date column is stored as data type DATE, and it is being compared to '10-SEP-95', which is a literal CHAR value. PL/SQL does an implicit conversion on this literal to data type DATE when the procedure containing this SQL select
Another common use of PL/SQL procedures is the creation of database triggers. These triggers are packaged procedures that act like SQL*Forms triggers in that they "fire" automatically when a database table meets certain criteria as a result of
a SQL operation. As such, database triggers are not explicitly referenced by other procedures or applications.
There are three distinct pieces to consider when building a database trigger. First is the event that causes the trigger to fire. This firing leads to the action to be taken, which you can think of as the database trigger code. Finally, you must
consider any optional constraints you might want to place on your trigger. Take a closer look at how database triggers are built.
Like packaged procedures, all database triggers follow a standard form in development. The following is an example of creating a database trigger:
CREATE TRIGGER name (trigger firing event) ... (optional trigger constraint) BEGIN (trigger action) END;
As shown by this example, all database triggers start with the CREATE TRIGGER name statement that is the trigger entry point for the named trigger. The trigger firing event code begins with a keyword to specify when the trigger is to fire.
This section of code identifies the SQL operation that passes
control to the trigger action code. Any constraints on the SQL operation are identified in the optional trigger constraint specification area.
The following example illustrates the creation and use of a database trigger:
CREATE TRIGGER check_order_total AFTER UPDATE OF order_total ON order FOR EACH ROW WHEN (new.status = 'NEW') BEGIN IF :new.order_total = 0 THEN INSERT INTO order_log values(:new.order); UPDATE order SET :new.status = 'ERR'; END IF; END;
This example shows that the trigger event specification begins with a keyword, AFTER in this case, that determines when the trigger should fire. The FOR EACH ROW statement has the trigger fire once for each row instead of the default of once per table.
A constraint on firing is that the status of the updated order must be 'NEW'. The trigger action is to INSERT a row into the order_log table and UPDATE the order status to 'ERR'.
I previously discussed the use of exception handling within PL/SQL, but there are three more areas to consider. These areas are reraising exceptions, continuing procedure execution after an exception, and retrying a transaction.
I already discussed using the RAISE statement to raise exceptions within your code, but you can also use RAISE to reraise an exception. Consider the following example:
CREATE OR REPLACE PACKAGE order_total AS ot_failure EXCEPTION; ... BEGIN ... BEGIN ... if g_recip_counter > max_lines then RAISE ot_failure; end if; EXCEPTION when OT_FAILURE then ... RAISE; END; ... EXCEPTION when OT_FAILURE then ... END; END order_total;
In this example, the exception is raised in a sub-block with an ot_failure exception handler defined. After processing this error inside the handler, the exception is reraised for further processing in the main procedure block. This is accomplished with
another ot_failure exception handler.
After an exception is raised in a PL/SQL sub-block, it is possible to continue execution before exiting the driving block. Place executable code in the driving block after the exception handler. The following is an illustration of this technique:
<<outer>> BEGIN ... <<inner>> BEGIN ... if g_recip_counter > max_lines then raise ot_failure; end if; EXCEPTION when OT_FAILURE then ... END inner; UPDATE order SET status = 'SU'; INSERT INTO suspense_queue VALUES (order,g_out_msg); EXCEPTION ... END outer;
This example shows that the exception was handled in the inner sub-block at which time control was passed to the outer driving block. As the inner exception handler ended, execution resumed with the UPDATE statement in the outer
block. In this manner, execution of the procedure can continue after an otherwise fatal error is encountered.
Another method of continuing procedure execution after an exception is raised is known as retrying a transaction. The technique is similar to continuing execution after a raised exception in that the transaction to be retried must exist in a sub-block.
Using iterative loop control, you can repeat a transaction as often as you like after an exception is raised. The following example illustrates the use of this technique:
BEGIN ... FOR i in 1..10 LOOP ... BEGIN SAVEPOINT update_order (SQL transactions) COMMIT; EXIT; EXCEPTION WHEN ... THEN ROLLBACK to update_order (fix data problems) ... END; END LOOP; END;
Under the control of the FOR loop, the SQL transactions can be tried a total of ten times before procedure execution is terminated. The SAVEPOINT update_order is the point of rollback for failed transactions. If an error is encountered during the SQL
transactions phase of this sub-block, control transfers to the exception handler, which tries to resolve the data problems. After execution of the error handler, control transfers to the FOR loop for another pass.
Two additional topics for discussion of the subject of PL/SQL control structures are the EXIT statement and sequential control statements. Both control structures are rarely used, but you might encounter a situation that requires the functionality.
Earlier in the chapter, I mentioned using the EXIT statement as a means to break out of a basic FOR loop. You can also use the EXIT statement in retrying transactions after raised exceptions. In this respect, EXIT provides a mechanism for unconditional
transfer of control from one point of code to another and is used selectively, if at all.
Using sequential control in PL/SQL is not an essential element in successful code development. However, the technique is worth mentioning for a comprehensive view of the language. Two statements are available for sequential control: GOTO and NULL.
GOTO is an unconditional branch statement that transfers control to a label defined within the scope of the branch logic. The label must precede an executable statement or define a PL/SQL block, as shown in the following example:
<<count_lines>> for i in 1..g_line_counter LOOP ... if i>max_lines then GOTO clean_up; end if; ... end LOOP init_lines; <<clean_up>> g_recip_counter = i-1; ...
In this example, the conditional branch transfers control to the clean_up label for further processing. Use of the GOTO statement is discouraged because it could lead to unstructured code. Other constructs within PL/SQL enable you to write code that is
easier to understand and maintain.
Mainly limited to improving code readability, the NULL statement is a way to show that all possible logic choices have been considered. NULL is considered an executable statement.
if g_recip_counter > max_lines then g_recip_counter = max_lines; else NULL; end if;
This example uses NULL to show that there is nothing to do if g_recip_counter is within range of max_lines. Obviously, you can end this code without the ELSE clause, but using the NULL shows that other options were considered.
In this chapter, I discussed PL/SQL procedures, subprograms, and the structures associated with this procedural language extension to the Oracle database. You also saw a sample SQL script for building a packaged procedure. In the course of this chapter,
I began to develop a sample packaged procedure named order_total.
I continued the development of this procedure with a discussion of error handling through the use of exceptions. Many of the examples presented during discussions of various topics came directly from the order_total procedure. As such, this procedure covers the basic elements of the PL/SQL language and is presented now in its entirety. I discuss additional topics of interest to PL/SQL developers at the end of this sample package code.