Skip to content

PL/SQL Stored Procedures 🤖

Prerequisites: PL/SQL Basics, Exception Handling

Mentor's Note: An Anonymous block is a one-time thing. A Stored Procedure is a program that is Saved in the database. Anyone with permission can call it, from any app, any number of times. It's the ultimate tool for code reuse! 💡

🌟 The Scenario: The Coffee Machine ☕

  • The Procedure: You write a program called brew_coffee.
  • The Input (IN): You tell it how many sugars. 🍬
  • The Output (OUT): It gives you a cup of coffee. ☕
  • The Result: You don't have to know how the machine works internally; you just "Call" the procedure.

💻 1. Creating a Procedure

CREATE OR REPLACE PROCEDURE welcome_user(p_name IN VARCHAR2) AS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello ' || p_name || '! Welcome to Surat Academy.');
END;
/

💻 2. Working with Parameters (The 3 Modes) 🛠️

Mode Direction Purpose
IN Input 📥 Passing data into the procedure (Read-only).
OUT Output 📤 Returning a result back to the caller.
IN OUT Both 🔄 Passing a value in, changing it, and sending it back.

Example: The Salary Adjuster

CREATE OR REPLACE PROCEDURE adjust_salary(
   p_emp_id IN NUMBER,
   p_inc    IN NUMBER,
   p_new_sal OUT NUMBER -- 📤 Return value
) AS
BEGIN
   UPDATE employees SET salary = salary + p_inc 
   WHERE id = p_emp_id
   RETURNING salary INTO p_new_sal;
END;
/

🏗️ Architect's Note: Performance & Security 🛡️

  1. Compilation: Procedures are compiled once. When you call them, they run instantly without the overhead of parsing SQL. ⚡
  2. Security: You can give an intern permission to "EXECUTE" the give_raise procedure without giving them "UPDATE" permission on the EMPLOYEES table. This keeps your data safe. 🔒
  3. Abstraction: If you change the math inside the procedure, you don't have to update your Java or Python code. The app just gets the new result automatically.

📈 Learning Path