14-1.

What is the difference between a procedure and function?

14-2.

What are the four sections in a procedure or function? Which of these sections are optional, and which are required?

14-3.

What statement do you use to return a value from within a function? Can you use this same statement in a procedure?

14-4.

Write a procedure that displays “hello world!” on your monitor.

14-5.

Write a function that returns the string “hello world!”

14-6.

How many RETURNs can you place in your function?

14-7.

Which of the following function headers are valid, and which cause compile errors?

14-8.

What are the different parameter modes available in PL/SQL?

14-9.

Can you provide default values for parameters?

14-10.

Are parameters in PL/SQL passed by value or by reference?

14-11.

How many RETURNs should the executable section of a function contain?

14-12.

Should you include RETURNs in your function’s exception section?

14-13.

Should the following function have an exception section? If so, what should it handle?

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;
END deptname;
/

14-14.

What are the restrictions on setting default values for parameters?

14-15.

What advantage is there to providing default values for parameters?

14-16.

Suppose that five years ago, a developer wrote a procedure with the following header:

PROCEDURE calc_totals (
   department_id_in IN department.department_id%TYPE)

There are many calls to this procedure in a variety of production applications. A new requirement has now been requested by users to be able to calculate totals per department and also for sections within departments. How would you change the header of calc_totals so that:

14-17.

Can you skip over parameters when you call a procedure or function?

14-18.

Given the header for the calc_profit procedure:

PROCEDURE calc_profit
   (company_id_in IN NUMBER,
    profit_out OUT NUMBER
    fiscal_year_in IN NUMBER,
    profit_type_in IN VARCHAR2 := 'NET_PROFITS',
    division_in IN VARCHAR2 := 'ALL_DIVISIONS')

Which of the following calls are valid:

14-19.

Suppose that I have the following procedure header:

PROCEDURE calc_totals (
   id_in IN department.department_id%TYPE,
   total_type_in IN VARCHAR2 := 'ALLREV',
   quarter_in IN INTEGER DEFAULT 1,
   currency_in IN VARCHAR2
   )

and I want to call calc_totals, specifying values for only the first, third, and fourth arguments, relying on the default value for the second argument. Which of the following calls to calc_totals meet that requirement?

14-20.

What does it mean to overload a procedure?

14-21.

In what parts of PL/SQL code can you overload a program?

14-22.

Identify changes you would make to improve the structure, performance, and functionality of the following program. Rewrite the function to incorporate your improvements.

FUNCTION status_desc (status_cd_in IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
   IF    status_cd_in = 'C' THEN RETURN 'CLOSED';
   ELSIF status_cd_in = 'O' THEN RETURN 'OPEN';
   ELSIF status_cd_in = 'A' THEN RETURN 'ACTIVE';
   ELSIF status_cd_in = 'I' THEN RETURN 'INACTIVE';
   END IF;
END;

14-23.

Suppose that five years ago, a developer wrote a packaged procedure with the following header:

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

There are many calls to this procedure (stored in the server database) in a variety of production applications written in Oracle Forms. Client-side Oracle Forms PL/SQL code does not recognize default values in parameters lists of stored code. A new requirement has now been requested by users to be able to calculate totals per department and also for sections within departments. How would you change the implementation of the calc package to satisfy these requirements?

14-24.

Improve the following procedure:

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

14-25.

Improve the structure, performance, and functionality of the following function:

FUNCTION company_name (
   company_id_in IN company.company_id%TYPE,
   industry_type_out OUT VARCHAR2
   )
   RETURN VARCHAR2
IS
   cname company.company_id%TYPE;
   found_it EXCEPTION;
BEGIN
   SELECT name, industry_type
     INTO cname, industry_type_out
     FROM company
    WHERE company_id = company_id_in;
   RAISE found_it;
EXCEPTION
   WHEN found_it THEN RETURN cname;
END;

14-26.

The remainder of this chapter is a case study of a function published by Oracle Corporation in its Oracle Forms documentation. Now, I don’t mean to pick on Oracle Corporation. In fact, I find that in general their documentation—including their sample code fragments—is outstanding and improved beyond words over earlier manuals. To show just how far they’ve come, we’ll look at a function in the Oracle Forms Reference Manual, Volume 1.

The manual presents a function called Is_Value_In_List, which returns the row number of the specified value if it is found in the record group (or “list”). Here is the Oracle Corporation-suggested implementation for such a function (reproduced as is, but without the comments):

/* Filename on web page: isvalinlis.sql */
FUNCTION Is_Value_In_List( the_value VARCHAR2,
               the_rg_name VARCHAR2,
               the_rg_column VARCHAR2)
RETURN NUMBER IS
   the_rowcount   NUMBER;
   rg_id          RECORDGROUP;
   gc_id          GROUPCOLUMN;
   col_val        VARCHAR2(80);
   Exit_Function  EXCEPTION;
BEGIN
   rg_id := Find_Group( the_rg_name );

   If Id_Null(rg_id) THEN
      Message ('Record Group '||the_rg_name||' does not exist.'),
      RAISE Exit_Function;
   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 Exit_Function;
   END IF;
   the_rowcount := Get_Group_Row_Count( rg_id );
   FOR j IN 1..the_rowcount LOOP
      col_val := Get_Group_Char_Cell( gc_id, j );
      IF UPPER(col_val) = UPPER(the_value) THEN
         RETURN j;
      END IF;
   END LOOP;
   RAISE Exit_Function;

EXCEPTION
   WHEN Exit_Function THEN
      RETURN 0;
END;

Here’s how you would use the function in another program:

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

As the first question in the case study, critique this usage. Do you find the module name particularly descriptive or appropriate? To help you answer this question, begin by expressing the condition in the IF statement as an English sentence.

14-27.

Rewrite the function header so that its name more accurately reflects what it returns. Provide an example of how it would be used in another program, and apply the “sentence” test.

14-28.

The function uses exceptions in several inappropriate ways. Explain how.

14-29.

What’s the effect of the RETURN statement inside the FOR loop?

14-30.

How could you redesign the function so that it has only one RETURN statement?

14-31.

The Is_Value_In_List function works properly, yet has a serious long-term problem. Identify the problem.

14-32.

You have been asked to rewrite Is_Value_In_List. Provide an outline, in pseudo-code, of your algorithm.

14-33.

Rewrite the Is_Value_In_List function. Be sure to address the problems you identified in earlier questions.

14-34.

Rewrite the function so that it returns a Boolean value, and provide an example of how it would be used in another program. Express the function’s usage in a sentence. Is this any better?