Basics of PL/SQL Package Structures

 

      This article covers some of the basics of PL/SQL package structure.
There is much that is left out including package cursors and exception
structures. Beginners should learn a lot from following the code logic.
Intermediate programmers may pick up a couple of things. Advanced
programmers will probably already know everything presented here.
If you attempt to follow the code logic, don’t expect the code to do
anything meaningful. The point of the code is to illustrate some
structural components.

CREATE OR REPLACE PACKAGE Demo AS

-- This is a package specification (not a package body). It exists to define the
-- public interface to this packages.

my_package_string VARCHAR2(2000)  := 'Read or Write from any scope';
-- my_package_string can be read or written from any where in this package
-- or from other packages or stand-alone procedures or functions.

my_package_integer  CONSTANT PLS_INTEGER  := 1234;
-- my_package_integer can be read but not written from any where in this
--  package or from other packages or stand-alone procedures or functions.

my_package_date DATE;

--===================================================================
-- fun_2 is an 'overloaded' function. When fun_2 is called, the datatypes
-- of the parameter list and the return type (aka, the signature) will be
-- evaluated and the appropriate fun_2 will be called. Each fun_2 can do
-- entirely different things as defined by the corresponding function
-- in the BODY of the package.

-- ADVICE - Use overloading judiciously. Beginning programmers often
-- discover function and procedure overloading and seriously abuse the
-- ability. Make sure that overloaded functions actually perform the
-- same task. It is possible to create a package with a single overloaded
-- function or procedure that does everything based on user defined
-- data types. Don't do it.
FUNCTION fun_2 ( first_name IN VARCHAR2 )
   RETURN VARCHAR2;
-- This function declaration says that there will be a function by the same
-- name and same signature define in the body of this package. Furthermore,
-- the fact that it is defined in this package specification allows it to be
-- called by other packages, procedures and functions.

FUNCTION fun_2 ( first_name IN VARCHAR2,last_name IN VARCHAR2 )
   RETURN VARCHAR2;
  
FUNCTION fun_2 ( first_name IN VARCHAR2 )
   RETURN INTEGER;

FUNCTION fun_2 ( first_name IN CLOB )
   RETURN VARCHAR2;
-- This over load using a clob is particularly nasty.
-- This package and body will sucessfully compile as long
-- as there is no call to fun_2 using a varchar2 input parameter.
-- If you try to code " my_varchar := fun_2('FRED');" or
-- "my_varchar := fun_2(input_varchar);" the compile will fail because
-- both "(first_name IN clob)" and "(first_name IN varchar2)" are
-- suitable signatures and the compiler can not assume which one
-- you intend to use. Worse yet is if you do not attempt to call
-- fun_2 within this procedure and the compile is successful because
-- later when an external process attempts to use this package's fun_2
-- with the varchar2 signature, a run_time error will be thrown!
-- PLS-00307: too many declarations of 'FUN_2' match this call
  
--==================================================================
  
PROCEDURE demo_path_selector (path_choice IN         INTEGER,
                              the_result  OUT NOCOPY VARCHAR2);


END Demo;
/


CREATE OR REPLACE PACKAGE BODY Demo AS

body_level_string_2          VARCHAR2(200) DEFAULT 'Hi there';
-- this is a body level global variable. It can be accessed
-- from any function or procedure defined in this body, but it
-- can not be accessed from other packages or stand-alone procedures
-- and functions.

FUNCTION fun_2 ( first_name IN VARCHAR2 )
   RETURN VARCHAR2
AS
  BEGIN
    RETURN SUBSTR(first_name,2);
  END fun_2;
-- This fun_2 is defined in the package spec. Therefor, other packages
-- that use this function will be executing this process.

FUNCTION fun_2 ( first_name IN VARCHAR2,last_name IN VARCHAR2 )
   RETURN VARCHAR2
   AS
   BEGIN
     RETURN first_name||' '||last_name;
   END fun_2;
  
FUNCTION fun_2 ( first_name IN VARCHAR2 )
   RETURN INTEGER
   AS
   BEGIN
     RETURN 123;
   END fun_2;

FUNCTION fun_2 ( first_name IN CLOB )
   RETURN VARCHAR2
   AS
     temp_str VARCHAR2(2000) := first_name;
   BEGIN
     RETURN temp_str;
   END fun_2; 

FUNCTION fun_3 ( first_name IN VARCHAR2 )
   RETURN VARCHAR2
AS
  temp_string VARCHAR2(200);
  name_str CLOB := first_name ||
                    body_level_string_2; -- defined at the body level
  BEGIN
    temp_string := fun_2(name_str);
    temp_string := Demo.fun_2(name_str);
    RETURN temp_string;
  END fun_3;
-- fun_3 is not defined in the package spec. It is a body level
-- function. This means that only procedures, functions, or
-- the initialization section defined in this package body
-- will have access to fun_3

PROCEDURE demo_path_selector (path_choice IN         INTEGER,
                              the_result  OUT NOCOPY VARCHAR2)
AS
  FUNCTION local_fun_2 (first_name IN VARCHAR)
        RETURN VARCHAR2
    AS
      temp_string VARCHAR2(4) := 'Mr. ';
    BEGIN
        RETURN temp_string||first_name;
    END local_fun_2;
    -- local_fun_2 is a subprogram function. This means that is available only
    -- to code running in the same procedure/function. Subprograms
    -- can themselves contain subprograms nested
    -- to an undetermined number of levels.
 
  BEGIN
    -- ====== main code logic ======
    IF path_choice = 1 THEN
      the_result := local_fun_2
           (Demo.my_package_string); -- defined and initialized in package spec
    ELSE
      the_result := fun_3('Abdul');
    END IF;
  END demo_path_selector;
-- In the procedure above 'demo_path_selector' you have seen how to create
-- subprogram functions and procedures. The unfortunate result of doing this
-- is that it drives the main code logic to the bottom. If you want your
-- code to be on top, and your local functions and procedures on the bottom
-- you can use 'forward declaration'. Basically, you take your local function
-- and divide it into two parts, a function spec, and a function body.
-- Below is the same procedure as
-- above, but this time with a forward declaration and with the
-- main body turned into a subprogram procedure call

-- The down side of this approach is that it makes procedure somewhat
-- convoluted and perhaps that is too high of a price to pay for having
-- the main code logic on top.

-- Note that within the demo_path_selector2 procedure, there is a
-- a subprogram with the same name. We can get away with this because
-- of the way subprogram calls are resolved. The compiler walks up the
-- scope until a match is found on the called procedure name. However,
-- just because it can be done does not mean that it should be done.
-- It would be less confusing if the sub program had a different name.

PROCEDURE demo_path_selector2 (path_choice IN         INTEGER,
                              the_result  OUT NOCOPY VARCHAR2)
AS
 
    FUNCTION local_fun_2 (first_name IN VARCHAR)  -- forward declaration
        RETURN VARCHAR2;
       
    PROCEDURE demo_path_selector2 (path_choice IN         INTEGER,
                                the_result  OUT NOCOPY VARCHAR2)
    AS
      BEGIN
        -- ====== main code logic ======
        IF path_choice = 1 THEN
          the_result := local_fun_2('Smith');
        ELSE
          the_result := fun_3('Abdul');
        END IF;
      END demo_path_selector2;
       
    FUNCTION local_fun_2 (first_name IN VARCHAR) -- body of forward declaration
        RETURN VARCHAR2
    AS
      temp_string VARCHAR2(4) := 'Mr. ';
      BEGIN
        RETURN temp_string||first_name;
      END local_fun_2;
   
  BEGIN
    demo_path_selector2 (path_choice,
                         the_result);                      
  END demo_path_selector2;

BEGIN
-- this is the initialization section. Any executable code placed in this
-- section will be run the first time the package is loaded for a given session.
--
  SELECT SYSDATE + 1 INTO my_package_date FROM DUAL; 
END Demo;
/