SELECT…INTO Statement - Oracle

This is a special statement; it's a bit different from an ordinary SQL SELECT statement. It retrieves data from a table and places the values into variables. Why is this mechanism necessary? In general, a SELECT statement retrieves some information from tables and directly displays it on the screen. But in PL/SQL, we need to control the output in whatever way we like.

So, after placing the values of the SELECT..INTO statement into the variables, we display them using the DBMS_OUTPUT package. The SELECT…INTO statement should return one and only one row. It raises an error (or exception) if it returns more than one row or even none. So, it is our responsibility to design the SELECT..INTO statement in such a way that it should return one and only one row.

Let us consider our first example.

Declare
     v_empno number(4) := &empno;
     v_ename varchar2(10);
begin
     Select ename into v_ename
     from emp
     Where empno=v_empno;
     dbms_output.put_line('Name: '|| v_ename);
end;

The above program illustrates a simple PL/SQL block structure with variable declarations, PL/SQL body and even DBMS_OUTPUT package.

From the above program, we declare two variables, "v_empno" and "v_ename." We accept input from the user just by putting the"&empno" into place. All this happens within the declaration section. Within the body (between "begin" and "end"), we fetch the "ename" and place it in "v_ename" from the table "emp." based on the "where" condition framed from the user input. To display the value available in "v_ename," we use DBMS_OUTPUT.PUT_LINE statement.

Please note that we need to execute the "set serveroutput on" statement at the sql prompt prior to the execution of the program, as we are working with DBMS_OUTPUT package.

Let us consider another example:

Declare
     v_empno number(4) := &empno;
     v_ename varchar2(10);
     v_sal number(4);
     v_job varchar2(20);
begin
     Select ename,sal,job into v_ename,v_sal,v_job
     from emp
     Where empno=v_empno;
     dbms_output.put_line('Name: '|| v_ename);
     dbms_output.put_line('Salary: '|| v_sal);
     dbms_output.put_line('Job: '|| v_job);
end;

I hope that this example is very clear in all of its aspects. The only difference is that we added few more fields (sal and job) and variables (v_sal and v_job) and displayed them. The most important issue to remember is that the order of the fields should coincide with the order of the variables.