Previous | Table of Contents | Next

Page 187


Type Character Description
Expressions and Lists (used in statements, data type declarations, parameter list declarations, variable and table references) := Assignment
( Start List or Subexpression
) End List or Subexpression
, Separates List Items (as in parameter lists)
.. Range Operator (used in FOR-IN loops)
|| String Concatenation
=> Association (used in parameter lists)
; Statement End
% Cursor Attribute or Object Type
. Member Specifier for Qualified References
@ Remote Database Indicator
` Start/End of Character String
" Start/End of Quoted Identifier
: Host Variable Indicator
& Bind Variable Indicator
Comments and Labels -- Single Line Comment
/* Start Multiline Comment
*/ End Multiline Comment
<< Start Label
>> End Label

Notice that there are several ways you can say "Not Equal" in PL/SQL. For consistency, one form (!=) is used throughout this tutorial. In case you were wondering, Modulo arithmetic is handled by a built-in function.

NOTE
Comments cannot be nested. Anything typed after a single-line comment is ignored. Anything typed between multiline comment delimiters is ignored.

PL/SQL's Block Structure

The PL/SQL block is the fundamental programming construct. Programming in blocks lends itself to a top-down, structured modularity and a straightforward, logical organization.

An unnamed PL/SQL block has three sections: the Declaration and Body sections, and optionally, the Exception section.

DECLARE
  -- declarations
BEGIN
  -- executable code
EXCEPTION
  -- exception handlers
END;

Actually, the Declaration section is optional, too, but you can't do much more than some one-liners without declaring variables! All user-defined variables, constants, data types, cursors,

Page 188

functions, and procedures are declared in the Declaration section. If none are to be defined, you may omit this section.

For example, consider the familiar `Hello, World!' example used in many programming language books (see Listing 10.1).

NOTE
Refer to Chapter 8, "SQL*Plus For Administrators," if you want more information on the SET statement.

Listing 10.1 hello.sql—Hello World Program

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.enable;
    DBMS_OUTPUT.put_line(`Hello, World!');
END;
/

Line 1 tells SQL*Plus to write out whatever the server returns to it.

Lines 2 and 5 provide the scope of the current block.

Line 3 turns on the outputting mechanism.

Line 4 prints the string `Hello, World!'.

Line 6 executes the unnamed PL/SQL block.

The server responds with:

Hello, World!
PL/SQL procedure successfully completed.
SQL>

Nothing is returned to the screen until just after the procedure completes. This is because the server is processing the PL/SQL block (most probably across the network) and it returns any messages only when it is completely finished.

Literal strings are always enclosed in single quotes. Before we go on, let's talk a little bit about literals.

Literals Literals are fixed strings, numbers, and Boolean values. They cannot be modified at runtime; they are strictly read-only values. Single-character and string literals are specified with enclosing single quotes. Here are some examples of string literals:

Page 189

Character and string literals are treated as a CHAR type (fixed-length character string). It can be assigned to any CHAR or VARCHAR2 variable. PL/SQL is only case sensitive within a string literal.

A numeric literal can have any integer or floating-point value, for example:

     12345     integer literal

     -12345.0     floating-point literal

     12345.67890     floating-point literals can have any arbitrary precision

     100.     this is a floating-point literal, too, with zero precision

     1.2345E2     you can use scientific notation!

     1.2345E-2

     0.12345 or .12345     leading zero is optional

It is good programming practice to declare constants to contain literal values that are used throughout a program. You can give them meaningful names. Additionally, they are more maintainable; if a literal value must be changed, it need only be modified in one place. This technique avoids the unmaintainable "magic number" syndrome, where special values are used that nobody can remember why. Listing 10.2 shows an example.

Listing 10.2 circle.sql—Using Constants to Make Code More Maintainable

DECLARE
  PI CONSTANT REAL := 3.14159265359;
  circumference REAL;
  area REAL;
  radius REAL := &Radius;
BEGIN
  circumference := PI * radius * 2.0;
  area := PI * radius**2;
  DBMS_OUTPUT.put_line(`Radius = ` || TO_CHAR(radius) ||
                       `, Circumference = ` || TO_CHAR(circumference) ||
                       `, Area = ` || TO_CHAR(area));
END;
/

When you run this program, SQL*Plus first prompts you for a value for the bind variable specified with the ampersand (line 5). Here's what happens:

Enter value for radius: 3.5
old   5:   radius REAL := &Radius;
new   5:   radius REAL := 3.5;

Page 190

Radius = 3.5, Circumference = 21.99114857513, Area = 38.4845100064775
PL/SQL procedure successfully completed.

Notice that the bind variable is not a PL/SQL variable; it is a placeholder for the value you must supply. Also, it's not necessary to turn on the server's output again, or re-enable output, if you've been following along since the first example. Once these things are turned on, they stay on for the duration of your session (unless you deliberately turn them off or reconnect).

Note how the constant variable PI is specified and receives its value in its declaration. You could also use the keyword DEFAULT in place of the assignment operator ":="; it means the same thing in the context of a declaration.

The Boolean literals TRUE and FALSE are also supported (note they are not enclosed in single quotes). They can be used in any Boolean expression and assigned to any Boolean variable.

The truth table in Table 10.2 illustrates all binary Boolean combinations for the relational operators AND, OR, and XOR (eXclusive OR) and unary negation (NOT or ~) for the values TRUE, FALSE, and NULL. The relational operator XOR is not supported in PL/SQL; however, the PL/SQL built-in function XOR() performs this operation.

Table 10.2 Trilogic Truth Table for AND, OR, XOR, NOT


p q p AND q p OR q p XOR q NOT p
TRUE TRUE TRUE TRUE FALSE FALSE
TRUE FALSE FALSE TRUE TRUE FALSE
TRUE NULL NULL TRUE NULL FALSE
FALSE TRUE FALSE TRUE TRUE TRUE
FALSE FALSE FALSE FALSE FALSE TRUE
FALSE NULL FALSE NULL NULL TRUE
NULL TRUE NULL TRUE NULL NULL
NULL FALSE FALSE NULL NULL NULL
NULL NULL NULL NULL NULL NULL

As an example, take a look at Listing 10.3.

Listing 10.3 booly.sql—Using Booleans

DECLARE
  T CONSTANT BOOLEAN NOT NULL := TRUE;  -- must have a value
  x BOOLEAN;
  y BOOLEAN;
BEGIN  -- illustrate Booleans
  x := 1 = 2;  -- this expression evaluates to false

Previous | Table of Contents | Next