Basic PLSQL Language Elements

Basic PL/SQL Language Elements

The raw materials for a PL/SQL program are its lexical elements: comments, identifiers, operators, and functions. We'll briefly examine these individual bits and pieces before seeing how they fit together into a functioning program.

Comments

There are two types of comments in PL/SQL: single-line and multiline comments. Single-line comments begin with two dash marks.

 -- This is a comment 

Multiline comments are identical to Java multiline comments. (They are called multiline comments because they can span multiple lines, but they can also be limited to a single-line.) They begin with a slash-asterisk (/*) and end with an asterisk-slash (*/):

 /* This is a multiline    comment.  */ 

Comments can begin anywhere on a line, but the single-line comment, obviously, must be the last (or only) text on a line.

Identifiers

The main use of identifiers in PL/SQL is for variable names. These are the rules that an identifier must follow:

• Must begin with a letter, followed by letters, numbers, dollar signs, hash marks, or underscores.

• Not case-sensitive: MYVAR, myvar, and myVar will be interpreted as referring to the same variable.

• An identifier cannot be a PL/SQL-reserved word, such as BEGIN or END.

• Can be enclosed in double quotes, in which case none of the restrictions above apply. For example, the variable "BEGIN" is a valid name and is different than "begin". Using quoted identifiers isn't recommended but is sometimes necessary.

• Can be up to 30 characters long.

Identifiers can also be used as labels. These labels can be used with GOTO statements or to label loops. A label is enclosed by double angle brackets and must precede an executable statement. In the following example, we use NULL, which is a valid executable statement that does nothing, to meet this last requirement when the GOTO statement would otherwise be the last executable statement in the PL/SQL block:

   /* ... */   IF USER_INPUT = 'QUIT' THEN GOTO END_PROGRAM;   /* ... */ <<END_PROGRAM>> NULL; 

Operators

The operators in PL/SQL should be familiar, but they are just different enough to cause occasional trouble for someone familiar with Java or C/C++. The two most notable differences are that, in PL/SQL, the assignment operator is (:=) and the equality operator is (=). Consider this example:

 IF X = Y THEN   Z := X; END IF; 

Fortunately, mistaking one of these for the other or attempting to use (==) will not go unnoticed by the PL/SQL engine.

Assignment Operator

The assignment operator assigns a value to a variable. The value can be either a constant or an expression:

Operator	Example
:=	MYVAR := 100;

String Concatenation Operator

Strings can be concatenated in PL/SQL using the concatenation operator.

Operator	Example
||	MYSTR := 'ABC' || 'XYZ';

Comparison Operators

Comparison operators compare two values and return a Boolean value, TRUE or FALSE, depending on the result of the comparison.

Operator	Description
=	Equal
!=, <>, ~=, ^=	Not equal
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to

Notice that we have a choice of inequality operators. It's best to choose one and stick to it.

The result of a comparison can be used immediately as a condition for a control statement, such as IF or WHILE:

 WHILE X > Y LOOP   /* program statements */ END LOOP; 

The value resulting from a comparison can also be assigned to a Boolean variable. For example, assuming MYBOOL was declared as a BOOLEAN, we can make the following assignment:

 MYBOOL = X > Y; 

Logical Operators

The logical operators are used with Boolean values. Boolean values, such as those resulting from comparisons, can be combined using the logical operators AND and OR. A Boolean value can be negated using the logical operator NOT.

Operator	Description
AND	Logical AND
OR	Logical OR
NOT	Logical NOT

These operators can be used to construct arbitrarily complex conditions. For example, assuming MYFLAG is a Boolean type, we can assign it a TRUE or FALSE value with the following logical expression:

 MYFLAG =  NOT (STARTCHAR = ENDCHAR) OR CHARSREAD > 0; 

Note that AND, OR, and NOT correspond to the Java operators (&&), (||), and (!), respectively.

SQL Operators and Functions

PL/SQL also supports the SQL comparison operators, such as the LIKE operator for comparing strings with the wildcard characters (_) and (%). See Chapter 3 for a more detailed description and examples of these operators.

Operator	Description
LIKE	String comparison
IS [NOT] NULL	Tests for null value
IN	Test for inclusion in set
BETWEEN…AND	Range test

All SQL functions are also available under PL/SQL, except for aggregate functions such as COUNT() and SUM(). For example, assuming MYSTR is a string (VARCHAR2 or CHAR), we can convert it to all uppercase using the UPPER() function:

 MYSTR := UPPER(MYSTR);