Previous | Table of Contents | Next

Page 191

  y := XOR(T, x);  -- and this evaluates to true
  IF (x) THEN  -- test for true
    DBMS_OUTPUT.put_line(`x = TRUE');
  ELSIF (NOT x) THEN  -- test for false
    DBMS_OUTPUT.put_line(`x = FALSE');
  ELSE  -- x must be null
    DBMS_OUTPUT.put_line(`x is NULL');
  END IF;
  IF (y) THEN  -- test for true
  DBMS_OUTPUT.put_line(`y = TRUE');
  ELSIF (NOT y) THEN  -- test for false
   DBMS_OUTPUT.put_line(`y = FALSE');
  ELSE  -- y must be null
    DBMS_OUTPUT.put_line(`y is NULL');
    DBMS_OUTPUT.put_line(`Booly for you!');
  END IF;
END;
/

The server responds with:

x = FALSE
y = TRUE
PL/SQL procedure successfully completed.

There are no functions that convert a Boolean value to a string or a string back to a Boolean; you will write them later. Note on line 2 how you specify that a variable must not be NULL. This has limited value in this context, but the syntax is supported. Be aware that there is a slight overhead associated with variables declared NOT NULL.

Experiment with the value of T and x and rerun the block to see what you get (the SQL*Plus command EDIT will allow you to edit the text and rerun it). If you leave off the default assignment, you'll get:

NOTE
See Chapter 8 for more details on the EDIT command.
ERROR at line 1:
ORA-06550: line 2, column 3:
PLS-00322: declaration of a constant `T' must contain an initialization
assignment
ORA-06550: line 2, column 5:
PL/SQL: Item ignored

You have encountered your first compiler error. The ERROR at line 1 tells you that the entire unnamed block had an error. This is because the whole block is shipped over to the server as a single transaction. The next error message is helpful and tells you that you forgot to initialize the constant variable. In fact, all constants must be initialized, even if it's to NULL, which would be perfectly valid without the NOT NULL clause. The ORA-06550 says that the Oracle Server experienced some sort of PL/SQL compilation error (it is left to the PL/SQL compiler to describe the actual error).

Page 192

And if you drop the CONSTANT and initialization, but leave the NOT NULL, you'll get:

ERROR at line 1:
ORA-06550: line 2, column 5:
PLS-00218: a variable declared NOT NULL must have an initialization assignment

At runtime, when the block is entered, a variable is initialized to the specified value. If no assignment is prescribed, then by default the variable is assigned a NULL value. Try leaving off the CONSTANT, NOT NULL and initialization, and you'll get:

x = FALSE
y is NULL
Booly for you!

The variables receive their values at runtime, and not at compile time, because like automatic (or stack) variables in other languages, they don't actually exist until the block is in scope. This means that they are deallocated when the block is out of scope, and reallocated when the block is reentered. Be aware of this overhead for procedures and functions that are invoked multiple times. In fact, take advantage of it. Because variables are initialized to NULL by default, if they do require an initial non-NULL value, this is a good place to do it.

When a variable is declared CONSTANT, its value cannot be changed at runtime. To attempt to do so would raise an exception. Also, the type and length of the initializing literal must match that of the variable. For example, if a declaration for a variable x was CHAR(4) := `NO GOOD!', an exception would be raised at runtime (not at compile time). As you saw earlier, constants must be assigned a value in their declaration, even if the desired value is NULL. Otherwise, a compile error occurs.

A variable can be declared NOT NULL to indicate that it may never hold a NULL value. Such a variable must be initialized to some non-NULL value in its declaration. Note the correct placement of the CONSTANT and NOT NULL keywords before and after the data type, respectively.

Here are some more examples of valid and invalid initializing statements.

DECLARE
  price        NUMBER(5,2) := 19.92;   -- valid initialization
  discount     NUMBER(3,3) := .0625;   -- gets rounded to .063
  max_quantity INTEGER(4) := 50000;    -- runtime error!
  max_discount CONSTANT REAL := 0.75;  -- valid
  min_discount CONSTANT REAL;          -- compile error! not initialized
  disc_type    VARCHAR2(1) := NULL;    -- valid and redundant
  disc_name    CONSTANT CHAR(20) := NULL;  -- valid but of dubious value
  quantity     INTEGER NOT NULL := 0;  -- correct usage
  item_name    VARCHAR2(30) DEFAULT `Hammer';  -- alternate assignment

Branching While you're looking at it, let's enumerate all the syntactical flavors of the IF…THEN statement.

Using Conditional (IF…THEN…ELSE)LogicYour basic conditional test-and-branch logic has the form:

IF (some condition is true) THEN  -- test condition
  …   -- condition was true, do this stuff

Page 193

ELSE     -- condition was false
  …   -- so do this stuff instead
END IF;  -- end of test

For the record, the first block of statements after the IF…THEN is called the antecedent, while the block following the ELSE is called the consequent. You can have multiple statements inside the THEN, ELSE, and ELSIF blocks. If you want, you can put BEGIN…END statements inside the antecedent or consequent. The consequent is, of course, optional. Notice that the block is ended with END IF. It's a good habit to always comment the top and end of a conditional statement, especially a long one, so you know what it's for.

IF (some condition is true) THEN  -- test for some condition
  BEGIN
    …
  END;
ELSE  -- condition was false
  DECLARE  -- define some local variable
    x NUMBER;
  BEGIN
    …
  END;
END IF;  -- test for some condition

The preceding variable x won't even be created unless the consequent is executed. You can use this to limit the use of local variables to only the block that needs them and save a little memory.

How you choose to indent this is up to you, but consider indenting one block inside another block.

You can nest these statements to any depth. You can perform some very complex logic in this manner.

The conditional expression is computed using short-circuit evaluations. This means that if the first part of a complex OR expression evaluates to TRUE, or the first part of an AND expression evaluates to FALSE, evaluation stops immediately and there is no need to evaluate further. Most other languages also support short-circuit evaluation. Such expressions are always evaluated left to right, with expressions in parentheses taking higher precedence.

Testing for the NULL ConditionThe special literal value NULL can be assigned to variables of any data type. It represents an unknown value. You must use the special syntax IS NULL and IS NOT NULL to test for its existence and absence, respectively. The tests

IF (x = NULL)  THEN … and
IF (x != NULL) THEN …

will always fail. The correct way is

IF (x IS NULL)     THEN … and
IF (x IS NOT NULL) THEN … .

Using Sequential IF LogicSometimes you need to test a series of values, one right after another. It would be inefficient to code a series of IF…THEN statements if an earlier one might succeed, and all the subsequent ones would be expected to fail.

Previous | Table of Contents | Next