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
5 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