Packages

In this section, you ll learn how to group procedures and functions together into packages . Packages allow you to encapsulate related functionality into one self-contained unit. By modularizing your PL/SQL code in such a manner, you can potentially build up your own libraries of code that other programmers could reuse.

Packages are typically made up of two components : a specification and a body . The package specification contains information about the package, and it lists the available procedures and functions. These are potentially available to all database users, so I ll refer to these procedures and functions as being public (although only users who have the privileges to access your package can use it). The specification generally doesn t contain the code that makes up those procedures and functions ”the package body contains the actual code.

The procedures and functions listed in the specification are available to the outside world, but any procedures and functions only contained in the body are only available within that body ”they are private to that body. By using a combination of public and private procedures and functions, you can build up very complex packages whose complexity is hidden from the outside world. This is one of the primary goals of all programming: hide complexity from your users.

Creating a Package Specification

You create a package specification using the CREATE PACKAGE statement. The simplified syntax for the CREATE PACKAGE statement is as follows :

 CREATE [OR REPLACE] PACKAGE  package_name  {IS  AS}  package_specification  END  package_name;  

where

• package_name specifies the name of the package.

• package_specification specifies the list of procedures and functions (along with any variables , type definitions, and cursors ) that are available to your package s users.

The following example creates a package specification for a package named product_package:

 CREATE OR REPLACE PACKAGE product_package AS   TYPE t_ref_cursor IS REF CURSOR;   FUNCTION get_products_ref_cursor RETURN t_ref_cursor;   PROCEDURE update_product_price (     p_product_id IN products.product_id%TYPE,     p_factor IN NUMBER   ); END product_package; / 

Note  

The package defines a type named t_ref_cursor. PL/SQL enables you to create your own types, and you ll learn more about that in the next chapter.

The type t_ref_cursor uses the PL/SQL REF CURSOR type. A REF CURSOR is similar to a pointer in the C programming language, and it basically points to rows retrieved from the database using a PL/SQL cursor. In the following section, you ll see the use of a REF CURSOR to point to the result set returned by a SELECT statement that retrieves rows from the products table using a PL/SQL cursor. This is done using the function get_products_ref_cursor(), which returns a variable of type t_ref_cursor.

Creating a Package Body

You create a package body using the CREATE PACKAGE BODY statement. The simplified syntax for the CREATE PACKAGE BODY statement is as follows:

 CREATE [OR REPLACE] PACKAGE BODY  package_name  {IS  AS}  package_body  END  package_name;  

where

• package_name specifies the name of the package, which must match the package name previously set in the package specification.

• package_body specifies the code for the procedures and functions, along with any variables and cursors.

The following example creates the package body for product_package:

 CREATE OR REPLACE PACKAGE BODY product_package AS   FUNCTION get_products_ref_cursor   RETURN t_ref_cursor IS     products_ref_cursor t_ref_cursor;   BEGIN     -- get the REF CURSOR     OPEN products_ref_cursor FOR       SELECT product_id, name, price       FROM products;     -- return the REF CURSOR     RETURN products_ref_cursor;   END get_products_ref_cursor;   PROCEDURE update_product_price (     p_product_id IN products.product_id%TYPE,     p_factor IN NUMBER   ) AS     v_product_count INTEGER;   BEGIN     -- count the number of products with the     -- supplied product_id (should be 1 if the product exists)     SELECT COUNT(*)     INTO v_product_count     FROM products     WHERE product_id = p_product_id;     -- if the product exists (v_product_count = 1) then     -- update that product's price     IF v_product_count = 1 THEN       UPDATE products       SET price = price * p_factor       WHERE product_id = p_product_id;       COMMIT;     END IF;   EXCEPTION     WHEN OTHERS THEN       -- perform a rollback when an exception occurs       ROLLBACK;   END update_product_price; END product_package; / 

The get_products_ref_cursor() function opens a cursor and retrieves the product_id, name, and price columns from the products table The reference to this cursor (the REF CURSOR ) is then returned by the function. This REF CURSOR may then be accessed to read the column values. The update_product_price() procedure updates the price of a product.

Calling Functions and Procedures in a Package

When calling functions and procedures in a package, you include the package name in the call. The following example calls product_package.get_products_ref_cursor(), which returns a cursor containing the product_id, name, and price for the products:

  SELECT product_package.get_products_ref_cursor   FROM dual;  GET_PRODUCTS_REF_CUR -------------------- CURSOR STATEMENT : 1 CURSOR STATEMENT : 1 PRODUCT_ID NAME                                PRICE ---------- ------------------------------ ----------          1 Modern Science                      19.95          2 Chemistry                              30          3 Supernova                           25.99          4 Tank War                            13.95          5 Z Files                             49.99          6 2412: The Return                    14.95          7 Space Force 9                       13.49          8 From Another Planet                 12.99          9 Classical Music                     10.99         10 Pop 3                               15.99         11 Creative Yell                       14.99         12 My Front Line                       13.49 

The next example calls product_package.update_product_price() to multiply product #3 s price by 1.25:

 CALL product_package.update_product_price(3, 1.25); 

The next query retrieves the details for product #3; notice the price has increased:

  SELECT price   FROM products   WHERE product_id = 3;  PRICE ----------      32.49 

Getting Information on Functions and Procedures in a Package

You can get information on your functions and procedures in a package from the user_procedures view; this view was covered earlier in the section Getting Information on Procedures. The following example retrieves the object_name, and procedure_name columns from user_procedures for product_package:

  SELECT object_name, procedure_name   FROM user_procedures   WHERE object_name = 'PRODUCT_PACKAGE';  OBJECT_NAME                    PROCEDURE_NAME ------------------------------ ------------------------------ PRODUCT_PACKAGE                GET_PRODUCTS_REF_CURSOR PRODUCT_PACKAGE                UPDATE_PRODUCT_PRICE 

Dropping a Package

You drop a package using DROP PACKAGE . For example, the following statement drops product_package:

 DROP PACKAGE product_package;