Stored procedure

-- Тусгаарлагчийг // болгох
DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
  -- кодууд энд, ; хэрэглэж болно
  SELECT 'Сайн уу!' AS мэндчилгээ;
END //

-- Тусгаарлагчийг буцаах
DELIMITER ;

DELIMITER //

CREATE PROCEDURE GetAllUsers()
BEGIN
  SELECT id, username, email FROM users;
END //

DELIMITER ;

-- Ажиллуулах
CALL GetAllUsers();

DELIMITER //

CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
  SELECT id, username, email
  FROM users
  WHERE id = user_id;
END //

DELIMITER ;

-- Дуудах
CALL GetUserById(5);
CALL GetUserById(42);

DELIMITER //

CREATE PROCEDURE CountOrders(
  IN  status_filter VARCHAR(20),
  OUT total         INT
)
BEGIN
  SELECT COUNT(*) INTO total
  FROM orders
  WHERE status = status_filter;
END //

DELIMITER ;

-- Дуудах ба үр дүн авах
CALL CountOrders('paid', @result);
SELECT @result AS төлбөр_хийгдсэн;

DELIMITER //

CREATE PROCEDURE ApplyDiscount(INOUT price DECIMAL(10,2), IN discount INT)
BEGIN
  SET price = price * (1 - discount / 100.0);
END //

DELIMITER ;

SET @price = 15000;
CALL ApplyDiscount(@price, 20);
SELECT @price AS хямдарсан_үнэ;
-- Үр дүн: 12000.00

DELIMITER //

CREATE PROCEDURE ClassifyOrder(IN amount DECIMAL(10,2))
BEGIN
  IF amount >= 50000 THEN
    SELECT 'Том захиалга' AS ангилал;
  ELSEIF amount >= 10000 THEN
    SELECT 'Дунд захиалга' AS ангилал;
  ELSE
    SELECT 'Жижиг захиалга' AS ангилал;
  END IF;
END //

DELIMITER ;

CALL ClassifyOrder(75000);  -- Том захиалга
CALL ClassifyOrder(12000);  -- Дунд захиалга
CALL ClassifyOrder(3500);   -- Жижиг захиалга

DELIMITER //

CREATE PROCEDURE InsertTestData(IN count INT)
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i <= count DO
    INSERT INTO users (username, email)
    VALUES (
      CONCAT('user', i),
      CONCAT('user', i, '@test.com')
    );
    SET i = i + 1;
  END WHILE;

  SELECT CONCAT(count, ' хэрэглэгч нэмэгдлээ') AS үр_дүн;
END //

DELIMITER ;

CALL InsertTestData(100);

DELIMITER //

CREATE PROCEDURE SumNumbers(IN max_num INT, OUT result INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  SET result = 0;

  sum_loop: LOOP
    IF i > max_num THEN
      LEAVE sum_loop;   -- давталтаас гарах
    END IF;

    SET result = result + i;
    SET i = i + 1;
  END LOOP sum_loop;
END //

DELIMITER ;

CALL SumNumbers(100, @total);
SELECT @total;  -- 5050

DELIMITER //

CREATE PROCEDURE PlaceOrder(
  IN  p_user_id    INT,
  IN  p_product_id INT,
  IN  p_quantity   INT,
  OUT p_order_id   INT,
  OUT p_message    VARCHAR(200)
)
BEGIN
  DECLARE v_stock INT;
  DECLARE v_price DECIMAL(10,2);

  -- Нөөц шалгах
  SELECT stock, price INTO v_stock, v_price
  FROM products WHERE id = p_product_id;

  IF v_stock IS NULL THEN
    SET p_message = 'Бараа олдсонгүй';
    SET p_order_id = -1;

  ELSEIF v_stock < p_quantity THEN
    SET p_message = CONCAT('Нөөц хүрэлцэхгүй. Байгаа нөөц: ', v_stock);
    SET p_order_id = -1;

  ELSE
    -- Захиалга үүсгэх
    INSERT INTO orders (user_id, product_id, quantity, amount)
    VALUES (p_user_id, p_product_id, p_quantity, v_price * p_quantity);

    SET p_order_id = LAST_INSERT_ID();

    -- Нөөц хасах
    UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;

    SET p_message = 'Захиалга амжилттай';
  END IF;
END //

DELIMITER ;

CALL PlaceOrder(1, 3, 2, @order_id, @msg);
SELECT @order_id AS захиалгын_дугаар, @msg AS мэдэгдэл;

-- Бүх procedure жагсаах
SHOW PROCEDURE STATUS WHERE Db = 'myshop';

-- Procedure-н код харах
SHOW CREATE PROCEDURE PlaceOrder;

-- Устгах
DROP PROCEDURE IF EXISTS PlaceOrder;