11.2 Procedures, Functions, and Packages

Although PL/SQL can still be used to write scripts, also known as anonymous blocks, the focus of this chapter is PL/SQL routines stored in the Oracle server. PL/SQL routines stored in the database may be one of two types: stored procedures or stored functions.^[2]

^[2] Database triggers are another type of stored PL/SQL, but they are outside the scope of this discussion.

Stored functions and procedures are essentially identical except for the following:

• Stored functions have a return type, whereas procedures do not.

• Because stored functions return a value, they can be used in expressions, whereas procedures cannot.

Stored functions and procedures may be compiled individually, or they may be grouped together into packages. Along with being a convenient way to group related functionality together, packages are important for the following reasons:

• Packages are loaded into memory as a whole, increasing the likelihood that a procedure or function will be resident in memory when called.

• Packages can include private elements, allowing logic to be hidden from view.

• Placing functions and procedures inside packages eliminates the need to recompile all functions and procedures that reference a newly-recompiled one.

• Function and procedure names may be overloaded within packages, whereas standalone functions and procedures cannot be overloaded.

• Functions and procedures inside packages can be checked for side effects at compile time rather than at execution time, which improves performance.

If these reasons haven't convinced you to place your stored functions and procedures inside packages, here's a bit of advice we can give after working with PL/SQL since Version 2.0: you will never be sorry that you bundled your PL/SQL code into packages, but you will eventually be sorry if you don't.

Packages consist of two distinct parts: the package specification, which defines the signatures of the package's public procedures and functions, and the package body, which contains the code for the public procedures and functions and may also contain code for any private functions and procedures not included in the package specification. To give you an idea of what a package looks like, here is a simple example of a package specification:

CREATE OR REPLACE PACKAGE my_pkg AS
  PROCEDURE my_proc(arg1 IN VARCHAR2);

  FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2;
END my_pkg;

and its matching package body:

CREATE OR REPLACE PACKAGE BODY my_pkg AS
  FUNCTION my_private_func(arg1 IN NUMBER) RETURN VARCHAR2 IS
    return_val VARCHAR2(20);
  BEGIN
    SELECT col1 INTO return_val
    FROM tab2
    WHERE col2 = arg1;

    RETURN return_val;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
      RETURN `NOT FOUND';
  END my_private_func;

  PROCEDURE my_proc(arg1 IN VARCHAR2) IS
  BEGIN
    UPDATE tab1 SET col1 = col1 + 1
    WHERE col2 = arg1;
  END my_proc;

  FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2 IS
  BEGIN
    RETURN my_private_func(arg1);
  END my_func;
END my_pkg;

As you can see, the my_pkg package includes one public procedure and one public function. The package specification includes the parameter names and types of the procedure and function, along with the return type of the function, but does not include any implementation code. The package body includes the implementation logic for the public function and procedure, and it also includes a private function (my_private_func) that is only accessible from inside the package body.