Type the command GET mypi and press Enter. The function will be loaded to your buffer and listed on the screen. Type / to execute this function. You should now see the following error message:

Warning: Function created with compilation errors.

Where do you go from here? Simply type SHOW ERRORS and press Enter. Your screen should contain the same errors as the following output:

OUTPUT

LINE/COL ERROR
-------- ----------------------------------------------------------------
2/5      PLS-00103: Encountered the symbol "RETIRN" when expecting one of
         the following:
         ( return compress compiled wrapped
         The symbol "return was inserted before "RETIRN" to continue.

6/1      PLS-00103: Encountered the symbol "END" when expecting one of
         the following:
         * & = - + ; < / > in mod not rem an exponent (**)
         <> or != or ~= >= <= <> and or like between is null is not ||

What does this tell you? You could look up Oracle help, but the error code PLS-00103 will only state that there is a syntax error when parsing the PL/SQL code. However, you can go immediately to the line and column in question.

Go back and edit mypi by typing EDIT mypi and pressing Enter. Now go to row 2, column 5. The error is at the keyword RETURN. You can see that it expected a RETURN statement but could not find any. Go ahead and make the correction.

Now go to column 6, line 1. The error message states that it encountered the END statement before finding required punctuation. When looking for errors, start working your way from the error to the top of the code. In line 6 you should see that the required ; is missing! Go ahead and make that correction, save the function, and exit.

Type GET mypi and press Enter. You always have to reload the code back into the buffer after it has been changed. Now execute the PL/SQL code to create the function by typing / and pressing Enter. If the function was successfully created, you should see the words:

Function    created.

The NULL Statement

The NULL statement is simply a statement that does nothing. Its format is simply

NULL;

Why Use the NULL Statement?

There are many reasons to use the NULL statement. One reason is to improve readability, especially if you have a block of code that does absolutely nothing. Another good reason is to use it as a placeholder. As you saw in the mypi function, you used the NULL statement to indicate that the function had no procedures, but simply returned a value.

How to Call a Function from PL/SQL With or
Without Parameters

You can call a function from PL/SQL in many ways. If there are no parameters to pass, you can simply call the function without the parentheses as you did in verifying the mypi function.

The second way is to pass actual values, using commas as placeholders for parameters that you do not want to pass (in this case DEFAULT would become the new value of the parameter). Go ahead and create the stored function squareme from Listing 5.6 to create the squared function. This function simply multiplies the number by itself.

INPUT
Listing 5.6. The stored function squareme.

CREATE OR REPLACE FUNCTION squareme(thenum number)
     RETURN NUMBER IS
BEGIN
     RETURN thenum * thenum;
END squareme;

After you have entered the function, go ahead and execute. You can now troubleshoot if there are any errors by typing the SQL command SHOW ERRORS at the SQL*Plus prompt.

You are now ready to enter a block of PL/SQL code to see actual output and the passing of parameters. Go ahead and enter and then execute the code in Listing 5.7.

INPUT
Listing 5.7. Passing parameters to squareme.

BEGIN
     DBMS_OUTPUT.PUT_LINE(`9 squared is ` || squareme(9) );
END;

OUTPUT
Your output should be

9 squared is 81

One last word on passing values to a function. Values can either be constants or variables. Remember, if no parameters are passed, make sure that you have a DEFAULT or that your code prohibits passing parameters to a function.

PL/SQL Statements

The two statements covered today are the IF statement and the FOR loop. These statements help you to control the execution of PL/SQL blocks. Tomorrow's lesson covers additional statements and loops.

The IF Statement

The IF statement allows you to evaluate one or more conditions. Some examples of IF statements are

• IF the salary is over $500,000 per year, grant the officer $2,000 in stock options.
• IF the pay_type equals Salaried, then the total hours paid is equal to 40.
• If the pay_rate is between $9.00 to $10.00 and the pay_type is Hourly, then the pay grade is level 9.

The Syntax for the IF Statement
The format of a simple IF statement is as follows:

IF <some_condition_evaluates_to_true>
THEN
<perform_statements>
END IF;

In this syntax, the first parameter some_condition_evaluates_to_true is the BOOLEAN condition you want to check. If the BOOLEAN condition evaluates to true, then the parameter perform_statements executes, which contains one or more statements.

Suppose you wanted to calculate for an hourly employee how many hours of overtime he worked during the week. Go ahead and type the IF statement from Listing 5.8.

INPUT
Listing 5.8. Calculating overtime hours with IF.

set echo on
DECLARE
  v_HoursWorked Number := 50 ; --Number of hours worked by hourly employee
  v_OverTime Number := 0 ; --Storage of Overtime Hours
BEGIN
  IF v_HoursWorked > 40 THEN

                                                         continues