Stored Procedure — IN, OUT, INOUT parametrlar

~25 daq · MySQL · View, Stored Procedure, Trigger

Muammodan boshlaylik

Buyurtma berish — bir nechta qadam: ombordagi sonni tekshirish, buyurtma yaratish, qator qo'shish, ombordan ayirish — hammasi bitta tranzaksiyada. Bu mantiq hozir PHP saytda yozilgan. Endi mobil ilova (Node.js) va Telegram bot (Python) ham buyurtma qabul qilishi kerak.

Uch xil tilda bir xil mantiqni uch marta yozasizmi? Biror qoida o'zgarsa — uch joyni yangilaysizmi?

Bir variant — mantiqni bazaning o'ziga joylash. Stored procedure — bazada saqlanadigan va nomi bilan chaqiriladigan SQL dastur.

Birinchi procedure

DELIMITER //

CREATE PROCEDURE toifa_mahsulotlari(IN p_toifa_id INT)
BEGIN
    SELECT id, nom, narx, soni
    FROM mahsulotlar
    WHERE toifa_id = p_toifa_id
    ORDER BY narx;
END //

DELIMITER ;

Chaqirish:

CALL toifa_mahsulotlari(1);

DELIMITER nima uchun?

Procedure tanasi ichida ; bor. Odatda ; — "buyruq tugadi, bajar" degani. MySQL CREATE PROCEDURE ning o'rtasidagi birinchi ; ni ko'rib, yarim-tayyor procedure ni bajarishga urinadi.

DELIMITER // — vaqtincha tugash belgisini // ga o'zgartiradi. Endi ; lar procedure ichida qoladi, butun CREATE esa // bilan tugaydi. Oxirida DELIMITER ; — qaytarish.

[!INFO] DELIMITER — MySQL mijoz buyrug'i (CLI, Workbench), SQL emas. Ilova kodidan (PDO, mysql2) procedure yaratishda u kerak emas — butun CREATE PROCEDURE matnini bitta so'rov sifatida yuborasiz.

Parametrlar: IN, OUT, INOUT

DELIMITER //

CREATE PROCEDURE mijoz_statistikasi(
    IN  p_mijoz_id     INT,
    OUT p_buyurtmalar  INT,
    OUT p_jami         BIGINT
)
BEGIN
    SELECT COUNT(DISTINCT b.id), COALESCE(SUM(q.soni * q.narx), 0)
    INTO p_buyurtmalar, p_jami
    FROM buyurtmalar b
    JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
    WHERE b.mijoz_id = p_mijoz_id AND b.holat <> 'bekor';
END //

DELIMITER ;

CALL mijoz_statistikasi(2, @soni, @jami);
SELECT @soni, @jami;
-- 2, 21360000

SELECT ... INTO o'zgaruvchi — natijani o'zgaruvchiga yozish. @soni — sessiya o'zgaruvchisi.

O'zgaruvchilar, shartlar, sikllar

Procedure ichida — to'laqonli dasturlash:

DELIMITER //

CREATE PROCEDURE buyurtma_berish(
    IN  p_mijoz_id    INT,
    IN  p_mahsulot_id INT,
    IN  p_soni        INT,
    OUT p_buyurtma_id INT
)
BEGIN
    DECLARE v_narx  INT;
    DECLARE v_ombor INT;

    -- Xato bo'lsa — hammasini bekor qilib, xatoni yuqoriga uzatish
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT narx, soni INTO v_narx, v_ombor
    FROM mahsulotlar WHERE id = p_mahsulot_id
    FOR UPDATE;

    IF v_narx IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Mahsulot topilmadi';
    ELSEIF v_ombor < p_soni THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Omborda yetarli emas';
    END IF;

    INSERT INTO buyurtmalar (mijoz_id) VALUES (p_mijoz_id);
    SET p_buyurtma_id = LAST_INSERT_ID();

    INSERT INTO buyurtma_qatorlari (buyurtma_id, mahsulot_id, soni, narx)
    VALUES (p_buyurtma_id, p_mahsulot_id, p_soni, v_narx);

    UPDATE mahsulotlar SET soni = soni - p_soni WHERE id = p_mahsulot_id;

    COMMIT;
END //

DELIMITER ;

CALL buyurtma_berish(2, 5, 1, @bid);
SELECT @bid;   -- 6

CALL buyurtma_berish(2, 3, 50, @bid);
-- ERROR 1644 (45000): Omborda yetarli emas

Endi PHP, Node.js va Python — hammasi bitta CALL buyurtma_berish(...) chaqiradi. Qoida bitta joyda.

Asosiy elementlar

DECLARE v_jami INT DEFAULT 0;           -- lokal o'zgaruvchi (BEGIN boshida)
SET v_jami = v_jami + 1;                -- qiymat berish

IF shart THEN ... ELSEIF ... ELSE ... END IF;

CASE WHEN ... THEN ... END CASE;

WHILE v_i < 10 DO
    SET v_i = v_i + 1;
END WHILE;

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...';   -- o'z xatoingizni tashlash

45000 — "foydalanuvchi belgilagan xato" uchun standart kod. Ilova uni oddiy SQL xatosi sifatida oladi.

Boshqarish

SHOW PROCEDURE STATUS WHERE Db = 'dokon';
SHOW CREATE PROCEDURE buyurtma_berish\G

DROP PROCEDURE IF EXISTS buyurtma_berish;

MySQL da ALTER PROCEDURE tanani o'zgartira olmaydi — DROP + CREATE qilinadi.

Ruxsatlar:

GRANT EXECUTE ON PROCEDURE dokon.buyurtma_berish TO 'bot_app'@'%';

Bot faqat procedure ni chaqira oladi — jadvallarga to'g'ridan-to'g'ri yozish huquqi shart emas.

Ilovadan chaqirish

<?php
// PHP + PDO
$stmt = $pdo->prepare('CALL buyurtma_berish(?, ?, ?, @bid)');
$stmt->execute([$mijozId, $mahsulotId, $soni]);
$bid = $pdo->query('SELECT @bid')->fetchColumn();

// Node.js + mysql2
await conn.query('CALL buyurtma_berish(?, ?, ?, @bid)', [mijozId, mahsulotId, soni]);
const [[{ bid }]] = await conn.query('SELECT @bid AS bid');

Afzalliklar va kamchiliklar

Stored procedure — bahsli mavzu. Ikkala tomonni bilish kerak.

Zamonaviy amaliyot: ko'pchilik veb-loyihalarda biznes mantiq ilovada (Laravel, NestJS) saqlanadi, procedure lar kam ishlatiladi. Procedure lar mos keladigan joylar:

• Ko'p turli tildagi ilovalar bitta bazadan foydalanadi
• Ma'lumotga juda yaqin og'ir ishlov (millionlab qatorni qayta hisoblash)
• Bank, korporativ tizimlar — DBA jamoasi mantiqni nazorat qiladi
• Texnik vazifalar: arxivlash, tozalash (Event Scheduler bilan, 44-dars)

Nima uchun shunday?

Nima uchun stored procedure lar bir vaqtlar juda mashhur bo'lgan, endi esa kamroq ishlatiladi?

1990-2000-yillarda ilova va baza o'rtasidagi tarmoq sekin edi, ilovalar esa "yupqa" mijozlar edi — mantiqni bazaga qo'yish tabiiy edi. Bugun tarmoq tez, ilova serverlari arzon va osonlik bilan ko'paytiriladi, baza esa eng qimmat va kengaytirish qiyin resurs. Shu bilan birga, Git, avtomatik testlar va CI/CD ilova kodida ancha yaxshi ishlaydi. Shuning uchun og'irlik markazi ilovaga ko'chdi. Lekin procedure lar yo'qolmadi — ular hamon to'g'ri joyda kuchli vosita.

Xulosa

Eslab qoling:

• CREATE PROCEDURE nom(IN ..., OUT ...) BEGIN ... END — bazadagi dastur
• CLI/Workbench da DELIMITER // ... DELIMITER ;
• CALL nom(...) — chaqirish; OUT → @o'zgaruvchi
• DECLARE, SET, IF, WHILE, SELECT ... INTO
• SIGNAL SQLSTATE '45000' — o'z xatoingiz; DECLARE EXIT HANDLER — xatoni ushlash
• Tranzaksiya + ROLLBACK handler ichida
• Kam ishlatilsin: ko'p ilovali tizimlar, og'ir ma'lumot ishlovi, texnik vazifalar

Keyingi dars

42-dars: Stored Function — Procedure dan farqi, qachon ishlatiladi