PL/SQL SELECT INTO 📥
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 INTOis 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
EXCEPTIONblock to handle this gracefully.
B. TOO_MANY_ROWS
If your WHERE clause finds more than one row, Oracle throws an error.
- The Secret:
SELECT INTOis 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;