Day 1: Learning the Basics of PL/SQL, Page 2
Alternatives to Retyping
Until now, you have been retyping each PL/SQL block as you tried it. If you made a mistake, you had to type the code all over again. There are some alternatives to typing PL/SQL straight into SQL*Plus. Depending on your personal preferences, and on what you are trying to do, there are three basic ways to go about this:
Cut and paste from Notepad.
Use the SQL*Plus EDIT command.
The first method involves running Windows Notepad, typing your PL/SQL code (or SQL queries) into it, and then copying and pasting from Notepad into SQL*Plus to execute the desired code. This method is ideal for experimenting with short snippets of PL/SQL code and SQL queries. You can keep several related items in the same text file where you can easily call them up when you want to work on them.
The second method makes use of a SQL*Plus command to execute a file. For example, if you have a text file named test.sql with the code from Listing 1.2, you could execute that file by typing this command:
The resulting output would look like:
The variable X = 65400
Note - When you're executing a file, the default file extension is .SQL. SQL*Plus looks for the file first in the default directory and then follows a search path that you can define. How you define this path is operating system–specific and outside the scope of this book. For details, you should consult the SQL*Plus User's Guide and also your operating system documentation.
Executing commands from a file like this is most useful in cases where you are re-creating a stored procedure, function, or database trigger and you have the definition already stored in its own text file.
The third option involves using the SQL*Plus EDIT command to invoke your system's text editor. Under Windows, this will be Notepad unless you have specifically defined a different editor. When you issue the EDIT command, SQL*Plus will launch Notepad and automatically place in it the text of the most recently executed PL/SQL block or SQL statement. See Figure 1.3 for an example of this.
Using the SQL*Plus EDIT command.
After you've brought up Notepad, you can edit the PL/SQL block to your satisfaction and then exit from Notepad, being sure to save the file. When you save your file, SQL*Plus will not immediately reexecute it. It is merely placed in an internal buffer. You must use the / command, by typing / on a line by itself, in order to execute the code you just edited.
Using the EDIT command works well as long as you keep in mind one important thing. SQL*Plus remembers only the most recent SQL statement or PL/SQL block. If you have been working on a PL/SQL block, and you execute just one SQL statement, that statement will replace the PL/SQL block you have been editing.
Caution - Do not allow the SQL*Plus buffer to contain your only copy of a long procedure. It's too easy to enter a SQL command without thinking and wipe out the much longer PL/SQL procedure you have been developing.
Which of these three methods you choose is up to you, and depends in part on your personal preferences. You are likely to find the first method, copying and pasting between Notepad and SQL*Plus, most useful during the first few chapters of this book. As you write larger PL/SQL functions and procedures, you will find yourself gravitating toward keeping each in its own file.
Writing Your First Function
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 Listing 1.3, which will create a PL/SQL function to return the value that was output by Listing 1.2.
Listing 1.3 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: /
Compare the code in Listing 1.3 to that in Listing 1.2. Notice that the keyword DECLARE has been replaced in lines 1 and 2 by the words CREATE OR REPLACE FUNCTION ss_thresh RETURN NUMBER AS. This will be explained further in Day 3. Also notice that the calls to dbms_output.put_line() have been replaced by the RETURN command (line 6), which returns the value of the variable X to the caller. The only output from Listing 1.3 is a confirmation that the function has been successfully created, which is shown in line 9.
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.
Listing 1.4 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, as shown in Listing 1.5, to do this.
Listing 1.5 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 :=.
Figure 1.4 shows the SQL*Plus screen as it would look after executing Listings 1.4 and 1.5.
Error listing for SS_THRESH.
Tip - Typing = instead of := is a common mistake to make, especially if you also program in other languages that do use = for assignment.
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; SS_THRESH --------- 72600
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;
Executing PL/SQL Using Developer 2000's Procedure Builder
Procedure Builder is part of Oracle's Developer 2000 development environment. It allows you to develop and debug PL/SQL program units for use in Developer 2000 applications. With Developer 2000, PL/SQL is used on the client to program the behavior behind the forms, reports, and menus that you develop. You end up with a PL/SQL engine on the client as well as on the server. A nice benefit of this is that Procedure Builder can be used to execute PL/SQL code without having to be connected to a database.
Note - Many of the advanced features discussed later in this book are available only when executing PL/SQL code in the database.
Starting Procedure Builder
If you have Developer 2000 installed, you start Procedure Builder by selecting Start, Programs, Developer 2000 R2.0, Procedure Builder. The opening screen is shown in Figure 1.5 and is divided into three sections.
Procedure Builder's opening screen.
As you can see, the Procedure Builder window is divided into three major parts. The Object Navigator window allows you to navigate through the various program units, PL/SQL libraries, and database objects to which you have access. The other two parts of the display combine to make up the PL/SQL Interpreter window. The top pane is used when debugging PL/SQL code and shows the code being debugged. The bottom pane is where you can type in and execute ad-hoc PL/SQL blocks.
New Term - PL/SQL may be used to write procedures, functions, package bodies, package types, and triggers. These constructs are referred to as program units.
Using Interactive PL/SQL
The PL/SQL interpreter allows you to enter a PL/SQL anonymous block and have it executed. The small block in Listing 1.2, that you typed in earlier, is one such anonymous block. You can type that block into Procedure Builder and execute it, but first you need to make one small change. The code shown in Listing 1.2 contains the following two calls to DBMS_OUTPUT:
dbms_output.put_line('The variable X = '); dbms_output.put_line(x);
DBMS_OUTPUT is a package that only exists within the database server. Procedure Builder will return errors if you try to execute the code as it stands now. Fortunately, Oracle has a package similar to DBMS_OUTPUT that can be used in its place when you are executing code on a client. The name of that package is TEXT_IO, and it also contains an entry point named PUT_LINE. Take the code shown in Listing 1.2, replace the calls to DBMS_OUTPUT.PUT_LINE with TEXT_IO.PUT_LINE, and you have the code shown in Listing 1.6. This code will run from Procedure Builder.
Listing 1.6 A PL/SQL Block Using TEXT_IO That Will Run from Procedure Builder
DECLARE x INTEGER; BEGIN x := 72600; text_io.put_line('The variable X = '); text_io.put_line(x); END;
Now, you can take this code and type it into Procedure Builder's PL/SQL Interpreter. The interpreter will automatically execute the block when you finish entering the last line. The results will look like the following:
The variable X = 72600
Procedure Builder has been written specifically to work with PL/SQL. Unlike SQL*Plus, you do not need to enter a forward-slash to tell Procedure Builder that you are done entering a block of PL/SQL.
Creating the SS_THRESH Function
Creating a function (or any other program unit such as a procedure or package) using Procedure Builder requires a bit more than just typing the CREATE FUNCTION statement into the interpreter. To create a function, you need to tell Procedure Builder that you want to create a new program unit. Do this by selecting the File, New, Program Unit menu option. You will see the dialog box shown in Figure 1.6.
Creating a New Program Unit.
This dialog contains radio buttons allowing you to choose the type of program unit that you are creating and also contains a textbox for the program unit's name. Choose Function, type the name SS_THRESH into the textbox, and click OK. You will see a screen similar to that shown in Figure 1.7.
Entering the code for SS_THRESH.
Figure 1.7 shows the function with the code already written. Of course, Procedure Builder does not write the code for you. When Procedure Builder opens this window, it places a skeleton function in the textbox. You have to fill in the details. When you get the code entered the way that you want it, click the Compile button to compile it, and then click the Close button to close the window.
To execute the function that you just created, type the following statement into the PL/SQL interpreter:
When you execute this statement, Procedure Builder will execute the function and display the following results:
Connecting to a Database
In addition to creating PL/SQL program units on the client, Procedure Builder can also be used to create and execute program units in a database. To do this, you first need to connect to a database. Use the File, Connect menu option to connect to a database. Once you've logged in, you will be able to browse database program units using the Object Navigator. Figure 1.8 shows the program units owned by the user named JEFF.
Program units in the JEFF schema.
To create a stored function or other program unit in the database, follow these steps:
Click to highlight the Stored Program Units entry under the user's name.
Click the Create Toolbar button.
Proceed as you would when creating a local program unit.
Except for having to choose the schema, the process for creating a PL/SQL function in the database is the same as for creating one locally.
Using SQLPlus Worksheet
If you have Enterprise Manager available, consider using SQLPlus Worksheet for the examples in this book. SQLPlus Worksheet is completely compatible with SQL*Plus, and can be used for all the examples in this book. The advantage that SQL*Plus worksheet has over SQL*Plus is in the interface. Rather than type in large blocks of code one line at a time, you can use a text editor-like interface. After you get the code entered the way that you want it, you can click a toolbar button to execute it.
Executing a PL/SQL Block Using SQLPlus Worksheet
Figure 1.9 shows the SQLPlus Worksheet.
The SQLPlus Worksheet.
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.
You wrote your first PL/SQL stored function, which should give you a good feel for the mechanics of programming with PL/SQL.
SQL*Plus is the tool used throughout this book for PL/SQL code examples. SQLPlus Worksheet and Procedure Builder are two other tools that may also be used to write and execute PL/SQL code.
Q&A As you can see, the SQLPlus Worksheet screen is divided into two halves. The upper half is used for the entry and editing of SQL statements and PL/SQL blocks. The lower half is used to display output. The execute toolbar button, the one with the lightning bolt, is used to execute the statements that you have entered in the upper pane.
There are two ways to use SQLPlus Worksheet to execute commands from a file. One way is to use the File, Open menu option to load the contents of a file into the upper pane, and then click the lightning bolt button. The other way is to use the Worksheet, Run Local Script menu option.
Q & A
Where does PL/SQL code execution take place?
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 Developer/2000, also have the capability to execute PL/SQL blocks locally on the client machine.
Can I write a complete application with PL/SQL?
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.
I executed some PL/SQL code which used dbms_output.put_line() to print some data, but I didn't see anything. How come?
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.
I am using Procedure Builder, and I get errors when I try to execute code that contains calls to dbms_output.put_line(). Why?
When you use Procedure Builder to execute code locally, you must use text_io.put_line rather than dbms_output.put_line(). If you are using Procedure Builder, and you have connected to a database, you will be able to execute calls to dbms_output.put_line(), but you won't see the results.
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."
What tells SQL*Plus to send your PL/SQL code to the Oracle database for execution?
What is the fundamental basis of all PL/SQL code?
List an advantage of pushing program logic up to the server level.
Name three Oracle products that use PL/SQL.
What command tells SQL*Plus to display PL/SQL output?
Name at least two options for managing your PL/SQL source code.
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.
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.
This article is brought to you by Sams Publishing, publisher of Sams Teach Yourself PL/SQL in 21 Days, Second Edition.
Page 2 of 2