012-015

Previous

Table of Contents

Next

Packages

The ability to group related procedures and functions into a single object is useful when building applications because the grouping process leads to a very modular application. A package consists of procedures, functions, global variables , constants, and type definitions.

Packages have two component pieces, a package specification (or package spec ) and a package body . The package spec is generally used for the definition of constants, global variables, and user -defined datatypes for the package (although these declarations can also exist in the package body).

In addition to these declarations, the package spec defines the interfaces for procedures and functions that can be executed by applications. All declarations in the package spec are public and can be referenced or called by other stored PL/SQL objects and applications.

Listing 1.12 presents code for a typical package specification. This package spec defines two functions. The spec also asserts a purity level for each function, which allows the packaged functions to be called inside an SQL statement.

Listing 1.12 A typical package spec.

 PACKAGE Conversions IS    FUNCTION Inches_To_Centimeters (nInches IN     number) RETURN number;    PRAGMA restrict_references (Inches_To_Centimeters, NDWS, NPWS);       FUNCTION Centimeters_To_Inches (nCentimeters IN     number) RETURN number;       PRAGMA restrict_references (Centimeters_To_Inches, NDWS, NPWS); END Conversions; 

Any objects defined in a package body without having been first declared in the package spec are private objects, which can only be referenced by objects defined within the package. Global variables, user-defined datatypes, constants, procedures, and functions can all be declared inside a package body without being declared in a package spec. In a system where security is paramount, private objects are quite common. Listing 1.13 shows the code for a typical package body.

Listing 1.13 A typical package body.

 PACKAGE BODY Conversions IS FUNCTION Inches_To_Centimeters (nInches IN     number) RETURN number IS BEGIN    RETURN (nInches * 2.54); END Inches_To_Centimeters; -- FUNCTION Centimeters_To_Inches (nCentimeters IN     number) RETURN number IS BEGIN    RETURN (nCentimeters / 2.54); END Centimeters_To_Inches; -- FUNCTION Inches_To_Meters (nInches IN     number) RETURN number IS BEGIN    RETURN (100 * Inches_To_Centimeters (nInches)); END Inches_To_Meters; -- END Conversions; 

In this example, the function Inches_To_Meters ( ) is private and can only be called by other procedures and functions defined within the package body. The functions Inches_To_Centimeters ( ) and Centimeters_To_Inches( ) are public objects and can be called by any user or application with the appropriate privileges.

Database Triggers

Database triggers are the ideal tool for enforcing business rules that relate directly to data. Each database trigger is associated with a single table in the database and is configured to fire at a certain point when data is modified. Typical uses of database triggers include the following:

   Checking columns for conformity to complex rules that cannot be enforced with check constraints.

   Updating relevant data in other tables.

   Marking newly created or recently modified rows for another process to analyze.

   Ensuring that columns are populated .

   Signaling that a particular event has occurred.

The real strength of database triggers is that a trigger always fires when a defined action is performed on the trigger s associated table. Even statements executed via the command line in SQL*Plus will fire a trigger, making triggers the perfect method for enforcing business rules. Triggers can be written to fire at both statement level and row level.

Statement-level triggers fire once for each statement (or transaction) that affects the associated table. For instance,

 UPDATE STUDENTS SET    overall_gpa = 4.0; 

would fire a statement-level trigger once, no matter how many rows of data were affected by the statement.

Row-level triggers fire once for each row affected by a statement that affects the trigger s associated statement. For instance,

 UPDATE STUDENTS SET    overall_gpa = 4.0; 

would fire a row-level trigger once for each row in the STUDENTS table.

Triggers are cued by specific events. A trigger that fires when its associated table is updated does not fire when a new row is inserted into the associated table, unless the same trigger is coded to fire for both updates and inserts on the associated table. Triggers can be written to fire when DELETE , INSERT , and UPDATE statements affect the trigger s associated table.

Previous

Table of Contents

Next