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 then assign a value to the variable in the subprogram.

The data types of the variable and the value must be compatible. If a data type can be implicitly converted to another data type, 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 for assignment. The syntax is as follows:

variable  := expression ;

Here, 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, and the initial values of pi and radius are specified. An assignment statement is used to assign an expression to the variable area.

Assign a value to a variable by using the 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 must correspond to the type of the column in the SELECT statement.

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, you must specify a variable_name that is compatible with the type of the select_item. Because SQL does not support the BOOLEAN data type, variable_name cannot be a BOOLEAN variable.

Example: Assign a value to a variable by using the 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 calling the procedure
The new wages is 16000 --After calling the procedure

Assign a value to a BOOLEAN variable

A BOOLEAN value supports logical operations. A BOOLEAN variable can be assigned only the TRUE, FALSE, or NULL value.

Example: Assign a value to a BOOLEAN variable.

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