PL/SQL SELECT INTO Existing Table
Last Updated : 05 Nov, 2024
PL/SQL is a programming language that is used alongside SQL for writing procedural code such as stored procedures, functions, triggers, and packages within the Oracle Database. It was developed by Oracle Corporation and is widely used in database programming.
PL/SQL is a programming language that has three parts: the Declarative part, the Executable part, and the Exception-handling part. PL/SQL blocks are created using four keywords: DECLARE, BEGIN, EXCEPTION, and END. In the DECLARE part, we declare constants and variables. In the BEGIN block, we write the instructions that need to be executed. In the EXCEPTION part, we handle any exceptions that might occur. Finally, the END keyword indicates the end of the PL/SQL statements. In this article, you will learn about how to use a SELECT INTO statement in an existing table in PL/SQL, its functionality along with some examples.
How SELECT INTO Works in PL/SQL
The SELECT INTO statement is one of the most frequently used statements in PL/SQL. With the SELECT INTO statement in PL/SQL, you can fetch records from the database and bind them to those variables.
The SELECT INTO statement for the database the most plays the role of both fetching data from the database into the code and substituting variables with the data into the PL/SQL code. It puts the result of a query question to a variable or a variety of variables. These parameters should correspond to the datatypes of the columns used in the query, if they are not, an error may arise.
Syntax
SELECT column1, column2, . . . . , column_n
INTO
variable1, variable2, . . . . , variable_n
FROM table
WHERE expresion1, expression2, . . . . , expression_n;
Explanation: In the above syntax, we are first selecting the column from which value is to be fetched using the SELECT keyword, and then by using the WHERE clause we are getting that particular data and then we are copying that particular data into the variable using INTO keyword.
Example of PL/SQL SELECT INTO Existing Table
Let's take an example of EMPLOYEE table having EMP_ID, NAME, AGE, and SALARY as columns.
PL/SQL CREATE TABLE EMPLOYEE ( EMP_ID INT PRIMARY KEY, NAME VARCHAR(50), AGE INT, SALARY INT ); INSERT INTO EMPLOYEE (EMP_ID, NAME, AGE, SALARY) VALUES (001, 'Sahil', 21, 15000), (002, 'Alen', 22, 13000), (003, 'John', 22, 14000), (004, 'Alex', 20, 13000), (005, 'Mathew', 22, 14000), (006, 'Sia', 21, 15000), (007, 'David', 22, 16000), (008, 'Tim', 21, 14000), (009, 'Leo', 20, 15000), (010, 'Tom', 21, 16000);
Output:
EMPLOYEE tableExample 1: SELECT INTO a Single Variable
Let's print the SALARY of the Employee whose EMP_ID =1.
Syntax:
SELECT column1, column2, . . . . , column_n
INTO
variable1, variable2, . . . . , variable_n
FROM table
WHERE expresion1, expression2, . . . . , expression_n;
PL/SQL Block:
DECLARE
v_salary NUMBER(8);
BEGIN
SELECT SALARY INTO v_salary
FROM EMPLOYEE
WHERE EMP_ID= 1;
DBMS_OUTPUT.PUT_LINE( v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee with EMP_ID=1 not found.');
END;
Output:
15000
Explanation: Here we are first declaring a variable ‘ v_salary’ to store the SALARY of the Employee. Then in the BEGIN section, we are fetching the SALARY by using the SELECT INTO statement and using a WHERE clause. Once the SALARY is fetched then we print the SALARY using the DBMS_OUTPUT.PUT_LINE. If the data is not present in the table then it will throw an EXCEPTION as EMP_ID not found and at last, we are using the END keyword to end the PL/SQL block.
Example 2: SELECT INTO Two Variables from Two Columns
Let's print the SALARY and AGE of the employee whose EMP_ID =1.
Syntax:
SELECT column1, column2, . . . . , column_n
INTO
variable1, variable2, . . . . , variable_n
FROM table
WHERE expresion1, expression2, . . . . , expression_n;
PL/SQL Block:
DECLARE
v_salary NUMBER(8);
v_age NUMBER(8);
BEGIN
SELECT SALARY, AGE
INTO v_salary, v_age
FROM EMPLOYEE
WHERE EMP_ID= 1;
DBMS_OUTPUT.PUT_LINE(v_salary);
DBMS_OUTPUT.PUT_LINE( v_age);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee with EMP_ID=1 not found');
END;
Output:
15000
21
Explanation: Here we are first declaring the variables ‘ v_salary’, and ‘v_age’ to store the SALARY and AGE of the Employee. Then in the BEGIN section, we are fetching the SALARY and AGE by using the SELECT INTO statement and using a WHERE clause. Once the SALARY and AGE are fetched then we print the SALARY and AGE using the DBMS_OUTPUT.PUT_LINE. If the data is not present in the table then it will throw an EXCEPTION as EMP_ID is not found and at last we are using the END keyword to end the PL/SQL block.
Important Considerations
- The SELECT INTO statement is designed to fetch only a single row. If the query returns more than one row, PL/SQL raises a TOO_MANY_ROWS exception.
- Always include exception handling for NO_DATA_FOUND and TOO_MANY_ROWS to ensure the block doesn’t fail unexpectedly.
- For cases where multiple rows are expected, consider using a cursor instead of SELECT INTO.
- Ensure that the variables receiving data from the query have data types that match or are compatible with the queried columns.
Conclusion
In Conclusion, the declaration SELECT INTO statement in PL/SQL is a very effective method to pull the data from the database and assign that data to the variables working with PL/SQL code. One of its major functions is to offer a time-efficient selection of one or more rows and increase the flexibility of data manipulation and handling.
It is necessary, however, to remind yourself that SELECT INTO should be used mainly for retrieving data into variables, but not for inserting data into existing tables. The feature, therefore, speeds up subsets of data collection within PL/SQL programs, letting the developers simply work with the table data for a host of purposes like calculations, validations, and business logic implementation.
Similar Reads
PL/SQL INSERT INTO SELECT
In PL/SQL, the INSERT INTO SELECT statement is used to insert data into a table by selecting data from one or more tables. This is a powerful feature for populating tables with data from existing tables or views, making it useful for data migration, reporting, and backup processes. In this guide, we
5 min read
SQLite INSERT INTO SELECT
SQLite is a lightweight and server-less relational database management system. It requires very minimal configuration which has proven to be very helpful for developers to integrate it into any applications with ease. Due to its server-less architecture, we can use SQLite in various mobile applicati
4 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records into the specified table. We use this statement to insert data directly into a table by specifying column names in a specific order. The SELECT statement is used to retrieve data from the table, and it can be used in conjunction with
6 min read
SELECT Data from Multiple Tables in SQL
In SQL (Structured Query Language), it is a common requirement to retrieve data from more than one table at once. When you work with relational databases, you often have to combine data from multiple tables to get meaningful results. SQL provides many methods for selecting data from multiple tables,
4 min read
SQL SELECT INTO Statement
The SELECT INTO statement in SQL is a powerful and efficient command that allow users to create a new table and populate it with data from an existing table or query result in a single step. This feature is especially useful for creating backups, extracting specific subsets of data, or preparing new
5 min read
MySQL INSERT INTO SELECT Statement
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. INSERT INTO SELECT statement i
5 min read
What is Nested Select Statement in PL/SQL?
PL/SQL is a Procedural Language/Structured Query Language and it enables users to write procedural logic directly within the database, including procedures, functions, triggers, and packages. In this article, we will understand Nested select statements in PL/SQL along with the examples and so on. Un
4 min read
CREATE TABLE in SQL Server
SQL Server provides a variety of data management tools such as querying, indexing, and transaction processing. It supports multiple programming languages and platforms, making it a versatile RDBMS for various applications. With its robust features and reliability, SQL Server is a popular choice for
4 min read
PL/SQL Nested Table
PL/SQL, Oracle's procedural extension of SQL, offers powerful capabilities for managing and processing data within the database. One of its key features is support for complex data structures, including collections like nested tables. A nested table in PL/SQL is a dynamic, array-like data structure
5 min read
SQL Server SELECT INTO @Variable
In the world of SQL Server, the SELECT INTO statement is a powerful syntax for retrieving data from one or more tables and inserting it into a new table. However, what if you want to store the result set of a SELECT query into a variable rather than a table? This is where the SELECT INTO @Variable s
6 min read