8 PL/SQL Subprograms

Overview of PL/SQL Subprograms

A PL/SQL subprogram is a named PL/SQL block that can be invoked with a set of parameters (if it has them). A subprogram can be either a procedure or a function. Typically, you use a procedure to perform an action and a function to compute and return a value.

You can create a subprogram either at schema level, inside a package, or inside a PL/SQL block (which can be another subprogram).

A subprogram created at schema level is a standalone stored subprogram. You create it with the CREATE PROCEDURE or CREATE FUNCTION statement. It is stored in the database until you drop it with the DROP PROCEDURE or DROP FUNCTION statement.

A subprogram created inside a package is a packaged subprogram. It is stored in the database until you drop the package with the DROP PACKAGE statement.

A subprogram created inside a PL/SQL block is a nested subprogram. You can either declare and define it at the same time, or you can declare it first (forward declaration) and then define it later in the same block. A nested subprogram is stored in the database only if it is nested in a standalone or packaged subprogram.

Subprogram Calls

A subprogram call has this form:

subprogram_name [ ( [ parameter [, parameter]... ] ) ]

If the subprogram has no parameters, or specifies an initial value for every parameter, you can either omit the parameter list or specify an empty parameter list.

A procedure call is a PL/SQL statement. For example:

raise_salary(employee_id, amount);

A function call is part of an expression. For example:

IF salary_ok(new_salary, new_title) THEN ...

Reasons to Use Subprograms

• Subprograms let you extend the PL/SQL language.

  Procedure calls are like new statements. Function calls are like new expressions and operators.

• Subprograms let you break a program into manageable, well-defined modules.

  You can use top-down design and the stepwise refinement approach to problem solving.

• Subprograms promote re-usability.

  Once tested, a subprogram can be reused in any number of applications. You can invoke PL/SQL subprograms from many different environments, so that you need not rewrite them each time you use another language or API to access the database.

• Subprograms promote maintainability.

  You can change the internal details of a subprogram without changing the other subprograms that invoke it. Subprograms are an important component of other maintainability features, such as packages and ADTs.

• Dummy subprograms ("stubs") let you defer the definition of procedures and functions until after you have tested the main program.

  You can design applications from the top down, thinking abstractly, without worrying about implementation details.

• Subprograms can be grouped into PL/SQL packages.

  Packages make code even more reusable and maintainable, and can be used to define an API.

• You can hide the implementation details of subprograms by placing them in PL/SQL packages.

  You can define subprograms in a package body without declaring their specifications in the package specification. However, such subprograms can be invoked only from inside the package. At least one statement must appear in the executable part of a subprogram. The NULL statement meets this requirement.

Subprogram Parts

A subprogram always has a name, and can have a parameter list.

Like every PL/SQL block, a subprogram has an optional declarative part, a required executable part, and an optional exception-handling part, and can specify PRAGMA AUTONOMOUS_TRANSACTION, which makes it autonomous (independent).

The declarative part of a subprogram does not begin with the keyword DECLARE, as the declarative part of an anonymous block does. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.

The executable part of a subprogram contains statements that assign values, control execution, and manipulate data.

The exception-handling part of a subprogram contains code that handles run-time errors.

Example 8-1 declares and defines a procedure (at the same time) inside an anonymous block. The procedure has the required executable part and the optional exception-handling part, but not the optional declarative part. The executable part of the block invokes the procedure.

-- Declarative part of block begins
DECLARE
  in_string  VARCHAR2(100) := 'This is my test string.';
  out_string VARCHAR2(200);

  -- Procedure declaration and definition begins
  PROCEDURE double (original IN VARCHAR2,
                    new_string OUT VARCHAR2)
  IS
    -- Declarative part of procedure (optional) goes here
    -- Executable part of procedure begins
    BEGIN
      new_string := original || ' + ' || original;
    -- Executable part of procedure ends
    -- Exception-handling part of procedure begins
    EXCEPTION
      WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Output buffer not long enough.');
    END;
    -- Exception-handling part of procedure ends
    -- Procedure declaration and definition ends
-- Declarative part of block ends
-- Executable part of block begins
BEGIN
  double(in_string, out_string);  -- Procedure invocation
  DBMS_OUTPUT.PUT_LINE(in_string || ' - ' || out_string);
END;
-- Executable part of block ends
/

A procedure and a function have the same structure, except that:

• A function heading must include a RETURN clause that specifies the data type of the return value. A procedure heading cannot have a RETURN clause.

• A function must have at least one RETURN statement in its executable part. In a procedure, the RETURN statement is optional. For details, see "RETURN Statement".

• Only a function declaration or definition can include these options:

RETURN Statement

The RETURN statement (not to be confused with the RETURN clause, which specifies the data type of the return value of a function) immediately ends the execution of the subprogram that contains it and returns control to the caller. Execution continues with the statement following the subprogram call.

A subprogram can contain several RETURN statements. The subprogram need not end with a RETURN statement. Running any RETURN statement completes the subprogram immediately.

In a procedure or anonymous block, a RETURN statement cannot contain an expression and does not return a value.

In a function, a RETURN statement must contain an expression. When the RETURN statement runs, the expression is evaluated, and its value is assigned to the function identifier. The function identifier acts like a variable of the type specified in the RETURN clause.

The expression in a function RETURN statement can be arbitrarily complex. For example:

CREATE OR REPLACE FUNCTION half_of_square(original NUMBER)
  RETURN NUMBER IS
BEGIN
  RETURN (original * original)/2 + (original * 4);
END half_of_square;
/

A function must have at least one execution path that leads to a RETURN statement.

Example 8-2 declares and defines a function (at the same time) inside an anonymous block. The function has the optional declarative part and the required executable part, but not the optional exception-handling part. The executable part of the block invokes the function.

-- Declarative part of block begins
DECLARE
  -- Function declaration and definition begins
  FUNCTION square (original NUMBER)
    RETURN NUMBER  -- RETURN clause
  AS
  -- Declarative part of function begins
    original_squared NUMBER;
  -- Declarative part of function ends
  -- Executable part of function begins
  BEGIN
    original_squared := original * original;
    RETURN original_squared;  -- RETURN statement
  -- Exception-handling part of function (optional) goes here
  END;
  -- Executable part of function ends
  -- Function declaration and definition ends
-- Declarative part of block ends
-- Executable part of block begins
BEGIN
  DBMS_OUTPUT.PUT_LINE(square(100));  -- Function invocation
END;
-- Executable part of block ends
/

Nested Subprograms that Invoke Each Other

In a block, you can create multiple nested subprograms. If they invoke each other, you need forward declaration, because a subprogram must be declared before it can be invoked. With forward declaration, you declare a subprogram, but do not define it until after you have defined the other subprograms that invoke it. A forward declaration and its corresponding definition must appear in the same block.

The block in Example 8-3 creates two procedures that invoke each other.

DECLARE
  -- Declare proc1 (forward declaration):
  PROCEDURE proc1(number1 NUMBER);

  -- Declare and define proc 2:
  PROCEDURE proc2(number2 NUMBER) IS
  BEGIN
    proc1(number2);
  END;

  -- Define proc 1:
  PROCEDURE proc1(number1 NUMBER) IS
  BEGIN
    proc2 (number1);
  END;

BEGIN
  NULL;
END;
/

Subprogram Parameters

A subprogram heading can declare formal parameters. Each formal parameter declaration can specify a mode and a initial value. When you invoke the subprogram, you can pass actual parameters to it.

Topics:

• Formal and Actual Subprogram Parameters

• Subprogram Parameter Passing Methods

• Subprogram Parameter Modes

• Initial Values for Subprogram Parameters

• Positional, Named, and Mixed Notation for Actual Parameters

DECLARE
  emp_num NUMBER(6) := 120;
  bonus   NUMBER(6) := 100;
  merit   NUMBER(4) := 50;

  PROCEDURE raise_salary (
    emp_id NUMBER,  -- formal parameter
    amount NUMBER   -- formal parameter
  ) IS
  BEGIN
    UPDATE employees
    SET salary = salary + amount
    WHERE employee_id = emp_id;
  END raise_salary;

BEGIN
  raise_salary(emp_num, bonus);  -- actual parameters
  raise_salary(emp_num, merit + bonus);  -- actual parameters
END;
/

CREATE OR REPLACE PROCEDURE s
  (n IN PLS_INTEGER)
IS
BEGIN
  NULL;
END;
/
DECLARE
  y PLS_INTEGER :=1;  -- Avoids implicit conversion
  z INTEGER :=1;      -- Causes implicit conversion
BEGIN
  s(y);
  s(z);
END;
/

i1 IN VARCHAR2 CHARACTER SET ANY_CS
i2 IN VARCHAR2 CHARACTER SET i1%CHARSET

DECLARE
  emp_num        NUMBER(6) := 120;
  bonus          NUMBER(6) := 50;
  emp_last_name  VARCHAR2(25);

  PROCEDURE raise_salary ( emp_id IN NUMBER
                         , amount IN NUMBER
                         , emp_name OUT VARCHAR2 )
  IS
  BEGIN
    UPDATE employees
    SET salary = salary + amount
    WHERE employee_id = emp_id;

    SELECT last_name INTO emp_name
    FROM employees
    WHERE employee_id = emp_id;
  END raise_salary;

BEGIN
  raise_salary(emp_num, bonus, emp_last_name);
  DBMS_OUTPUT.PUT_LINE
    ('Salary was updated for: ' || emp_last_name);
END;
/

DECLARE
  emp_num NUMBER(6) := 120;
  bonus   NUMBER(6);
  merit   NUMBER(4);

  PROCEDURE raise_salary (emp_id IN NUMBER,
    amount IN NUMBER := 100,
    extra  IN NUMBER := 50
  ) IS
  BEGIN
    UPDATE employees
    SET salary = salary + amount + extra
    WHERE employee_id = emp_id;
  END raise_salary;

BEGIN
  -- Same as raise_salary(120, 100, 50):
  raise_salary(120);

  -- Same as raise_salary(120, 100, 25):
  raise_salary(emp_num, extra => 25);
END;
/

DECLARE
  cnt  pls_integer := 0;

  FUNCTION dflt RETURN pls_integer IS
  BEGIN
    cnt := cnt + 1;
    RETURN 42;
  END dflt;
  
  PROCEDURE p (
    i IN pls_integer := dflt() -- initial value is expression
  ) IS
  BEGIN
    DBMS_Output.Put_Line(i);
  END p;
  
BEGIN
  FOR j IN 1..5 LOOP
    p(j);  -- Actual parameter is provided
  END loop;
  DBMS_Output.Put_Line('cnt: '||cnt);
  p();  -- Actual parameter is not provided
  DBMS_Output.Put_Line('cnt: '||cnt);
END;
/

1
2
3
4
5
Cnt: 0
42
Cnt: 1

DECLARE
  emp_num NUMBER(6) := 120;
  bonus   NUMBER(6) := 50;

  PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS
  BEGIN
    UPDATE employees SET salary =
      salary + amount WHERE employee_id = emp_id;
  END raise_salary;
BEGIN
  -- Positional notation:
  raise_salary(emp_num, bonus);
  -- Named notation (parameter order is insignificant):
  raise_salary(amount => bonus, emp_id => emp_num);
  raise_salary(emp_id => emp_num, amount => bonus);
  -- Mixed notation:
  raise_salary(emp_num, amount => bonus);
END;
/
CREATE OR REPLACE FUNCTION compute_bonus (
  emp_id NUMBER,
  bonus NUMBER
)  RETURN NUMBER
IS
  emp_sal NUMBER;
BEGIN
  SELECT salary INTO emp_sal
  FROM employees
  WHERE employee_id = emp_id;

  RETURN emp_sal + bonus;
END compute_bonus;
/

SELECT compute_bonus(120, 50) FROM DUAL;                   -- positional
SELECT compute_bonus(bonus => 50, emp_id => 120) FROM DUAL; -- named
SELECT compute_bonus(120, bonus => 50) FROM DUAL;           -- mixed

Overloaded Subprograms

PL/SQL lets you overload local subprograms, packaged subprograms, and type methods. You can use the same name for several different subprograms if their formal parameters differ in number, order, or data type family.

Example 8-10 defines two subprograms with the same name, initialize. The procedures initialize different types of collections. Because the processing in these two procedures is the same, it is logical to give them the same name.

You can put the two initialize procedures in the same block, subprogram, package, or ADT. PL/SQL determines which procedure to invoke by checking their formal parameters. The version of initialize that PL/SQL uses depends on whether you invoke the procedure with a date_tab_typ or num_tab_typ parameter.

DECLARE
  TYPE date_tab_typ IS TABLE OF DATE   INDEX BY PLS_INTEGER;
  TYPE num_tab_typ  IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  hiredate_tab  date_tab_typ;
  sal_tab       num_tab_typ;

  PROCEDURE initialize (tab OUT date_tab_typ, n INTEGER) IS
  BEGIN
    FOR i IN 1..n LOOP
      tab(i) := SYSDATE;
    END LOOP;
  END initialize;

  PROCEDURE initialize (tab OUT num_tab_typ, n INTEGER) IS
  BEGIN
    FOR i IN 1..n LOOP
      tab(i) := 0.0;
    END LOOP;
  END initialize;

BEGIN
  initialize(hiredate_tab, 50);  -- Invokes first (date_tab_typ) version
  initialize(sal_tab, 100);      -- Invokes second (num_tab_typ) version
END;
/

For an example of an overloaded procedure in a package, see Example 10-6.

Topics:

• Subprograms that You Can Overload

• Subprograms that You Cannot Overload

• Subprogram Overload Errors

CREATE OR REPLACE PACKAGE pkg1 IS
  PROCEDURE s (p VARCHAR2);
  PROCEDURE s (p VARCHAR2);
END pkg1;
/

CREATE OR REPLACE PACKAGE pkg2 IS
  SUBTYPE t1 IS VARCHAR2(10);
  SUBTYPE t2 IS VARCHAR2(10);
  PROCEDURE s (p t1);
  PROCEDURE s (p t2);
END pkg2;
/

CREATE OR REPLACE PROCEDURE p IS
  a pkg2.t1 := 'a';
BEGIN
  pkg2.s(a);  -- Causes compile-time error PLS-00307
END p;
/

CREATE OR REPLACE PACKAGE pkg2 IS
  SUBTYPE t1 IS VARCHAR2(10);
  SUBTYPE t2 IS VARCHAR2(10);
  PROCEDURE s (p1 t1);
  PROCEDURE s (p2 t2);
END pkg2;
/

CREATE OR REPLACE PROCEDURE p IS
  a pkg2.t1 := 'a';
BEGIN
  pkg2.s(p1=>a);  -- Compiles without error
END p;
/

CREATE OR REPLACE PACKAGE pkg3 IS
  PROCEDURE s (p1 VARCHAR2);
  PROCEDURE s (p1 VARCHAR2, p2 VARCHAR2 := 'p2');
END pkg3;
/

CREATE OR REPLACE PROCEDURE p IS
  a1 VARCHAR2(10) := 'a1';
  a2 VARCHAR2(10) := 'a2';
BEGIN
  pkg3.s(p1=>a1, p2=>a2);  -- Compiles without error
  pkg3.s(p1=>a1);          -- Causes compile-time error PLS-00307
END p;
/

Subprogram Call Resolution

Figure 8-1 shows how the PL/SQL compiler resolves subprogram calls. When the compiler encounters a subprogram call, it tries to find a declaration that matches the call. The compiler searches first in the current scope and then, if necessary, in successive enclosing scopes. The compiler looks more closely when it finds one or more subprogram declarations in which the subprogram name matches the name of the called subprogram.

To resolve a call among possibly like-named subprograms at the same level of scope, the compiler must find an exact match between the actual and formal parameters. They must match in number, order, and data type (unless some formal parameters were assigned initial values). If no match is found or if multiple matches are found, the compiler generates a semantic error.

Figure 8-1 How the PL/SQL Compiler Resolves Calls

Description of "Figure 8-1 How the PL/SQL Compiler Resolves Calls"

In Example 8-18, the function balance tries to invoke the enclosing procedure swap, using appropriate actual parameters. However, balance contains two local procedures named swap, and neither has parameters of the same type as the enclosing procedure swap. Therefore, the invocation causes compilation error PLS-00306.

DECLARE
  PROCEDURE swap (
    n1 NUMBER,
    n2 NUMBER
  )
  IS
    num1 NUMBER;
    num2 NUMBER;

    FUNCTION balance
      (bal NUMBER)
      RETURN NUMBER
    IS
      x NUMBER := 10;

      PROCEDURE swap (
        d1 DATE,
        d2 DATE
      ) IS
      BEGIN
        NULL;
      END;

      PROCEDURE swap (
        b1 BOOLEAN,
        b2 BOOLEAN
      ) IS
      BEGIN
        NULL;
      END;

    BEGIN  -- balance
      swap(num1, num2);  -- Causes compile-time error PLS-00306
      RETURN x;
    END balance;

  BEGIN  -- enclosing procedure swap
    NULL;
  END swap;

BEGIN  -- anonymous block
  NULL;
END;   -- anonymous block
/

Invoker's Rights and Definer's Rights (AUTHID Property)

The AUTHID property of a stored PL/SQL unit affects the name resolution and privilege checking of SQL statements that the unit issues at run time. The AUTHID property does not affect compilation, and has no meaning for units that have no code, such as collection types.

AUTHID property values are exposed in the static data dictionary view *_PROCEDURES. For units for which AUTHID has meaning, the view shows the value CURRENT_USER or DEFINER; for other units, the view shows NULL.

For stored PL/SQL units that you create or alter with the following statements, you can use the optional AUTHID clause to specify either DEFINER (the default) or CURRENT_USER:

• "CREATE FUNCTION Statement"

• "CREATE PACKAGE Statement"

• "CREATE PROCEDURE Statement"

• "CREATE TYPE Statement"

• "ALTER TYPE Statement"

A unit whose AUTHID value is CURRENT_USER is called an invoker's rights unit, or IR unit. A unit whose AUTHID value is DEFINER is called a definer's rights unit, or DR unit. An anonymous block always behaves like an IR unit. A trigger or view always behaves like a DR unit.

The AUTHID property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege checking at run time:

• The context for name resolution is CURRENT_SCHEMA.

• The privileges checked are those of the CURRENT_USER and the enabled roles.

When a session starts, CURRENT_SCHEMA has the value of the schema owned by SESSION_USER, and CURRENT_USER has the same value as SESSION_USER. (To get the current value of CURRENT_SCHEMA, CURRENT_USER, or SESSION_USER, use the SYS_CONTEXT function, documented in Oracle Database SQL Language Reference.)

CURRENT_SCHEMA can be changed during the session with the SQL statement ALTER SESSION SET CURRENT_SCHEMA. CURRENT_USER cannot be changed programmatically, but it might change when a PL/SQL unit or a view is pushed onto, or popped from, the call stack.

During a server call, when a DR unit is pushed onto the call stack, the database stores the currently enabled roles and the current values of CURRENT_USER and CURRENT_SCHEMA. It then changes both CURRENT_USER and CURRENT_SCHEMA to the owner of the DR unit, and enables only the role PUBLIC. (The stored and new roles and values are not necessarily different.) When the DR unit is popped from the call stack, the database restores the stored roles and values. In contrast, when an IR unit is pushed onto, or popped from, the call stack, the values of CURRENT_USER and CURRENT_SCHEMA, and the currently enabled roles do not change.

For dynamic SQL statements issued by a PL/SQL unit, name resolution and privilege checking are done once, at run time. For static SQL statements, name resolution and privilege checking are done twice: first, when the PL/SQL unit is compiled, and then again at run time. At compilation time, the AUTHID property has no effect—both DR and IR units are treated like DR units. At run time, however, the AUTHID property determines whether a unit is IR or DR, and the unit is treated accordingly.

Topics:

• Choosing AUTHID CURRENT_USER or AUTHID DEFINER

• AUTHID and SQL Command SET ROLE

• Need for Template Objects in IR Units

• Overriding Default Name Resolution in IR Units

• IR Subprograms, Views, and Database Triggers

• IR Database Links

• IR ADTs

• IR Instance Methods

DECLARE
  today  DATE;
BEGIN
  SELECT sysdate INTO today FROM "PUBLIC".DUAL;
END;
/

CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER
  USING connect_string;

CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...

CREATE TYPE person_typ AUTHID CURRENT_USER AS OBJECT (
  person_id    NUMBER,
  person_name  VARCHAR2(30),
  person_job   VARCHAR2(10),

  STATIC PROCEDURE new_person_typ (
    person_id NUMBER,
    person_name VARCHAR2,
    person_job VARCHAR2,
    schema_name VARCHAR2,
    table_name VARCHAR2
  ),

  MEMBER PROCEDURE change_job (
   SELF IN OUT NOCOPY person_typ,
   new_job VARCHAR2
  )
);
/
CREATE TYPE BODY person_typ AS
  STATIC PROCEDURE new_person_typ (
    person_id NUMBER,
    person_name VARCHAR2,
    person_job VARCHAR2,
    schema_name VARCHAR2,
    table_name VARCHAR2
  )
  IS
    sql_stmt VARCHAR2(200);
  BEGIN
    sql_stmt := 'INSERT INTO ' || schema_name || '.'
       || table_name || ' VALUES (HR.person_typ(:1, :2, :3))';

    EXECUTE IMMEDIATE sql_stmt
      USING person_id, person_name, person_job;
  END;

  MEMBER PROCEDURE change_job (
    SELF IN OUT NOCOPY person_typ,
    new_job VARCHAR2
  )
  IS
  BEGIN
    person_job := new_job;
  END;
END;
/

GRANT EXECUTE ON person_typ TO OE;

DROP TABLE person_tab;
CREATE TABLE person_tab OF hr.person_typ;

BEGIN
  hr.person_typ.new_person_typ(1001,
                               'Jane Smith',
                               'CLERK',
                               'oe',
                               'person_tab');
  hr.person_typ.new_person_typ(1002,
                               'Joe Perkins',
                               'SALES',
                               'oe',
                               'person_tab');
  hr.person_typ.new_person_typ(1003,
                               'Robert Lange',
                               'DEV',
                               'oe',
                               'person_tab');
END;
/

-- OE creates procedure that invokes change_job:

CREATE OR REPLACE PROCEDURE reassign (
  p IN OUT NOCOPY hr.person_typ,
  new_job VARCHAR2
) AS
BEGIN
  p.change_job(new_job);  -- runs with privileges of OE
END;
/
-- OE grants EXECUTE privilege on procedure reassign to HR:

GRANT EXECUTE ON reassign to HR;

-- HR passes person_typ object to procedure reassign:

DECLARE
  p1  person_typ;
BEGIN
  p1 := person_typ(1004, 'June Washburn', 'SALES');
  oe.reassign(p1, 'CLERK');  -- current user is OE, not HR
END;
/

Recursive Subprograms

A recursive subprogram is one that invokes itself. Each recursive call creates an instance of each item declared in the subprogram, including parameters, variables, cursors, and exceptions. Likewise, instances of SQL statements are created at each level in the recursive descent.

Be careful where you place a recursive call. If you place it inside a cursor FOR loop or between OPEN and CLOSE statements, another cursor is opened at each call, which might exceed the limit set by the database initialization parameter OPEN_CURSORS.

There must be at least two paths through a recursive subprogram: one that leads to the recursive call and one that does not. At least one path must lead to a terminating condition. Otherwise, the recursion continues until PL/SQL runs out of memory and raises the predefined exception STORAGE_ERROR.

Recursion is a powerful technique for simplifying the design of algorithms. Basically, recursion means self-reference. In a recursive mathematical sequence, each term is derived by applying a formula to preceding terms. The Fibonacci sequence (0, 1, 1, 2, 3, 5, 8, 13, 21, ...), is an example. Each term in the sequence (after the second) is the sum of the two terms that immediately precede it.

In a recursive definition, something is defined as simpler versions of itself. Consider the definition of n factorial (n!), the product of all integers from 1 to n:

n! = n * (n - 1)!

External Subprograms

Although PL/SQL is a powerful, flexible language, some tasks are more easily done in another language, such as C or Java.

External C subprograms extend the functionality of the database server, and move computation-bound programs from client to server, where they run faster. External C subprograms are useful for interfacing with embedded systems, solving engineering problems, analyzing data, and controlling real-time devices and processes.

Java and other widely used languages have reusable libraries for common design patterns.

You can use PL/SQL call specifications to invoke external subprograms written in other languages, making their capabilities and libraries available from PL/SQL. For example, you can invoke Java stored procedures from any PL/SQL block, subprogram, or package.

If this Java class is stored in the database, it can be invoked as shown in Example 8-21:

import java.sql.*;
import oracle.jdbc.driver.*;
public class Adjuster {
  public static void raiseSalary (int empNo, float percent)
  throws SQLException {
    Connection conn = new OracleDriver().defaultConnection();
    String sql = "UPDATE employees SET salary = salary * ?
                    WHERE employee_id = ?";
    try {
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setFloat(1, (1 + percent / 100));
      pstmt.setInt(2, empNo);
      pstmt.executeUpdate();
      pstmt.close();
    } catch (SQLException e)
          {System.err.println(e.getMessage());}
    }
}

The class Adjuster has one method, which raises the salary of an employee by a given percentage. Because raiseSalary is a void method, you publish it as a procedure using the call specification shown in Example 8-21 and then invoke the procedure raise_salary from an anonymous PL/SQL block.

CREATE OR REPLACE PROCEDURE raise_salary (
  empid NUMBER,
  pct NUMBER
)
AS LANGUAGE JAVA
NAME 'Adjuster.raiseSalary(int, float)';
/

DECLARE
  emp_id   NUMBER := 120;
  percent  NUMBER := 10;
BEGIN
  raise_salary(emp_id, percent);
END;
/

Java call specifications cannot be declared as nested procedures, but can be specified in ADT specifications and bodies, PL/SQL package specifications, PL/SQL package bodies, and as schema-level PL/SQL procedures and functions.

Example 8-22 creates a Java call specification and invokes the Java function from a PL/SQL procedure.

-- Java call specification:

CREATE PROCEDURE java_sleep (
  milli_seconds IN NUMBER
) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
/

CREATE OR REPLACE PROCEDURE sleep (
  milli_seconds IN NUMBER
) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time());
  java_sleep (milli_seconds);
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time());
END;
/

Subprogram Side Effects

The fewer side effects a function has, the better it can be optimized in a query, particularly when the PARALLEL_ENABLE or DETERMINISTIC options are used. For the syntax of these options, see "Function".

To be callable from SQL statements, a stored function (and any subprograms that it invokes) must obey these purity rules, which are meant to control side effects:

• When invoked from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the function cannot modify any database tables.

• When invoked from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement.

• When invoked from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot run SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM). Also, it cannot run DDL statements (such as CREATE) because they are followed by an automatic commit.

If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed).

Subprogram Parameter Aliasing

The NOCOPY hint (explained in "Subprogram Parameter Passing Methods") increases the possibility of aliasing—that is, having two different names refer to the same memory location. This can occur when a global variable appears as an actual parameter in a subprogram call and then is referenced in the subprogram. The result is indeterminate, because the optimizer can either obey or disregard the NOCOPY hint.

In Example 8-23, procedure add_entry references varray lexicon both as a parameter and as a global variable. When add_entry is invoked, the identifiers word_list and lexicon point to the same varray.

DECLARE
  TYPE Definition IS RECORD (
    word     VARCHAR2(20),
    meaning  VARCHAR2(200)
  );

  TYPE Dictionary IS VARRAY(2000) OF Definition;

  lexicon  Dictionary := Dictionary();

  PROCEDURE add_entry (
    word_list IN OUT NOCOPY Dictionary
  ) IS
  BEGIN
    word_list(1).word := 'aardvark';
    lexicon(1).word := 'aardwolf';
  END;

BEGIN
  lexicon.EXTEND;
  add_entry(lexicon);
  DBMS_OUTPUT.PUT_LINE(lexicon(1).word);
END;
/

Aliasing can also occur when the same actual parameter appears more than once in a subprogram call. In Example 8-24, n2 is an IN OUT parameter, so the value of the actual parameter is not updated until the procedure exits. That is why the first PUT_LINE prints 10 (the initial value of n) and the third PUT_LINE prints 20. However, n3 is a NOCOPY parameter, so the value of the actual parameter is updated immediately. That is why the second PUT_LINE prints 30.

DECLARE
  n NUMBER := 10;

  PROCEDURE do_something (
    n1 IN NUMBER,
    n2 IN OUT NUMBER,
    n3 IN OUT NOCOPY NUMBER) IS
  BEGIN
    n2 := 20;
    DBMS_OUTPUT.put_line(n1);  -- prints 10
    n3 := 30;
    DBMS_OUTPUT.put_line(n1);  -- prints 30
  END;

BEGIN
  do_something(n, n, n);
  DBMS_OUTPUT.put_line(n);  -- prints 20
END;
/

Because they are pointers, cursor variables also increase the possibility of aliasing. In Example 8-25, after the assignment, emp_cv2 is an alias of emp_cv1; both point to the same query work area. The first fetch from emp_cv2 fetches the third row, not the first, because the first two rows were fetched from emp_cv1. The second fetch from emp_cv2 fails because emp_cv1 is closed.

DECLARE
  TYPE EmpCurTyp IS REF CURSOR;
  c1 EmpCurTyp;
  c2 EmpCurTyp;

  PROCEDURE get_emp_data (
    emp_cv1 IN OUT EmpCurTyp,
    emp_cv2 IN OUT EmpCurTyp
  )
  IS
    emp_rec employees%ROWTYPE;
  BEGIN
    OPEN emp_cv1 FOR SELECT * FROM employees;
    emp_cv2 := emp_cv1;
    FETCH emp_cv1 INTO emp_rec;
    DBMS_OUTPUT.put_line('Fetched first row');
    FETCH emp_cv1 INTO emp_rec;
    DBMS_OUTPUT.put_line('Fetched second row');
    FETCH emp_cv2 INTO emp_rec;
    DBMS_OUTPUT.put_line('Fetched third row');
    CLOSE emp_cv1;
    FETCH emp_cv2 INTO emp_rec; -- causes error when get_emp_data is invoked
  END;
BEGIN
  get_emp_data(c1, c2);
END;
/

DECLARE
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 22
ORA-06512: at line 25

PL/SQL Function Result Cache

The PL/SQL function result caching mechanism provides a language-supported and system-managed means for caching the results of PL/SQL functions in a shared global area (SGA), which is available to every session that runs your application. The caching mechanism is both efficient and easy to use, and relieves you of the burden of designing and developing your own caches and cache-management policies.

To enable result-caching for a function, use the RESULT_CACHE clause. When a result-cached function is invoked, the system checks the cache. If the cache contains the result from a previous call to the function with the same parameter values, the system returns the cached result to the invoker and does not reexecute the function body. If the cache does not contain the result, the system runs the function body and adds the result (for these parameter values) to the cache before returning control to the invoker.

The cache can accumulate very many results—one result for every unique combination of parameter values with which each result-cached function was invoked. If the system needs more memory, it ages out (deletes) one or more cached results.

Oracle Database automatically detects all data sources (tables and views) that are queried while a result-cached function is running. If changes to any of these data sources are committed, the cached result becomes invalid and must be recomputed. The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently or never.

Topics:

• Enabling Result-Caching for a Function

• Developing Applications with Result-Cached Functions

• Restrictions on Result-Cached Functions

• Examples of Result-Cached Functions

• Advanced Result-Cached Function Topics

-- Package specification
CREATE OR REPLACE PACKAGE department_pkg IS
  TYPE dept_info_record IS RECORD (average_salary      NUMBER,
                                   number_of_employees NUMBER);
  -- Function declaration
  FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record
  RESULT_CACHE;
END department_pkg;
/
CREATE OR REPLACE PACKAGE BODY department_pkg AS
  -- Function definition
  FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record
    RESULT_CACHE;
  IS
    rec dept_info_record;
  BEGIN
    SELECT AVG(SALARY), COUNT(*) INTO rec
      FROM EMPLOYEES
        WHERE DEPARTMENT_ID = dept_id;
    RETURN rec;
  END get_dept_info;
END department_pkg;
/
DECLARE
  dept_id   NUMBER := 50;
  avg_sal   NUMBER;
  no_of_emp NUMBER;
BEGIN
  avg_sal   := department_pkg.get_dept_info(50).average_salary;
  no_of_emp := department_pkg.get_dept_info(50).number_of_employees;
  DBMS_OUTPUT.PUT_LINE('dept_id = ' ||dept_id);
  DBMS_OUTPUT.PUT_LINE('average_salary = '|| avg_sal);
  DBMS_OUTPUT.PUT_LINE('number_of_employees = ' ||no_of_emp);
END;
/

department_pkg.get_dept_info(10);

department_pkg.get_dept_info(10).average_salary;

-- Global Configuration Settings
DROP TABLE global_config_params;
CREATE TABLE global_config_params
  (name  VARCHAR2(20), -- parameter NAME
   value VARCHAR2(20), -- parameter VALUE
   PRIMARY KEY (name)
  );

-- Application-Level Configuration Settings
CREATE TABLE app_level_config_params
  (app_id  VARCHAR2(20), -- application ID
   name    VARCHAR2(20), -- parameter NAME
   value   VARCHAR2(20), -- parameter VALUE
   PRIMARY KEY (app_id, name)
  );

-- Role-Level Configuration Settings
CREATE TABLE role_level_config_params
  (role_id  VARCHAR2(20), -- application (role) ID
   name     VARCHAR2(20),  -- parameter NAME
   value    VARCHAR2(20),  -- parameter VALUE
   PRIMARY KEY (role_id, name)
  );

CREATE OR REPLACE FUNCTION get_value
  (p_param VARCHAR2,
   p_app_id  NUMBER,
   p_role_id NUMBER
  )
  RETURN VARCHAR2
  RESULT_CACHE
IS
  answer VARCHAR2(20);
BEGIN
  -- Is parameter set at role level?
  BEGIN
    SELECT value INTO answer
      FROM role_level_config_params
        WHERE role_id = p_role_id
          AND name = p_param;
    RETURN answer;  -- Found
    EXCEPTION
      WHEN no_data_found THEN
        NULL;  -- Fall through to following code
  END;
  -- Is parameter set at application level?
  BEGIN
    SELECT value INTO answer
      FROM app_level_config_params
        WHERE app_id = p_app_id
          AND name = p_param;
    RETURN answer;  -- Found
    EXCEPTION
      WHEN no_data_found THEN
        NULL;  -- Fall through to following code
  END;
  -- Is parameter set at global level?
    SELECT value INTO answer
     FROM global_config_params
      WHERE name = p_param;
    RETURN answer;
END;

CREATE OR REPLACE FUNCTION fibonacci (n NUMBER)
   RETURN NUMBER RESULT_CACHE IS
BEGIN
  IF (n =0) OR (n =1) THEN
    RETURN 1;
  ELSE
    RETURN fibonacci(n - 1) + fibonacci(n - 2);
  END IF;
END;
/

CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR
 RESULT_CACHE
IS
  date_hired DATE;
BEGIN
  SELECT hire_date INTO date_hired
    FROM HR.EMPLOYEES
      WHERE EMPLOYEE_ID = emp_id;
  RETURN TO_CHAR(date_hired);
END;
/

SELECT value FROM config_tab WHERE name = param_name;

SELECT value FROM config_tab
WHERE name = param_name
AND app_id = SYS_CONTEXT('Config', 'App_ID');

CREATE OR REPLACE FUNCTION get_param_value
  (param_name VARCHAR,
   appctx     VARCHAR  := SYS_CONTEXT('Config', 'App_ID')
  )
  RETURN VARCHAR
  RESULT_CACHE
IS
  rec VARCHAR(2000);
BEGIN
  SELECT value INTO rec
    FROM config_tab
      WHERE Name = param_name;
END;
/

NAME                     NULL?      TYPE
----------------------   --------   ---------------
PRODUCT_ID               NOT NULL   NUMBER(6)
LANGUAGE_ID              NOT NULL   VARCHAR2(3)
TRANSLATED_NAME          NOT NULL   NVARCHAR2(50)
TRANSLATED_DESCRIPTION   NOT NULL   NVARCHAR2(2000)

CREATE OR REPLACE FUNCTION get_product_name_1 (prod_id NUMBER, lang_id VARCHAR2)
  RETURN NVARCHAR2
  RESULT_CACHE
IS
  result VARCHAR2(50);
BEGIN
  SELECT translated_name INTO result
    FROM Product_Descriptions
      WHERE PRODUCT_ID = prod_id
        AND LANGUAGE_ID = lang_id;
  RETURN result;
END;

CREATE OR REPLACE FUNCTION get_product_name_2 (prod_id NUMBER, lang_id VARCHAR2)
  RETURN NVARCHAR2
IS
  TYPE product_names IS TABLE OF NVARCHAR2(50) INDEX BY PLS_INTEGER;

  FUNCTION all_product_names (lang_id NUMBER) RETURN product_names
    RESULT_CACHE
  IS
    all_names product_names;
  BEGIN
    FOR c IN (SELECT * FROM Product_Descriptions
      WHERE LANGUAGE_ID = lang_id) LOOP
        all_names(c.PRODUCT_ID) := c.TRANSLATED_NAME;
    END LOOP;
    RETURN all_names;
  END;
BEGIN
  RETURN all_product_names(lang_id)(prod_id);
END;