CREATE PROCEDURE

Ushbu qo'llanmada PostgreSQLda yangi saqlanadigan protseduralar (stored procedures) yaratish uchun CREATE PROCEDURE buyrug'idan qanday foydalanishni o'rganasiz.

PostgreSQL CREATE PROCEDURE buyrug'iga kirish

Saqlanadigan protsedura (Stored Procedure) — bu ma'lumotlar bazasi serverida oldindan kompilyatsiya qilingan va saqlanadigan bir yoki bir nechta SQL buyruqlari to'plamidir.

Funksiyalardan farqli o'laroq, protseduralar SELECT orqali emas, balki CALL buyrug'i orqali chaqiriladi va ular tranzaksiyalarni (masalan, COMMIT va ROLLBACK) to'g'ridan-to'g'ri o'z ichida boshqarish imkoniyatiga ega.

CREATE PROCEDURE buyrug'ining asosiy sintaksisi:

CREATE OR REPLACE PROCEDURE procedure_name(parameter_list) 
AS
$$
-- protsedura tanasi
$$
LANGUAGE SQL;

Sintaksis tushuntirishi:

• CREATE PROCEDURE — yangi protsedura yaratadi.
• OR REPLACE — agar protsedura mavjud bo'lsa, uni xatosiz yangilaydi.
• procedure_name(parameter_list) — protsedura nomi va parametrlari.
• AS $$ ... $$ — protsedura tanasi kodi.
• LANGUAGE SQL — protsedura tili (SQL yoki plpgsql).

Agar protsedura ichida bir nechta SQL buyruqlarini bitta atomik blokda bajarmoqchi bo'lsangiz, BEGIN ATOMIC ... END blokidan foydalanishingiz mumkin:

CREATE OR REPLACE PROCEDURE procedure_name(parameter_list) 
LANGUAGE SQL
BEGIN ATOMIC
    sql_statement1;
    sql_statement2;
END;

BEGIN ATOMIC ... END barcha buyruqlarning yaxlitligini ta'minlaydi: agar biror buyruq xato bilan tugasa, butun protsedura bekor qilinadi.

Protsedurani chaqirish (CALL)

Protsedurani bajarish uchun CALL buyrug'idan foydalaniladi:

CALL procedure_name(arguments);

Namunaviy jadvallar

Misollarni bajarish uchun quyidagi jadvallarni yaratamiz:

DROP TABLE IF EXISTS transactions CASCADE;
DROP TABLE IF EXISTS inventories CASCADE;
DROP TABLE IF EXISTS products CASCADE;

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    safety_stock INT NOT NULL DEFAULT 0
);

CREATE TABLE inventories (
    product_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity >= 0),
    PRIMARY KEY (product_id, warehouse_id)
);

CREATE TABLE transactions (
    transaction_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    user_id INT NOT NULL,
    type VARCHAR(20) NOT NULL,
    quantity INT NOT NULL,
    transaction_date DATE NOT NULL
);

INSERT INTO products (product_name, safety_stock) VALUES
('Samsung Galaxy S24', 10),
('Apple iPhone 15', 15);

INSERT INTO inventories (product_id, warehouse_id, quantity) VALUES
(1, 1, 100),
(2, 1, 50);

CREATE PROCEDURE amaliy misollari

1) Oddiy protsedura yaratish

Mahsulotning xavfsizlik zaxirasi miqdorini (safety_stock) yangilovchi protsedura tuzamiz:

CREATE OR REPLACE PROCEDURE update_safety_stock(
    id INT, 
    new_safety_stock INT
)
AS
$$
    UPDATE products
    SET safety_stock = new_safety_stock
    WHERE product_id = id;
$$
LANGUAGE SQL;

Protsedurani CALL yordamida chaqiramiz:

CALL update_safety_stock(1, 25);

O'zgarishni tekshiramiz:

SELECT product_id, product_name, safety_stock
FROM products
WHERE product_id = 1;

Natija:

 product_id |    product_name    | safety_stock 
------------+--------------------+--------------
          1 | Samsung Galaxy S24 |           25

2) Bir nechta buyruqlarni atomik bajaruvchi protsedura

Tovarni ombordan chiqarish (issue) yoki qabul qilish (receipt) paytida ham tranzaksiya yozuvini (transactions), ham qoldiqni (inventories) bir vaqtda yangilaydigan protsedura yaratamiz:

CREATE OR REPLACE PROCEDURE update_inventory(
    p_id INT,
    w_id INT,
    u_id INT,
    t_type VARCHAR,
    qty INT
)
LANGUAGE SQL
BEGIN ATOMIC
    INSERT INTO transactions (product_id, warehouse_id, user_id, type, quantity, transaction_date)
    VALUES (p_id, w_id, u_id, t_type, qty, CURRENT_DATE);

    UPDATE inventories
    SET quantity = CASE t_type 
        WHEN 'receipt' THEN quantity + qty 
        WHEN 'issue' THEN quantity - qty 
        END
    WHERE product_id = p_id AND warehouse_id = w_id;
END;

Xatolik va orqaga qaytarish (Rollback) sinovi

Aytaylik, omborda bor-yo'g'i 100 ta tovar bor. Agar kimdir 10 000 ta chiqarishga harakat qilsa:

CALL update_inventory(1, 1, 1, 'issue', 10000);

inventories jadvalida CHECK (quantity >= 0) cheklovi mavjud bo'lgani sababli, UPDATE amali xatolik beradi:

ERROR:  new row for relation "inventories" violates check constraint "inventories_quantity_check"

Chunki protsedura BEGIN ATOMIC ... END blokida ekan, UPDATE bajarilmagani tufayli birinchi qilingan INSERT ham butunlay bekor qilinadi va bazaga asossiz tranzaksiya yozilmaydi.

Endi to'g'ri miqdor (10 dona) bilan chaqiramiz:

CALL update_inventory(1, 1, 1, 'issue', 10);

Natijalarni tekshiramiz:

SELECT type, quantity FROM transactions WHERE product_id = 1;
SELECT quantity FROM inventories WHERE product_id = 1 AND warehouse_id = 1;

Natija:

 type  | quantity 
-------+----------
 issue |       10

 quantity 
----------
       90

Qoldiq 100 dan 90 ga kamaydi va tranzaksiya ro'yxatga olindi.

Xulosa

• CREATE PROCEDURE orqali ma'lumotlar bazasida saqlanadigan protseduralar tuziladi.
• Protseduralar CALL procedure_name(...) buyrug'i orqali ishga tushiriladi.
• Funksiyalardan farqli o'laroq, protseduralar qiymat qaytarish shart bo'lmagan biznes-jarayonlarni va tranzaksiyalarni boshqarish uchun eng qulay vositadir.
• BEGIN ATOMIC ... END bloki barcha SQL amallarining yaxlit va xavfsiz bajarilishini ta'minlaydi.