Assignment

After a variable is declared, you can assign a value to it in different ways.

You can assign a value to a variable in the following ways:

• Use an assignment statement to assign an expression to the variable.

• Use the SELECT INTO or FETCH statement to assign a value to the variable.

• Pass the variable as an OUT or IN OUT parameter to a subprogram and assign a value to the variable in the subprogram.

The data types of the variable and the value must be compatible. If one data type can be implicitly converted to another, the two data types are compatible.

Assign a value to a variable by using an assignment statement

After a variable is declared, you can use an assignment statement to assign an expression to the variable.

In PL/SQL, use the := operator to perform assignment. The syntax is as follows:

variable  := expression ;

In the preceding statement, variable is a PL/SQL variable, and expression is a PL/SQL expression.

Example: Assign a value to a variable by using an assignment statement.

obclient> DECLARE
     pi     CONSTANT NUMBER := 3.14159;
     radius NUMBER NOT NULL := 1;
     area   NUMBER := (pi * radius**2);
BEGIN
    DBMS_OUTPUT.PUT_LINE('The area of the circle is '||area);
END;
/
Query OK, 0 rows affected

The area of the circle is 3.14159

In this example, the variables pi, radius, and area are declared. The initial values of pi and radius are specified, and an assignment statement is used to assign an expression to the variable area.

Assign a value to a variable by using a SELECT INTO statement

You can directly assign a value to a variable by using the SELECT INTO statement. Each time the SELECT INTO statement is executed, a value is assigned to the variable. The type of the variable to be assigned must correspond to the type of the column in the SELECT clause.

A simple form of the SELECT INTO statement is as follows:

SELECT select_item [, select_item ]...
INTO variable_name [, variable_name ]...
FROM table_name;

For each select_item, a variable_name of a compatible type must be specified. Because SQL does not support the BOOLEAN type, variable_name cannot be a BOOLEAN variable.

Example: Assign a value to a variable by using a SELECT INTO statement.

obclient> CREATE TABLE employees(
   employee_id    NUMBER(4,0),
   name           VARCHAR(10),
   salary         NUMBER(7,2)
  );
Query OK, 0 rows affected

obclient> INSERT INTO employees (employee_id,name,salary)VALUES(105,'Sam Smith',15000);
Query OK, 1 row affected
obclient> DECLARE
    emp_id      employees.employee_id%TYPE :=105;
    emp_name    employees.name%TYPE;
    wages       employees.salary%TYPE;
BEGIN
    SELECT name, NVL(salary,0) INTO emp_name, wages
    FROM employees WHERE employee_id = emp_id;
    DBMS_OUTPUT.PUT_LINE(emp_name||'----'||to_char(wages));
END;
/
Query OK, 0 rows affected

Sam Smith----15000

Assign a value to a variable that is passed as a parameter to a subprogram

If you pass a variable as an OUT or IN OUT parameter to a subprogram and the subprogram assigns a value to the parameter, the variable retains the value after the subprogram completes.

Example: Assign a value to a variable that is passed as a parameter to a subprogram.

obclient> DECLARE
  wages   employees.salary%TYPE;
  PROCEDURE new_salary (
    emp_id        employees.employee_id%TYPE :=105,
    salary    IN OUT NUMBER,
    add_salary    NUMBER
  ) IS
  BEGIN
    salary := salary + add_salary;
  END;
BEGIN
  SELECT salary INTO wages
  FROM employees
  WHERE employee_id = 105;
  DBMS_OUTPUT.PUT_LINE
   ('The original wages is ' || wages);
  new_salary (105, wages, 1000);--Call the procedure.
  DBMS_OUTPUT.PUT_LINE
   ('The new wages is ' || wages);
END;
/
 Query OK, 0 rows affected

The original wages is 15000 --Before the procedure is called.
The new wages is 16000 --After the procedure is called.

Assign a value to a BOOLEAN variable

Boolean values (BOOLEAN) support logical operations. A BOOLEAN variable can be assigned only the values TRUE, FALSE, and NULL.

Example: Assign a value to a BOOLEAN variable.

obclient> DECLARE
  done BOOLEAN;     --The default initial value is NULL.
BEGIN
    done := FALSE;  --Assign a literal value.
    WHILE NOT done LOOP
        Null;
        done := TRUE;
    END LOOP;
END;
/