Select Into Statement in Oracle PL/SQL - Javainhand Tutorial

In this PL/SQL Tutorial, We will explore the Select into Statement in Oracle PL/SQL.

Before we proceed in this blog post, We will discuss the Select Statement in Oracle SQL.

Select Statement in Oracle SQL

Select Statement is used for the fetch records or data form database with the help of one or more tables.

Syntax of Select Statement in Oracle SQL

SELECT * FROM TABLE_NAME WHERE CONDITION;

By the Above syntax you can understand how to retrieve all column data from the table if you want to retrieve specify column data then use that column name instead of asterisk(*) sign.   

Select Into Statement in Oracle PL/SQL

Select Into Statement is basically used for fetching records or data from the database with the help of one or more tables and storing that value into specified variables.

Syntax of Select into Statement in Oracle PL/SQL

SELECT COLUMN_NAME INTO VARIABLE_NAME FROM TABLE_NAME WHERE CONDITION;

Rule which need to be follow:-

1. Variables and column data type will be the same.
2. Not store more than one value into variable otherwise it will PL/SQL program throw an exception which is called too_many_rows exception.

Example of Select into Statement in Oracle PL/SQL

For Example, here we are using the EMPLOYEES table because this table is already available in your database instance. If you do not have an EMPLOYEES table then you can create with the below SQL command.

CREATE TABLE "EMPLOYEES" 
   (	"EMPLOYEE_ID" NUMBER, 
	"FIRST_NAME" VARCHAR2(20), 
	"LAST_NAME" VARCHAR2(25) , 
	"EMAIL" VARCHAR2(25), 
	"DEPARTMENT_ID" NUMBER,
	 CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
   ) ;

After creating the table, We need some data information so we will understand Select Into Statement in PL/SQL. I am sharing few data which you can directly insert into your table.

Insert into 
EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT_ID) 
values 
(100,'Steven','King','SKING',90);
/
Insert into
EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT_ID)
values 
(101,'Neena','Kochhar','NKOCHHAR',90);
/
Insert into
EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT_ID) 
values 
(102,'Lex','De Haan','LDEHAAN',90);
/
Insert into 
EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT_ID) 
values 
(103,'Alexander','Hunold','AHUNOLD',60);

Now, we are ready with the table and it’s data. I think you have created the same table and it’s record.

Example 1:- In this example, We will fetch employee first name using unique employee id.

DECLARE
--V_FIRST_NAME VARIABLE DECLARE--
V_FIRST_NAME  VARCHAR2(100); 
BEGIN
--STORE EMPLOYEE FIRST NAME INTO V_FIRST_NAME VARIABLE--
SELECT FIRST_NAME INTO V_FIRST_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID=100;
--PRINT V_FIRST_NAME VARIABLE VALUE--
DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME); 
END;

In the above example, we store first name of the employee whose employee_id is 100 and then print that variable value using dbms_output.put_line statement. In this example, we are not handling any exceptions because in this section our main focus about the Select into Statement.

Note:- Some error could occur which we have to identify before the executing the program.

1.     If an employee whose id is 100 that has no first name in the table after the executing the program we will face no_data_found exception.

2. if we find more than one record on the employee_id 100 then we will face  too_many_rows exception.

So be careful, Whenever you use Select into Statement in PL/SQL program then check that sql statement always returns a single record. So your select statement always returns one record.

Above Exceptions which we are talking about, all these exceptions. We will cover the exceptions handling blog posts.

Example 2:- In the example, we will fetch employee first name and last name using employee id.

DECLARE
--V_FIRST_NAME VARIABLE DECLARE--
V_FIRST_NAME  VARCHAR2(100); 
V_LAST_NAME VARCHAR2(100);
BEGIN
--STORE EMPLOYEE FIRST NAME INTO V_FIRST_NAME VARIABLE--
SELECT FIRST_NAME,LAST_NAME INTO V_FIRST_NAME,V_LAST_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID=100;
--PRINT V_FIRST_NAME VARIABLE VALUE--
DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME||'  '||V_LAST_NAME); 
END;

Conclusion:-

Select into statement in PL/SQL programs always use to store only one record into the variables.

Few Error which you will face in future or current programming Example

1. PL/SQL: ORA-00947: not enough values
2. 06502. 00000 -  "PL/SQL: numeric or value error%s"
3. ORA-01403: no data found
4. ORA-01422: exact fetch returns more than requested number of rows

PL/SQL:ORA-00947:Not Enough Values

This Error comes when you are getting two values or three values but not enough variables used in the Select Statement. The following PL/SQL program helps to understand.

SELECT FIRST_NAME,LAST_NAME INTO V_FIRST_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID=222;

PL/SQL:ORA-06502: "PL/SQL:numeric or value error"

This Error comes when variables and columns data type mismatch. The following PL/SQL program will help to understand.

DECLARE
--V_FIRST_NAME VARIABLE DECLARE--
V_FIRST_NAME  NUMBER;
V_LAST_NAME VARCHAR2(100);
BEGIN
--STORE EMPLOYEE FIRST NAME INTO V_FIRST_NAME VARIABLE--
SELECT FIRST_NAME INTO V_FIRST_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID=100;
--PRINT V_FIRST_NAME VARIABLE VALUE--
DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME); 
END;

ORA-01403:No Data Found

This Error comes when variables and columns data type mismatch. The following PL/SQL program will help to understand.

DECLARE
--V_FIRST_NAME VARIABLE DECLARE--
V_FIRST_NAME  VARCHAR2(100);
V_LAST_NAME VARCHAR2(100);
BEGIN
--STORE EMPLOYEE FIRST NAME INTO V_FIRST_NAME VARIABLE--
SELECT FIRST_NAME INTO V_FIRST_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID=22;
--PRINT V_FIRST_NAME VARIABLE VALUE--
DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME); 
END;

When you run the above PL/SQL program then you will find that we do not have any records whose has employee_id 22.

ORA-01422:exact fetch returns more than requested number of rows

This error comes when the select statement returns more than one record. The following PL/SQL program will help to understand.

DECLARE
--V_FIRST_NAME VARIABLE DECLARE--
V_FIRST_NAME  VARCHAR2(100);
V_LAST_NAME VARCHAR2(100);
BEGIN
--STORE EMPLOYEE FIRST NAME INTO V_FIRST_NAME VARIABLE--
SELECT FIRST_NAME INTO V_FIRST_NAME FROM EMPLOYEES
WHERE DEPARTMENT_ID=90;
--PRINT V_FIRST_NAME VARIABLE VALUE--
DBMS_OUTPUT.PUT_LINE(V_FIRST_NAME); 
END;

That's all for today, I hope this post will help to understand Select into Statement in PL/SQL program. If this post is helpful for you then please share with others and if you have any doubt regarding this post then ask your question in the comment box.