Displaying the Function's Return Value

Now that you have written and compiled the function, it's time to execute it and see the results. The easiest way to do this using SQL*Plus is to issue the following SQL command:

SELECT SS_THRESH FROM DUAL;

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.

After executing the preceding SELECT command, your SQL*Plus screen should look something like that shown in Figure 1.9.

Figure 1.9.
Executing the
SS_THRESH function.

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 happens to represent the Social Security Contribution and Benefit Base. This value 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. One of the benefits of this approach is that 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 > 65400;

Summary

In this chapter you learned a little about PL/SQL, what it is, and why it is used. You know that PL/SQL is Oracle's procedural language extension to SQL, and that you can use it to write procedures and functions that execute on the server.

This chapter also explains the relationship between PL/SQL, SQL, and SQL*Plus. This should give you a good grasp of how PL/SQL fits into the larger Oracle picture.

Finally, you wrote your first PL/SQL stored function, which should give you a good feel for the mechanics of programming with PL/SQL.

Q&A

Q Where does PL/SQL code execution take place?
A Usually, execution takes place at the server level. For the examples in this book, that will always be the case. Some Oracle products, such as Oracle Forms and Developer/2000, also have the capability to execute PL/SQL blocks locally on the client machine.
Q Can I write a complete application with PL/SQL?
A Generally speaking you cannot, at least not as most people envision an application. For an end-user application, you would still need a tool, such as PowerBuilder or Developer/2000, in order to design screens and generate reports.

Q I executed some PL/SQL code which used dbms_output.put_line() to print some data, but I didn't see anything. How come?
A You probably forgot to enable the server output option. Use this SQL*Plus command:

SET SERVEROUTPUT ON

If you forget that, your PL/SQL output goes to oblivion.

Workshop

Use the following workshop to test your comprehension of this chapter and put what you've learned into practice. You'll find the answers to the quiz and exercises in Appendix A, "Answers."

Quiz

1. What tells SQL*Plus to send your PL/SQL code to the Oracle database for execution?
2. What is the fundamental basis of all PL/SQL code?
3. List an advantage of pushing program logic up to the server level.
4. Name three Oracle products that use PL/SQL.
5. What command tells SQL*Plus to display PL/SQL output?
6. Name at least two options for managing your PL/SQL source code.

Exercises

1. If you didn't encounter any errors when compiling your first function, try putting some in on purpose. Then try out the SHOW ERRORS command.
2. Try each of the three ways mentioned in the chapter for managing your source code. Become familiar with the SQL*Plus EDIT command. Try using the @ command or the START command to execute your PL/SQL code from a text file.