How To Assign Value To Variable From Select Statement in PL/SQL?

Use INTO clause to assign a value to a variable from the SELECT statement in PL/SQL. Below is an example:

Assign Value to a Variable from SELECT Statement in PL/SQL Example

In the following PL/SQL program, it will get the employee name into variable V_ENAME from the EMP table for employee number 7369.

SET SERVEROUTPUT ON;

DECLARE
   v_ename   emp.ename%TYPE;
BEGIN
   SELECT ename
     INTO v_ename
     FROM emp
    WHERE empno = 7369;

   DBMS_OUTPUT.put_line (v_ename);
END;
/

Output

SMITH
PL/SQL procedure successfully completed.

Assign Multiple Values to Multiple Variables

In this example, it will get employee name and salary into V_ENAME and N_SAL variables from the EMP table.

SET SERVEROUTPUT ON;

DECLARE
   v_ename   emp.ename%TYPE;
   n_sal     emp.sal%TYPE;
BEGIN
   SELECT ename, sal
     INTO v_ename, n_sal
     FROM emp
    WHERE empno = 7369;

   DBMS_OUTPUT.put_line ('Name: ' || v_ename);
   DBMS_OUTPUT.put_line ('Salary: ' || n_sal);
END;
/

Output

Name: SMITH
Salary: 7450.6
PL/SQL procedure successfully completed.

Note: The SELECT statement should return one row to perform such queries.