8 Using PL/SQL Subprograms

Using the RETURN Statement

The RETURN statement immediately ends the execution of a subprogram and returns control to the caller. Execution continues with the statement following the subprogram call. (Do not confuse the RETURN statement with the RETURN clause in a function spec, which specifies the datatype of the return value.)

A subprogram can contain several RETURN statements. The subprogram does not have to conclude with a RETURN statement. Executing any RETURN statement completes the subprogram immediately.

In procedures, a RETURN statement does not return a value and so cannot contain an expression. The statement returns control to the caller before the end of the procedure.

In functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier, which acts like a variable of the type specified in the RETURN clause. Observe how the function balance returns the balance of a specified bank account:

FUNCTION balance (acct_id INTEGER) RETURN REAL IS
   acct_bal REAL;
BEGIN
   SELECT bal INTO acct_bal FROM accts
      WHERE acct_no = acct_id;
   RETURN acct_bal;
END balance;
/

The following example shows that the expression in a function RETURN statement can be arbitrarily complex:

FUNCTION compound (
   years  NUMBER,
   amount NUMBER,
   rate   NUMBER) RETURN NUMBER IS
BEGIN
   RETURN amount * POWER((rate / 100) + 1, years);
END compound;
/

In a function, there must be at least one execution path that leads to a RETURN statement. Otherwise, you get a function returned without value error at run time.

Actual Versus Formal Subprogram Parameters

Subprograms pass information using parameters:

• The variables declared in a subprogram spec and referenced in the subprogram body are formal parameters.

• The variables or expressions passed from the calling subprogram are actual parameters.

A good programming practice is to use different names for actual and formal parameters.

When you call a procedure, the actual parameters are evaluated and the results are assigned to the corresponding formal parameters. If necessary, before assigning the value of an actual parameter to a formal parameter, PL/SQL converts the datatype of the value. For example, if you pass a number when the procedure expects a string, PL/SQL converts the parameter so that the procedure receives a string.

The actual parameter and its corresponding formal parameter must have compatible datatypes. For instance, PL/SQL cannot convert between the DATE and REAL datatypes, or convert a string to a number if the string contains extra characters such as dollar signs.

PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS
BEGIN
   UPDATE emp SET sal = sal + amount WHERE empno = emp_id;
END raise_salary;
/

raise_salary(emp_num, amount);

raise_salary(emp_num, merit + cola);

Using Positional, Named, or Mixed Notation for Subprogram Parameters

When calling a subprogram, you can write the actual parameters using either:

• Positional notation. You specify the same parameters in the same order as they are declared in the procedure.

  This notation is compact, but if you specify the parameters (especially literals) in the wrong order, the bug can be hard to detect. You must change your code if the procedure's parameter list changes.

• Named notation. You specify the name of each parameter along with its value. An arrow (=>) serves as the association operator. The order of the parameters is not significant.

  This notation is more verbose, but makes your code easier to read and maintain. You can sometimes avoid changing your code if the procedure's parameter list changes, for example if the parameters are reordered or a new optional parameter is added. Named notation is a good practice to use for any code that calls someone else's API, or defines an API for someone else to use.

• Mixed notation. You specify the first parameters with positional notation, then switch to named notation for the last parameters.

  You can use this notation to call procedures that have some required parameters, followed by some optional parameters.

DECLARE
   acct INTEGER := 12345;
   amt  REAL := 500.00;
   PROCEDURE credit_acct (acct_no INTEGER, amount REAL) IS
      BEGIN NULL; END;
BEGIN
-- The following calls are all equivalent.
   credit_acct(acct, amt);                       -- positional
   credit_acct(amount => amt, acct_no => acct);  -- named
   credit_acct(acct_no => acct, amount => amt);  -- named
   credit_acct(acct, amount => amt);             -- mixed
END;
/

Specifying Subprogram Parameter Modes

You use parameter modes to define the behavior of formal parameters. The three parameter modes are IN (the default), OUT, and IN OUT.

Any parameter mode can be used with any subprogram. Avoid using the OUT and IN OUT modes with functions. To have a function return multiple values is a poor programming practice. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.

PROCEDURE split_name
(
   phrase IN VARCHAR2, first OUT VARCHAR2, last OUT VARCHAR2
)
IS
   first := SUBSTR(phrase, 1, INSTR(phrase, ' ')-1);
   last := SUBSTR(phrase, INSTR(phrase, ' ')+1);
   IF first = 'John' THEN
      DBMS_OUTPUT.PUT_LINE('That is a common first name.');
   END IF;
END;
/

Using Default Values for Subprogram Parameters

By initializing IN parameters to default values, you can pass different numbers of actual parameters to a subprogram, accepting the default values for any parameters you omit. You can also add new formal parameters without having to change every call to the subprogram.

PROCEDURE create_dept (
    new_dname VARCHAR2 DEFAULT 'TEMP',
    new_loc   VARCHAR2 DEFAULT 'TEMP') IS
BEGIN
   NULL;
END;
/

create_dept;             -- Same as create_dept('TEMP','TEMP');
create_dept('SALES');    -- Same as create_dept('SALES','TEMP');
create_dept('SALES', 'NY');

create_dept(new_loc => 'NEW YORK');

Guidelines for Overloading with Numeric Types

You can overload two subprograms if their formal parameters differ only in numeric datatype. This technique might be useful in writing mathematical APIs, where several versions of a function could use the same name, each accepting a different numeric type. For example, a function accepting BINARY_FLOAT might be faster, while a function accepting BINARY_DOUBLE might provide more precision.

To avoid problems or unexpected results passing parameters to such overloaded subprograms:

• Make sure to test that the expected version of a subprogram is called for each set of expected parameters. For example, if you have overloaded functions that accept BINARY_FLOAT and BINARY_DOUBLE, which is called if you pass a VARCHAR2 literal such as '5.0'?

• Qualify numeric literals and use conversion functions to make clear what the intended parameter types are. For example, use literals such as 5.0f (for BINARY_FLOAT), 5.0d (for BINARY_DOUBLE), or conversion functions such as TO_BINARY_FLOAT(), TO_BINARY_DOUBLE(), and TO_NUMBER().

PL/SQL looks for matching numeric parameters starting with PLS_INTEGER or BINARY_INTEGER, then NUMBER, then BINARY_FLOAT, then BINARY_DOUBLE. The first overloaded subprogram that matches the supplied parameters is used. A VARCHAR2 value can match a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE parameter.

For example, consider the SQRT function, which takes a single parameter. There are overloaded versions that accept a NUMBER, a BINARY_FLOAT, or a BINARY_DOUBLE parameter. If you pass a PLS_INTEGER parameter, the first matching overload (using the order given in the preceding paragraph) is the one with a NUMBER parameter, which is likely to be the slowest. To use one of the faster versions, use the TO_BINARY_FLOAT or TO_BINARY_DOUBLE functions to convert the parameter to the right datatype.

For another example, consider the ATAN2 function, which takes two parameters of the same type. If you pass two parameters of the same type, you can predict which overloaded version is used through the same rules as before. If you pass parameters of different types, for example one PLS_INTEGER and one BINARY_FLOAT, PL/SQL tries to find a match where both parameters use the "higher" type. In this case, that is the version of ATAN2 that takes two BINARY_FLOAT parameters; the PLS_INTEGER parameter is converted "upwards".

The preference for converting "upwards" holds in more complicated situations. For example, you might have a complex function that takes two parameters of different types. One overloaded version might take a PLS_INTEGER and a BINARY_FLOAT parameter. Another overloaded version might take a NUMBER and a BINARY_DOUBLE parameter. What happens if you call this procedure name and pass two NUMBER parameters? PL/SQL looks "upward" first to find the overloaded version where the second parameter is BINARY_FLOAT. Because this parameter is a closer match than the BINARY_DOUBLE parameter in the other overload, PL/SQL then looks "downward" and converts the first NUMBER parameter to PLS_INTEGER.

Restrictions on Overloading

Only local or packaged subprograms, or type methods, can be overloaded. You cannot overload standalone subprograms.

You cannot overload two subprograms if their formal parameters differ only in name or parameter mode. For example, you cannot overload the following two procedures:

DECLARE
   PROCEDURE reconcile (acct_no IN INTEGER) IS
   BEGIN NULL; END;
   PROCEDURE reconcile (acct_no OUT INTEGER) IS
   BEGIN NULL; END;
/

You cannot overload subprograms whose parameters differ only in subtype. For example, you cannot overload procedures where one accepts an INTEGER parameter and the other accepts a REAL parameter, even though INTEGER and REAL are both subtypes of NUMBER and so are in the same family.

You cannot overload two functions that differ only in the datatype of the return value, even if the types are in different families. For example, you cannot overload two functions where one returns BOOLEAN and the other returns INTEGER.

How Overloading Works with Inheritance

The overloading algorithm allows substituting a subtype value for a formal parameter that is a supertype. This capability is known as substitutability. If more than one instance of an overloaded procedure matches the procedure call, the following rules apply to determine which procedure is called:

If the only difference in the signatures of the overloaded procedures is that some parameters are object types from the same supertype-subtype hierarchy, the closest match is used. The closest match is one where all the parameters are at least as close as any other overloaded instance, as determined by the depth of inheritance between the subtype and supertype, and at least one parameter is closer.

A semantic error occurs when two overloaded instances match, and some argument types are closer in one overloaded procedure to the actual arguments than in any other instance.

A semantic error also occurs if some parameters are different in their position within the object type hierarchy, and other parameters are of different datatypes so that an implicit conversion would be necessary.

For example, here we create a type hierarchy with 3 levels:

CREATE TYPE super_t AS object
  (n NUMBER) NOT final;
CREATE OR replace TYPE sub_t under super_t
  (n2 NUMBER) NOT final;
CREATE OR replace TYPE final_t under sub_t
  (n3 NUMBER);

We declare two overloaded instances of a function, where the only difference in argument types is their position in this type hierarchy:

CREATE PACKAGE p IS
   FUNCTION foo (arg super_t) RETURN NUMBER;
   FUNCTION foo (arg sub_t) RETURN NUMBER;
END;
/
CREATE PACKAGE BODY p IS
   FUNCTION foo (arg super_t) RETURN NUMBER IS BEGIN RETURN 1; END;
   FUNCTION foo (arg sub_t) RETURN NUMBER  IS BEGIN RETURN 2; END;
END;
/

We declare a variable of type final_t, then call the overloaded function. The instance of the function that is executed is the one that accepts a sub_t parameter, because that type is closer to final_t in the hierarchy than super_t is.

set serveroutput on
declare
v final_t := final_t(1,2,3);
begin
  dbms_output.put_line(p.foo(v));
end;
/

In the previous example, the choice of which instance to call is made at compile time. In the following example, this choice is made dynamically.

CREATE TYPE super_t2 AS object
  (n NUMBER, MEMBER FUNCTION foo RETURN NUMBER) NOT final;
/
CREATE TYPE BODY super_t2 AS
 MEMBER FUNCTION foo RETURN NUMBER IS BEGIN RETURN 1; END; END;
/
CREATE OR replace TYPE sub_t2 under super_t2
  (n2 NUMBER,
   OVERRIDING MEMBER FUNCTION foo RETURN NUMBER) NOT final;
/
CREATE TYPE BODY sub_t2 AS
 OVERRIDING MEMBER FUNCTION foo RETURN NUMBER IS BEGIN RETURN 2;
END;
END;
/
CREATE OR replace TYPE final_t2 under sub_t2
  (n3 NUMBER);
/

We declare v as an instance of super_t2, but because we assign a value of sub_t2 to it, the appropriate instance of the function is called. This feature is known as dynamic dispatch.

set serveroutput on
declare
  v super_t2 := final_t2(1,2,3);
begin
  dbms_output.put_line(v.foo);
end;
/

Advantages of Invoker's Rights

Invoker's rights subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data using identical tables in different schemas. All the schemas in one instance can call procedures owned by a central schema. You can even have schemas in different instances call centralized procedures using a database link.

Consider a company that uses a stored procedure to analyze sales. If the company has several schemas, each with a similar SALES table, normally it would also need several copies of the stored procedure, one in each schema.

To solve the problem, the company installs an invoker's rights version of the stored procedure in a central schema. Now, all the other schemas can call the same procedure, which queries the appropriate to SALES table in each case.

You can restrict access to sensitive data by calling from an invoker's rights subprogram to a definer's rights subprogram that queries or updates the table containing the sensitive data. Although multiple users can call the invoker's rights subprogram, they do not have direct access to the sensitive data.

Specifying the Privileges for a Subprogram with the AUTHID Clause

To implement invoker's rights, use the AUTHID clause, which specifies whether a subprogram executes with the privileges of its owner or its current user. It also specifies whether external references (that is, references to objects outside the subprogram) are resolved in the schema of the owner or the current user.

The AUTHID clause is allowed only in the header of a standalone subprogram, a package spec, or an object type spec. In the CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, or CREATE TYPE statement, you can include either AUTHID CURRENT_USER or AUTHID DEFINER immediately before the IS or AS keyword that begins the declaration section.

DEFINER is the default option. In a package or object type, the AUTHID clause applies to all subprograms.

Note: Most supplied PL/SQL packages (such as DBMS_LOB, DBMS_PIPE, DBMS_ROWID, DBMS_SQL, and UTL_REF) are invoker's rights packages.

Who Is the Current User During Subprogram Execution?

In a sequence of calls, whenever control is inside an invoker's rights subprogram, the current user is the session user. When a definer's rights subprogram is called, the owner of that subprogram becomes the current user. The current user might change as new subprograms are called or as subprograms exit.

To verify who the current user is at any time, you can check the USER_USERS data dictionary view. Inside an invoker's rights subprogram, the value from this view might be different from the value of the USER built-in function, which always returns the name of the session user.

How External References Are Resolved in Invoker's Rights Subprograms

If you specify AUTHID CURRENT_USER, the privileges of the current user are checked at run time, and external references are resolved in the schema of the current user. However, this applies only to external references in:

• SELECT, INSERT, UPDATE, and DELETE data manipulation statements

• The LOCK TABLE transaction control statement

• OPEN and OPEN-FOR cursor control statements

• EXECUTE IMMEDIATE and OPEN-FOR-USING dynamic SQL statements

• SQL statements parsed using DBMS_SQL.PARSE()

For all other statements, the privileges of the owner are checked at compile time, and external references are resolved in the schema of the owner. For example, the assignment statement below refers to the packaged function balance. This external reference is resolved in the schema of the owner of procedure reconcile.

CREATE PROCEDURE reconcile (acc_id IN INTEGER)
   AUTHID CURRENT_USER AS
   bal NUMBER;
BEGIN
   bal := bank_ops.balance(acct_id);
   ...
END;
/

Overriding Default Name Resolution in Invoker's Rights Subprograms

Occasionally, you might want an unqualified name to refer to some particular schema, not the schema of the caller. In the same schema as the invoker's rights subprogram, create a public synonym for the table, procedure, function, or other object using the CREATE SYNONYM statement:

CREATE PUBLIC SYNONYM emp FOR hr.employees;

When the invoker's rights subprogram refers to this name, it will match the synonym in its own schema, which resolves to the object in the specified schema. This technique does not work if the calling schema already has a schema object or private synonym with the same name. In that case, the invoker's rights subprogram must fully qualify the reference.

Granting Privileges on Invoker's Rights Subprograms

To call a subprogram directly, users must have the EXECUTE privilege on that subprogram. By granting the privilege, you allow a user to:

• Call the subprogram directly

• Compile functions and procedures that call the subprogram

For external references resolved in the current user's schema (such as those in DML statements), the current user must have the privileges needed to access schema objects referenced by the subprogram. For all other external references (such as function calls), the owner's privileges are checked at compile time, and no run-time check is done.

A definer's rights subprogram operates under the security domain of its owner, no matter who is executing it. The owner must have the privileges needed to access schema objects referenced by the subprogram.

You can write a program consisting of multiple subprograms, some with definer's rights and others with invoker's rights. Then, you can use the EXECUTE privilege to restrict program entry points. That way, users of an entry-point subprogram can execute the other subprograms indirectly but not directly.

Granting Privileges on an Invoker's Rights Subprogram: Example

Suppose user UTIL grants the EXECUTE privilege on subprogram FFT to user APP:

GRANT EXECUTE ON util.fft TO app;

Now, user APP can compile functions and procedures that call subprogram FFT. At run time, no privilege checks on the calls are done. As Figure 8-2 shows, user UTIL need not grant the EXECUTE privilege to every user who might call FFT indirectly.

Since subprogram util.fft is called directly only from invoker's rights subprogram app.entry, user util must grant the EXECUTE privilege only to user APP. When UTIL.FFT is executed, its current user could be APP, SCOTT, or BLAKE even though SCOTT and BLAKE were not granted the EXECUTE privilege.

Figure 8-2 Indirect Calls to an Invoker's Rights Subprogram

Description of the illustration lnpls026.gif

Using Roles with Invoker's Rights Subprograms

The use of roles in a subprogram depends on whether it executes with definer's rights or invoker's rights. Within a definer's rights subprogram, all roles are disabled. Roles are not used for privilege checking, and you cannot set roles.

Within an invoker's rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer's rights subprogram). Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects because roles apply at run time, not at compile time.

Using Views and Database Triggers with Invoker's Rights Subprograms

For invoker's rights subprograms executed within a view expression, the schema that created the view, not the schema that is querying the view, is considered to be the current user.

This rule also applies to database triggers.

Using Database Links with Invoker's Rights Subprograms

You can create a database link to use invoker's rights:

CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER
   USING connect_string;

A current-user link lets you connect to a remote database as another user, with that user's privileges. To connect, Oracle uses the username of the current user (who must be a global user). Suppose an invoker's rights subprogram owned by user BLAKE references the database link below. If global user SCOTT calls the subprogram, it connects to the Dallas database as user SCOTT, who is the current user.

CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...

If it were a definer's rights subprogram, the current user would be BLAKE, and the subprogram would connect to the Dallas database as global user BLAKE.

Using Object Types with Invoker's Rights Subprograms

To define object types for use in any schema, specify the AUTHID CURRENT_USER clause. (For more information about object types, see Chapter 12, " Using PL/SQL Object Types".) Suppose user BLAKE creates the following object type:

CREATE TYPE Num AUTHID CURRENT_USER AS OBJECT (
   x NUMBER,
   STATIC PROCEDURE new_num (
      n NUMBER, schema_name VARCHAR2, table_name VARCHAR2)
);
/

CREATE TYPE BODY Num AS
   STATIC PROCEDURE new_num (
      n NUMBER, schema_name VARCHAR2, table_name VARCHAR2) IS
      sql_stmt VARCHAR2(200);
   BEGIN
      sql_stmt := 'INSERT INTO ' || schema_name || '.'
         || table_name || ' VALUES (blake.Num(:1))';
      EXECUTE IMMEDIATE sql_stmt USING n;
   END;
END;
/

Then, user BLAKE grants the EXECUTE privilege on object type Num to user SCOTT:

GRANT EXECUTE ON Num TO scott;

Finally, user SCOTT creates an object table to store objects of type Num, then calls procedure new_num to populate the table:

CONNECT scott/tiger;
CREATE TABLE num_tab OF blake.Num;
/
BEGIN
   blake.Num.new_num(1001, 'scott', 'num_tab');
   blake.Num.new_num(1002, 'scott', 'num_tab');
   blake.Num.new_num(1003, 'scott', 'num_tab');
END;
/

The calls succeed because the procedure executes with the privileges of its current user (SCOTT), not its owner (BLAKE).

For subtypes in an object type hierarchy, the following rules apply:

• If a subtype does not explicitly specify an AUTHID clause, it inherits the AUTHID of its supertype.

• If a subtype does specify an AUTHID clause, its AUTHID must match the AUTHID of its supertype. Also, if the AUTHID is DEFINER, both the supertype and subtype must have been created in the same schema.

-- user blake creates a definer-rights procedure
CREATE PROCEDURE reassign (p Person, new_job VARCHAR2) AS
BEGIN
   -- user blake calls method change_job, so the
   -- method executes with the privileges of blake
   p.change_job(new_job);
   ...
END;
/

-- user scott passes a Person object to the procedure
DECLARE
   p1 Person;
BEGIN
   p1 := Person(...);
   blake.reassign(p1, 'CLERK');
   ...
END;
/

What Is a Recursive Subprogram?

A recursive subprogram is one that calls itself. Each recursive call creates a new instance of any items declared in the subprogram, including parameters, variables, cursors, and exceptions. Likewise, new 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 Oracle 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 would go on until PL/SQL runs out of memory and raises the predefined exception STORAGE_ERROR.