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.
The assignment statement sets the current value of a variable, field, parameter, or element that has been declared in the current scope.
The assignment operator () in the assignment statement can also appear in a constant or variable declaration. In a variable declaration, it assigns a default value to the variable. Without a default value, a variable is initialized to every time a block is entered.
If a variable does not have a default value, always use the assignment statement to assign a value to it before using it in an expression.
Description of the illustration assignment_statement.gif
Keyword and Parameter Descriptions
The name of an attribute of . The name must be unique within (but can be used in other object types).
You cannot initialize an attribute in its declaration. You cannot impose the constraint on an attribute.
The name of a collection.
The name of a PL/SQL cursor variable.
The expression whose value is to be assigned to the target (the item to the left of the assignment operator) when the assignment statement executes.
The value of must have a data type that is compatible with the data type of the target.
If the target is a variable defined as , the value of cannot be . If the target is a Boolean variable, the value of must be , , or . If the target is a cursor variable, the value of must also be a cursor variable.
The name of a field in .
Specify if you want to assign the value of to a specific field of a record.
Omit if you want to assign the value of to all fields of at once; that is, if you want to assign one record to another. You can assign one record to another only if their declarations refer to the same table or cursor, as in Example 2-17, "Assigning One Record to Another, Correctly and Incorrectly".
The name of a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument.
The data type of a host cursor variable is compatible with the return type of any PL/SQL cursor variable.
The name of a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument.
A numeric expression whose value has data type or a data type implicitly convertible to (see Table 3-10, "Possible Implicit PL/SQL Data Type Conversions").
Specify if you want to assign the value of to a specific element of .
Omit if you want to assign the value of to all elements of at once; that is, if you want to assign one collection to another. You can assign one collection to another only if the collections have the same data type (not merely the same element type).
The name of an indicator variable for .
An indicator variable indicates the value or condition of its host variable. For example, in the Oracle Precompiler environment, indicator variables let you detect or truncated values in output host variables.
The name of an instance of an object type.
The name of a formal or parameter of the subprogram in which the assignment statement appears.
The name of a user-defined or record.
The name of a PL/SQL variable.