Table 5.1. Types of modes.

The following includes some examples of the MODE statement:

FUNCTION addemployee(
     p_hiredate_in IN DATE,
     p_employeeID_out OUT NUMBER,
     p_hourlyrate_in_out IN OUT NUMBER,
     p_empname_in_out IN OUT varchar2)

In addemployee, you would not be able to change the hire date because it would be read-only. You would assign an employee ID in the function and write it out to this parameter. You can change the hourly rate if the initial rate was too low, and you could change the employee name if the employee gets married before being added to your database.

Assigning Values to Parameters
Sometimes you might want to assign values to parameters either by using := or DEFAULT. When using a default value, if no parameter is passed, the DEFAULT assignment is used within the function. If a value is passed, that value is used. The following has an example of assignment with both DEFAULT and :=:

p_emptype CHAR DEFAULT `H'
p_hourlyrate NUMBER := 4.25 --minimum wage

Return Types

In a function, you must declare a return datatype. The return datatype can be any datatype allowed by Oracle, such as

• CHAR
• VARCHAR2

• NUMBER
• INTEGER
• DATE
• BOOLEAN (true/false values)
• TABLE
• RECORD

As you see, you have complete flexibility in the way you process and return data from the function to the PL/SQL statements.

Exception Handling

As you saw in Listing 5.2, you coded the EXCEPTION statement. Again, this statement tells Oracle what to do if some error occurs while processing the function or procedure.

The Syntax for Exceptions

EXCEPTION
     WHEN OTHERS THEN
          <statements>

In this syntax, statements is one or more statements that will be processed when the exception occurs.

You could always code a NULL statement if no action is to be taken. This would help clarify your intent in the PL/SQL code to let others know you have thought of all conditions. NULL statements are covered later in this chapter in the section "The NULL Statement."

Creating a Stored Function

A stored function is a function saved to the database that can be called by any PL/SQL code. The only difference between a function and a stored function is the addition of the CREATE [OR REPLACE] keywords before the keyword FUNCTION. Refer to "The Syntax for Declaring a Function" earlier in this chapter for an explanation of the parameters.

The Syntax for Creating a Stored Function

CREATE [OR REPLACE]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;

You are now going to write your first stored function in PL/SQL. You will create a function that simply returns the shortened value of pi (3.14) and calls the function mypi.

The first line you will need is to identify the stored function:

CREATE OR REPLACE FUNCTION mypi

Notice that you did not have to put parentheses after the function name mypi because you do not need to pass parameters to the function. If you were calculating the diameter of a circle, you would then pass the value of the radius.

The next line requires the return type. In this case you are returning a NUMBER. The next line entered would be

RETURN NUMBER IS

You now need to start the body of your function by typing the keyword BEGIN:

BEGIN

Because you are not performing anything in this function except to return a value, you will code a NULL statement, which is discussed later in this chapter in the section "The NULL Statement," and the RETURN statement to pass the value of pi.

NULL;
RETURN 3.14;

You can now end the function by typing the keyword END followed by the function name:

END mypi;

Listing 5.3 contains the entire function that you should have just entered. Review the listing to make sure you have typed the lines in correctly.

INPUT
Listing 5.3. The mypi function.

CREATE OR REPLACE FUNCTION mypi
    RETURN NUMBER IS
BEGIN
     NULL;
     RETURN 3.14;
END mypi; --end of mypi function

OUTPUT
When you type / to execute the code, you should see the following Oracle output:

PL/SQL procedure successfully completed.

You can now call the function mypi from any PL/SQL statements.

To prove that the process worked, you can write a miniprocedure to see the value of pi. Enter the code in Listing 5.4 and type / to execute the PL/SQL block.

INPUT
Listing 5.4. Verifying the mypi function.

BEGIN
     DBMS_OUTPUT.PUT_LINE(`value of pi is ` || mypi);
END;
/

By using the SET SERVEROUTPUT ON statement with the DBMS_OUTPUT.PUT_LINE, you can send variables to the console (screen). Your output should have been

OUTPUT

value of pi is 3.14

Finding Errors

In Day 1, you learned how to use the EDIT command directly from Oracle. Instead of entering the code line by line, you can now practice using the EDIT command. For learning how to debug compilation errors, I am going to make extensive use of the built-in editor. If you haven't done so already, you are going to enter the mypi function into the editor, with planned errors.

To start the process, simply type EDIT mypi and press Enter. Because the SQL code does not exist, Oracle will prompt you to create a new file. Go ahead and click Yes. When the edit screen appears, enter the code exactly as it appears in Listing 5.5.

INPUT
Listing 5.5. A poorly written function.

CREATE OR REPLACE FUNCTION mypi
     RETIRN NUMBER IS
BEGIN
     NULL;
     RETURN 3.14
END

After you have entered the function, click File and then Exit, and when it asks you if you want to save changes, click Yes. You are now ready to execute this poorly written function.