Lesson 15 � Intermediate

STORED PROCEDURES:
REUSABLE SQL CODE.

Har baar same query likhna boring hai � baar baar SELECT * FROM students WHERE course = 'Python' paste karo. Stored procedure ek baar code likho, baar baar CALL se run karo. Application se database calls kam hoti hain, security bhi badhti hai.

? 22 min✓ Intermediate✓ Prerequisite: Views

WHY: Stored procedure kyun zaroori hai?

Socho ek e-commerce app hai. Har customer ke orders nikalne ke liye ek complex query hai � JOINs, filters, sorting. Agar yeh query har API call mein likhi jaaye toh code hoga, bug fix mushkil hoga, aur database par load badhega. Stored procedure mein ek baar likho, application seedha CALL kare � code clean, database fast, security tight.

PROCEDURE

Reusable SQL code block. Ek baar CREATE karo, baar baar CALL se run karo. Code duplication khatam.

PARAMETERS

Input ya output values pass karo. IN parameter se data lo, OUT parameter se result wapas bhejo. Flexible aur powerful.

CALL

Procedure run karne ka command. Jaise function call karte ho programming mein, waise hi CALL se procedure chalao.

HOW: Procedure banana aur run karna

Stored procedure banana simple hai � CREATE PROCEDURE se code likho, CALL se chalao. MySQL mein DELIMITER lagana zaroori hai taaki semicolon issue na ho.

1. Simple procedure banao

Sabse basic procedure � students table ka saara data dikhaye:

-- Simple procedure: saare students dikhao
DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
 SELECT * FROM students;
END //
DELIMITER ;

-- Procedure run karo
CALL GetAllStudents();

ℹ

Mental model: Stored procedure ek function hai jo database mein stored rehta hai. CREATE PROCEDURE = function define karna, CALL = function call karna. DELIMITER isliye lagate hain kyunki procedure ke andar bhi semicolon hote hain � MySQL ko pata hona chahiye ki procedure kab khatam hota hai.

2. Parameters ke saath procedure

Input parameter se course name lo aur uss course ke students dikhao:

-- IN parameter: input value pass karo
DELIMITER //
CREATE PROCEDURE GetStudentsByCourse(IN courseName VARCHAR(50))
BEGIN
 SELECT * FROM students WHERE course = courseName;
END //
DELIMITER ;

-- Procedure call karo with parameter
CALL GetStudentsByCourse('Python');
CALL GetStudentsByCourse('SQL');

ℹ

Pro tip: IN parameter default hota hai � agar tum IN na bhi likho toh kaam karega. Lekin explicit likhna acchi practice hai � code readable rehta hai. Parameter ka naam aur data type dono likho.

3. Multiple parameters

Ek se zyada parameters pass karo � course aur minimum marks dono filter karo:

-- Multiple IN parameters
DELIMITER //
CREATE PROCEDURE GetTopStudents(IN courseName VARCHAR(50), IN minMarks DECIMAL(5,2))
BEGIN
 SELECT * FROM students
 WHERE course = courseName AND marks >= minMarks
 ORDER BY marks DESC;
END //
DELIMITER ;

-- Dono parameters pass karo
CALL GetTopStudents('Python', 85.00);

4. OUTPUT parameter

OUT parameter se result wapas bhejo � total count nikalo:

-- OUT parameter: result wapas bhejo
DELIMITER //
CREATE PROCEDURE GetStudentCount(OUT totalCount INT)
BEGIN
 SELECT COUNT(*) INTO totalCount FROM students;
END //
DELIMITER ;

-- Procedure call karo aur output lo
CALL GetStudentCount(@count);
SELECT @count AS total_students;

ℹ

Key point: INTO keyword se query ka result OUT parameter mein store hota hai. @count ek session variable hai � procedure ke bahar bhi access kar sakte ho. Har OUT parameter ke liye alag variable banao.

5. IN + OUT dono

Ek procedure mein input bhi lo aur output bhi bhejo:

-- IN + OUT parameters
DELIMITER //
CREATE PROCEDURE GetCourseStats(
 IN courseName VARCHAR(50),
 OUT studentCount INT,
 OUT avgMarks DECIMAL(5,2)
)
BEGIN
 SELECT COUNT(*), AVG(marks)
 INTO studentCount, avgMarks
 FROM students WHERE course = courseName;
END //
DELIMITER ;

-- Call karo
CALL GetCourseStats('Python', @count, @avg);
SELECT @count AS students_in_python, @avg AS average_marks;

6. Variables declare karo

Procedure ke andar variables use karo calculations ke liye:

-- Local variables in procedure
DELIMITER //
CREATE PROCEDURE GetGradeDistribution(IN courseName VARCHAR(50))
BEGIN
 DECLARE totalStudents INT;
 DECLARE gradeA INT;
 DECLARE gradeB INT;
 DECLARE gradeC INT;

 SELECT COUNT(*) INTO totalStudents
 FROM students WHERE course = courseName;

 SELECT COUNT(*) INTO gradeA
 FROM students WHERE course = courseName AND marks >= 90;

 SELECT COUNT(*) INTO gradeB
 FROM students WHERE course = courseName AND marks >= 80 AND marks < 90;

 SELECT COUNT(*) INTO gradeC
 FROM students WHERE course = courseName AND marks >= 70 AND marks < 80;

 SELECT gradeA AS 'Grade A (90+)',
 gradeB AS 'Grade B (80-89)',
 gradeC AS 'Grade C (70-79)',
 totalStudents AS 'Total';
END //
DELIMITER ;

CALL GetGradeDistribution('Python');

7. Procedure modify aur delete karo

Procedure update ya delete karna ho toh:

-- Procedure update karo (drop + create)
DROP PROCEDURE IF EXISTS GetAllStudents;

DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
 SELECT id, name, course, marks FROM students ORDER BY name;
END //
DELIMITER ;

-- Saare procedures dikhao
SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';

-- Procedure ka code dekho
SHOW CREATE PROCEDURE GetAllStudents;

Try it: Procedure banao aur call karo

Neeche ka editor SQL jaisa hai. Apni stored procedure likho aur "Run Query" dabao. Screen par results dikhenge � yeh browser-based simulation hai, real database nahi.

SQL playgroundStored procedures banao aur call karo

query.sql

Run Query dabayein

Quick check

Ek stored procedure banao jo students ko course ke basis par filter kare. Procedure ka naam GetByCourse rakho aur ek IN parameter lo.

CREATE PROCEDURE se shuru karo, procedure ka naam likho, IN parameter mein course name lo, phir BEGIN...END mein SELECT query likho.

Common stored procedure mistakes

DELIMITER bhoolna: MySQL mein procedure create karte waqt DELIMITER // lagana zaroori hai � warna semicolon se procedure break ho jaata hai.
IF EXISTS na lagana: DROP PROCEDURE IF EXISTS lagao warna "procedure already exists" error aayega baar baar create karne pe.
Parameter ka data type na dena: Har parameter ka data type likho � IN c VARCHAR(50) likho, IN c mat likho.
OUT parameter mein SELECT seedha karna: SELECT COUNT(*) INTO totalCount likho, SELECT COUNT(*) seedha mat likho � result OUT parameter mein store hona chahiye.
Procedure ka naam duplicate rakhna: Ek naam se ek hi procedure ho sakta hai. Agar change karna ho toh pehle DROP karo phir CREATE karo.

Stored Procedures clear hai?

Ab Triggers par chalo � automatic SQL actions seekho jo data change hone par apne aap fire hote hain.

STORED PROCEDURES:REUSABLE SQL CODE.