Assignment

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

You can assign a value to a variable by using the following methods:

• Use an assignment statement to specify the value of an expression for 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 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 specify the value of an expression for the variable.

In PL/SQL, use the := operator to assign a value to a variable. The syntax is as follows:

variable  := expression ;

In the 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, variables pi, radius, and area are declared, and the initial values of pi and radius are specified. An assignment statement is used to specify the value of an expression for 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, the variable is assigned a value. The type of the variable to be assigned must correspond to that of the column specified 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 that is compatible with the type of the select_item must be specified. SQL does not support the BOOLEAN type. Therefore, the 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 is executed.

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, or NULL. Example: Assign a value to a BOOLEAN variable.

obclient> DECLARE

done BOOLEAN;     --The default initial value is NULL

BEGIN

done := FALSE;  --Specify the value of a literal
WHILE NOT done LOOP
  Null;
done := TRUE;
    END LOOP;
    END;
        /
        ```
    END LOOP;
END;
/