1.12 Named Program Units

PL/SQL allows you to create a variety of named program units, or containers for code. These include:

Procedure: A program that executes one or more statements
Function: A program that returns a value
Package: A container for procedures, functions, and data structures
Trigger: A program that executes in response to database changes
Object type: Oracle's version of an object-oriented class; object types can contain member procedures and functions

1.12.1 Procedures

Procedures are program units that execute one or more statements and can receive or return zero or more values through their parameter lists. The syntax of a procedure is:

CREATE [OR REPLACE] PROCEDURE name 
   [ (parameter [,parameter]) ]
   [AUTHID { CURRENT_USER | DEFINER } ]
   [DETERMINISTIC] 
{ IS | AS }
   declaration_section
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

A procedure is called as a standalone executable PL/SQL statement:

apply_discount(new_company_id, 0.15);

1.12.2 Functions

Functions are program units that execute zero or more statements and return a value through the RETURN clause. Functions can also receive or return zero or more values through their parameter lists. The syntax of a function is:

CREATE [OR REPLACE] FUNCTION name 
   [ (parameter [,parameter]) ] 
   RETURN return_datatype
   [AUTHID { CURRENT_USER | DEFINER } ] 
   [DETERMINISTIC] 
   [PARALLEL_ENABLE] 
   [PIPELINED]
   [AGGREGATE USING]
{ IS | AS }
   [declaration_section]
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

A function must have at least one RETURN statement in the execution section. The RETURN clause in the function header specifies the datatype of the returned value.

See Section 1.12.3.9 for information on the keywords OR REPLACE, AUTHID, DETERMINISTIC, PARALLEL_ENABLE, PIPELINED, and AGGREGATE USING. See Section 1.12.3.11 for additional information on AUTHID.

A function can be called anywhere that an expression of the same type can be used. You can call a function:

In an assignment statement:
```
sales95 := tot_sales(1995,'C');
```

To set a default value:

DECLARE
   sales95 NUMBER DEFAULT tot_sales(1995,'C');
BEGIN

In a Boolean expression:
```
IF tot_sales(1995,'C') > 10000
THEN
...
```

In a SQL statement:

SELECT first_name ,surname
   FROM sellers
WHERE tot_sales(1995,'C') > 1000;

As an argument in another program unit's parameter list.

Here, for example, max_discount is a programmer-defined function and SYSDATE is a built-in function:

apply_discount(company_id, max_discount(SYSDATE));

1.12.3 Parameters

Procedures, functions, and cursors may have a parameter list. This list contains one or more parameters that allow you to pass information back and forth between the sub-program and the calling program. Each parameter is defined by its name, datatype, mode, and optional default value. The syntax for a parameter is:

parameter_name [mode] [NOCOPY] datatype
   [ { := | DEFAULT } value]

1.12.3.1 Datatype

The datatype can be any PL/SQL or programmer-defined datatype, but cannot be constrained by a size (NUMBER is valid, NUMBER(10) is not valid). The actual size of the parameter is determined from the calling program or via a %TYPE constraint.

CREATE OR REPLACE PROCEDURE empid_to_name
(in_id           emp.emp_id%TYPE -- Compiles OK.
,out_last_name   VARCHAR2        -- Compiles OK.
,out_first_name  VARCHAR2(10)    -- Won't compile.
) IS
...

The lengths of out_last_name and out_first_name are determined by the calling program:

DECLARE
   surname     VARCHAR2(10);
   first_name  VARCHAR2(10);
BEGIN
   empid_to_name(10, surname, first_name);
END;

1.12.3.2 Mode

The mode of a parameter specifies whether the parameter can be read from or written to, as shown in the following table:

Mode

Description

Parameter usage

IN

Read-only

The value of the actual parameter can be referenced inside the program, but the parameter cannot be changed.

OUT or IN OUT

Read/write

The program can both reference (read) and modify (write) the parameter.

If the mode is not explicitly defined, it defaults to IN.

OUT parameters are not the same as IN OUT parameters. When running the called program, the runtime engine ignores (sets to NULL) any argument value you supply for an OUT parameter; it preserves the value provided for an IN OUT. If an exception is raised during execution of a procedure or function, assignments made to OUT or IN OUT parameters get rolled back unless the parameter includes the NOCOPY option.

The NOCOPY compiler hint for parameters makes the parameter a call by reference instead of a call by value. Normally, PL/SQL passes IN/OUT parameters by value—a copy of the parameter is created for the sub-program. When parameter items get large, as collections and objects do, the copy can eat memory and slow down processing. NOCOPY directs PL/SQL to pass the parameter by reference, using a pointer to the single copy of the parameter.

The disadvantage of NOCOPY is that when an exception is raised during execution of a program that has modified an OUT or IN OUT parameter, the changes to the actual parameters are not "rolled back" because the parameters were passed by reference instead of being copied.

1.12.3.3 Default values

IN parameters can be given default values. If an IN parameter has a default value, then you do not need to supply an argument for that parameter when you call the program unit. It automatically uses the default value. For example:

CREATE OR REPLACE PROCEDURE hire_employee
   (emp_id       IN VARCHAR2
   ,hire_date    IN DATE := SYSDATE
   ,company_id   IN NUMBER := 1
   )
IS
   ...

Here are some example calls to the above procedure:

-- Use two default values.
hire_employee(new_empno);
-- Use one default value.
hire_employee(new_empno,'12-Jan-1999');
-- Use non-trailing default value, named notation.
hire_employee(emp_id=>new_empno, comp_id=>12);

1.12.3.4 Parameter-passing notations

Formal parameters are the names that are declared in the header of a procedure or function. Actual parameters (arguments) are the values or expressions placed in the parameter list when a procedure or function is called. In the empid_to_name example shown earlier in Section 1.12.3.1, the actual parameters to the procedure are in_id, out_last_name, and out_first_name. The formal parameters used in the call to this procedure are 10, surname, and first_name.

PL/SQL lets you use either of two styles for passing arguments in parameter lists: positional notation or named notation.

Positional notation: The default. Each value in the list of arguments supplied in the program call is associated with the parameter in the corresponding position.
Named notation: Explicitly associates the argument value with its parameter by name (not position). When you use named notation, you can supply the arguments in any order and you can omit IN arguments that have default values.

The call to the empid_to_name procedure is shown here with both notations:

BEGIN
   -- Implicit positional notation. 
   empid_to_name(10, surname, first_name);
 
   -- Explicit named notation. 
   empid_to_name(in_id=>10 
      ,out_last_name=>surname 
      ,out_first_name=>first_name);
END;

You may combine positional and named notation, as long as positional arguments appear to the left of any named notation arguments; for example:

empid_to_name(10, surname, out_first_name => first_name);

When calling stored functions from SQL, named notation is not supported.

1.12.3.5 Local programs

A local program is a procedure or function that is defined in the declaration section of a PL/SQL block. The declaration of a local program must appear at the end of the declaration section, after the declarations of any types, records, cursors, variables, and exceptions. A program defined in a declaration section may only be referenced within that block's executable and exception sections. It is not defined outside that block.

The following program defines a local procedure and function:

PROCEDURE track_revenue
IS
   l_total NUMBER;

   PROCEDURE calc_total (year_in IN INTEGER) IS
   BEGIN
      calculations here ...
   END;

   FUNCTION below_minimum (comp_id IN INTEGER)  
      RETURN BOOLEAN
   IS
   BEGIN
      ...
   END;
BEGIN
   ...main procedure logic here
END;

Local programs may be overloaded with the same restrictions as overloaded packaged programs.

1.12.3.6 Program overloading

PL/SQL allows you to define two or more programs with the same name within any declaration section, including a package specification or body. This is called overloading. If two or more programs have the same name, they must be different in some other way so that the compiler can determine which program should be used.

Here is an example of overloaded programs in a built-in package specification:

PACKAGE DBMS_OUTPUT 
IS
   PROCEDURE PUT_LINE (a VARCHAR2);
   PROCEDURE PUT_LINE (a NUMBER);
   PROCEDURE PUT_LINE (a DATE);
END;

Each PUT_LINE procedure is identical, except for the datatype of the parameter. That is enough difference for the compiler.

To overload programs successfully, one or more of the following conditions must be true:

Parameters must differ by datatype family (number, character, datetime, or Boolean).
The program type must be different (you can overload a function and a procedure of the same name and identical parameter list).
The numbers of parameters must be different.

You cannot overload programs if:

Only the datatypes of the functions' RETURN clauses are different.
Parameter datatypes are within the same family (CHAR and VARCHAR2, NUMBER and INTEGER, etc.).
Only the modes of the parameters are different.

1.12.3.7 Forward declarations

Programs must be declared before they can be used. PL/SQL supports mutual recursion , in which program A calls program B, whereupon program B calls program A. To implement this mutual recursion, you must use a forward declaration of the programs. This technique declares a program in advance of the program definition, thus making it available for other programs to use. The forward declaration is the program header up to the IS/AS keyword:

PROCEDURE perform_calc(year_in IN NUMBER)
IS
   /* Forward declaration for total_cost 
      function. */
   FUNCTION total_cost (...) RETURN NUMBER;

   /* The net_profit function can now use 
      total_cost. */
   FUNCTION net_profit(...) RETURN NUMBER
   IS
   BEGIN
      RETURN total_sales(...) - total_cost(...);
   END;

   /* The Total_cost function calls net_profit. */
   FUNCTION total_cost (...) RETURN NUMBER
   IS
   BEGIN
      IF net_profit(...) < 0
      THEN
         RETURN 0;
         ELSE
         RETURN...;
      END IF;
   END;
BEGIN /* procedure perform_calc */
   ...
END perform_calc;

1.12.3.8 Table functions

Table functions take a collection or REF CURSOR (set of rows) as input and return a collection of records (set of rows) as output. The PIPE ROW command is used to identify the input and output streams. This streamlined nature allows you to pipeline table functions together, eliminating the need to stage tables between transformations. Table functions typically appear in the FROM clause of SQL statements. For example:

CREATE FUNCTION pet_family 
(dad_in IN pet_t, mom_in IN pet_t)
RETURN pet_nt PIPELINED IS
   l_count PLS_INTEGER;
   retval pet_nt := pet_nt ( );

BEGIN
   PIPE ROW (dad_in);  -- identify streaming input
   PIPE ROW (mom_in);  -- identify streaming input

   IF mom_in.breed = 'RABBIT' THEN l_count := 12;
   ELSIF mom_in.breed = 'DOG' THEN l_count := 4;
   ELSIF mom_in.breed = 'KANGAROO' THEN l_count := 1;
   END IF;

   FOR indx IN 1 .. l_count
   LOOP
      -- stream the results into the ouput pipeline
      PIPE ROW (pet_t ('BABY' || indx, mom_in.breed
                       ,SYSDATE));
   END LOOP;

   RETURN;
END;

1.12.3.9 Compiling stored PL/SQL programs

The following keywords are available when creating Oracle9i stored programs:

OR REPLACE: Used to rebuild an existing program unit, preserving privileges granted on it.
AUTHID: Defines whether the program will execute with the privileges of, and resolve names like, the object owner (DEFINER), or as the user executing the function (CURRENT_USER). Prior to Oracle8i, only the built-in packages DBMS_SQL and DBMS_UTILITY executed as CURRENT_USER. The default AUTHID is DEFINER.
DETERMINISTIC: Required for function-based indexes. A function is DETERMINISTIC if it always returns the same value when called with the same parameters. Deterministic functions do not meaningfully reference package variables or the database. The built-in INITCAP is deterministic, but SYSDATE is not.
PARALLEL_ENABLED [(PARTITION in_parm BY {ANY HASH | RANGE}) ]: Tells the optimizer that a function is safe for parallel execution. The PARTITION BY clause is only available to functions that have a REF CURSOR IN parameter. This clause is used with table functions and tells the optimizer how the input can be partitioned.
PIPELINED (Oracle9i): Used with table functions. Specifies that the results of this table function should be returned iteratively via the PIPE ROW command. A pipelined function can start to return data as it is generated instead of all at once after processing is complete.
AGGREGATE USING (Oracle9i): Required for aggregate functions. Tells Oracle that the function evaluates a group of rows and returns a single result. For example, the built-in function AVG is an aggregate function.

1.12.3.10 Native compilation of PL/SQL (Oracle9i)

With Oracle9i you can speed up many of your PL/SQL programs by compiling the stored programs natively. Oracle will translate your PL/SQL program into C code and compile it into a shared library (DLL on NT). You must have a supported C compiler on your database server machine to use native compilation. To compile natively, you must follow these steps:

Edit the makefile, spnc_makefile.mk, which you should find in the $ORACLE_HOME/plsql subdirectory.
Set the initialization parameter PLSQL_COMPILER_FLAGS = `NATIVE'. Individual developers may alter the value of PLSQL_COMPILER_FLAGS using the ALTER SESSION statement.
The following parameters many also need to be set: PLSQL_NATIVE_C_COMPILER, PLSQL_NATIVE_LINKER, PLSQL_NATIVE_LIBRARY_DIR, PLSQL_NATIVE_MAKE_UTILITY, and PLSQL_NATIVE_MAKE_FILE_NAME. The DBA can set these parameters in the Oracle initialization file or using an ALTER SYSTEM statement.
Create or replace your stored programs.
Verify the native compilation by querying the data dictionary view USER_STORED_SETTINGS and also by locating the shared library or DLL in the database server's file system.

1.12.3.11 Privileges and stored PL/SQL

Stored SQL supports two models for addressing privileges at runtime. The default is definer rights, which tells Oracle that the privileges of the owner or definer of the program should be used. With the definer rights model, the owner of the program must have the required privileges granted directly to him—he cannot inherit the privileges from a role.

With invoker rights, the user who executes the program does so using his own privileges. Anonymous PL/SQL blocks always execute with invoker rights. To create a program that uses the invoker rights model, include the keywords AUTHID CURRENT_USER in your program's declaration.

[ Team LiB ]