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. |
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.sqlHello 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.sqlUsing 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.sqlUsing 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