Assign values

After you declare variables, you can assign values to the variables by using different methods.

Use one of the following methods to assign values:

• Use the value assignment statement to specify values of expressions for variables.

• Use the SELECT INTO or FETCH statement to assign values to variables.

• Pass variables as OUT or IN OUT parameters to subprograms, and assign values to the variables in the subprograms.

The data types of the variables must be compatible with those of the values. If a data type can be implicitly converted to another data type, the two data types are compatible.

Use value assignment statements to assign values to variables

After you declare variables, you can use value assignment statements to specify values of expressions for the variables.

PL uses := to assign values by using the following syntax:

variable  := expression ;

variable specifies the PL variable, and expression specifies the PL expression.

Example: Use a value assignment statement to assign a value to a variable.

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 the example, the pi, radius, and area variables are declared, and initial values are specified for pi and radius. A value assignment statement is used to specify a value of an expression for the area variable.

Use the SELECT INTO statement to assign values to variables

You can use the SELECT INTO statement to directly assign values to variables. Value assignment will be implemented once each time the SELECT INTO statement is executed. The types of variables to which values are to be assigned must be in one-to-one mapping with the columns in SELECT.

The SELECT INTO statement can be simplified as follows:

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

Each select_item must have one corresponding variable_name whose type is compatible with that of select_item. SQL does not support the BOOLEAN type, so the value of variable_name cannot be of the BOOLEAN type.

Example: Use the SELECT INTO statement to assign values to variables.

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 values to variables that are passed to subprograms as parameters

If you pass a variable as an OUT or IN OUT parameter to a subprogram and the subprogram has assigned a value to the parameter, the value retains after the variable is executed in the subprogram.

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

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 values to BOOLEAN variables

BOOLEAN values support logic operations. BOOLEAN variables can be set only to TRUE, FALSE, or NULL.

Example: Assign a value to a BOOLEAN variable.

obclient> DECLARE
  done BOOLEAN;     -- By default, the initial value is NULL.
BEGIN
    done := FALSE;  -- Specify the value of the literal.
    WHILE NOT done LOOP
        Null;
        done := TRUE;
    END LOOP;
END;
/