Stored Function — Procedure dan farqi, qachon ishlatiladi

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

Muammodan boshlaylik

Bir nechta so'rovda bir xil hisob takrorlanadi:

SELECT nom, narx - ROUND(narx * CASE WHEN toifa_id = 3 THEN 20 WHEN narx > 10000000 THEN 5 ELSE 10 END / 100) ...

Chegirma qoidasi uch so'rovda, hisobot VIEW sida va ilovada nusxalangan. Qoida o'zgarsa — hamma joyni qidirasiz.

UPPER(), ROUND() kabi o'rnatilgan funksiyalar bor. O'zingizning chegirmali_narx() funksiyangizni yaratib, SELECT da ishlatsa bo'lmaydimi? Bo'ladi — bu stored function.

Function yaratish

DELIMITER //

CREATE FUNCTION chegirma_foizi(p_narx INT, p_toifa_id INT)
RETURNS TINYINT
DETERMINISTIC
NO SQL
BEGIN
    RETURN CASE
        WHEN p_toifa_id = 3       THEN 20     -- aksessuarlar
        WHEN p_narx > 10000000    THEN 5      -- qimmat mahsulotlar
        ELSE 10
    END;
END //

DELIMITER ;

Endi o'rnatilgan funksiya kabi:

SELECT
    nom,
    narx,
    chegirma_foizi(narx, toifa_id) AS foiz,
    narx - ROUND(narx * chegirma_foizi(narx, toifa_id) / 100) AS yakuniy
FROM mahsulotlar;

+--------------------+----------+------+----------+
| nom                | narx     | foiz | yakuniy  |
+--------------------+----------+------+----------+
| iPhone 15          | 12500000 |    5 | 11875000 |
| Samsung Galaxy A55 |  4800000 |   10 |  4320000 |
| MacBook Air M3     | 16900000 |    5 | 16055000 |
| Lenovo IdeaPad     |  7200000 |   10 |  6480000 |
| AirPods Pro        |  3100000 |   20 |  2480000 |
| Qopqoq             |   120000 |   20 |    96000 |
| Sovg'a kartasi     |   500000 |   10 |   450000 |
+--------------------+----------+------+----------+

Function ni SELECT, WHERE, ORDER BY, GROUP BY — istalgan ifoda o'rnida ishlatish mumkin.

Procedure va function farqi

Qoida:

• Hisoblash — bitta qiymat, so'rov ichida → function
• Amal — o'zgartirishlar, tranzaksiya, ko'p qadam → procedure

Xususiyatlar: DETERMINISTIC va boshqalar

Function e'lonida MySQL uning tabiatini bilishni talab qiladi (ayniqsa binary log yoqilgan serverlarda):

ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its
declaration and binary logging is enabled

Bu xatoni ko'rsangiz — xususiyatlarni to'g'ri yozing. Yolg'on yozmang: NOW() ishlatadigan funksiyani DETERMINISTIC deb e'lon qilsangiz — replication da replica boshqa natija hisoblashi mumkin.

Jadvaldan o'qiydigan function

DELIMITER //

CREATE FUNCTION mijoz_jami(p_mijoz_id INT)
RETURNS BIGINT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_jami BIGINT;

    SELECT COALESCE(SUM(q.soni * q.narx), 0) INTO v_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';

    RETURN v_jami;
END //

DELIMITER ;

SELECT ism, mijoz_jami(id) AS jami FROM mijozlar;

⚠️ Tezlik tuzog'i

SELECT ism, mijoz_jami(id) FROM mijozlar;   -- 100 000 mijoz

Function har bir qator uchun alohida chaqiriladi — 100 000 marta ichki SELECT ... JOIN ... SUM. Bu correlated subquery (22-dars) bilan bir xil, lekin MySQL optimizatori function ichiga "ko'ra olmaydi" va uni JOIN ga aylantira olmaydi.

-- ✅ Bitta so'rov — ancha tez
SELECT m.ism, COALESCE(SUM(q.soni * q.narx), 0) AS jami
FROM mijozlar m
LEFT JOIN buyurtmalar b        ON b.mijoz_id = m.id AND b.holat <> 'bekor'
LEFT JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
GROUP BY m.id, m.ism;

Bundan tashqari, WHERE mijoz_jami(id) > 10000000 — indeks ishlatilmaydi va har bir qator uchun hisoblanadi.

Qoida: jadvaldan o'qiydigan function lar — faqat bitta qator yoki kichik natijalar uchun. Katta to'plamlar uchun — JOIN.

Foydali function misollari

DELIMITER //

-- Telefon formatini normallashtirish
CREATE FUNCTION telefon_toza(p_tel VARCHAR(30))
RETURNS VARCHAR(20)
DETERMINISTIC NO SQL
BEGIN
    DECLARE v VARCHAR(30) DEFAULT REGEXP_REPLACE(p_tel, '[^0-9]', '');
    IF CHAR_LENGTH(v) = 9 THEN SET v = CONCAT('998', v); END IF;
    RETURN CONCAT('+', v);
END //

-- Narxni chiroyli formatlash
CREATE FUNCTION som(p INT)
RETURNS VARCHAR(30)
DETERMINISTIC NO SQL
RETURN CONCAT(REPLACE(FORMAT(p, 0), ',', ' '), ' so''m') //

DELIMITER ;

SELECT telefon_toza('90 111-22-33');   -- +998901112233
SELECT som(12500000);                  -- 12 500 000 so'm

Bir qatorli function uchun BEGIN ... END shart emas.

Generated column (hisoblangan ustun) da DETERMINISTIC function lar ishlatilmaydi — u yerda faqat o'rnatilgan funksiyalar. Lekin oddiy UPDATE da bemalol:

UPDATE mijozlar SET telefon = telefon_toza(telefon) WHERE telefon IS NOT NULL;

Boshqarish

SHOW FUNCTION STATUS WHERE Db = 'dokon';
SHOW CREATE FUNCTION chegirma_foizi\G
DROP FUNCTION IF EXISTS chegirma_foizi;

Nima uchun shunday?

Nima uchun function ichida COMMIT yoki ROLLBACK qilib bo'lmaydi?

Chunki function so'rov ichida chaqiriladi — SELECT ning o'rtasida, har bir qator uchun. Agar function tranzaksiyani yakunlasa — tashqi SELECT ni bajarayotgan tranzaksiya ham "sindiriladi". Bir qatorni o'qish jarayonida baza holati o'zgarib ketadi. Function — ifoda: u qiymat hisoblaydi va qaytaradi. Holatni o'zgartiradigan amallar — procedure ning ishi.

Xulosa

Eslab qoling:

• CREATE FUNCTION nom(...) RETURNS tur ... BEGIN ... RETURN ...; END
• So'rov ichida ishlatiladi: SELECT nom(ustun) FROM ...
• Function — bitta qiymat, hisob; procedure — amal, tranzaksiya, natija to'plamlari
• DETERMINISTIC / NOT DETERMINISTIC, NO SQL / READS SQL DATA — to'g'ri e'lon qiling
• Jadvaldan o'qiydigan function har bir qator uchun alohida — katta to'plamda JOIN ishlating
• WHERE function(ustun) — indeks ishlamaydi
• Yaxshi nomzodlar: formatlash, normallashtirish, biznes formulalari

Keyingi dars

43-dars: Trigger — BEFORE, AFTER, INSERT, UPDATE, DELETE