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.