Assigning Values to a Variable With the PL/SQL SELECT INTO Statement

Another way to assign values to a variable is by selecting (or fetching) database values into it. With the PL/SQL SELECT INTO statement, you can retrieve data from one row in a table. In Example: Assigning Values to Variables Using PL/SQL SELECT INTO, 10 percent of the salary of an employee is selected into the bonus variable. Now, you can use the bonus variable in another computation, or insert its value into a database table.

In the example, the DBMS_OUTPUT.PUT_LINE procedure is used to display output from the PL/SQL program. For more information, see "Inputting and Outputting Data with PL/SQL".

Assigning Values to Variables Using PL/SQL SELECT INTO

DECLARE -- declare and assign values
  bonus_rate CONSTANT NUMBER(2,3) := 0.05;
  bonus      NUMBER(8,2);
  emp_id     NUMBER(6) := 120;  -- assign a test value for employee ID
BEGIN
-- retreive a salary from the employees table, then calculate the bonus and 
-- assign the value to the bonus variable
  SELECT salary * bonus_rate INTO bonus FROM employees
    WHERE employee_id = emp_id;
-- display the employee_id, bonus amount, and bonus rate
    DBMS_OUTPUT.PUT_LINE ( 'Employee: ' || TO_CHAR(emp_id) 
      || ' Bonus: ' || TO_CHAR(bonus) || ' Bonus Rate: ' || TO_CHAR(bonus_rate)); 
END;
/