The Syntax for a PL/SQL Block

DECLARE
      variable_declarations
BEGIN
   program_code
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.

The declaration section of the PL/SQL block is optional, although in practice it is unusual not to have any declarations at all.

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 semicolon at the end is the PL/SQL statement terminator, and signifies the end of the block.

Blocks such as the one shown in the syntax "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
  END;
  more program code
END;

Nesting blocks is often done for error-handling purposes. You will read more about error handling on Day 8, "Procedures, Packages, Errors, and Exceptions."

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.

1. 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.

Figure 1.2.
Initial SQL*Plus
screen.

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.

INPUT
Listing 1.1. Your first PL/SQL block.

DECLARE
  x     INTEGER;
BEGIN
  x := 65400;
END;
/

After you type the slash, SQL*Plus transmits your code to Oracle for execution. After your code executes, your SQL*Plus screen should look like Figure 1.3.

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, but for now it's enough to know that you can use the dbms_output.put_line procedure as shown in Listing 1.2.

Figure 1.3.
SQL*Plus screen after
executing a PL/SQL
block.

INPUT
Listing 1.2. PL/SQL block showing the use of the dbms_output.put_line procedure.

DECLARE
  x     INTEGER;
BEGIN
  x := 65400;
  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

1. 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.
2. Next, type in the PL/SQL code from Listing 1.2. The resulting output from SQL*Plus should look like that shown in Figure 1.4.

OUTPUT
Figure 1.4.
Producing output
with dbms_
output.put_line().

Alternatives to Retyping

Until now you have been retyping each PL/SQL block as you tried it. No doubt, if you made a mistake, you had to type the code all over again. It is possible to place your PL/SQL code in files, and depending on your personal preferences and just what you are trying to do, there are three basic ways to go about this:

• Cut and paste from Notepad.
• Execute a text file using the SQL*Plus @ command.
• Use the SQL*Plus EDIT command.