EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL SELECT INTO
 

PL/SQL SELECT INTO

Updated April 5, 2023

PL/SQL SELECT INTO

 

 

Introduction to PL/SQL SELECT INTO

The following article provides an outline for PL/SQL SELECT INTO. PL/SQL select into statement is used for taking the values of a single row belonging to a single record into a single variable. It becomes easier to handle the data when you have a complete row data present inside the variable in PL/ SQL program. In this article, we will study the syntax of the PL/ SQL select into statement, most common exceptions that occur while using this statement, and will also learn how we can implement the select into a statement to retrieve a single column or multiple column values in a variable with the help of certain examples.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

The PL/ SQL is the easiest, fastest and simplest way of fetching the data of a single row into a variable. The syntax of PL/ SQL SELECT INTO statement used for retrieving the data of a particular row containing single or multiple column values in it into a single variable is as shown below –

SELECT list_of_values INTO list_of_variables FROM name_of_table WHERE required_condition;

The terminologies used in the above syntax are as explained one by one in the below description –

  • List of values – This is the name of the columns or expression values that we are trying to retrieve from the table data.
  • List of variables – These are the variable names inside which we will store the retrieved values from the table of the list of values. Note that the number of variables and the datatype of variables used in this list should be the same or corresponding to the datatype of the list of values and the number of values retrieved from it.
  • Name of the table – This is the table name from which we want to retrieve the data for the list of values specified in the SELECT statement.
  • Required condition – This can be any condition that we are trying to specify in order to filter out the table data and retrieve only one row from the content of the table that we want to store into variables. Use of where clause is optional in nature.

Note: While using select into statement, there are certain things that we need to keep in our minds that are listed below –

We can make the use of other clauses and statements along with the SELECT statement such as UNION, HAVING, INNER JOIN, and GROUP BY to get one row retrieved from a select statement.

In case if the query statement used by using select statement in it retrieves more than one-row values, then the PL/ SQL DBMS will raise an exception of TOO_MANY_ROWS.

In case if the query statement used by using select statement in it retrieves none of the row values, then the PL/ SQL DBMS will raise an exception of NO_DATA_FOUND.

You can make the use of the SELECT INTO statement in PL/ SQL to retrieve the row containing single or multiple column values in the resultant for storing them in variables.

Examples of PL/SQL SELECT INTO

Let us now try to understand the implementation of PL/ SQL select into a statement with the help of certain examples. Let us talk about the sample data firstly, which we will consider for demonstrating the use of select into a statement. We have one existing table in our PL/ SQL database whose name is customer_details. To check the existing content of the table, you can fire the following query statement –

SELECT * FROM customer_details;

The execution of the above query statement gives out the following output –

pl sql select into 1

Example #1 – Retrieving a single column into a variable

Now, we will try to retrieve the value of the first name of the customer stored inside the f_name column into a variable with the name first_name. For this, we will make use of the following PL/ SQL program. Along with that, we will also retrieve the value of the variable in the DBMS output and will verify whether the column value is properly retrieved in the variable or not –

DECLARE
first_name customer_details.f_name%TYPE;
BEGIN
-- retrieve the value of the first nam eof customer with id 101 assign it to first_name
SELECT f_name INTO first_name
FROM customer_details
WHERE customer_id = 101;
-- Display the customer first name
dbms_output.put_line( first_name );
END;

The execution of the above query statement gives out the following output –

pl sql select into 2

Example #2 – Retrieving the whole row record into a variable

We can even retrieve the complete row record into a single variable by making the use of the SELECT INTO statement. Let us consider an example where we will try to retrieve the complete row data of the customer having the customer id as 110. We can do this by making use of below PL/ SQL program below –

DECLARE
cust_record_variable customer_details%ROWTYPE;
BEGIN
-- retrieve the value of the row record of customer with id 110 assign it to cust_record_variable
SELECT * INTO cust_record_variable
FROM customer_details
WHERE customer_id = 110;
-- Display the customer information
dbms_output.put_line( cust_record_variable.f_name || ', Email Id: ' || cust_record_variable.email_id );
END;

The execution of the above query statement gives out the following output.

Output 3

Note that we can access the individual column value stored inside the variable by using the dot notation in the format such as “name of the variable. name of column”.

Example #3 – Retrieving the data into multiple variables

We can even retrieve the data of a single row and each and every individual column value of the row of the table or retrieved data into a corresponding variable for each and every field. Consider the same above example where we are trying to retrieve the first name and email id of the customer. Along with that, we will also retrieve the mobile number details but in individual variables as demonstrated in the below example –

DECLARE
cust_f_name customers.f_name%TYPE;
cust_email_id contacts.email_id%TYPE;
cust_mobile_number contacts.mobile_number%TYPE;
BEGIN
-- retrieve the value of the row record consisting first name, email id and mobile number of customer with id 110 assign it to respective variables
SELECT
f_name,
email_id,
mobile_number
INTO
cust_f_name,
cust_email_id,
cust_mobile_number
FROM
customers
WHERE
customer_id = 110;
-- Display the customer information
dbms_output.put_line(
cust_f_name || ', Contact Details: ' ||
cust_email_id || ' ' || cust_mobile_number );
END;

The execution of the above query statement gives out the following output –

Output 4

Conclusion

PL/SQL select into statement is used for taking the values of a single row belonging to a single record into a single variable. It becomes easier to handle the data when you have a complete row data present inside the variable in PL/ SQL program.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL SELECT INTO” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PL/SQL Cursor Loop
  2. PL/SQL to_DATE
  3. PL/SQL TRIM
  4. PL/SQL GROUP BY

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW