Day 5

Using Functions, IF
Statements, and Loops

by Timothy Atwood

Functions, IF statements, and loops are simple yet powerful features of PL/SQL. Today's lesson covers the following topics:

• Functions
• The NULL statement
• The IF statement
• Nested IFs
• The ELSIF statement
• FOR loops

PL/SQL Functions

As you saw in Day 1, "Learning the Basics of PL/SQL," functions are very similar to PL/SQL procedures except for the following differences:

• Functions return a value.
• Functions are used as part of an expression.

Why should you write functions? There are many reasons. The main reason is to reduce the total lines of coding and take a modular approach to writing code. You could retype in each PL/SQL block the same repetitive lines of code, or you could write a function. What if all those blocks of code had to be changed for one small reason? Just trying to find and change all these would make a COBOL programmer shudder when contemplating all the year 2000 changes! With functions, you would simply make the change in one location. Keep in mind that if the parameters to be passed to the function have changed, you still will have some editing to do within the PL/SQL blocks.

Even if you do not write your own functions, don't forget that Oracle provides you with a vast array of powerful built-in functions. However, if you do not get the opportunity to write your own functions, you will be missing out on a very powerful feature of PL/SQL.

In Day 1, you created a function called SS_THRESH, which is shown in Listing 5.1. This function simply returned a value formatted as a number with nine total digits, two of which are allocated to the decimal place. Your values would range from _9999999.99 to 9999999.99.

INPUT
Listing 5.1. The SS_THRESH function.

CREATE OR REPLACE FUNCTION ss_thresh
RETURN NUMBER AS
  x     NUMBER(9,2);
BEGIN
  x := 65400;
  RETURN x;
END;
/

The Syntax for Declaring a Function
A function is declared as follows:

FUNCTION function_name [(parameters {IN|OUT|IN OUT})]
     RETURN return_datatype
IS|AS
     <declaration statements>
BEGIN
     <executable statements>
[EXCEPTION]
     <exception handler statements>
END function_name;

In this syntax, the statements and parameters are as follows:

• function_name—The function name follows the keyword FUNCTION and follows the standard naming convention, which is covered on Day 3, "Writing Declarations and Blocks."
• parameters—Functions allow for the passing in and out of parameters. This is optional as denoted by the brackets []. The SS_THRESH function does not require parameters.
• RETURN—This is the type of data returned. You can have more than one RETURN statement in a function, but only one will be executed. One RETURN statement is required even if you return nothing or ignore what is returned.
• IS|AS—These parameters allow you to set up variables local to the function. These variables can hold values passed from the parameters or hold values assigned in the function. The variables can't be seen outside of the function.
• BEGIN—This statement starts the execution of statements pertinent to the function.
• EXCEPTION—Again, this statement is optional, but allows you to handle the PL/SQL block properly when an error occurs. If you do not address this up front, then when an error occurs, control will be passed back to the PL/SQL block with the original values instead of the values that should have been returned.
• END—This statement denotes the end of the function. The function name must follow the END statement or errors will result.

Listing 5.2 shows an example of a full function.

Listing 5.2. The emptype function.

CREATE OR REPLACE FUNCTION emptype (paytype CHAR)
     RETURN VARCHAR2 IS
BEGIN
     IF paytype = `H' THEN
          RETURN `Hourly';
     ELSIF paytype = `S' THEN
          RETURN `Salaried';
     ELSIF paytype = `E' THEN
          RETURN `Executive';
     ELSE
          RETURN `Invalid Type';
     END IF;
EXCEPTION
     WHEN OTHERS THEN
          RETURN `Error Encountered';
END emptype;
/

The function in Listing 5.2 is titled emptype. It uses parameters passed from a procedure called paytype of type CHAR. The function returns a value of type VARCHAR2, which will be Hourly, Salaried, Executive, Invalid Type, or Error Encountered. When you begin the function's statements, you use IF...ELSIF to determine text to return. IF statements are covered later in the chapter in the section "The IF Statement." If an exception occurs, the function stops processing and returns the value Error Encountered. The function is then terminated by calling the END statement followed by the function name.

Defining Formal Parameters

Parameters are a key feature of PL/SQL. A parameter is a value that you can pass from a block of statements to a function. The function then performs calculations, checks, and so on, and might or might not return a value based upon the conditions in the function. This concept is similar to functions in other third-generation languages such as C. Make sure that you code your functions to accept parameters that can be used from other areas of your PL/SQL code.

The Syntax for Defining a Parameter

parameter_name [MODE] parameter_type [:= value | DEFAULT value]

In this syntax, parameter_name is the name you assign to the parameter, and parameter_type is the variable type you assign.

The simplest parameter can be coded as

(p_squared NUMBER)

p_squared is the parameter_name, and NUMBER is the parameter_type.

But what if you want to accept a parameter from a table, and you do not want to hard-code a parameter type because it might change in the future? You can simply add a %TYPE after the parameter, and the %TYPE will pick up the field type from the table. For instance, the following line sets the parameter p_emptype to the field definition of pay_type in the table employee:

(p_emptype employee.pay_type%TYPE)

This is a common method when referencing tables and is highly recommended. There is much less rework if the type in the database changes from CHAR to VARCHAR2 than if you hard-code the type in the function and then go back and change the function, too. You can see how good functions and good coding can reduce the effort required to develop an application!

MODE
The optional MODE statement gives you complete control over your incoming parameters. Table 5.1 lists the three types of modes along with how the MODE statement operates.