Learning the Basics of PL/SQL - Page 2
June 4, 2001
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.
|
