Calling Procedures and Functions in Packages

Previous

Next

To call the procedures or functions of the emp_actions package created in Example: Creating a Package Body, you can execute the statements in Example: Calling a Subprogram in a Package. 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.

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