MySQL Stored Procedures

Stored Procedure क्या है?

Stored procedure named SQL routine है जो MySQL में stored और CALL से invoke होती है। यह compound BEGIN ... END, parameters, local variables, conditions, loops, handlers, multiple statements, result sets और data changes use कर सकती है।

Verified Orders Lab

DROP TABLE IF EXISTS orders_proc_lab;
CREATE TABLE orders_proc_lab (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  approved_by VARCHAR(50)
) ENGINE = InnoDB;

INSERT INTO orders_proc_lab
(order_id, customer_id, status, order_date, total_amount, approved_by)
VALUES
(1,101,'PAID','2026-08-01',1200,'Admin'),
(2,101,'PENDING','2026-08-05',500,NULL),
(3,102,'PAID','2026-08-03',750,'Admin'),
(4,103,'CANCELLED','2026-08-04',300,NULL),
(5,101,'PAID','2026-08-10',1500,'Manager'),
(6,102,'PENDING','2026-08-11',900,NULL),
(7,104,'PAID','2026-08-12',2200,'Manager'),
(8,101,'PAID','2026-08-14',650,'Admin');

Lab read summaries और controlled status changes support करती है। Routine creation appropriate privileges वाले account से चलाएँ।

Procedure Create और CALL

DELIMITER //
CREATE PROCEDURE list_customer_orders(
  IN p_customer_id INT
)
READS SQL DATA
BEGIN
  SELECT order_id, status, order_date, total_amount
  FROM orders_proc_lab
  WHERE customer_id = p_customer_id
  ORDER BY order_date, order_id;
END //
DELIMITER ;

CALL list_customer_orders(101);

DELIMITER mysql command-line client command है, server stored SQL नहीं। यह internal semicolons को one CREATE statement का part pass कराता है। GUI/drivers का workflow अलग हो सकता है।

IN, OUT और INOUT Parameters

DELIMITER //
CREATE PROCEDURE customer_paid_summary(
  IN  p_customer_id INT,
  OUT p_paid_count INT,
  OUT p_paid_total DECIMAL(12,2)
)
READS SQL DATA
BEGIN
  SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
  INTO p_paid_count, p_paid_total
  FROM orders_proc_lab
  WHERE customer_id = p_customer_id
    AND status = 'PAID';
END //
DELIMITER ;

CALL customer_paid_summary(101, @paid_count, @paid_total);
SELECT @paid_count, @paid_total;

INOUT supplied value normalize/accumulate कर सकता है, पर confusing contract hide न करें। Clear result set या documented output object app code के लिए easier हो सकता है।

Local Variables, IF और Validation

DELIMITER //
CREATE PROCEDURE approve_order(
  IN p_order_id INT,
  IN p_approved_by VARCHAR(50)
)
MODIFIES SQL DATA
BEGIN
  DECLARE v_status VARCHAR(20) DEFAULT NULL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_status = NULL;

  IF p_approved_by IS NULL OR TRIM(p_approved_by) = '' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Approver name is required';
  END IF;

  SELECT status INTO v_status
  FROM orders_proc_lab
  WHERE order_id = p_order_id;

  IF v_status IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Order not found';
  ELSEIF v_status = 'CANCELLED' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Cancelled order cannot be approved';
  ELSE
    UPDATE orders_proc_lab
    SET status='PAID', approved_by=p_approved_by
    WHERE order_id=p_order_id;
  END IF;
END //
DELIMITER ;

DECLARE block start में executable statements से पहले आता है। Parameters p_ और locals v_ रखें ताकि columns से ambiguity न हो।

CALL approve_order(6, 'Principal');

Transactions, Handlers और Errors

Procedure automatically isolated transaction नहीं बनाती। Autocommit/session rules apply होते हैं। Routine transaction statements रख सकती है जहाँ permitted हों, पर context caller की same session का है।

DELIMITER //
CREATE PROCEDURE safe_mark_paid(
  IN p_order_id INT, IN p_user VARCHAR(50)
)
MODIFIES SQL DATA
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  UPDATE orders_proc_lab
  SET status='PAID', approved_by=p_user
  WHERE order_id=p_order_id AND status='PENDING';

  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='Pending order not found';
  END IF;
  COMMIT;
END //
DELIMITER ;

Handler cleanup करे और RESIGNAL से failure client तक जाए। Errors swallow न करें।

Privileges, DEFINER और SQL SECURITY

• Create के लिए CREATE ROUTINE।
• Invoke के लिए EXECUTE।
• SQL SECURITY DEFINER default; internal statements definer privileges से।
• INVOKER caller privileges से।
• Definer controlled durable account हो।

CREATE DEFINER = 'app_routine'@'localhost'
PROCEDURE report_orders(IN p_customer_id INT)
SQL SECURITY DEFINER
READS SQL DATA
SELECT order_id, status, total_amount
FROM orders_proc_lab
WHERE customer_id = p_customer_id;

DEFINER routine direct table grant के बिना constrained operation expose कर सकती है, पर dynamic SQL, broad parameters या weak validation boundary तोड़ सकती हैं। Least privilege और audit रखें।

Inspect, Deploy, Test और Drop

SHOW CREATE PROCEDURE customer_paid_summary;
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

SELECT ROUTINE_NAME, SQL_DATA_ACCESS, SECURITY_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
  AND ROUTINE_TYPE = 'PROCEDURE';

DROP PROCEDURE IF EXISTS list_customer_orders;

1. Definitions source control/migrations में रखें।
2. Explicit types और NULL/boundaries validate करें।
3. Outputs, affected rows और SQLSTATEs test करें।
4. Transaction ownership document करें।
5. Write routines concurrency/locks test करें।
6. Internal predicates index और plans inspect करें।
7. Restore के बाद DEFINER/EXECUTE grants review करें।
8. Many unrelated result sets avoid करें।
9. Latency/frequency monitor करें।

आगे stored functions, transactions और prepared statements पढ़ें।

Official संदर्भ

• MySQL 8.4: CREATE PROCEDURE and FUNCTION
• MySQL 8.4: CALL Statement
• MySQL 8.4: Stored Routine Privileges

Parameter modes, invocation, characteristics, privilege behavior और management official MySQL 8.4 manual से verify किए गए हैं।