`SELECT INTO` Statement in PL/SQL

When working with PL/SQL, one of the most common requirements is to fetch data from a database table and store it into variables. This is especially useful when you want to process or display the fetched data within your PL/SQL block.

To accomplish this, we use the SELECT INTO statement.

In this article, we’ll explore the SELECT INTO statement in detail, understand its syntax, learn how it works, and see multiple examples to solidify our understanding. Let’s dive in!

---

🔍 What is SELECT INTO in PL/SQL?

The SELECT INTO statement is used to retrieve data from a table and assign it directly to one or more variables.

✅ Use Cases

• Fetch a single record’s data into variables

• Perform calculations or validations based on retrieved data

• Use data for further operations (like insert/update/logic control)

---

📌 Syntax of SELECT INTO

SELECT column1, column2, ..., columnN 
INTO variable1, variable2, ..., variableN 
FROM table_name 
WHERE condition;

⚠️ Important Points:

1. Only one row must be returned. If the query returns more than one row, you’ll get the TOO_MANY_ROWS error.

2. If no rows are returned, you’ll get a NO_DATA_FOUND exception.

3. The data types of variables must match the column types they are fetching.

---

🛠 Example 1: Fetching a Single Column

Let’s say we have a table named students with the following structure:

We want to fetch the score of a student based on their ID.

🔽 Code:

DECLARE
   v_score NUMBER(3);
BEGIN
   SELECT score 
   INTO v_score 
   FROM students 
   WHERE student_id = 1;

   DBMS_OUTPUT.PUT_LINE('Student score is: ' || v_score);
END;

🧠 Explanation:

• We declare a variable v_score of type NUMBER(3).

• The SELECT statement fetches the score where student_id = 1.

• The result is stored in v_score, and printed using DBMS_OUTPUT.

---

🛠 Example 2: Fetching Multiple Columns into Multiple Variables

Now let’s fetch both the name and score of a student.

🔽 Code:

DECLARE
   v_name  VARCHAR2(50);
   v_score NUMBER(3);
BEGIN
   SELECT name, score 
   INTO v_name, v_score 
   FROM students 
   WHERE student_id = 2;

   DBMS_OUTPUT.PUT_LINE(v_name || ' scored ' || v_score || ' marks.');
END;

🧠 Explanation:

• We use two variables: v_name for the name column, and v_score for the score column.

• SELECT name, score INTO v_name, v_score ensures that each column is assigned to the appropriate variable.

---

🛠 Example 3: Using SELECT INTO with Expressions

You can also fetch computed values using expressions.

🔽 Code:

DECLARE
   v_total_score NUMBER(5);
BEGIN
   SELECT (score + 10)
   INTO v_total_score
   FROM students
   WHERE student_id = 1;

   DBMS_OUTPUT.PUT_LINE('Score after bonus: ' || v_total_score);
END;

🧠 Explanation:

• We fetch the score and add 10 to it using an expression.

• The result is stored in v_total_score.

---

🛠 Example 4: Handling Exceptions

Since SELECT INTO throws errors for multiple or no rows, let’s handle that gracefully.

🔽 Code:

DECLARE
   v_score NUMBER(3);
BEGIN
   SELECT score 
   INTO v_score 
   FROM students 
   WHERE student_id = 99;

   DBMS_OUTPUT.PUT_LINE('Score: ' || v_score);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No student found with that ID.');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('More than one student found!');
END;

🧠 Explanation:

• NO_DATA_FOUND: When no row is returned.

• TOO_MANY_ROWS: When more than one row is returned.

• The exception block ensures your program doesn't crash.

---

🧪 Example 5: Fetching Data from a Joined Table

You can even use SELECT INTO with joins to fetch combined information.

Suppose we have:

students table:

scores table:

🔽 Code:

DECLARE
   v_name  VARCHAR2(50);
   v_score NUMBER(3);
BEGIN
   SELECT s.name, sc.score
   INTO v_name, v_score
   FROM students s
   JOIN scores sc ON s.student_id = sc.student_id
   WHERE s.student_id = 1;

   DBMS_OUTPUT.PUT_LINE(v_name || ' has scored ' || v_score);
END;

---

🧱 Best Practices

• Always validate that the SELECT statement returns only one row.

• Always use the appropriate data type for variables.

• Use EXCEPTION blocks to handle errors.

• Prefer naming conventions for variables (v_ prefix is common).

• Use LIMIT 1 in advanced SQL to avoid TOO_MANY_ROWS (Note: only in cursors or dynamic SQL).

---

📚 Summary

---

🧩 Final Thoughts

The SELECT INTO statement is a fundamental part of PL/SQL programming. Whether you're building procedures, writing anonymous blocks, or automating logic within the Oracle database, this is a tool you will use often. Make sure to understand how it behaves with one or more rows, how to handle errors, and how to match your variables correctly.

---

Thanks for reading! Keep learning ! 🙌

---