PL/SQL User's Guide and Reference

Assignment Statement

Description

An assignment statement sets the current value of a variable, field, parameter, or element. The statement consists of an assignment target followed by the assignment operator and an expression. When the statement is executed, the expression is evaluated and the resulting value is stored in the target. For more information, see "Assignments"

Syntax

assignment_statement ::=

{  cursor_variable_name
 | :host_cursor_variable_name
 | :host_variable_name[:indicator_name]
 | parameter_name
 | plsql_table_name(index)
 | record_name.field_name
 | variable_name} := expression;

Keyword and Parameter Description

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope. Only the value of another cursor variable can be assigned to a cursor variable.

host_cursor_variable_ name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

host_variable_name

This identifies a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host variables must be prefixed with a colon. For more information, see "Using Host Variables"

indicator_name

This identifies an indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable "indicates" the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables let you detect nulls or truncated values in output host variables. For more information, see "Using Indicator Variables"

parameter_name

This identifies a formal OUT or IN OUT parameter of the subprogram in which the assignment statement appears.

plsql_table_name

This identifies a PL/SQL table previously declared within the current scope.

index

This is a numeric expression that must yield a value of type BINARY_INTEGER or a value implicitly convertible to that datatype. For more information, see "Datatype Conversion"

record_name.field_name

This identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.

variable_name

This identifies a PL/SQL variable previously declared within the current scope.

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions"

. When the assignment statement is executed, the expression is evaluated and the resulting value is stored in the assignment target. The value and target must have compatible datatypes.

Usage Notes

By default, unless a variable is initialized in its declaration, it is initialized to NULL every time a block or subprogram is entered. So, never reference a variable before you assign it a value.

You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR.

Only the values TRUE and FALSE and the non-value NULL can be assigned to a Boolean variable. When applied to an expression, the relational operators return a Boolean value. So, the following assignment is legal:

DECLARE
   out_of_range BOOLEAN;
   ...
BEGIN
   ...
   out_of_range := (salary < minimum) OR (salary > maximum);

As the next example shows, you can assign the value of an expression to a specific field in a record:

DECLARE
   emp_rec emp%ROWTYPE;
BEGIN
   ...
   emp_rec.sal := current_salary + increase;

Moreover, you can assign values to all fields in a record at once. PL/SQL allows aggregate assignment between entire records if their declarations refer to the same cursor or table. For example, the following assignment is legal:

DECLARE
   emp_rec1 emp%ROWTYPE;
   emp_rec2 emp%ROWTYPE;
   dept_rec dept%ROWTYPE;
BEGIN
   ...
   emp_rec1 := emp_rec2;

The next assignment is illegal because you cannot use the assignment operator to assign a list of values to a record:

dept_rec := (60, 'PUBLICITY', 'LOS ANGELES');

Using the following syntax, you can assign the value of an expression to a specific element in a PL/SQL table:

plsql_table_name(index) := expression;

In the following example, you assign the uppercase value of last_name to the third row in PL/SQL table ename_tab:

ename_tab(3) := UPPER(last_name);

Examples

Several examples of assignment statements follow:

wages := hours_worked * hourly_salary;
country := 'France';
costs := labor + supplies;
done := (count > 100);
dept_rec.loc := 'BOSTON';
comm_tab(5) := sales * 0.15;