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.
refers to program code that gets triggered in the event of a runtime error or
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
DECLAREis 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
DECLAREand come before
ENDkeywords delimit the procedural portion of the block. This is where the code goes. The
EXCEPTIONkeyword 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 PL/SQL Block Showing the Use of the
DECLARE x NUMBER; BEGIN x := 72600; dbms_output.put_line('The variable X = '); dbms_output.put_line(x); END; /
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 ONcommand, or you won't see any output. You also can use the
SET SERVEROUTPUT OFFcommand to turn off output when you don't want to see it.