PL/SQL User's Guide and Reference

Assignments

Variables and constants are initialized every time a block or subprogram is entered. By default, variables are initialized to NULL. So, unless you expressly initialize a variable, its value is undefined, as the following example shows:

DECLARE
   count INTEGER;
   ...
BEGIN
   count := count + 1;  -- assigns a null to count

Therefore, never reference a variable before you assign it a value.

You can use assignment statements to assign values to a variable. For example, the following statement assigns a new value to the variable bonus, overwriting its old value:

bonus := salary * 0.15;

The expression following the assignment operator can be arbitrarily complex, but it must yield a datatype that is the same as or convertible to the datatype of the variable.

Boolean Values

Only the values TRUE and FALSE and the non-value NULL can be assigned to a Boolean variable. For example, given the declaration

DECLARE
   done BOOLEAN;

the following statements are legal:

BEGIN
   done := FALSE;
   WHILE NOT done LOOP ...

When applied to an expression, the relational operators return a Boolean value. So, the following assignment is legal:

done := (count > 500);

Database Values

Alternatively, you can use the SELECT (or FETCH) statement to have Oracle assign values to a variable. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list. An example follows:

SELECT ename, sal + comm INTO last_name, wages FROM emp
   WHERE empno = emp_id;

However, you cannot select column values into a Boolean variable.