be happy and make others to be happy: PACKAGES

Managing Packages

You can create, modify, and drop packages and package bodies using the Object Browser page, the SQL Commands page, the Script Editor page, or SQL Command Line (SQL*Plus). You can view existing packages and package bodies with the Object Browser page.
The SQL CREATE PACKAGE statement is used to create package specification (specs). The CREATE PACKAGE BODY statement is used to define the package body.

Writing Packages With PL/SQL Code

With PL/SQL, you can break down an application into well-defined modules. Using PL/SQL code, you can write program units that are stored as database objects that can be reused. These objects include packages, subprograms, and triggers. Subprograms and packages are discussed in this section; triggers are discussed in Chapter 6, "Using Triggers".

Guidelines for Writing Packages

When writing packages, keep them general so they can be reused in future applications. Become familiar with the Oracle-supplied packages, and avoid writing packages that duplicate features already provided by Oracle.
Design and define package specifications before the package bodies. Place in a specification only those parts that must be visible to calling programs. That way, other developers cannot build unsafe dependencies on your implementation details.
To reduce the need for recompiling when code is changed, place as few items as possible in a package specification. Changes to a package body do not require recompiling calling procedures. Changes to a package specification require Oracle Database XE to recompile every stored subprogram that references the package.

Creating Packages in the SQL Commands Page

To create and run a package specification or body in the SQL Commands page:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the home page, click the SQL icon to display the SQL page.
Click the SQL Commands icon to display the SQL Commands page.
On the SQL Commands page, enter the PL/SQL code for the package specification or body. Use the code in Example 5-8.

Description of the illustration xe_create_pkg_sql.gif
Click the Run button to create the package specification or body. If necessary, select (highlight) only the specific code for creating the package specification or body before clicking the Run button. Any comments outside the package or package body block are not legal in the SQL Commands page.
If you want to save the PL/SQL code for future use, click the Save button.
In the Name field, enter a name for the saved PL/SQL code (emp_actions_pkg_spec). You can also enter an optional description. Click the Save button to save the code.
To access saved PL/SQL code, click the Saved SQL tab and select the name of the saved PL/SQL code that you want to access.
To create, run, and save the PL/SQL code for a package body, repeat the steps in this example with the code in Example 5-9.

In the previous steps you created a package. For information about how to execute or call a subprogram in the package, see "Calling Procedures and Functions in Packages".

Creating Packages With the Object Browser Page

You can use the Object Browser page to create packages. This section explains how to create a package specification.
To create a package specification:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the Database Home Page, click the Object Browser icon.
In the Detail pane, select Package from the Create menu.
In the Create Package page, select the Specification option and click Next.
Enter the package name (emp_actions_new), and then click the Next button.
Enter the PL/SQL source code for the package specification. Use the code in Example 5-8.

Description of the illustration xe_create_pkg_objbrows.gif
After entering the code for the package specification, click the Finish button.
Click the Body tab, then the Edit button to enter the source code for the package body. Use the code in Example 5-9, substituting emp_actions_new for emp_actions.
Click the Compile button to run the package. If errors are raised, correct the source code and try compiling again. Compiling the package also saves any changes made to the package.
When you have finished, click the Finish button.

In the previous steps, you created a package. For information about how to execute or call a subprogram in the package, see "Calling Procedures and Functions in Packages".

Viewing Packages With the Object Browser Page

To find out which packages and package bodies exist in your database, use the Object Browser.
To use the Object Browser page to view packages and package bodies:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the Database Home Page, click the Object Browser icon.
In the object list, select Packages then click the name of the package you want to display.
The package specification information displays.
With the package specification displayed, click the Body tab to view the package body if it exists.

Creating Packages With the SQL CREATE PACKAGE Statement

To create packages, use the SQL CREATE PACKAGE and CREATE PACKAGE BODY statements. You can use these SQL statements in the SQL Commands page, the Script Editor page, the Object Browser page, or SQL Command Line (SQL*Plus). In Example 5-8 and Example 5-9, the OR REPLACE option is used so that you can update an existing package without having to first drop the package.
In Example 5-8, the emp_actions package specification contains two procedures that update the employees table and one function that provides information. The package specification provides the declaration of the subprograms. The package body provides the contents of the subprograms.

Example 5-8 Creating a Package Specification

CREATE OR REPLACE PACKAGE emp_actions AS  -- package specification
  
  PROCEDURE hire_employee (lastname VARCHAR2, 
    firstname VARCHAR2, email VARCHAR2, phoneno VARCHAR2,
    hiredate DATE, jobid VARCHAR2, sal NUMBER, commpct NUMBER,
    mgrid NUMBER, deptid NUMBER);
  PROCEDURE remove_employee (empid NUMBER);
  FUNCTION emp_sal_ranking (empid NUMBER) RETURN NUMBER;
END emp_actions;
/

In Example 5-9, the emp_actions package body is created. The package body provides the contents of the subprograms in the package specification.

Example 5-9 Creating a Package Body

CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- package body

-- code for procedure hire_employee, which adds a new employee
  PROCEDURE hire_employee (lastname VARCHAR2,
    firstname VARCHAR2, email VARCHAR2, phoneno VARCHAR2, hiredate DATE,
    jobid VARCHAR2, sal NUMBER, commpct NUMBER, mgrid NUMBER, deptid NUMBER) IS
    min_sal    employees.salary%TYPE; -- variable to hold minimum salary for jobid
    max_sal    employees.salary%TYPE; -- variable to hold maximum salary for jobid
    seq_value  NUMBER;  -- variable to hold next sequence value
  BEGIN
    -- get the next sequence number in the employees_seq sequence
    SELECT employees_seq.NEXTVAL INTO seq_value FROM DUAL;
    -- use the next sequence number for the new employee_id
    INSERT INTO employees VALUES (seq_value, lastname, firstname, email,
     phoneno, hiredate, jobid, sal, commpct, mgrid, deptid);
     SELECT min_salary INTO min_sal FROM jobs WHERE job_id = jobid;
     SELECT max_salary INTO max_sal FROM jobs WHERE job_id = jobid;
     IF sal > max_sal THEN
       DBMS_OUTPUT.PUT_LINE('Warning: ' || TO_CHAR(sal) 
                 || ' is greater than the maximum salary '
                 || TO_CHAR(max_sal) || ' for the job classification ' || jobid );
     ELSIF sal < min_sal THEN
       DBMS_OUTPUT.PUT_LINE('Warning: ' || TO_CHAR(sal) 
                 || ' is less than the minimum salary '
                 || TO_CHAR(min_sal) || ' for the job classification ' || jobid );
     END IF;
  END hire_employee;

-- code for procedure remove_employee, which removes an existing employee
  PROCEDURE remove_employee (empid NUMBER) IS
     firstname employees.first_name%TYPE;
     lastname  employees.last_name%TYPE;
  BEGIN
    SELECT first_name, last_name INTO firstname, lastname FROM employees 
      WHERE employee_id = empid;
    DELETE FROM employees WHERE employee_id = empid;
    DBMS_OUTPUT.PUT_LINE('Employee: ' || TO_CHAR(empid) || ', ' 
                      || firstname || ', ' || lastname || ' has been deleted.');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || TO_CHAR(empid) || ' not found.');
  END remove_employee;

-- code for function emp_sal_ranking, which calculates the salary ranking of the
-- employee based on the minimum and maximum salaries for the job category
  FUNCTION emp_sal_ranking (empid NUMBER) RETURN NUMBER IS
    minsal        employees.salary%TYPE; -- declare a variable same as salary
    maxsal        employees.salary%TYPE; -- declare a variable same as salary
    jobid         employees.job_id%TYPE; -- declare a variable same as job_id
    sal           employees.salary%TYPE; -- declare a variable same as salary
  BEGIN
-- retrieve the jobid and salary for the specific employee ID
    SELECT job_id, salary INTO jobid, sal FROM employees 
       WHERE employee_id = empid;
-- retrieve the minimum and maximum salaries for the job ID
    SELECT min_salary, max_salary INTO minsal, maxsal FROM jobs
       WHERE job_id = jobid;
-- return the ranking as a decimal, based on the following calculation
    RETURN ((sal - minsal)/(maxsal - minsal));
  END emp_sal_ranking;
END emp_actions;
/

-- the following BEGIN..END block calls, or executes, the emp_sal_ranking
-- function in the emp_actions package with an argument value
DECLARE
  empid NUMBER := 163; -- use a test value for the employee_id
BEGIN
  DBMS_OUTPUT.put_line('The salary ranking for employee ' || empid || ' is: ' 
                       || ROUND(emp_actions.emp_sal_ranking(empid),2) );
END;
/

The output of the PL/SQL block is similar to:
The salary ranking for employee 163 is: .58
Note that the function result for employee 163 is different from the result for Example 5-5. While the functions have the same function name (emp_sal_ranking), they are not the same function. The function in the package is identified by the package name prefix, as in emp_actions.emp_sal_ranking.
For methods on calling subprograms in a package, see "Calling a Subprogram in a Package".

Editing Packages

To edit packages and package bodies, you can use the Object Browser page, the SQL Commands page, or the SQL CREATE OR REPLACE statement with SQL Command Line.
If you use the SQL CREATE OR REPLACE statement with SQL Command Line, you simply type in the modified package specification or body code. See "Entering and Executing SQL Statements and Commands".
To edit a package in the SQL Commands page:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the home page, click the SQL icon to display the SQL page.
Click the SQL Commands icon to display the SQL Commands page.
Click the Saved SQL tab to display the saved SQL modules.
Click the name of the saved SQL that contains the package code that you want to edit.
Modify the source code for the package. Click the Run button if you want to execute the package.
When you are finished, you can click the Save button to save the code for future use.

To edit a package with the Object Browser page:

Log in to the Database Home Page. See "Logging in to the Database Home Page". To run the examples in this guide, log in as user HR with your password for the HR account.
On the Database Home Page, click the Object Browser icon.
In the object list, select Packages and then click the package you want to display.
The package specification information displays.
With the package specification displayed, click the Edit button to modify the package specification. You can click the Body tab to edit the source code for the package body if it exists.
Click the Compile button to ensure your changes did raise any errors when executed. Compiling the package also saves the changes.

Dropping Packages

You can use the SQL DROP statement or the Object Browser page to drop packages and package bodies.
You can drop a package or package body with the SQL DROP statement. When drop a package specification, the corresponding package body is dropped also. You can choose to drop only the package body. For example:

-- drop only the package body
DROP PACKAGE BODY my_package;
-- drop the package specification and package body
DROP PACKAGE my_package;
To drop a package or package body with the Object Browser page:

Log in to the Database Home Page. See "Logging in to the Database Home Page". To run the examples in this guide, log in as user HR with your password for the HR account.
On the Database Home Page, click the Object Browser icon.
Select Packages in the object list, then click the package you want to display.
The package specification information displays.
With the package specification displayed, click the Drop button to drop the package specification and package body. You can click the Body tab and then the Drop button to drop only the packaged body if it exists.
Click the Finish button to confirm that you want to drop the package specification or package body.

Calling Procedures and Functions in Packages

To call the procedures or functions of the emp_actions package created in Example 5-9, you can execute the statements in Example 5-10. The subprograms can be executed in a BEGIN .. END block or from another subprogram. Note the use of the package name as a prefix to the subprogram name.

Example 5-10 Calling a Subprogram in a Package

-- the following calls the hire_employee subprogram in the emp_actions package
-- with the associated parameter values
BEGIN
  emp_actions.hire_employee('Townsend', 'Mark', 'MTOWNSEND',
   '555.123.2222', '31-JUL-05', 'AC_MGR', 9000, .1, 101, 110);
END;
/

-- the following calls the remove_employee subprogram in the emp_actions package
-- in this case, remove the employee just added (employee_id = 208)
-- note that the employee ID might be different on your system
BEGIN
  emp_actions.remove_employee(208);
END;
/

-- cleanup: drop the package
DROP PACKAGE emp_actions;

Packages are stored in the database, where they can be shared by many applications. Calling a packaged subprogram for the first time loads the whole package and caches it in memory, saving on disk I/O for subsequent calls. Thus, packages enhance reuse and improve performance in a multiple-user, multiple-application environment.
If a subprogram does not take any parameters, you can include an empty set of parentheses or omit the parentheses, both in PL/SQL and in functions called from SQL queries. For calls to a method that takes no parameters, an empty set of parentheses is optional within PL/SQL scopes, but they are required within SQL scopes.

Accessing Variables in Packages

You can create a package specification that is designated only to supply common variables to other packages or subprograms. With the variables in one package, they can be easily maintained for all subprograms that use the variables, rather than maintaining the variables in all the individual subprograms. Common variables are typically used in multiple subprograms, such as a sales tax rate.
In Example 5-11, the variables my_var_pi, my_var_e, and my_var_sales_tax can be used by any subprogram. If you change the value of any of those variables, then all subprograms that use the variable will get the new value without having to change anything in those individual subprograms.
Note that you need to use of the package name as a prefix to the variable name, such as my_var_pkg.my_var_pi.

Example 5-11 Creating Variables in a PL/SQL Package Specification

CREATE OR REPLACE PACKAGE my_var_pkg AS
-- set up a variable for pi, used in calculations with circles and spheres
  my_var_pi         NUMBER := 3.14016408289008292431940027343666863227;
-- set up a variable for e, the base of the natural logarithm
  my_var_e          NUMBER := 2.71828182845904523536028747135266249775;
-- set up a variable for the current retail sales tax rate
  my_var_sales_tax  NUMBER := 0.0825;
END my_var_pkg;
/

Example 5-12 shows how variables that are defined in the my_var_pkg package specification can be used in PL/SQL subprograms.

Example 5-12 Using Variables From a Package Specification

CREATE OR REPLACE PROCEDURE circle_area(radius NUMBER) IS
  c_area NUMBER;
BEGIN
-- the following uses the value of the my_var_pi variable in my_var_pkg for pi 
-- in the following calculation of the area of a circle
  c_area := my_var_pkg.my_var_pi * radius**2;
  DBMS_OUTPUT.PUT_LINE('Radius: ' || TO_CHAR(radius) 
                       || ' Area: ' || TO_CHAR(c_area) );
END circle_area;
/

BEGIN -- some examples of the use of package variables
-- call the circle_area procedure with radius equal to 3, my_var_pi is used to
-- calculate the area in circle_area
  circle_area(3);
-- determine the sales tax on a $25 item using my_var_sales_tax for the tax rate
  DBMS_OUTPUT.PUT_LINE('Sales tax on $25.99 is $' 
                        || TO_CHAR(25.99 * my_var_pkg.my_var_sales_tax) );
END;
/

Accessing Types in Packages

You can create a package specification that is designated only to supply common types, along with common variables, to other packages or subprograms. With the types in one package, they can be easily maintained for all subprograms that use the types, rather than maintaining the types in all the individual subprograms. Common types, such as a REF CURSOR, can be used to declare variables in other packages and subprograms. See "Cursor Variables (REF CURSORs)".
In Example 5-13, the emp_refcur_typ and my_refcur_typ types can be used by any subprogram to declare cursor variables. Note that you need to use of the package name as a prefix to the type name, such as my_var_pkg.my_refcur_typ.

Example 5-13 Creating Types and Variables in a PL/SQL Package Specification

CREATE OR REPLACE PACKAGE my_var_pkg AS
-- set up a strongly typed cursor variable for the employees table
  TYPE emp_refcur_typ IS REF CURSOR RETURN employees%ROWTYPE;
-- set up a weakly typed cursor variable for multiple use
  TYPE my_refcur_typ IS REF CURSOR;
-- set up a variable for pi, used in calculations with circles and spheres
  my_var_pi         NUMBER := 3.14016408289008292431940027343666863227;
-- set up a variable for e, the base of the natural logarithm
  my_var_e          NUMBER := 2.71828182845904523536028747135266249775;
-- set up a variable for the current retail sales tax rate
  my_var_sales_tax  NUMBER := 0.0825;
END my_var_pkg;
/

Example 5-14 show how the emp_refcur_typ cursor variable that is defined in the my_var_pkg package specification can be used in PL/SQL subprograms.

Example 5-14 Using the emp_refcur_typ REF CURSOR From a Package Specification

-- this procedure uses the strongly-typed my_var_pkg.emp_refcur_typ REF CURSOR
CREATE OR REPLACE PROCEDURE display_emp_cursor (
                             emp_cursor IN OUT my_var_pkg.emp_refcur_typ) AS
  person employees%ROWTYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('-- Here are the employees in the result set --');
  LOOP
    FETCH emp_cursor INTO person;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(person.employee_id || ' - ' || person.last_name 
                                            || ', ' || person.first_name);
  END LOOP;
END display_emp_cursor;
/

-- this procedure uses the strongly-typed my_var_pkg.emp_refcur_typ REF CURSOR
CREATE OR REPLACE PROCEDURE get_emp_id (firstname IN VARCHAR2, 
                                        lastname IN VARCHAR2) AS
  emp_cursor my_var_pkg.emp_refcur_typ;
BEGIN
-- search for employee IDs based on the input for first and last names
  OPEN emp_cursor FOR SELECT * FROM employees 
    WHERE SUBSTR(UPPER(first_name), 1, LENGTH(firstname)) = UPPER(firstname) 
    AND SUBSTR(UPPER(last_name), 1, LENGTH(lastname)) = UPPER(lastname);
-- pass emp_cursor to the display_emp_cursor procedure for processing
  display_emp_cursor(emp_cursor); 
  CLOSE emp_cursor;
END get_emp_id;
/

BEGIN -- some examples of the use of package types
-- call the get_emp_id procedure that uses a REF CURSOR defined in a package
  get_emp_id('steve', 'kin');
END;
/

Example 5-15 show how the my_refcur_typ cursor variable that is defined in the my_var_pkg package specification can be used to return a result set that could be accessed by other subprograms.

Example 5-15 Using the my_refcur_typ REF CURSOR From a Package Specification

-- this procedure uses the weakly-typed my_var_pkg.my_refcur_typ REF CURSOR
CREATE OR REPLACE PROCEDURE get_emp_info (firstname IN VARCHAR2,
  lastname IN VARCHAR2, emp_cursor IN OUT my_var_pkg.my_refcur_typ) AS
BEGIN
-- the following returns employee info based on first and last names
  OPEN emp_cursor FOR SELECT employee_id, first_name, last_name, email,
    phone_number FROM employees 
    WHERE SUBSTR(UPPER(first_name), 1, LENGTH(firstname)) = UPPER(firstname)
    AND SUBSTR(UPPER(last_name), 1, LENGTH(lastname)) = UPPER(lastname);
END get_emp_info;
/

-- the procedure can be updated to change the columns returned in the result set
CREATE OR REPLACE PROCEDURE get_emp_info (firstname IN VARCHAR2,
  lastname IN VARCHAR2, emp_cursor IN OUT my_var_pkg.my_refcur_typ) AS
BEGIN
-- because this procedure uses a weakly typed REF CURSOR, the cursor is flexible
-- and the SELECT statement can be changed, as in the following
  OPEN emp_cursor FOR SELECT e.employee_id, e.first_name, e.last_name, e.email,
    e.phone_number, e.hire_date, j.job_title FROM employees e
    JOIN jobs j ON e.job_id = j.job_id
    WHERE SUBSTR(UPPER(first_name), 1, LENGTH(firstname)) = UPPER(firstname)
    AND SUBSTR(UPPER(last_name), 1, LENGTH(lastname)) = UPPER(lastname);
END get_emp_info;
/

be happy and make others to be happy

Infolinks

Tuesday, 3 July 2012

PACKAGES