Page 280
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 datatypes 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 datatype 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 datatype 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 follows :
STANDARD.TO_NUMBER ...
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. You can find a complete list of internal functions in Chapter 11, "SQL."
I want to discuss a few more areas of PL/SQL for the sake of completeness. These topics are relevant to your general understanding of the PL/SQL language and should be reviewed.
Use of the DECLARE statement is limited to the creation of subblocks 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 subblock labeled inner.
Page 281
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 like this:
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," earlier in this chapter, 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
Page 282
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 subblock where it is defined. Variable i, however, is defined local to the init_line_items procedure. You can reference it in the inner subblock as shown. The defined exception, ot_failure, is global to the package body and can 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 in a subblock local identifiers that use the same names 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 dot notation.
PL/SQL supports both explicit and implicit datatype 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 datatype is supplied and a different datatype 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 datatype DATE, and it is compared to '10-SEP-95', which is a literal CHAR value. PL/SQL does an implicit conversion on this literal to datatype DATE when the procedure containing this SQL SELECT is compiled.
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.