 |
|
|
|
|
|
Programmers have long struggled with the power and limitations of SQL. Although SQL is very
appropriate for manipulating data, there is more to an application than simply using data. Oracle
introduced PL/SQL as a procedural language you can use to implement logic that goes beyond
the reach of SQL.
PL/SQL executes in the Oracle database engine and is tightly tied to the database. Oracle PL/SQL
has been in use for years, and entire enterprise strength applications (such as much of Oracle’s
own application suite) have been written in it.
PL/SQL is one of two languages used to implement logic in the database. Since Oracle 8i, Java has
also been available for implementing logic in the database, including database triggers.
This chapter introduces the basic features of the PL/SQL language.
This chapter is broken down into three main sections:
1. PL/SQL architecture
2. The language of PL/SQL
3. Controlling program flow
Over the next several chapters, you will learn how to use PL/SQL to implement application and
database logic. As a programmer, many of the capabilities of PL/SQL will be familiar to you and
you will be able to reuse techniques and tricks that you have learned from your language of choice.
Code Architecture
Any programming language presents itself in two guises—the syntax that is used to create the
application logic with the code and the actual execution of that code as a running application. This
section introduces the basic block structure of the PL/SQL language. A section at the end of this
chapter reviews how PL/SQL program units are executed at run time.
PL/SQL uses a block structure for organizing PL/SQL code. The block structure is shown in Figure 15-1.There are four distinct areas within a PL/SQL block: the block header, the declaration section, the execution
section, and the exception section. Each of these is described in the sections that follow.
The Block Header
The header section of a PL/SQL program unit contains the type of unit, the name of the unit, and any
parameters that are used to call the unit. The syntax for this section of the PL/SQL block is as follows:
PROGRAM_TYPE name ([parameter IN/OUT/IN OUT parm_type specs,] . . .)
[RETURN datatype]
PROGRAM_TYPE can be either FUNCTION, PROCEDURE, or PACKAGE. A PL/SQL function returns a value,
which is specified with the RETURN data type clause. The datatype variable describes the data type of
the returned value. Any PL/SQL function can be used in any standard SQL statement, which gives you a
lot of power and flexibility in your development efforts.
A PL/SQL function can use the RETURN keyword at any point in the execution section, followed by a
value. Once the runtime PL/SQL engine encounters a RETURN, it immediately ends the execution of the
function and returns the value.
A PL/SQL procedure does not use the RETURN keyword to return a value directly, but a PL/SQL procedure
can exchange information with the calling program through parameters. A PL/SQL package is a
collection of program units, which are covered in detail in Chapter 16, “PL/SQL and SQL.”
All of these program units can have parameters. A parameter is used to pass information to the PL/SQL
unit and get information back from the unit. Each parameter has an assigned data type (parm_type) and
is designated as an IN parameter, which is passed to the program unit, an OUT parameter, which is sent
back to the calling program, or an IN OUT parameter, which travels both ways. The specs portion of the
parameter can include the keyword NOT NULL, which forces this program unit to be called with a value
for the parameter, or DEFAULT, followed by a value, which assigns a default value for the parameter if a
value is not passed by the calling program.
Block header
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
BEGIN
326
Chapter 15
The block header is actually an optional portion of a PL/SQL program unit. If you create a PL/SQL program
unit that does not have a block header, it is called an anonymous block. The limitation of an anonymous
block is that you cannot call it from another section of code. You may choose to use this type of
block as a nested block—one that only operates in the context of a block that contains the nested block.
Typically, though, this expedient solution is roughly equivalent to writing code without documentation,
a practice that tends to haunt either you or the poor soul who is assigned to maintain the undocumented
code that you wrote. However, there may be times when you want to use a nested anonymous block for
more control over error handling, as described in an upcoming section.Parameter Notation
When calling a PL/SQL program unit with parameters, you can include the values for the parameters in
one of two ways:
? Positional notation, where each parameter value is listed in the order the parameters are
included in the header. If you do not want to pass a value for one or more of the parameters,
you simply include a comma to hold its position.
? Named notation, where you list the name of the parameter, followed by => and the value, as in
the following:
anyParam => ‘value of parm’,
When using named notation, you must include a value for all NOT NULL parameters that do not specify a
default value. It is good practice to include any parameters that require a value—which are NOT NULL
and do not have a default value—at the beginning of a list of parameters, to avoid forcing people to use
named notation or to specify all values until the required parameter.
Overloading
You may be able to increase the simplicity of your code and your efficiency by using a technique called
overloading with PL/SQL program units. Overloading takes advantage of the fact that a PL/SQL program
unit is identified by a unique combination of its name and the variables that it accepts.
You can have the same procedure or function called with different sets of parameters, as long as the signature
of those parameters is unique. The parameter signature is made up of the parameter position and
data type.
As an example, suppose that you wanted a PL/SQL function to return a number with the geographic
territory a customers resides in. You could overload a function to accept either a state abbreviation, a city
name and state abbreviation, or a zip code. Depending on the parameters passed, the appropriate version
of the function would be called and executed.
The code for the header section of each of these three procedures would look like the three examples
following:
FUNCTION returnTerritory (state_abbreviation IN VARCHAR2) RETURN NUMBER
FUNCTION returnTerritory (city_name IN VARCHAR2, state_abbreviation IN VARCHAR2)
RETURN NUMBER
FUNCTION returnTerritory (zp_code IN NUMBER) RETURN NUMBERSapereOnline |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
