Learning the Basics of PL/SQL - Page 2

Getting Started with PL/SQL

By now you should have a basic understanding of what PL/SQL is and how it relates to other Oracle products. You should have access to an Oracle database environment either at work or at home. During the rest of this chapter, you will learn some of the basics of PL/SQL, and you will write your first Oracle stored function.

PL/SQL Is Block Structured

New Term - PL/SQL is referred to as a block structured language A PL/SQL block is a syntactical unit that might contain program code, variable declarations, error handlers, procedures, functions, and even other PL/SQL blocks.

The Syntax for a PL/SQL Block

DECLARE
 variable_declarations
BEGIN
 program_code
EXCEPTION
 exception_handlers
END;

In this syntax, variable_declarations are any variables that you might want to define. Cursor definitions and nested PL/SQL procedures and functions are also defined here. program_code refers to the PL/SQL statements that make up the block. exception_handlers refers to program code that gets triggered in the event of a runtime error or exception.

The declaration section of a PL/SQL block is optional, although in practice it is unusual not to have any declarations at all. The exception handler portion of a PL/SQL block is also optional, and you won't see much of it until Day 7, "Procedures, Packages, Errors, and Exceptions."

Note - When you're defining PL/SQL functions, procedures, and triggers, the keyword DECLARE is not used. When defining a function, the function specification, or function header as it is sometimes called, begins the block. Similarly, procedure and trigger specifications begin procedure and trigger blocks. Function, procedure, and trigger blocks are covered in more detail on Day 2, "Writing Declarations and Blocks."

New Term - Any variable declarations must immediately follow DECLARE and come before BEGIN. The BEGIN and END keywords delimit the procedural portion of the block. This is where the code goes. The EXCEPTION keyword signifies the end of the main body of code, and begins the section containing exception handling code. The semicolon at the end of the block, and at the end of each statement, is the PL/SQL statement terminator, and signifies the end of the block.

Tip - Omitting the semicolon at the end of a block is a common oversight. Leave it off, and you'll get a syntax error. Remember to include it and you will save yourself lots of aggravation.

Blocks such as the one shown in "The Syntax for a PL/SQL Block" form the basis for all PL/SQL programming. An Oracle stored procedure consists of one PL/SQL block. An Oracle stored function consists of one PL/SQL block. An Oracle database trigger consists of one PL/SQL block. It is not possible to execute PL/SQL code except as part of a block.

PL/SQL blocks can be nested. One block can contain another block as in the following example:

DECLARE
 variable declarations go here
BEGIN
 some program code
 BEGIN
  code in a nested block
 EXCEPTION
  exception_handling_code
 END;
 more program code
END;

Nesting of blocks is often done for error-handling purposes. You will read more about error handling on Day 7.

Compiling and Executing a Simple Block

Are you ready to try writing your first PL/SQL code? Good. Remember that for this and all other examples in this book, you will be using SQL*Plus to send the PL/SQL code to the Oracle database for execution.

Begin by running SQL*Plus and connecting to your Oracle database. Your initial SQL*Plus screen should look like the one shown in Figure 1.2.
Next, type in the following lines of code from Listing 1.1 exactly as shown. Notice the slash at the end. It must be typed in as well, exactly as shown.

Initial SQL*Plus screen.

Listing 1.1 Your First PL/SQL Block

DECLARE
 x   NUMBER;
BEGIN
 x := 72600;
END;
/

Tip - The slash at the end tells SQL*Plus that you are done typing PL/SQL code. SQL*Plus will then transmit that code to the Oracle database for execution. The slash has meaning to SQL*Plus only, not to PL/SQL.

Tip - The slash character must be typed on a line by itself, and it must be the first character on that line; otherwise, it will get sent to the database and generate an error message.

After you type the slash, SQL*Plus transmits your code to Oracle for execution. After your code executes, your output should look like the following:

declare
 x  integer;
begin
 x := 65400;
end;
/

PL/SQL procedure successfully completed

The code you just executed was probably not very exciting, possibly because there was no output. PL/SQL does have some limited output facilities, and next you will learn how to produce some simple screen output.

What About Some Output?

When it was originally designed, PL/SQL had no output facilities at all. Remember that PL/SQL is not a standalone language. It is almost always used in conjunction with some other program or tool that handles the input, output, and other user interaction.

Oracle now includes the DBMS_OUTPUT package with PL/SQL, which provides you with some limited output capabilities. You will learn more about packages during Day 8, "Using SQL," but for now it's enough to know that you can use the dbms_output.put_line procedure as shown in Listing 1.2.

Listing 1.2 PL/SQL Block Showing the Use of the `dbms_output.put_line` Procedure

DECLARE
 x   NUMBER;
BEGIN
 x := 72600;
 dbms_output.put_line('The variable X = ');
 dbms_output.put_line(x);
END;
/

The dbms_output.put_line() procedure takes exactly one argument and generates a line of text as output from the database server. In order for you to see that line of text, you must tell SQL*Plus to display it. This is done with the SQL*Plus command:

SQL> SET SERVEROUTPUT ON

Type the preceding command now. It needs to be executed only once per session, so you won't need to reissue it unless you exit SQL*Plus and get back in again.
Next, type in the PL/SQL code from Listing 1.2. The resulting output from SQL*Plus should look like that shown below.
```
The variable x=
72600
```

Note - It is SQL*Plus that prints the server output on the screen for you to see. You must remember to execute the SET SERVEROUTPUT ON command, or you won't see any output. You also can use the SET SERVEROUTPUT OFF command to turn off output when you don't want to see it.