Skip to content

PL/SQL SELECT INTO 📥

Prerequisites: Oracle SELECT Statement, PL/SQL Variables

Mentor's Note: In regular SQL, you fetch data to see it on your screen. In PL/SQL, you fetch data to use it in your program. SELECT INTO is the bridge that carries data from your tables into your variables. 💡

🌟 The Scenario: The Targeted Pickup 📦

Imagine you are at a pizza shop. - The Database: The oven full of pizzas. - The SELECT INTO: You say "Give me the specific pizza with Order ID 101." - The Variable: You put that pizza in Your Box (Variable) so you can take it home and eat it. ✅

💻 1. The Basic Syntax

DECLARE
   v_name VARCHAR2(100);
BEGIN
   -- 🚀 Action: Pull one specific value
   SELECT first_name INTO v_name
   FROM employees
   WHERE employee_id = 100;

   DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_name);
END;

🛑 2. The Strict Rules (Architect's Note) 🛡️

SELECT INTO has two very strict rules. If you break them, your program will crash!

A. NO_DATA_FOUND

If your WHERE clause finds Zero rows, Oracle throws an error. - Solution: Use an EXCEPTION block to handle this gracefully.

B. TOO_MANY_ROWS

If your WHERE clause finds more than one row, Oracle throws an error. - The Secret: SELECT INTO is designed for Exactly One row. If you need many rows, you must use a Cursor.

💻 3. Safe Implementation Example

DECLARE
   v_salary NUMBER;
BEGIN
   SELECT salary INTO v_salary 
   FROM employees WHERE employee_id = 999; -- ID doesn't exist!
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('⚠️ Error: That employee was not found.');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('⚠️ Error: Multiple employees found.');
END;

📈 Learning Path