Oracle PL/SQL - Assigning Values to Variables with the SELECT INTO Statement

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Introduction
  4. Variable Declarations

Introduction

A simple form of the SELECT INTO statement is: 

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

For each select_item, there must be a corresponding, type-compatible variable_name.

Because SQL does not have a BOOLEAN type, variable_name cannot be a BOOLEAN variable.

The following code uses a SELECT INTO statement to assign to the variable bonus the value that is 10% of the salary of the employee whose empid is 100.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   ww  w  . ja  v a  2 s.c om

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL> DECLARE
  2    bonus   NUMBER(8,2);
  3  BEGIN
  4    SELECT salary * 0.10 INTO bonus
  5    FROM emp
  6    WHERE empid = 100;
  7    DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus));
  8  END;
  9
 10  /
bonus = 2400

PL/SQL procedure successfully completed.

SQL>

Related Topic