In the previous tutorial, we learned what is PL/SQL as well as how to write a sample PL/SQL code. There we saw that PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java. In this article, we will learn how to use SELECT INTO statement to retrieve data of a single row from a database and assign the selected values into variables.
PLSQL SELECT INTO statement offers a quick and fast way to retrieve data into variables. The following is the syntax of PLSQL SELECT INTO statement:
SELECT
Col1, col2, col3, etc
INTO
Var1, var2, var3, etc
FROM
table_name
WHERE
condition;
From the above syntax, we realize that the number of selected list must be equal to the number of variables list and the corresponding datatype must be similar.
Now let’s see an example of PLSQL SELECT INTO statement
I will a sample table called users with the columns in the figure above to illustrate the example.
The following code will fetch the email of the user based on the id. Note that ID is the primary key of the table, therefore, we will retrieve only one row because the table has one row with the given id.
DECLARE
user_email users.email%TYPE;
BEGIN
-- retrieve email of the user with id 121 and assign it to variable user_email
SELECT email
INTO user_email
FROM users
WHERE id = 121;
-- print the email fetched from the table
DBMS_OUTPUT.put_line (user_email);
END;
Notes:
- We first declared a variable, user_email, to hold the email of the user. The datatype of the variable is compatible with the email column datatype in the table.
- We used SELECT INTO statement to get the value from the database and assign it our declared variable, user_email.
- We used dbms_output.put_line procedure to print the email of the user on the screen.
- If we passed an ID that does not exist in the table, we will get NO_DATA_FOUND exception, which is thrown by the db whenever there is no data found.
Selecting multiple columns example
Now that we have seen how to select into one variable, let’s see how we can select into more than one variable. This time I will select name, email and department of user with id 121.
DECLARE
user_name users.name%TYPE;
user_email users.email%TYPE;
user_dept users.department%TYPE;
BEGIN
-- select list
SELECT name, email, department
--variable list
INTO user_name, user_email, user_dept
FROM users
WHERE id = 121;
-- print the email fetched from the table
DBMS_OUTPUT.put_line (
user_email || ', ' || user_email || ', ' || user_dept);
END;
We confirm that the number of the select list must be equal to the number of variable list and that their corresponding datatype should be compatible.
Selecting entire row example
We have known how to select into one variable as well as multiple variables. Let us learn how to select a complete row using PLSQL SELECT INTO statement.
The following example gets the entire row of a user with a given ID:
DECLARE
user_ users%ROWTYPE;
BEGIN
-- select list
SELECT *
--variable list
INTO user_
FROM users
WHERE id = 121;
-- print the email fetched from the table
DBMS_OUTPUT.put_line (
'Name: ' || user_.name || ', ' || 'Email: ' || user_.email);
END;
To select an entire row, declare a record based on the row of the users table. This will hold the entire row data of the users table.
Common errors in PL/SQL SELECT INTO statement
When writing PL/SQL SELECT INTO, you will most probably encounter the following errors.
- ORA-01403: no data found – this means that there is no data to be fetched. Therefore, oracle issues the error.
- ORA-00947: not enough values The INTO list contains fewer variables than the SELECT list – This means that the number of columns in the select clause is more than the number of variables in the into clause.
- ORA-00913: too many values The INTO list contains more variables than the SELECT list – This means that the number of columns in the select clause is less than the number of variables in the into clause.
- ORA-06502: PL/SQL: numeric or value error – This means that the number of select list and variable list are same but their corresponding datatypes are incompatible.
Common exceptions handling in PL/SQL SELECT INTO statement
The good news is that you can always handle errors prior to happening. Oracle has predefined PL/SQL exceptions that you can use. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.
To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes.
Exception handling in Oracle Database
DECLARE
user_ users%ROWTYPE;
BEGIN
-- select list
SELECT *
--variable list
INTO user_
FROM users
WHERE id = 1210;
-- print the email fetched from the table
DBMS_OUTPUT.put_line (
'Name: ' || user_.name || ', ' || 'Email: ' || user_.email);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('The ID does not exist');
END;
The above code prints 'The ID does not exist' because my table has not user with ID number 1210. You can use OTHERS handler to handle other errors.
Conclusion
Now, you should know how to use the PL/SQL SELECT INTO statement to fetch a single row from a table into variables. That’s it in this section. I hope you liked it. Kindly please share it on your social network and help me reach out to more people. Thanks & have a great day!
