Perhaps one of the most useful things you can do with your knowledge of PL/SQL is to use it to write stored functions and stored procedures. Encapsulating the code you wrote earlier into a stored function enables you to compile it once and store it in the database for future use. The next time you want to run that PL/SQL block, all you need to do is invoke the function. Using SQL*Plus, type in the input code shown in the following List, which will create a PL/SQL function to return the value that was output by.

The SS_THRESH Function

1: CREATE OR REPLACE FUNCTION ss_thresh

2: RETURN NUMBER AS

3: x NUMBER;

4: BEGIN

5: x := 72600;

6: RETURN x;

7: END;

8: /

Function created

Notice that Oracle has created the function. SQL*Plus indicates this by displaying the words Function created.

Finding Compilation Errors

You probably were able to type in the code from Listing 1.3 and create the SS_THRESH function with no errors. However, that might not have been the case. To show you how to deal with an error, Listing 1.4 contains the same code as Listing 1.3, but with one small error.

The SS_THRESH Function with an Error

1: CREATE OR REPLACE FUNCTION ss_thresh

2: RETURN NUMBER AS

3: x NUMBER;

4: BEGIN

5: x = 72600;

6: RETURN x;

7: END;

8: /

Warning: Function created with compilation errors.

Unlike most compilers, which will display a listing of errors found in source code, Oracle stores any errors it finds in a database table named USER_ERRORS. If you want to see the specific details, and you may well, you need to retrieve the error listing yourself. Use the SQL*Plus command SHOW ERRORS to do this.

The SHOW ERRORS Command

1: SHOW ERRORS

Errors for FUNCTION SS_THRESH:

LINE/COL ERROR

-------- ---------------------------------------------------------------

5/5 PLS-00103: Encountered the symbol "=" when expecting one of the

following:

:= . ( @ % ;

The symbol ":= was inserted before "=" to continue.

As you can see, the error listing has two columns of output. The first column contains the line number where the error occurred and also the character position within that line. The second column contains the specific error message. In this example, the error occurred in line 5 at the fifth character position. The error message tells you that Oracle encountered an equal sign when it was really expecting something else. That "something else," in this case, is the assignment operator, represented by :=.

The SS_THRESH function does not have any parameters, so be sure not to add any parentheses when you call it. In other words, don't use SS_THRESH() because Oracle will return an error. The table DUAL is a special Oracle table that always exists, always has exactly one row, and always has exactly one column. It's the perfect table to use when experimenting with functions. Selecting the function from the DUAL table causes the function result to be displayed.

Can Even This Simple Function Be Useful?

The SS_THRESH function is a very simple function, and you might rightly wonder if something so absurdly simple can be useful. The value this function returns is the Social Security Contribution and Benefit Base, a value that changes from year to year. If you were a programmer working on a payroll system and needed to write several queries using this value, you could use a function like this to encapsulate this information. To encapsulate information means to embed it within a function so that values like this don't need to be replicated all through your code, and so that any changes can be made in one central place. There's another benefit to this approach. Your queries become more self-documenting. It's a bit easier to remember six months later what you meant when you see

SELECT * FROM employee_table

WHERE emp_salary > SS_THRESH;

than if you had simply hard-coded the value

SELECT * FROM employee_table

WHERE emp_salary > 72600;