PL/SQL User's Guide and Reference

Contents Index Home Previous Next

Lexical Units

A line of PL/SQL text contains groups of characters known as lexical units, which can be classified as follows:

For example, the line

bonus := salary * 0.10;  -- compute bonus

contains the following lexical units:

To improve readability, you can separate lexical units by spaces. In fact, you must separate adjacent identifiers by a space or punctuation. For example, the following line is illegal because the reserved words END and IF are joined:

IF x > y THEN high := x; ENDIF;  -- illegal

However, you cannot embed spaces in lexical units except for string literals and comments. For example, the following line is illegal because the compound symbol for assignment (:=) is split:

count : = count + 1;  -- illegal

To show structure, you can divide lines using carriage returns and indent lines using spaces or tabs. Compare the following IF statements for readability: 

IF x>y THEN max:=x;ELSE max:=y;END IF;     |     IF x > y THEN
                                           |         max := x;
                                           |     ELSE
                                           |         max := y;
                                           |     END IF;

Delimiters

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. For example, you use delimiters to represent arithmetic operations such as addition and subtraction.

Simple Symbols

Simple symbols consist of one character; a list follows:

+ addition operator
- subtraction/negation operator
* multiplication operator
/ division operator
= relational operator
< relational operator
> relational operator
( expression or list delimiter
) expression or list delimiter
; statement terminator
% attribute indicator
, item separator
. component selector
@ remote access indicator
' character string delimiter
" quoted identifier delimiter
: host variable indicator

Compound Symbols

Compound symbols consist of two characters; a list follows:

** exponentiation operator
<> relational operator
!= relational operator
~= relational operator
^= relational operator
<= relational operator
>= relational operator
:= assignment operator
=> association operator
.. range operator
|| concatenation operator
<< (beginning) label delimiter
>> (ending) label delimiter
- - single-line comment indicator
/* (beginning) multi-line comment delimiter
*/ (ending) multi-line comment delimiter

Identifiers

You use identifiers to name PL/SQL program objects and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages. Some examples of identifiers follow:

X
t2
phone#
credit_limit
LastName
oracle$number

An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are illegal, as the following examples show:

mine&yours    -- illegal ampersand
debit-amount  -- illegal hyphen
on/off        -- illegal slash
user id       -- illegal space

The next examples show that adjoining and trailing dollar signs, underscores, and number signs are legal:

money$$$tree
SN## 
try_again_

You can use upper, lower, or mixed case to write identifiers. PL/SQL is not case sensitive except within string and character literals. So, if the only difference between identifiers is the case of corresponding letters, PL/SQL considers the identifiers to be the same, as the following example shows: 

lastname
LastName  -- same as lastname
LASTNAME  -- same as lastname and LastName

The length of an identifier cannot exceed 30 characters. But, every character, including dollar signs, underscores, and number signs, is significant. For example, PL/SQL considers the following identifiers to be different: 

lastname
last_name

Identifiers should be descriptive. So, use meaningful names such as credit_limit and cost_per_thousand. Avoid obscure names such as cr_lim and cpm.

Reserved Words

Some identifiers, called reserved words, have a special syntactic meaning to PL/SQL and so should not be redefined. For example, the words BEGIN and END, which bracket the executable part of a block or subprogram, are reserved. As the next example shows, if you try to redefine a reserved word, you get a compilation error: 

DECLARE
   end BOOLEAN;  -- illegal; causes compilation error

However, you can embed reserved words in an identifier, as the following example shows:

DECLARE
   end_of_game BOOLEAN;  -- legal

Often, reserved words are written in upper case to promote readability. However, like other PL/SQL identifiers, reserved words can be written in lower or mixed case. For a list of reserved words, see Appendix E.

Predefined Identifiers

Identifiers globally declared in package STANDARD, such as the exception INVALID_NUMBER, can be redeclared. However, redeclaring predefined identifiers is error prone because your local declaration overrides the global declaration.

Quoted Identifiers

For flexibility, PL/SQL lets you enclose identifiers within double quotes. Quoted identifiers are seldom needed, but occasionally they can be useful. They can contain any sequence of printable characters including spaces but excluding double quotes. Thus, the following identifiers are legal: 

"X+Y"
"last name"
"on/off switch"
"employee(s)"
"*** header info ***"

The maximum length of a quoted identifier is 30 characters not counting the double quotes.

Using PL/SQL reserved words as quoted identifiers is allowed but not recommended. It is poor programming practice to reuse reserved words.

Some PL/SQL reserved words are not reserved by SQL. For example, you can use the PL/SQL reserved word TYPE in a CREATE TABLE statement to name a database column. But, if a SQL statement in your program refers to that column, you get a compilation error, as the following example shows: 

SELECT acct, type, bal INTO ...  -- causes compilation error

To prevent the error, enclose the uppercase column name in double quotes, as follows:

SELECT acct, "TYPE", bal INTO ...

The column name cannot appear in lower or mixed case (unless it was defined that way in the CREATE TABLE statement). For example, the following statement is invalid:

SELECT acct, "type", bal INTO ...  -- causes compilation error

Alternatively, you can create a view that renames the troublesome column, then use the view instead of the base table in SQL statements.

Literals

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal 147 and the Boolean literal FALSE are examples.

Numeric Literals

Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. An integer literal is an optionally signed whole number without a decimal point. Some examples follow: 

030   6   -14   0   +32767

A real literal is an optionally signed whole or fractional number with a decimal point. Several examples follow:

6.6667   0.0   -12.0   3.14159   +8300.00   .5   25.

PL/SQL considers numbers such as 12.0 and 25. to be reals even though they have integral values.

Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Simply suffix the number with an E (or e) followed by an optionally signed integer. A few examples follow: 

2E5   1.0E-7   3.14159e0   -1E38   -9.5e-3

E stands for "times ten to the power of." As the next example shows, the number after E is the power of ten by which the number before E must be multiplied:

5E3 = 5 X 103 = 5 X 1000 = 5000

The number after E also corresponds to the number of places the decimal point shifts. In the last example, the implicit decimal point shifted three places to the right; in the next example, it shifts three places to the left:

5E-3 = 5 X 10-3 = 5 X 0.001 = 0.005

Character Literals

A character literal is an individual character enclosed by single quotes (apostrophes). Several examples follow:

'Z'   '%'   '7'   ' '   'z'   '('

Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals 'Z' and 'z' to be different.

The character literals '0' .. '9' are not equivalent to integer literals, but can be used in arithmetic expressions because they are implicitly convertible to integers.

String Literals

A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotes. Several examples follow: 

'Hello, world!'
'XYZ Corporation'
'10-NOV-91'
'He said "Life is like licking honey from a thorn."'
'$1,000,000'

All string literals except the null string ('') have datatype CHAR.

Given that apostrophes (single quotes) delimit string literals, how do you represent an apostrophe within a string? As the next example shows, you write two single quotes, which is not the same as writing a double quote: 

'Don''t leave without saving your work.'

PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:

'baker'
'Baker'

Boolean Literals

Boolean literals are the predefined values TRUE and FALSE and the non-value NULL, which stands for a missing, unknown, or inapplicable value. Remember, Boolean literals are values, not strings. For example, TRUE is no less a value than the number 25.

Comments

The PL/SQL compiler ignores comments, but you should not. Adding comments to your program promotes readability and aids understanding. Generally, you use comments to describe the purpose and use of each code segment. PL/SQL supports two comment styles: single-line and multi-line.

Single-Line

Single-line comments begin with a double hyphen (- -) anywhere on a line and extend to the end of the line. A few examples follow: 

-- begin processing
SELECT sal INTO salary FROM emp  -- get current salary
   WHERE empno = emp_id;
bonus := salary * 0.15;  -- compute bonus amount

Notice that comments can appear within a statement at the end of a line.

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can "comment-out" the line: 

-- DELETE FROM emp WHERE comm IS NULL;

Multi-line

Multi-line comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines. An example follows: 

/* Compute a 15% bonus for top-rated employees. */
IF rating > 90 THEN
   bonus := salary * 0.15 /* bonus is based on salary */
ELSE
   bonus := 0;
END IF;

The next three examples illustrate some popular formats:

/* The following line computes the area of a circle using pi, 
   which is the ratio between the circumference and diameter. */
area := pi * radius**2;

/**************************************************************
 * The following line computes the area of a circle using pi, * 
 * which is the ratio between the circumference and diameter. * 
 **************************************************************/
area := pi * radius**2;

/*
  The following line computes the area of a circle using pi,
  which is the ratio between the circumference and diameter.
*/
area := pi * radius**2;

You can use multi-line comment delimiters to comment-out whole sections of code, as the following example shows:

/* 
OPEN c1;
LOOP
   FETCH c1 INTO emp_rec;
   EXIT WHEN c1%NOTFOUND;
   ...
END LOOP;   CLOSE c1; 
*/

Restrictions

You cannot nest comments. Also, you cannot use single-line comments in a PL/SQL block that will be processed dynamically by an Oracle Precompiler program because end-of-line characters are ignored. As a result, single-line comments extend to the end of the block, not just to the end of a line. So, use multi-line comments instead.

Contents Index Home Previous Next