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 "Assigning Values to Variables".
assignment statement ::=
Description of the illustration assignment_statement.gif
Keyword and Parameter Description
An attribute of an object type. The name must be unique within the object type (but can be reused in other object types). You cannot initialize an attribute in its declaration using the assignment operator or clause. Also, you cannot impose the constraint on an attribute.
A nested table, index-by table, or varray previously declared within the current scope.
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.
A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of , see "Expression Definition". 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.
A field in a user-defined or record.
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.
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.
A numeric expression that must return a value of type , , or a value implicitly convertible to that datatype.
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.
An instance of an object type previously declared within the current scope.
A formal or parameter of the subprogram in which the assignment statement appears.
A user-defined or record previously declared within the current scope.
A PL/SQL variable previously declared within the current scope.
By default, unless a variable is initialized in its declaration, it is initialized to every time a block or subprogram is entered. Always assign a value to a variable before using that variable in an expression.
You cannot assign nulls to a variable defined as . If you try, PL/SQL raises the predefined exception . Only the values , , and can be assigned to a Boolean variable. You can assign the result of a comparison or other test to a Boolean variable.
You can assign the value of an expression to a specific field in a record. 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. Example 1-2, "Assigning Values to Variables With the Assignment Operator" shows how to copy values from all the fields of one record to another:
You can assign the value of an expression to a specific element in a collection, by subscripting the collection name.
Example 13-1 illustrates various ways to declare and then assign values to variables.
Example 13-1 Declaring and Assigning Values to VariablesDECLARE wages NUMBER; hours_worked NUMBER := 40; hourly_salary CONSTANT NUMBER := 17.50; -- constant value does not change country VARCHAR2(64) := 'UNKNOWN'; unknown BOOLEAN; TYPE comm_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER; commissions comm_tab; TYPE jobs_var IS VARRAY(10) OF employees.job_id%TYPE; jobids jobs_var; CURSOR c1 IS SELECT department_id FROM departments; -- cursor declaration deptid departments.department_id%TYPE; emp_rec employees%ROWTYPE; -- do not need TYPE declaration in this case BEGIN /* the following are examples of assignment statements */ wages := hours_worked * hourly_salary; -- compute wages country := UPPER('italy'); unknown := (country = 'UNKNOWN'); commissions(5) := 20000 * 0.15; commissions(8) := 20000 * 0.18; jobids := jobs_var('ST_CLERK'); jobids.EXTEND(1); jobids(2) := 'SH_CLERK'; OPEN c1; FETCH c1 INTO deptid; CLOSE c1; emp_rec.department_id := deptid; emp_rec.job_id := jobids(2); END; /
For examples, see the following:
Example 1-2, "Assigning Values to Variables With the Assignment Operator"
Example 1-3, "Assigning Values to Variables by SELECTing INTO"
Example 1-4, "Assigning Values to Variables as Parameters of a Subprogram"
Example 2-10, "Assigning Values to a Record With a %ROWTYPE Declaration"
"Assigning Values to Variables"
"Constant and Variable Declaration"
"SELECT INTO Statement"
Although it is not required, it is generally a good idea to initialize a variable when creating it. When variables are created, they can be assigned a value using the assignment operator, :=, or the DEFAULT keyword. Oracle suggests that you use DEFAULT for variables that have a typical value and the assignment operator for variables that do not. A declaration can also specify a NOT NULL constraint, to prevent the assignment of a NULL value to the variable. Variables that are not initialized have the value NULL. As with SQL, literal character data is always enclosed in single quotes. Similarly, quoted strings may not contain line breaks.
Declare-Clause/Demos/declare_v.sqlDECLARE my_variable VARCHAR2(11); BEGIN NULL; END;
This example consists of the simple declaration of a VARCHAR2 variable. Verify that code can compile (run will do nothing).
Declare-Clause/Demos/declare_v_and_init.sqlDECLARE my_variable VARCHAR2(11) := 'Hello World'; BEGIN NULL; END;
This example involves both the declaration and initialization of a VARCHAR2 variable. This code can also compile but there will be no visible results. Variable assignment can be performed using the := operator. (Keep this in mind if you have a background in a language where = is used for assignment).
Declare-Clause/Demos/declare_v_using_default_keyword.sql--using default keyword DECLARE my_variable VARCHAR2(11) NOT NULL default 'Hello World'; BEGIN dbms_output.put_line(my_variable); END;
This example declares, initializes, and sets the default value of a VARCHAR2 variable.
Variables can also be assigned values after declaration, in the executable section. This is typically done in one of two ways, using the assignment operator (:=) or a SELECT INTO statement.
Declare-Clause/Demos/assign_operator_date.sqlDECLARE my_date DATE; BEGIN my_date := current_date; DBMS_OUTPUT.PUT_LINE('My date is: ' || my_date); END;
The DATE data type is assigned in this example using the := operator.
Declare-Clause/Demos/assign_into_date.sqlDECLARE my_date DATE; BEGIN select sysdate into my_date from dual; DBMS_OUTPUT.PUT_LINE('My date is (from select into): ' || my_date); END;
The SELECT INTO statement is used above to assign a value (rather than using the := operator as in the previous example).
Once you have declared a variable and assigned it a value, you can display its assigned value in the context of PL/SQL using the DBMS_OUTPUT.PUT_LINE stored procedure.
Declare-Clause/Demos/declare_v_init_and_display.sqlDECLARE my_variable VARCHAR2(11) := 'Hello World'; BEGIN DBMS_OUTPUT.PUT_LINE(my_variable); END;
This demonstration illustrates the declaration, initialization and display of a VARCHAR2 variable. As discussed previously, ensure that serveroutput is on to display the results.
As is the case with most languages, special care must be taken when dealing with NULL values. For example, a variable can be assigned the value of NULL + 1. The resulting assignment sets the variable to NULL, which might not be the intent.
Declare-Clause/Demos/declare_n_null_increment.sqlDECLARE my_number NUMBER; BEGIN my_number := my_number + 1; DBMS_OUTPUT.PUT_LINE('My number is: ' || my_number); END;
This demonstration involves the declaration of a NUMBER which is not initialized but subsequently incremented. The absence of a displayed result illustrates that NULL is not the same as zero, and variables are not initialized to zero or another value by default.
Declare-Clause/Demos/declare_n_increment_display.sqlDECLARE my_number NUMBER := 0; BEGIN my_number := my_number + 1; DBMS_OUTPUT.PUT_LINE('My number is: ' || my_number); END;
The proper declaration, initialization, incrementing and display of a variable is shown in this example.
At times you may want to utilize a special character in a character string such as a tab or a newline. The CHR function can be used to insert these types of special characters.
Declare-Clause/Demos/chr_function.sqlBEGIN dbms_output.put_line('9:' || CHR(9) || '9.' || '10:' || CHR(10) || 'end 10.' || '13:' || CHR(13) || 'end 13.' || '15:' || CHR(15) || 'end 15.'); END;
In this example characters 9 (tab), 10 (line feed), 13 (carriage return), and 15 (shift in) are used to influence the output.