0280-0282

Previous Table of Contents Next

Page 280

Referencing Internal Functions

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.

Internal Functions

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."

Additional Topics

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.

The DECLARE Statement

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

Naming Conventions

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.

Synonyms

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.

Scope of Reference

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.

Datatype Conversion

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.

Database Triggers

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.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net