Updated April 5, 2023
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.
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 –
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 –
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.
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 –
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.
