Stored Procedure (SP)

SP কী?

Precompiled SQL block যা DB-তে সংরক্ষিত থাকে। একাধিক SQL statement একসাথে execute।

উপকারিতা

• Complex logic centralize
• Network traffic কম
• Performance ভালো
• Reusable

মৌলিক Syntax

DELIMITER $$

CREATE PROCEDURE GetActiveUsers()
BEGIN
    SELECT id, name, email FROM users
    WHERE status = 'active';
END$$

DELIMITER ;

CALL GetActiveUsers();

IN Parameter

DELIMITER $$
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END$$
DELIMITER ;

CALL GetUserById(5);

OUT Parameter

DELIMITER $$
CREATE PROCEDURE CountUsers(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM users;
END$$
DELIMITER ;

CALL CountUsers(@count);
SELECT @count;

Condition ও Logic

DELIMITER $$
CREATE PROCEDURE ApplyDiscount(IN min_price DECIMAL(10,2))
BEGIN
    DECLARE rate DECIMAL(3,2) DEFAULT 0.10;

    IF min_price >= 1000 THEN
        SET rate = 0.20;
    ELSEIF min_price >= 500 THEN
        SET rate = 0.15;
    END IF;

    UPDATE products
    SET price = price * (1 - rate)
    WHERE price >= min_price;

    SELECT CONCAT(ROW_COUNT(), ' updated') AS msg;
END$$
DELIMITER ;

CALL ApplyDiscount(500);

Transaction SP

DELIMITER $$
CREATE PROCEDURE TransferMoney(
    IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE bal DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Failed' AS status;
    END;

    START TRANSACTION;

    SELECT balance INTO bal FROM accounts WHERE id=from_id FOR UPDATE;

    IF bal < amount THEN
        ROLLBACK;
        SELECT 'Insufficient' AS status;
    ELSE
        UPDATE accounts SET balance=balance-amount WHERE id=from_id;
        UPDATE accounts SET balance=balance+amount WHERE id=to_id;
        COMMIT;
        SELECT 'Success' AS status;
    END IF;
END$$
DELIMITER ;

CALL TransferMoney(1, 2, 500);

Loop

DELIMITER $$
CREATE PROCEDURE InsertTest(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= n DO
        INSERT INTO users (name, email)
        VALUES (CONCAT('User_', i), CONCAT('u', i, '@test.com'));
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL InsertTest(100);

Management

SHOW PROCEDURE STATUS WHERE Db='shop_db';
SHOW CREATE PROCEDURE GetActiveUsers;
DROP PROCEDURE IF EXISTS GetActiveUsers;