Variable Assignment

Contact Us or call 1-877-932-8228
Variable Assignment

Variable Assignment

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.

Code Sample:

Declare-Clause/Demos/declare_v.sql
DECLARE
  my_variable VARCHAR2(11);
BEGIN
  NULL;
END;

Code Explanation

This example consists of the simple declaration of a VARCHAR2 variable. Verify that code can compile (run will do nothing).

Code Sample:

Declare-Clause/Demos/declare_v_and_init.sql
DECLARE
  my_variable VARCHAR2(11) := 'Hello World';
BEGIN
  NULL;
END;

Code Explanation

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).

Code Sample:

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;

Code Explanation

This exampledeclares, 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.

Code Sample:

Declare-Clause/Demos/assign_operator_date.sql
DECLARE
  my_date DATE;
BEGIN  
  my_date := current_date;
  DBMS_OUTPUT.PUT_LINE('My date is:  ' ||
                       my_date);
END;

Code Explanation

The DATE data type is assigned in this example using the := operator.

Code Sample:

Declare-Clause/Demos/assign_into_date.sql
DECLARE
  my_date DATE;
  
BEGIN  
  select sysdate
  into my_date
  from dual;
  
  DBMS_OUTPUT.PUT_LINE('My date is (from select into): '
                       || my_date);
END;

Code Explanation

The SELECT INTO statement is used above to assign a value (rather than using the := operator as in the previous example).

Variable Display

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.

Code Sample:

Declare-Clause/Demos/declare_v_init_and_display.sql
DECLARE
  my_variable VARCHAR2(11) := 'Hello World';
BEGIN
  DBMS_OUTPUT.PUT_LINE(my_variable);
END;

Code Explanation

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.

Code Sample:

Declare-Clause/Demos/declare_n_null_increment.sql
DECLARE
  my_number NUMBER;  
BEGIN
  my_number := my_number + 1;
  DBMS_OUTPUT.PUT_LINE('My number is: ' 
                       || my_number);
END;

Code Explanation

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.

Code Sample:

Declare-Clause/Demos/declare_n_increment_display.sql
DECLARE
  my_number NUMBER := 0;
BEGIN
  my_number := my_number + 1;
  DBMS_OUTPUT.PUT_LINE('My number is: ' || my_number);
END;

Code Explanation

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.

Code Sample:

Declare-Clause/Demos/chr_function.sql
BEGIN
 dbms_output.put_line('9:' || CHR(9) || '9.' ||
                      '10:' || CHR(10) || 'end 10.' ||
                      '13:' || CHR(13) || 'end 13.' ||
                      '15:' || CHR(15) || 'end 15.');
END;

Code Explanation

In this example characters 9 (tab), 10 (line feed), 13 (carriage return), and 15 (shift in) are used to influence the output.

Next