Ticker

6/recent/ticker-posts

Select Into Statement in Oracle PL/SQL - Javainhand Tutorial

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

Select Into Statement in Oracle PL/SQL

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

Select Statement in Oracle SQL

Select Statement is use 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 undertand 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 use for fetch record 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 same.
  2. Not store more than one value into variable otherwise it will PL/SQL program throw an exceptions which is called too_many_rows exception.

Example of Select into Statement in Oracle PL/SQL

For Example, here we are using EMPLOYEES table because this table is already available in your database instance. If you have not having 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 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 have store first name of the employee which 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 be occur which we have to identified before the executing your program.

1.     If employee which id is 100 have not first name then no_data_found exception occur.

2.     If employee which id is 100 have more than one records then too_many_rows exception occur.

So be careful, Whenever you will use Select into Statement in PL/SQL program then check where clause use primary key column or make your record unique using two or more conditions. So your select statement always return one record.

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

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 program 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 into the Select Statement. The following PL/SQL program help 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 have not any record which have employee_id 22.

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

This error comes when select statement returns more than one records. 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 helpful for you then please share with others and if you have any doubt regarding this post then ask your question in comment box.

Post a Comment

0 Comments