Page 212
Table 10.5 illustrates all valid implicit type conversions. If you don't see a conversion listed, you'll have to do an explicit conversion, using a built-in conversion function.
Table 10.5 Implicit Type Conversion
TO | BINARY INTEGER | CHAR | DATE | LONG | NUMBER | RAW | ROWID | VARCHAR2 |
FROM | ||||||||
BINARY INTEGER | ÷ | ÷ | ÷ | ÷ | ||||
CHAR | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ | |
DATE | ÷ | ÷ | ÷ | |||||
LONG | ÷ | ÷ | ÷ | |||||
NUMBER | ÷ | ÷ | ÷ | ÷ | ||||
RAW | ÷ | ÷ | ÷ | |||||
ROWID | ÷ | ÷ | ||||||
VARCHAR2 | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ | ÷ |
PLS_INTEGER has the same implicit conversion characteristics as BINARY_INTEGER, as well as implicitly converting between each other. The following examples are all valid declarations and assignments:
DECLARE date_from DATE := '20-AUG-85'; -- char to date (match NLS format) cnt INTEGER(3) := `0'; -- char to integer (subtype of number) loop_control BINARY_INTEGER; bin_val RAW(2) := `1'; -- char to raw str VARCHAR2(9) := '26-AUG-60'; -- char to varchar2 short_num VARCHAR2(3); BEGIN loop_control := cnt + `1'; -- char to number to binary integer str := date_from; -- date to varchar2 short_num := bin_val; -- raw to varchar2 cnt := short_num; -- varchar2 to integer
Anything can be converted implicitly to and from CHAR and VARCHAR2. If you need to convert a RAW to a NUMBER, you could first convert the RAW value to VARCHAR2, say, then to NUMBER. Implicit conversions hold for a data type's subtypes as well.
For DATE, an implicit conversion from CHAR or VARCHAR2 can only successfully occur when the string is in the default NLS format defined for the database. If the string is in a different format, you must perform an explicit conversion and supply a conversion string.
Explicit Type ConversionTable 10.6 lists all the PL/SQL functions available to perform explicit data type conversions.
Page 213
Table 10.6 Explicit Data Type Conversion
TO | VARCHAR2 | CHAR | NUMBER | DATE | RAW | ROWID |
FROM | ||||||
VARCHAR2 | TO_NUMBER (vc, [fmt [,lang]]) | TO_DATE (vc, [fmt [, lang]]) | HEXTORAW (vc) | CHARTOROWID (vc) | ||
CHAR | TO_NUMBER (c, [fmt [, lang]]) | TO_DATE (c, [fmt [, lang]]) | HEXTORAW (c) | CHARTOROWID (c) | ||
NUMBER | TO_CHAR (n, [format [,lang]]) | TO_CHAR (n, [format [,lang]]) | TO_DATE (n, [fmt [, lang]]) | |||
DATE | TO_CHAR (dt, [format [,lang]]) | TO_CHAR (dt, [format [,lang]]) | ||||
RAW | RAWTOHEX (raw) | RAWTOHEX (raw) | ||||
ROWID | ROWIDTOCHAR (rowid) | ROWIDTOCHAR (rowid) |
Page 214
Where:
Actually, TO_CHAR() returns VARCHAR2, but it can be easily used for CHAR because these two types convert implicitly. Note, however, that there are no conversion routines for LONG. To do much with LONG you'd have to first implicitly convert it to VARCHAR2 and then explicitly to some other type.
Unlike some languages, such as C or Pascal, assignments must be single statements. You can only assign one value to one variable at a clip. You cannot assign a value to a variable inside another statement, such as an IF conditional test.
Most data type mismatch errors are caught at compile-time, as long as the type of the expression can be determined. Value errors are caught at runtime.
One special concern arises when dealing with NULL values. Any mathematical operation involving a NULL value always returns a NULL value; by definition, the result is indeterminate. This does not hold true for string concatenation. For example:
DECLARE x NUMBER; y NUMBER; z NUMBER := 10; a VARCHAR2(20) := `Hello, `; BEGIN x := 1/NULL; -- result is NULL y := x * (z - 1); -- result is NULL z := (y - 1)**10; -- result is NULL a := a || NULL || `World!'; -- result is `Hello, World!'
PL/SQL supports three brands of looping:
The simplest kind of loop looks like this:
LOOP NULL; -- infinite loop! END LOOP;
Note the LOOP END LOOP syntax. Oracle won't detect the infinite loop. You have to supply a test to break out of the loop.
Page 215
There are a few ways of exiting a loop:
Obviously, the first two methods involving EXIT are preferred. Here are some examples:
DECLARE i NUMBER := 0; BEGIN LOOP -- example #1 i := i + 1; IF (i >= 100) THEN -- enough iterations i := 0; -- reset EXIT; -- unconditional termination END IF; -- enough iterations END LOOP; -- example #1 done LOOP -- example #2 i := i + 1; EXIT WHEN (i >= 100); -- conditional termination END LOOP; -- example #2 done
Certainly, the EXIT WHEN syntax is cleaner when all you have to do when the condition is met is to terminate the loop. Use the conditional logic when you have to reset values or do some other kind of work before getting out of the loop.
Controlling Your LoopYou can use the WHILE loop to test for a condition at the beginning of a loop. As long as the condition holds true, the loop continues to iterate. Conversely, if you need to test a condition at the end of a loop, use the LOOP-EXIT WHEN structure instead.
WHILE (x < 10) LOOP -- While loop ... x := x + 1; END LOOP; -- done LOOP -- simulated Repeat-Until (or Do-While) loop ... EXIT WHEN ... END LOOP; -- done
It's a good habit to always comment the top and bottom of a loop, especially a long one, so you know what it's for.
PL/SQL does not support a FORTRAN-like CONTINUE statement for branching to the end of a loop, skipping all the code in between. Some people will tell you to modularize your code with conditional logic in this case, causing your code to become hideously indented and more difficult to maintain. However, there is a solution. You can use the GOTO statement to simulate a CONTINUE statement (see Listing 10.12). CONTINUE is a reserved word in SQL, so you can't use it in a statement or label.