Basic PL/SQL Language ElementsThe 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. CommentsThere 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. IdentifiersThe main use of identifiers in PL/SQL is for variable names. These are the rules that an identifier must follow:
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; OperatorsThe 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 OperatorThe assignment operator assigns a value to a variable. The value can be either a constant or an expression:
String Concatenation OperatorStrings can be concatenated in PL/SQL using the concatenation operator.
Comparison OperatorsComparison operators compare two values and return a Boolean value, TRUE or FALSE, depending on the result of the comparison.
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 OperatorsThe 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.
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 FunctionsPL/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.
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); |