2 PL/SQL Language Fundamentals

Reserved Words and Keywords

Both reserved words and keywords have special meaning in PL/SQL. The difference between reserved words and keywords is that you cannot use reserved words as identifiers. You can use keywords as as identifiers, but it is not recommended.

Trying to redefine a reserved word causes a compilation error. For example:

SQL> DECLARE
  2    end BOOLEAN;
  3  BEGIN
  4    NULL;
  5  END;
  6  /
  end BOOLEAN;
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin was inserted before "END" to continue.
ORA-06550: line 5, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted
 
SQL> 

The PL/SQL reserved words are listed in Table D-1.

Keywords also have special meaning in PL/SQL, but you can redefine them (this is not recommended). The PL/SQL keywords are listed inTable D-2.

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 valid:

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

The maximum size of a quoted identifier is 30 characters not counting the double quotes. Though allowed, using PL/SQL reserved words as quoted identifiers is a poor programming practice.

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. For example:

030   6   -14   0   +32767

A real literal is an optionally signed whole or fractional number with a decimal point. For example:

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.

A numeric literal value that is composed only of digits and falls in the range -2147483648 to 2147483647 has a PLS_INTEGER data type; otherwise this literal has the NUMBER data type. You can add the f of d suffix to a literal value that is composed only of digits to specify the BINARY_FLOAT or BINARY_TABLE respectively. For the properties of the data types, see Predefined PL/SQL Numeric Data Types and Subtypes.

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. For example:

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

xEy stands for "x times ten to the power of y." As the next example shows, the number after E is the power of ten by which the number before E is multiplied (the double asterisk (**) is the exponentiation operator):

5E3 = 5 * 10**3 = 5 * 1000 = 5000

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

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

The absolute value of a NUMBER literal can be in the range 1.0E-130 up to (but not including) 1.0E126. The literal can also be 0. For information about results outside the valid range, see NUMBER Data Type.

SQL> DECLARE
  2    n NUMBER;
  3  BEGIN
  4    n := -9.999999E-130;
  5    n :=  9.999E125;
  6    n := 10.0E125;
  7  END;
  8  /
  n := 10.0E125;
       *
ERROR at line 6:
ORA-06550: line 6, column 8:
PLS-00569: numeric overflow or underflow
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
 
SQL> 

Real literals can also use the trailing letters f and d to specify the types BINARY_FLOAT and BINARY_DOUBLE, as shown in Example 2-2.

SQL> DECLARE
  2    x BINARY_FLOAT := sqrt(2.0f);
  3      -- single-precision floating-point number
  4    y BINARY_DOUBLE := sqrt(2.0d);
  5      -- double-precision floating-point number
  6  BEGIN
  7    NULL;
  8  END;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Character Literals

A character literal is an individual character enclosed by single quotes ('). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. For example:

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

PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals 'Z' and 'z' to be different. Also, 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. All string literals except the null string ('') have data type CHAR. For example:

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

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

'baker'
'Baker'

To represent an apostrophe within a string, you can write two single quotes, which is not the same as writing a double quote:

'I''m a string, you''re a string.'

You can also use the following notation to define your own delimiter characters for the literal. You choose a character that is not present in the string, and then need not escape other single quotation marks inside the literal:

-- q'!...!' notation allows use of single quotes inside literal
string_var := q'!I'm a string, you're a string.!';

You can use delimiters [, {, <, and (, pair them with ], }, >, and ), pass a string literal representing a SQL statement to a subprogram, without doubling the quotation marks around 'INVALID' as follows:

func_call(q'[SELECT index_name FROM user_indexes
  WHERE status ='INVALID']');

For NCHAR and NVARCHAR2 literals, use the prefix nq instead of q, as in the following example, where 00E0 represents the character é:

where_clause := nq'#WHERE COL_VALUE LIKE '%\00E9'#';

For more information about the NCHAR data type and unicode strings, see Oracle Database Globalization Support Guide.

BOOLEAN Literals

BOOLEAN literals are the predefined values TRUE, FALSE, and NULL. NULL 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.

Date and Time Literals

Datetime literals have various formats depending on the data type, as in Example 2-3.

SQL> DECLARE
  2    d1 DATE      := DATE '1998-12-25';
  3    t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01';
  4  
  5    t2 TIMESTAMP WITH TIME ZONE :=
  6       TIMESTAMP '1997-01-31 09:26:56.66 +02:00';
  7  
  8    -- Three years and two months
  9    -- For greater precision, use the day-to-second interval
 10  
 11    i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH;
 12  
 13    -- Five days, four hours, three minutes, two and 1/100 seconds
 14  
 15     i2 INTERVAL DAY TO SECOND :=
 16       INTERVAL '5 04:03:02.01' DAY TO SECOND;
 17  
 18  BEGIN
 19    NULL;
 20  END;
 21  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Single-Line Comments

A single-line comment begins with --. It can appear anywhere on a line, and it extends to the end of the line, as in Example 2-4.

SQL> DECLARE
  2    howmany     NUMBER;
  3    num_tables  NUMBER;
  4  BEGIN
  5    -- Begin processing
  6    SELECT COUNT(*) INTO howmany
  7      FROM USER_OBJECTS
  8        WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables
  9     num_tables := howmany;          -- Compute some other value
 10  END;
 11  /
 
PL/SQL procedure successfully completed.
 
SQL> 

While testing or debugging a program, you might want to disable a line of code by making it a comment. For example:

-- DELETE FROM employees WHERE comm_pct IS NULL

Multiline Comments

A multiline comments begins with /*, ends with */, and can span multiple lines, as in Example 2-5. You can use multiline comment delimiters to "comment out" sections of code.

SQL> DECLARE
  2     some_condition  BOOLEAN;
  3     pi              NUMBER := 3.1415926;
  4     radius          NUMBER := 15;
  5     area            NUMBER;
  6  BEGIN
  7    /* Perform some simple tests and assignments */
  8    IF 2 + 2 = 4 THEN
  9      some_condition := TRUE;
 10      /* We expect this THEN to always be performed */
 11    END IF;
 12    /* The following line computes the area of a circle using pi,
 13    which is the ratio between the circumference and diameter.
 14    After the area is computed, the result is displayed. */
 15    area := pi * radius**2;
 16    DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area));
 17  END;
 18  /
The area is: 706.858335
 
PL/SQL procedure successfully completed.
 
SQL>

Aggregate Assignment

A %ROWTYPE declaration cannot include an initialization clause, but there are two ways to assign values to all fields of a record at once:

• If their declarations refer to the same table or cursor, you can assign one record to another, as in Example 2-17.

• Use the SELECT or FETCH statement to assign a list of column values to a record.

  The column names must appear in the order in which they were defined in the CREATE TABLE or CREATE VIEW statement that created the referenced table or view. There is no constructor for a record type, so you cannot assign a list of column values to a record by using an assignment statement.

SQL> DECLARE
  2    dept_rec1  departments%ROWTYPE;
  3    dept_rec2  departments%ROWTYPE;
  4  
  5    CURSOR c1 IS SELECT department_id, location_id
  6      FROM departments;
  7  
  8    dept_rec3 c1%ROWTYPE;
  9    dept_rec4 c1%ROWTYPE;
 10  
 11  BEGIN
 12    dept_rec1 := dept_rec2;  -- declarations refer to same table
 13    dept_rec3 := dept_rec4;  -- declarations refer to same cursor
 14    dept_rec2 := dept_rec3;
 15  END;
 16  /
  dept_rec2 := dept_rec3;
               *
ERROR at line 14:
ORA-06550: line 14, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 14, column 3:
PL/SQL: Statement ignored
 
SQL> 

Example 2-18 uses the SELECT INTO statement to assign a list of column values to a record.

SQL> DECLARE
  2    dept_rec departments%ROWTYPE;
  3  BEGIN
  4    SELECT * INTO dept_rec
  5      FROM departments
  6        WHERE department_id = 30
  7          AND ROWNUM < 2;
  8  END;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Using Aliases

Select-list items fetched from a cursor associated with %ROWTYPE must have simple names or, if they are expressions, must have aliases, such as complete_name in Example 2-19.

SQL> BEGIN
  2    FOR item IN
  3      (SELECT (first_name || ' ' || last_name) complete_name
  4        FROM employees
  5           WHERE ROWNUM < 11
  6      ) LOOP
  7        DBMS_OUTPUT.PUT_LINE
  8          ('Employee name: ' || item.complete_name);
  9      END LOOP;
 10  END;
 11  /
Employee name: Ellen Abel
Employee name: Sundar Ande
Employee name: Mozhe Atkinson
Employee name: David Austin
Employee name: Hermann Baer
Employee name: Shelli Baida
Employee name: Amit Banda
Employee name: Elizabeth Bates
Employee name: Sarah Bell
Employee name: David Bernstein
 
PL/SQL procedure successfully completed.
 
SQL> 

Order of Evaluation

As with all operators, the order of evaluation for logical operators is determined by the operator precedence shown in Table 2-2, and can be changed by parentheses, as in Example 2-37. (Example 2-37 invokes the print_boolean procedure created in Example 2-34.)

SQL> DECLARE
  2    x  BOOLEAN := FALSE;
  3    y  BOOLEAN := FALSE;
  4  
  5  BEGIN
  6    print_boolean ('NOT x AND y', NOT x AND y);
  7    print_boolean ('NOT (x AND y)', NOT (x AND y));
  8    print_boolean ('(NOT x) AND y', (NOT x) AND y);
  9  END;
 10  /
NOT x AND y = FALSE
NOT (x AND y) = TRUE
(NOT x) AND y = FALSE
 
PL/SQL procedure successfully completed.
 
SQL>

Short-Circuit Evaluation

When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. This lets you write expressions that might otherwise cause errors.

In Example 2-38, short-circuit evaluation prevents the expression in line 8 from causing an error.

SQL> DECLARE
  2    on_hand  INTEGER := 0;
  3    on_order INTEGER := 100;
  4  BEGIN
  5    -- Does not cause divide-by-zero error;
  6    -- evaluation stops after first expression
  7  
  8    IF (on_hand = 0) OR ((on_order / on_hand) < 5) THEN
  9      DBMS_OUTPUT.PUT_LINE('On hand quantity is zero.');
 10    END IF;
 11  END;
 12  /
On hand quantity is zero.
 
PL/SQL procedure successfully completed.
 
SQL> 

When the value of on_hand is zero, the left operand yields TRUE, so PL/SQL does not evaluate the right operand. If PL/SQL evaluated both operands before applying the OR operator, the right operand would cause a division by zero error.

Short-circuit evaluation applies to IF statements, CASE statements, and CASE expressions in PL/SQL.

Comparison Operators

Comparison operators compare one expression to another. The result is always either TRUE, FALSE, OR NULL. Typically, you use comparison operators in conditional control statements and in the WHERE clauses of SQL data manipulation statements.

The comparison operators are:

• The relational operators summarized in Table 2-4

• IS NULL Operator

• LIKE Operator

• BETWEEN Operator

• IN Operator

Example 2-39 invokes the print_boolean procedure created in Example 2-34 to print values of some expressions that include relational operators.

SQL> BEGIN
  2    print_boolean ('(2 + 2 =  4)', 2 + 2 = 4);
  3  
  4    print_boolean ('(2 + 2 <> 4)', 2 + 2 <> 4);
  5    print_boolean ('(2 + 2 != 4)', 2 + 2 != 4);
  6    print_boolean ('(2 + 2 ~= 4)', 2 + 2 ~= 4);
  7    print_boolean ('(2 + 2 ^= 4)', 2 + 2 ^= 4);
  8  
  9    print_boolean ('(1 < 2)', 1 < 2);
 10  
 11    print_boolean ('(1 > 2)', 1 > 2);
 12  
 13    print_boolean ('(1 <= 2)', 1 <= 2);
 14  
 15    print_boolean ('(1 >= 1)', 1 >= 1);
 16  END;
 17  /
(2 + 2 =  4) = TRUE
(2 + 2 <> 4) = FALSE
(2 + 2 != 4) = FALSE
(2 + 2 ~= 4) = FALSE
(2 + 2 ^= 4) = FALSE
(1 < 2) = TRUE
(1 > 2) = FALSE
(1 <= 2) = TRUE
(1 >= 1) = TRUE
 
PL/SQL procedure successfully completed.
 
SQL>

SQL> DECLARE
  2  
  3    PROCEDURE compare (
  4      value   VARCHAR2,
  5      pattern VARCHAR2
  6    ) IS
  7    BEGIN
  8      IF value LIKE pattern THEN
  9        DBMS_OUTPUT.PUT_LINE ('TRUE');
 10      ELSE
 11        DBMS_OUTPUT.PUT_LINE ('FALSE');
 12      END IF;
 13    END;
 14  
 15  BEGIN
 16    compare('Johnson', 'J%s_n');
 17    compare('Johnson', 'J%S_N');
 18  END;
 19  /
TRUE
FALSE
 
PL/SQL procedure successfully completed.
 
SQL> 

SQL> DECLARE
  2  
  3    PROCEDURE half_off (sale_sign VARCHAR2) IS
  4    BEGIN
  5      IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN
  6        DBMS_OUTPUT.PUT_LINE ('TRUE');
  7      ELSE
  8        DBMS_OUTPUT.PUT_LINE ('FALSE');
  9      END IF;
 10    END;
 11  
 12  BEGIN
 13    half_off('Going out of business!');
 14    half_off('50% off!');
 15  END;
 16  /
FALSE
TRUE
 
PL/SQL procedure successfully completed.
 
SQL> 

SQL> BEGIN
  2    print_boolean ('2 BETWEEN 1 AND 3', 2 BETWEEN 1 AND 3);
  3    print_boolean ('2 BETWEEN 2 AND 3', 2 BETWEEN 2 AND 3);
  4    print_boolean ('2 BETWEEN 1 AND 2', 2 BETWEEN 1 AND 2);
  5    print_boolean ('2 BETWEEN 3 AND 4', 2 BETWEEN 3 AND 4);
  6  END;
  7  /
2 BETWEEN 1 AND 3 = TRUE
2 BETWEEN 2 AND 3 = TRUE
2 BETWEEN 1 AND 2 = TRUE
2 BETWEEN 3 AND 4 = FALSE
 
PL/SQL procedure successfully completed.
 
SQL> 

SQL> DECLARE
  2    letter VARCHAR2(1) := 'm';
  3  BEGIN
  4    print_boolean (
  5      'letter IN (''a'', ''b'', ''c'')',
  6      letter IN ('a', 'b', 'c')
  7    );
  8  
  9    print_boolean (
 10      'letter IN (''z'', ''m'', ''y'', ''p'')',
 11      letter IN ('z', 'm', 'y', 'p')
 12    );
 13  END;
 14  /
letter IN ('a', 'b', 'c') = FALSE
letter IN ('z', 'm', 'y', 'p') = TRUE
 
PL/SQL procedure successfully completed.
 
SQL> 

SQL> DECLARE
  2    a INTEGER; -- Initialized to NULL by default
  3    b INTEGER := 10;
  4    c INTEGER := 100;
  5  BEGIN
  6    print_boolean ('100 IN (a, b, c)', 100 IN (a, b, c));
  7    print_boolean ('100 NOT IN (a, b, c)', 100 NOT IN (a, b, c));
  8  
  9    print_boolean ('100 IN (a, b)', 100 IN (a, b));
 10    print_boolean ('100 NOT IN (a, b)', 100 NOT IN (a, b));
 11  
 12    print_boolean ('a IN (a, b)', a IN (a, b));
 13    print_boolean ('a NOT IN (a, b)', a NOT IN (a, b));
 14  END;
 15  /
100 IN (a, b, c) = TRUE
100 NOT IN (a, b, c) = FALSE
100 IN (a, b) = NULL
100 NOT IN (a, b) = NULL
a IN (a, b) = NULL
a NOT IN (a, b) = NULL
 
PL/SQL procedure successfully completed.
 
SQL> 

BOOLEAN Arithmetic Expressions

You can use the relational operators to compare numbers for equality or inequality. Comparisons are quantitative; that is, one number is greater than another if it represents a larger quantity. For example, given the assignments:

number1 := 75;
number2 := 70;

The following expression is true:

number1 > number2

In general, do not compare real numbers for exact equality or inequality. Real numbers are stored as approximate values. For example, the following IF condition might not yield TRUE:

DECLARE
   fraction BINARY_FLOAT := 1/3;
BEGIN
   IF fraction = 11/33 THEN
      DBMS_OUTPUT.PUT_LINE('Fractions are equal (luckily!)');
   END IF;
END;
/

BOOLEAN Character Expressions

You can compare character values for equality or inequality. By default, comparisons are based on the binary values of each byte in the string. For example, given the assignments:

string1 := 'Kathy';
string2 := 'Kathleen';

The following expression is true:

string1 > string2

By setting the initialization parameter NLS_COMP=ANSI, you can make comparisons use the collating sequence identified by the NLS_SORT initialization parameter. A collating sequence is an internal ordering of the character set in which a range of numeric codes represents the individual characters. One character value is greater than another if its internal numeric value is larger. Each language might have different rules about where such characters occur in the collating sequence. For example, an accented letter might be sorted differently depending on the database character set, even though the binary value is the same in each case.

Depending on the value of the NLS_SORT parameter, you can perform comparisons that are case-insensitive and even accent-insensitive. A case-insensitive comparison still returns true if the letters of the operands are different in terms of uppercase and lowercase. An accent-insensitive comparison is case-insensitive, and also returns true if the operands differ in accents or punctuation characters. For example, the character values 'True' and 'TRUE' are considered identical by a case-insensitive comparison; the character values 'Cooperate', 'Co-Operate', and 'coöperate' are all considered the same. To make comparisons case-insensitive, add _CI to the end of your usual value for the NLS_SORT parameter. To make comparisons accent-insensitive, add _AI to the end of the NLS_SORT value.

There are semantic differences between the CHAR and VARCHAR2 base types that come into play when you compare character values. For more information, see Differences Between CHAR and VARCHAR2 Data Types.

Many types can be converted to character types. For example, you can compare, assign, and do other character operations using CLOB variables. For details on the possible conversions, see PL/SQL Data Type Conversion.

BOOLEAN Date Expressions

You can also compare dates. Comparisons are chronological; that is, one date is greater than another if it is more recent. For example, given the assignments:

date1 := '01-JAN-91';
date2 := '31-DEC-90';

The following expression is true:

date1 > date2

Guidelines for BOOLEAN Expressions

It is a good idea to use parentheses when doing comparisons. For example, the following expression is not allowed because 100 < tax yields a BOOLEAN value, which cannot be compared with the number 500:

100 < tax < 500  -- not allowed

The debugged version follows:

(100 < tax) AND (tax < 500)

You can use a BOOLEAN variable itself as a condition; you need not compare it to the value TRUE or FALSE. In Example 2-45, the loops are equivalent.

SQL> DECLARE
  2    done BOOLEAN;
  3  BEGIN
  4    -- The following WHILE loops are equivalent
  5  
  6    done := FALSE;
  7    WHILE done = FALSE
  8    LOOP
  9      done := TRUE;
 10    END LOOP;
 11  
 12    done := FALSE;
 13    WHILE NOT (done = TRUE)
 14    LOOP
 15      done := TRUE;
 16    END LOOP;
 17  
 18    done := FALSE;
 19    WHILE NOT done
 20    LOOP
 21      done := TRUE;
 22    END LOOP;
 23  END;
 24  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Simple CASE Expression

A simple CASE expression selects a result from one or more alternatives, and returns the result. Although it contains a block that might stretch over several lines, it really is an expression that forms part of a larger statement, such as an assignment or a subprogram call. The CASE expression uses a selector, an expression whose value determines which alternative to return.

A CASE expression has the form illustrated in Example 2-46. The selector (grade) is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The first WHEN clause that matches the value of the selector determines the result value, and subsequent WHEN clauses are not evaluated. If there are no matches, then the optional ELSE clause is performed.

SQL> DECLARE
  2    grade CHAR(1) := 'B';
  3    appraisal VARCHAR2(20);
  4  BEGIN
  5    appraisal :=
  6      CASE grade
  7        WHEN 'A' THEN 'Excellent'
  8        WHEN 'B' THEN 'Very Good'
  9        WHEN 'C' THEN 'Good'
 10        WHEN 'D' THEN 'Fair'
 11        WHEN 'F' THEN 'Poor'
 12        ELSE 'No such grade'
 13      END;
 14    DBMS_OUTPUT.PUT_LINE
 15      ('Grade ' || grade || ' is ' || appraisal);
 16  END;
 17  /
Grade B is Very Good
 
PL/SQL procedure successfully completed.
 
SQL>

The optional ELSE clause works similarly to the ELSE clause in an IF statement. If the value of the selector is not one of the choices covered by a WHEN clause, the ELSE clause is executed. If no ELSE clause is provided and none of the WHEN clauses are matched, the expression returns NULL.

Searched CASE Expression

A searched CASE expression lets you test different conditions instead of comparing a single expression to various values. It has the form shown in Example 2-47.

A searched CASE expression has no selector. Each WHEN clause contains a search condition that yields a BOOLEAN value, so you can test different variables or multiple conditions in a single WHEN clause.

SQL> DECLARE
  2    grade      CHAR(1) := 'B';
  3    appraisal  VARCHAR2(120);
  4    id         NUMBER  := 8429862;
  5    attendance NUMBER := 150;
  6    min_days   CONSTANT NUMBER := 200;
  7  
  8    FUNCTION attends_this_school (id NUMBER)
  9     RETURN BOOLEAN IS
 10    BEGIN
 11      RETURN TRUE;
 12    END;
 13  
 14  BEGIN
 15    appraisal :=
 16      CASE
 17        WHEN attends_this_school(id) = FALSE
 18          THEN 'Student not enrolled'
 19        WHEN grade = 'F' OR attendance < min_days
 20          THEN 'Poor (poor performance or bad attendance)'
 21        WHEN grade = 'A' THEN 'Excellent'
 22        WHEN grade = 'B' THEN 'Very Good'
 23        WHEN grade = 'C' THEN 'Good'
 24        WHEN grade = 'D' THEN 'Fair'
 25        ELSE 'No such grade'
 26      END;
 27    DBMS_OUTPUT.PUT_LINE
 28      ('Result for student ' || id || ' is ' || appraisal);
 29  END;
 30  /
Result for student 8429862 is Poor (poor performance or bad attendance)
 
PL/SQL procedure successfully completed.
 
SQL>

The search conditions are evaluated sequentially. The BOOLEAN value of each search condition determines which WHEN clause is executed. If a search condition yields TRUE, its WHEN clause is executed. After any WHEN clause is executed, subsequent search conditions are not evaluated. If none of the search conditions yields TRUE, the optional ELSE clause is executed. If no WHEN clause is executed and no ELSE clause is supplied, the value of the expression is NULL.

NULL Values and the NOT Operator

Applying the logical operator NOT to a null yields NULL. Therefore, the following two IF statements are not always equivalent:

SQL> DECLARE
  2    x    INTEGER := 2;
  3    Y    INTEGER := 5;
  4    high INTEGER;
  5  BEGIN
  6    IF x > y THEN high := x;
  7    ELSE high := y;
  8    END IF;
  9  
 10    IF NOT x > y THEN high := y;
 11    ELSE high := x;
 12    END IF;
 13  END;
 14  /
 
PL/SQL procedure successfully completed.
 
SQL> 

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same value to high. However, if either x or y is null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high.

NULL Values and Zero-Length Strings

PL/SQL treats any zero-length string like a NULL value. This includes values returned by character functions and BOOLEAN expressions. For example, the following statements assign nulls to the target variables:

SQL> DECLARE
  2     null_string  VARCHAR2(80) := TO_CHAR('');
  3     address      VARCHAR2(80);
  4     zip_code     VARCHAR2(80) := SUBSTR(address, 25, 0);
  5     name         VARCHAR2(80);
  6     valid        BOOLEAN      := (name != '');
  7  BEGIN
  8    NULL;
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL> 

Use the IS NULL operator to test for null strings, as follows:

IF v_string IS NULL THEN ...

NULL Values and the Concatenation Operator

The concatenation operator ignores null operands. For example:

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE ('apple' || NULL || NULL || 'sauce');
  3  END;
  4  /
applesauce
 
PL/SQL procedure successfully completed.
 
SQL> 

NULL Values as Arguments to Built-In Functions

If a NULL argument is passed to a built-in function, a NULL value is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be NULL. If a search is successful, the corresponding result is returned. In Example 2-50, if the column manager_id is NULL, DECODE returns the value 'nobody'.

SQL> DECLARE
  2    manager  VARCHAR2(40);
  3    name     employees.last_name%TYPE;
  4  BEGIN
  5    -- NULL is a valid argument to DECODE.
  6    -- In this case, manager_id is NULL
  7    -- and the DECODE function returns 'nobody'.
  8  
  9    SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name
 10      INTO manager, name
 11        FROM employees
 12          WHERE employee_id = 100;
 13  
 14    DBMS_OUTPUT.PUT_LINE
 15      (name || ' is managed by ' || manager);
 16  END;
 17  /
King is managed by nobody
 
PL/SQL procedure successfully completed.
 
SQL>

The function NVL returns the value of its second argument if its first argument is NULL. In Example 2-51, if the column specified in the query is NULL, the function returns the value -1 to signify a nonexistent employee in the output.

SQL> DECLARE
  2    manager employees.manager_id%TYPE;
  3    name    employees.last_name%TYPE;
  4  BEGIN
  5    -- NULL is a valid argument to NVL.
  6    -- In this case, manager_id is null
  7    -- and the NVL function returns -1.
  8  
  9    SELECT NVL(manager_id, -1), last_name
 10      INTO manager, name
 11        FROM employees
 12          WHERE employee_id = 100;
 13  
 14     DBMS_OUTPUT.PUT_LINE
 15       (name || ' is managed by employee Id: ' || manager);
 16  END;
 17  /
King is managed by employee Id: -1
 
PL/SQL procedure successfully completed.
 
SQL>

The function REPLACE returns the value of its first argument if its second argument is NULL, whether the optional third argument is present or not. For example, the call to REPLACE in Example 2-52 does not make any change to the value of old_string.

SQL> DECLARE
  2    string_type  VARCHAR2(60);
  3    old_string   string_type%TYPE := 'Apples and oranges';
  4    v_string     string_type%TYPE := 'more apples';
  5  
  6    -- NULL is a valid argument to REPLACE,
  7    -- but does not match anything,
  8    -- so no replacement is done.
  9  
 10    new_string string_type%TYPE := REPLACE(old_string, NULL, v_string);
 11  BEGIN
 12    DBMS_OUTPUT.PUT_LINE('Old string = ' || old_string);
 13    DBMS_OUTPUT.PUT_LINE('New string = ' || new_string);
 14  END;
 15  /
Old string = Apples and oranges
New string = Apples and oranges
 
PL/SQL procedure successfully completed.
 
SQL>

If its third argument is NULL, REPLACE returns its first argument with every occurrence of its second argument removed. For example, the call to REPLACE in Example 2-53 removes all the dashes from dashed_string, instead of changing them to another character.

SQL> DECLARE
  2    string_type  VARCHAR2(60);
  3    dashed       string_type%TYPE := 'Gold-i-locks';
  4  
  5    -- When the substitution text for REPLACE is NULL,
  6    -- the text being replaced is deleted.
  7  
  8    name         string_type%TYPE := REPLACE(dashed, '-', NULL);
  9  BEGIN
 10     DBMS_OUTPUT.PUT_LINE('Dashed name    = ' || dashed);
 11     DBMS_OUTPUT.PUT_LINE('Dashes removed = ' || name);
 12  END;
 13  /
Dashed name    = Gold-i-locks
Dashes removed = Goldilocks
 
PL/SQL procedure successfully completed.
 
SQL>

If its second and third arguments are NULL, REPLACE just returns its first argument.

Conditional Compilation Control Tokens

The conditional compilation trigger character, $, identifies code that is processed before the application is compiled. A conditional compilation control token has the form:

preprocessor_control_token ::= $plsql_identifier

The $ must be at the beginning of the identifier name and there cannot be a space between the $ and the name. The $ can also be embedded in the identifier name, but it has no special meaning. The reserved preprocessor control tokens are $IF, $THEN, $ELSE, $ELSIF, $END, and $ERROR. For an example of the use of the conditional compilation control tokens, see Example 2-56.

Using Conditional Compilation Selection Directives

The conditional compilation selection directive evaluates static expressions to determine which text to include in the compilation. The selection directive is of the form:

$IF boolean_static_expression $THEN text
  [$ELSIF boolean_static_expression $THEN text]
  [$ELSE text]
$END

boolean_static_expression must be a BOOLEAN static expression. For a description of BOOLEAN static expressions, see Using Static Expressions with Conditional Compilation. For information about PL/SQL IF-THEN control structures, see Testing Conditions (IF and CASE Statements).

Using Conditional Compilation Error Directives

The error directive $ERROR raises a user-defined exception and is of the form:

$ERROR varchar2_static_expression $END

varchar2_static_expression must be a VARCHAR2 static expression. For a description of VARCHAR2 static expressions, see Using Static Expressions with Conditional Compilation. See Example 2-55.

Using Conditional Compilation Inquiry Directives

The inquiry directive is used to check the compilation environment. The inquiry directive is of the form:

inquiry_directive ::= $$id

An inquiry directive can be predefined as described in Using Predefined Inquiry Directives with Conditional Compilation or be user-defined. The following describes the order of the processing flow when conditional compilation attempts to resolve an inquiry directive:

1. The id is used as an inquiry directive in the form $$id for the search key.

2. The two-pass algorithm proceeds as follows:

   The string in the PLSQL_CCFLAGS compilation parameter is scanned from right to left, searching with id for a matching name (case-insensitive); done if found.

   The predefined inquiry directives are searched; done if found.

3. If the $$id cannot be resolved to a value, then the PLW-6003 warning message is reported if the source text is not wrapped. The literal NULL is substituted as the value for undefined inquiry directives. If the PL/SQL code is wrapped, then the warning message is disabled so that the undefined inquiry directive is not revealed.

For example, consider the following session setting:

ALTER SESSION SET
  PLSQL_CCFLAGS = 'plsql_ccflags:true, debug:true, debug:0';

The value of $$debug is 0 and the value of $$plsql_ccflags is true. The value of $$plsql_ccflags resolves to the user-defined PLSQL_CCFLAGS inside the value of the PLSQL_CCFLAGS compiler parameter. This occurs because a user-defined directive overrides the predefined one.

Consider the following session setting:

ALTER SESSION SET PLSQL_CCFLAGS = 'debug:true'

Now the value of $$debug is true, the value of $$plsql_ccflags is 'debug:true', the value of $$my_id is the literal NULL, and the use of $$my_id raises PLW-6003 if the source text is not wrapped.

For an example of the use of an inquiry directive, see Example 2-56.

Using Predefined Inquiry Directives with Conditional Compilation

Predefined inquiry directive names, which can be used in conditional expressions, include:

• PLSQL_LINE, a PLS_INTEGER literal whose value indicates the line number reference to $$PLSQL_LINE in the current PL/SQL unit

  An example of $$PLSQL_LINE in a conditional expression is:

  $IF $$PLSQL_LINE = 32 $THEN ...
  

• PLSQL_UNIT, a VARCHAR2 literal whose value indicates the current PL/SQL unit

  For a named PL/SQL unit, $$PLSQL_UNIT contains, but might not be limited to, the unit name. For an anonymous block, $$PLSQL_UNIT contains the empty string.

  An example of $$PLSQL_UNIT in a conditional expression is:

  IF $$PLSQL_UNIT = 'AWARD_BONUS' THEN ...
  

  The preceding example shows the use of PLSQL_UNIT in regular PL/SQL. Because $$PLSQL_UNIT = 'AWARD_BONUS' is a VARCHAR2 comparison, not a static expression, it is not supported with $IF. One valid use of $IF with PLSQL_UNIT is to determine an anonymous block, as follows:

  $IF $$PLSQL_UNIT IS NULL $THEN ...
  

• PL/SQL compilation parameters

The values of the literals PLSQL_LINE and PLSQL_UNIT can be defined explicitly with the compilation parameter PLSQL_CCFLAGS. For information about compilation parameters, see PL/SQL Units and Compilation Parameters.

Using Static Expressions with Conditional Compilation

Only static expressions which can be fully evaluated by the compiler are allowed during conditional compilation processing. Any expression that contains references to variables or functions that require the execution of the PL/SQL are not available during compilation and cannot be evaluated. For information about PL/SQL data types, see Predefined PL/SQL Scalar Data Types and Subtypes.

A static expression is either a BOOLEAN, PLS_INTEGER, or VARCHAR2 static expression. Static constants declared in packages are also static expressions.

Topics:

• Boolean Static Expressions

• PLS_INTEGER Static Expressions

• VARCHAR2 Static Expressions

• Static Constants

static_constant CONSTANT data_type := static_expression;

SQL> CREATE PACKAGE my_debug IS
  2    debug CONSTANT BOOLEAN := TRUE;
  3    trace CONSTANT BOOLEAN := TRUE;
  4  END my_debug;
  5  /
 
Package created.
 
SQL> CREATE PROCEDURE my_proc1 IS
  2  BEGIN
  3    $IF my_debug.debug $THEN
  4      DBMS_OUTPUT.put_line('Debugging ON');
  5    $ELSE
  6      DBMS_OUTPUT.put_line('Debugging OFF');
  7    $END
  8  END my_proc1;
  9  /
 
Procedure created.
 
SQL> CREATE PROCEDURE my_proc2 IS
  2  BEGIN
  3    $IF my_debug.trace $THEN
  4      DBMS_OUTPUT.put_line('Tracing ON');
  5    $ELSE DBMS_OUTPUT.put_line('Tracing OFF');
  6    $END
  7  END my_proc2;
  8  /
 
Procedure created.
 
SQL>

Using DBMS_DB_VERSION Package Constants

The DBMS_DB_VERSION package provides constants that are useful when making simple selections for conditional compilation. The PLS_INTEGER constants VERSION and RELEASE identify the current Oracle Database version and release numbers. The BOOLEAN constants VER_LE_9, VER_LE_9_1, VER_LE_9_2, VER_LE_10, VER_LE_10_1, and VER_LE_10_2 evaluate to TRUE or FALSE as follows:

• VER_LE_v evaluates to TRUE if the database version is less than or equal to v; otherwise, it evaluates to FALSE.

• VER_LE_v_r evaluates to TRUE if the database version is less than or equal to v and release is less than or equal to r; otherwise, it evaluates to FALSE.

• All constants representing Oracle Database 10g release 1 or earlier are FALSE

Example 2-55 illustrates the use of a DBMS_DB_VERSION constant with conditional compilation. Both the Oracle Database version and release are checked. This example also shows the use of $ERROR.

SQL> BEGIN
  2    $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
  3      $ERROR 'unsupported database release'
  4    $END
  5    $ELSE
  6      DBMS_OUTPUT.PUT_LINE
  7        ('Release ' || DBMS_DB_VERSION.VERSION || '.' ||
  8         DBMS_DB_VERSION.RELEASE || ' is supported.');
  9  
 10    -- This COMMIT syntax is newly supported in 10.2:
 11    COMMIT WRITE IMMEDIATE NOWAIT;
 12  $END
 13  END;
 14  /
Release 11.1 is supported.
 
PL/SQL procedure successfully completed.
 
SQL>

For information about the DBMS_DB_VERSION package, see Oracle Database PL/SQL Packages and Types Reference.

Using Conditional Compilation to Specify Code for Database Versions

Example 2-56 uses conditional compilation to determine whether the BINARY_DOUBLE data type can be used in the calculations for PL/SQL units in the database. The BINARY_DOUBLE data type can only be used in a database version that is 10g or later.

SQL> -- Set flags for displaying debugging code and tracing info:
SQL> 
SQL> ALTER SESSION SET PLSQL_CCFLAGS =
  2    'my_debug:FALSE, my_tracing:FALSE';
 
Session altered.
 
SQL> 
SQL> CREATE OR REPLACE PACKAGE my_pkg AS
  2    SUBTYPE my_real IS
  3      $IF DBMS_DB_VERSION.VERSION < 10 $THEN
  4        NUMBER;
  5        -- Check database version
  6      $ELSE
  7        BINARY_DOUBLE;
  8      $END
  9  
 10      my_pi my_real;
 11      my_e my_real;
 12  END my_pkg;
 13  /
 
Package created.
 
SQL> CREATE OR REPLACE PACKAGE BODY my_pkg AS
  2  BEGIN
  3    -- Set values for future calculations based on DB version
  4  
  5    $IF DBMS_DB_VERSION.VERSION < 10 $THEN
  6      my_pi := 3.14159265358979323846264338327950288420;
  7      my_e  := 2.71828182845904523536028747135266249775;
  8    $ELSE
  9      my_pi := 3.14159265358979323846264338327950288420d;
 10      my_e  := 2.71828182845904523536028747135266249775d;
 11    $END
 12  END my_pkg;
 13  /
 
Package body created.
 
SQL> CREATE OR REPLACE PROCEDURE circle_area(radius my_pkg.my_real) IS
  2    my_area      my_pkg.my_real;
  3    my_data_type  VARCHAR2(30);
  4  BEGIN
  5    my_area := my_pkg.my_pi * radius;
  6  
  7    DBMS_OUTPUT.PUT_LINE
  8      ('Radius: ' || TO_CHAR(radius) || ' Area: ' || TO_CHAR(my_area));
  9  
 10    $IF $$my_debug $THEN
 11      -- If my_debug is TRUE, run debugging code
 12      SELECT DATA_TYPE INTO my_data_type
 13        FROM USER_ARGUMENTS
 14          WHERE OBJECT_NAME = 'CIRCLE_AREA'
 15            AND ARGUMENT_NAME = 'RADIUS';
 16  
 17       DBMS_OUTPUT.PUT_LINE
 18         ('Data type of the RADIUS argument is: ' || my_data_type);
 19    $END
 20  END;
 21  /
 
Procedure created.
 
SQL>

Using DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text

DBMS_PREPROCESSOR subprograms print or retrieve the post-processed source text of a PL/SQL unit after processing the conditional compilation directives. This post-processed text is the actual source used to compile a valid PL/SQL unit. Example 2-57 shows how to print the post-processed form of my_pkg in Example 2-56 with the PRINT_POST_PROCESSED_SOURCE procedure.

SQL> CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE
  2    ('PACKAGE', 'HR', 'MY_PKG');
PACKAGE my_pkg AS
SUBTYPE my_real IS
BINARY_DOUBLE;
my_pi my_real;
my_e my_real;
END my_pkg;
 
Call completed.
 
SQL> 

PRINT_POST_PROCESSED_SOURCE replaces unselected text with whitespace. The lines of code in Example 2-56 that are not included in the post-processed text are represented as blank lines. For information about the DBMS_PREPROCESSOR package, see Oracle Database PL/SQL Packages and Types Reference.