DROP PROCEDURE

Ushbu qo'llanmada PostgreSQLda foydalanilmayotgan saqlanadigan protsedurani ma'lumotlar bazasidan butunlay o'chirish uchun DROP PROCEDURE buyrug'idan qanday foydalanishni o'rganasiz.

PostgreSQL DROP PROCEDURE buyrug'iga kirish

Agar saqlanadigan protseduraga ortiq ehtiyoj qolmasa, uni DROP PROCEDURE buyrug'i orqali o'chirib tashlash mumkin.

DROP PROCEDURE buyrug'ining asosiy sintaksisi:

DROP PROCEDURE [IF EXISTS] procedure_name (parameter_list)
[CASCADE | RESTRICT];

Sintaksis tushuntirishi:

• procedure_name — o'chirilishi kerak bo'lgan protsedura nomi.
• IF EXISTS — protsedura mavjud bo'lmasa, xatolik berilishini oldini oladi.
• (parameter_list) — bir xil nomli bir nechta protsedura bo'lsa, aynan qaysi birini o'chirishni belgilash uchun parametrlar ro'yxati (agar nom yagona bo'lsa, parametrlarni ko'rsatmaslik ham mumkin).
• CASCADE — protseduraga bog'liq boshqa ob'ektlarni ham birga o'chiradi.
• RESTRICT (sukut bo'yicha) — bog'liq ob'ektlar bo'lsa o'chirishni taqiqlaydi.

Bir vaqtning o'zida bir nechta protsedurani o'chirish:

DROP PROCEDURE 
    procedure_name1(parameter_list),
    procedure_name2(parameter_list);

[!WARNING] PostgreSQL protseduralar o'rtasidagi ichki chaqiriqlarni (dependency) avtomatik kuzatmaydi. Agar Protsedura A ichida Protsedura B chaqirilayotgan bo'lsa, siz Protsedura B ni hech qanday to'siqsiz o'chirib yuborishingiz mumkin. Ammo keyinchalik Protsedura A ishga tushirilganda "procedure does not exist" xatoligi yuzaga keladi.

DROP PROCEDURE amaliy misollari

Avval sinov uchun brendlar jadvali va bir nechta bog'liq protseduralarni yaratamiz:

DROP TABLE IF EXISTS brands CASCADE;

CREATE TABLE brands (
    brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    brand_name VARCHAR(255) NOT NULL
);

-- 1-protsedura: Brend qo'shish
CREATE OR REPLACE PROCEDURE add_brand(
     p_name VARCHAR(255)
)
AS 
$$
    INSERT INTO brands (brand_name)
    VALUES (p_name);
$$
LANGUAGE SQL;

-- 2-protsedura: Qoldiqni oshirish
CREATE OR REPLACE PROCEDURE increase_inventory(
    p_product_id INT,
    p_warehouse_id INT,
    p_quantity INT
)
AS 
$$
    UPDATE inventories
    SET quantity = quantity + p_quantity 
    WHERE product_id = p_product_id AND warehouse_id = p_warehouse_id;
$$
LANGUAGE SQL;

-- 3-protsedura: Tovarni qabul qilish (ichida increase_inventory chaqiriladi)
CREATE OR REPLACE PROCEDURE receive_product(
    p_product_id INT,
    p_warehouse_id INT,
    p_user_id INT,
    p_quantity INT
)
AS 
$$
    INSERT INTO transactions (product_id, warehouse_id, user_id, type, quantity, transaction_date)
    VALUES (p_product_id, p_warehouse_id, p_user_id, 'receipt', p_quantity, CURRENT_DATE);

    CALL increase_inventory(p_product_id, p_warehouse_id, p_quantity);
$$
LANGUAGE SQL;

1) Oddiy protsedurani o'chirish

add_brand protsedurasi yagona bo'lgani sababli uni parametrlarsiz o'chirish mumkin:

DROP PROCEDURE IF EXISTS add_brand;

Protsedura muvaffaqiyatli o'chirildi.

2) Boshqa protsedura chaqiradigan protsedurani o'chirish

Endi increase_inventory protsedurasini o'chiramiz:

DROP PROCEDURE increase_inventory;

O'chirish muvaffaqiyatli bajariladi. Endi unga tayanadigan receive_product protsedurasini chaqirib ko'ramiz:

CALL receive_product(1, 1, 1, 10);

Natijada quyidagi xatolik chiqadi:

ERROR:  procedure increase_inventory(integer, integer, integer) does not exist

Chunki increase_inventory o'chirib yuborilgan. Shuning uchun o'zaro bog'liq protseduralarni o'chirishda ehtiyot bo'lish zarur.

Endi receive_product ni ham tozalab tashlaymiz:

DROP PROCEDURE receive_product;

Xulosa

• DROP PROCEDURE orqali saqlanadigan protseduralar o'chiriladi.
• IF EXISTS opsiyasi mavjud bo'lmagan protseduralarni o'chirishdagi xatoliklarni oldini oladi.
• Protseduralarni o'chirishdan oldin boshqa protseduralar ularga tayanmasligini tekshirib olish tavsiya etiladi.