14-1.

A procedure executes one or more statements and then terminates. A function executes one or more statements and then returns a result via the RETURN statement.

14-2.

Procedures and functions are composed of the following four sections:

14-3.

Use the RETURN statement to return a value from a function. It has the following format:

RETURN expression;

where expression is a literal, variable, or complex expression whose datatype matches (or can be converted to) the datatype in the function’s header’s RETURN clause.

You can use RETURN inside a procedure, but you may not specify data to be returned. Instead you simply state:

RETURN;

The procedure immediately terminates and returns control to the enclosing block. You should avoid using RETURN in a procedure, however, because it leads to unstructured code that is hard to read and maintain.

14-4.

The following procedure displays “hello world!” (remember to use the SET SERVEROUT ON command when you run the program from SQL*Plus):

CREATE OR REPLACE PROCEDURE hello_world
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE ('hello world!'),
END;
/

14-5.

The following function returns “hello world!” as a string:

CREATE OR REPLACE FUNCTION RETURN VARCHAR2
IS
BEGIN
   RETURN 'hello world!';
END;
/

14-6.

As many as you want. There is no limit on the number of RETURNs in a function. At most one, however, runs for any execution of the function. Structured coding methodology also recommends that you have just one RETURN statement in your executable section, so as to follow the precept “one way in, one way out.”

14-7.

The function headers are:

14-8.

Yes; PL/SQL has three parameter modes:

14-9.

Yes. You can provide a default value in the parameter list using the DEFAULT keyword or the := assignment operator. Both techniques are shown here:

CREATE OR REPLACE PROCEDURE calc_totals (
   department_id_in IN department.department_id%TYPE,
   total_type_in IN VARCHAR2 := 'ALLREV',
   quarter_in IN INTEGER DEFAULT 1
   )

The total_type_in parameter has a default value of “ALLREV”; quarter_in has a default value of 1.

14-10.

In all versions of PL/SQL before Oracle 8.1, IN parameters are passed by reference, while OUT and IN OUT parameters are passed by value.

14-11.

You should try to have just one RETURN statement in the executable section of your functions, preferably the last line in the section. Think of the body of the function as a funnel, with all the other lines in the executable section narrowing down to a single RETURN statement at the end of the function. Here is a “template” for such a function:

CREATE OR REPLACE FUNCTION function_name RETURN datatype
IS
   retval datatype;
BEGIN
   ... Your code here...

   RETURN retval;

EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END function_name;
/

Why should you have only one RETURN statement? Multiple RETURN statements translate to multiple possible exit points from the function. Such a structure makes it harder for developers to understand, debug, and maintain the code.

14-12.

There is no one right or wrong answer to this question. Generally, you want to design your functions so that they are as reusable as possible, to avoid code redundancy and maintenance headaches. As a rule, a function is more useful if it does not allow exceptions to propagate out unhandled for predictable kinds of problems. So you should include an exception handler with a RETURN NULL;—or some other value that indicates something went wrong—for errors that might occur in the function. You could then allow other exceptions to go unhandled.

14-13.

You should include an exception handler for NO_DATA_FOUND, since the department ID number passed in might not be valid (i.e., might not have a corresponding row in the database). In this case, you would probably simply want to return NULL, indicating clearly that no department for that ID was found (a department cannot have a NULL name). But what about TOO_MANY_ROWS? I would argue that in this case, you might want to skip an exception handler for that error. The reason? If you have more than one row, you have a serious data integrity problem: two rows with the same primary key. Rather than trap the error, just let the function pass it back out as an unhandled exception. Let the program that called the function figure out what it wants to do with that situation.

Here is the modified version of the deptname function with appropriate error handling:

CREATE OR REPLACE FUNCTION deptname (
   id_in IN department.department_id%TYPE
   )
   RETURN VARCHAR2
IS
   retval department.name%TYPE;
BEGIN
   SELECT name
     INTO retval
     FROM department
    WHERE department_id = id_in;

   RETURN retval;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END deptname;
/

14-14.

The restrictions on default values for parameters are:

14-15.

When you provide a default value for a parameter, then if a developer calls that program and doesn’t need to provide a value for the parameter, the default value is used in the body of the program. This process is identical to that employed by SQL. Suppose that when you create a table you supply a default value for a column. When you perform an INSERT on that table and do not specify a value for that column, the default value is used. This technique often comes in handy when you need to add new IN parameters to an existing program and do not want to invalidate existing calls to the program.

14-16.

The key here is to make sure that you provide a default value for the new parameter (the section ID). Here is one possible implementation:

PROCEDURE calc_totals (
   department_id_in IN department.department_id%TYPE,
   section_id_in IN INTEGER DEFAULT NULL)

Since section_id_in is a trailing IN parameter with a default value, you do not have to provide a value for the parameter. This allows existing calls to calc_total to remain valid. NULL is a good choice for the default value, because it indicates that the section ID is not being used.

14-17.

Yes, you can “skip” parameters in either of two ways:

14-18.

The calls are:

14-19.

The calls that meet the requirement are:

14-20.

Overloading is the process of defining, in a single declaration section, more than one program with the same name. Since the name is the same, the program’s “signature” (name, parameter list, return TYPE) must differ in some other way, usually by the number and datatype of the parameters.

14-21.

You can overload in three “locations” in PL/SQL code:

14-22.

There are several possible problems with this function:

Here is my reworking of the function:

FUNCTION status_desc (status_cd_in IN VARCHAR2) RETURN VARCHAR2
IS
   retval VARCHAR2(20);
   v_status_cd CHAR(1) := UPPER (status_cd_in);
BEGIN
   IF    v_status_cd = 'C' THEN retval := 'CLOSED';
   ELSIF v_status_cd = 'O' THEN retval := 'OPEN';
   ELSIF v_status_cd = 'A' THEN retval := 'ACTIVE';
   ELSIF v_status_cd = 'I' THEN retval := 'INACTIVE';
   END IF;
   RETURN retval;
EXCEPTION
   WHEN VALUE_ERROR THEN RETURN NULL;
END;

This new version:

14-23.

You can’t simply add a second argument with a default value in this case; the earlier calls to calc.totals in the Oracle Forms applications don’t compile, because they haven’t passed values for all the arguments. You must instead rely on overloading:

PACKAGE calc
IS
   PROCEDURE totals (
      department_id_in IN department.department_id%TYPE);

   PROCEDURE totals (
      department_id_in IN department.department_id%TYPE,
      section_id_in IN INTEGER);
END calc;

Inside an Oracle Forms application, a developer can now call calc.totals in one of two ways, as shown in these examples:

calc.totals (:emp.deptno);
calc.totals (my_dept_var, :sections.section_id);

Existing calls to calc.totals do not need to be changed; new calls to the calc.totals procedure providing a section will unambiguously be routed to the “second” implementation of the totals procedure.

14-24.

To remove the redundancies, you can use a local procedure (defined in the declaration section) to centralize all common code elements:

PROCEDURE calc_percentages (total_in IN NUMBER)
IS
   FUNCTION formatted_pct  (val_in IN NUMBER)
      RETURN VARCHAR2 IS
   BEGIN
      RETURN TO_CHAR ((val_in/total_in) * 100, '$999,999'),
   END;
BEGIN
   food_sales_stg := formatted_pct  (sales.food_sales);
   service_sales_stg := formatted_pct  (sales.service_sales);
   toy_sales_stg := formatted_pct  (sales.toy_sales);
END;

14-25.

This is a poorly designed function for a number of reasons:

Here’s one possible reworking of this function, correcting these problems:

/* Filename on web page: twovals.pkg */
CREATE OR REPLACE PACKAGE company_pkg
IS
   TYPE name_and_ind_rectype IS RECORD (
      );

    FUNCTION name_and_ind_type (
       company_id_in IN company.company_id%TYPE
       )
       RETURN name_and_ind_rectype;

END company_pkg;
/
CREATE OR REPLACE PACKAGE company_pkg
IS
   CURSOR twovals_cur (company_id_in IN company.company_id%TYPE)
   IS
       SELECT name, industry_type
         FROM company
       WHERE company_id = company_id_in;

    FUNCTION name_and_ind_type (
       company_id_in IN company.company_id%TYPE
       )
       RETURN name_and_ind_rectype
    IS
       retval name_and_ind_rectype;
    BEGIN
      OPEN twovals_cur (company_id_in);
      FETCH twovals_cur INTO retval;
      RETURN retval;
    END;
END company_pkg;
/

14-26.

The function name is awkward. To see why, try to interpret its usage as an English sentence: “If is the value ‘hello’ is in the list ‘word group’ is greater than 0, then display the message.” It doesn’t quite flow properly as a sentence, does it? The reason for the awkwardness is that the function returns the row in which the value is found (RETURN INTEGER), not whether or not the value is in the list (which would RETURN BOOLEAN). The name confuses anyone who would use the module, because the name has a Boolean or TRUE-FALSE flavor, while the actual return value is the row number.

The best rule of thumb for naming a function is this: give it the name of the value that is being returned by the function. If you do this, you can include a call to the function in-line with its calling code and have it read smoothly and properly.

14-27.

As shown in the listing, the Is_Value_In_List function returns a row number that contains a matching value. If that’s what it does, then that’s what the name should reflect. Let’s change the name accordingly, try it in the code, and see how that reads:

FUNCTION matching_row(
   the_value VARCHAR2,
   the_rg_name VARCHAR2,
   the_rg_column VARCHAR2)
RETURN NUMBER;

IF matching_row ('hello', 'word_group', 'name') > 0
THEN
   MESSAGE ('They said hello already'),
END IF;

In conversational English, this code now reads as follows: “If the row in the list ‘word group’ that contains a match for ‘hello’ is greater than 0, then display the message.” That sentence can be readily understood. The function name describes precisely what it returns, and so it fits neatly within the code that calls it.

14-28.

The function make three inappropriate uses of exceptions. The first problem is that the function uses the RAISE statement as a branching mechanism. The second is that the function misuses an exception (an error condition) as an outcome (a logical condition). The third misuse is that the exception name, Exit_Function, is simply too general.

Let’s look at each misuse in turn.

First, the function inappropriately uses the RAISE statement as a control mechanism. The first two RAISEs occur when the user has passed an invalid record group name or column name. The exception transfers control to the exception section, and the handler RETURNs 0 from the function. This is a perfectly appropriate use of the exception and exception handler. The designer of this function has made certain that the function returns a value even if there is an error. The third use of the Exit_Function exception, however, occurs as the last statement in the executable section of the function. With just a quick read of the function, I would conclude that when the function runs to completion, the last thing it does is raise an exception! That is hardly a reasonable way to end a function. The last statement of a function’s body should, as a matter of course, be a RETURN statement, since that is the whole point of the function.

Second, the function confuses an exception with an outcome. If the function reaches the last RAISE statement, then the value was not found in the record group. Consequently, the function should return a zero value. “Since the exception handler already returns zero,” the author apparently reasoned, “I might as well just raise the exception and return the zero value.” But is “value not found” an exception or just one of the possible outcomes of the function? I would argue for the latter. The value may or may not be in the group. You are calling the function to determine that fact. One of the reasons the author could end up blurring the distinction between exception and outcome is that the user-defined exception is named poorly. “Exit_Function” clearly does not describe an exception condition. Instead, it describes an action to be taken in response to certain conditions.

Third and finally, the exception name is simply too general and should be renamed to something more descriptive, for example:

invalid_name EXCEPTION;

The exception should be raised only when there is a serious problem with the record group or column name. If the author had used this name, it is less likely that he would have tried to RAISE invalid_name in order to handle the “value not in group” condition. In this case, the very name of the exception should have raised a red flag in the author’s mind. If you find yourself naming structures in ways that are not appropriate to their normal use, stop for a moment and evaluate your motives. You may well find that you are, in effect, abusing the programming language. The result of such abuse is usually code that is harder to understand, debug, and maintain.

14-29.

The effect of placing the RETURN statement inside the loop is to break out of the function as soon as a match is found. If you are going to use a FOR loop, then you should let the loop run to completion; there shouldn’t be any reason to cut it short. If you know that you may want to bail out of the loop before you reach the limit, you should not use the FOR loop construct. You should use a WHILE loop with an appropriate condition or an infinite loop—with an EXIT statement.

14-30.

The easiest way to eliminate multiple RETURNs is to set a variable (for example, ret_val) to the desired return value, and then RETURN that variable in the last statement in the program. This simple step makes a function easier to understand. For example, Is_Value_In_List has two RETURN statements—one inside the exception section and one inside the FOR loop. Ideally, a function should have only one RETURN in the executable section.

The first step in understanding a program is to identify its entry and exit points. The entry point of the function is always the first executable statement. The exit point(s) of a function are its RETURN statements or unhandled exceptions. So one of the most critical elements of a function’s structure is the proper organization of its RETURNs. They should be easy to identify and should fit naturally into the logical flow of the program.

14-31.

The Is_Value_In_List functions work properly; you get the right answer each and every time you use it. Unfortunately, the biggest challenge in programming isn’t getting the code to work; rather, it’s constructing the code so that it works today and can be easily modified so that it works tomorrow with the latest enhancements specified by your users. Is_Value_In_List simply doesn’t meet this goal.

14-32.

Is_Value_In_List has three distinct phases:

14-33.

Here is a rewrite of Is_Value_In_List that addresses the concerns raised in the previous questions:

/* Filename on web page: matchrow.sf */
FUNCTION matching_row (
   the_value VARCHAR2,
   the_rg_name VARCHAR2,
   the_rg_column VARCHAR2
   )
   RETURN INTEGER
IS
   rg_id          recordgroup;
   gc_id          groupcolumn;
   col_val        VARCHAR2(80);

   invalid_rgname EXCEPTION;

   the_rowcount   INTEGER;
   rownum         INTEGER;
   retval         INTEGER;
BEGIN
   rg_id := FIND_GROUP (the_rg_name);
   IF ID_NULL (rg_id)
   THEN
      MESSAGE (
         'Record Group ' || the_rg_name || ' does not exist.'),
      RAISE invalid_rgname;
   END IF;

   gc_id := FIND_COLUMN (the_rg_name || '.' || the_rg_column);
   IF ID_NULL (gc_id)
   THEN
      MESSAGE ('Column ' || the_rg_column || ' does not exist.'),
      RAISE invalid_rgname;
   END IF;

   rownum := 1;
   the_rowcount := get_group_row_count (rg_id);

   WHILE (retval IS NULL AND rownum < the_rowcount)
   LOOP
      col_val := get_group_char_cell (gc_id, j);
      IF UPPER (col_val) = UPPER (the_value)
      THEN
         retval := rownum;
      ELSE
         rownum := rownum + 1;
      END IF;
   END LOOP;

   RETURN retval;

EXCEPTION
   WHEN invalid_rgname
   THEN
      RETURN 0;
END;

14-34.

You can change the function to return a Boolean TRUE if the value is found, or FALSE if the value is not found in the record group. Here’s the new header and an example:

FUNCTION Is_Value_In_List(
   the_value VARCHAR2,
   the_rg_name VARCHAR2,
   the_rg_column VARCHAR2)
RETURN BOOLEAN

IF Is_Value_In_List ('hello', 'word_group', 'name')
THEN
   MESSAGE ('they said hello already'),
END IF;

Now you can read this code as follows: “If is the value ‘hello’ is in the list ‘word group’, then display the message.” It still doesn’t sound quite right; the “is” doesn’t belong. Look at the module name. It still doesn’t quite describe the return value. Instead, it reads as a partial sentence starting with “is”. The most readable Boolean name for this function is simply: value_is_in_list. Then the code is:

IF value_is_in_list ('hello', 'word_group', 'name')
THEN
   MESSAGE ('they said hello already'),
END IF;

And you can read the code as follows: “If the value ‘hello’ is in the list ‘word group’, then display the message.” Now that is a grammatically correct sentence! Unfortunately, this rewrite no longer returns a row number: it returns a Boolean, which isn’t much help if you actually want the row’s location in the group. Back to the drawing board.