3-4- ish. Mavzu. Funktsiyalar va saqlangan protseduralarni yaratish, o'zgartirish, qo'llash va o'chirish (10 balli)

3-4- ish. Mavzu. Funktsiyalar va saqlangan protseduralarni yaratish, o'zgartirish, qo'llash va o'chirish (10 balli)
Keling internet do`kon loyihasini bir qismini bajaramiz. Masalan, maxsulotlar jadvalini to`liq yarataylik.

create table product ( id bigint generated always as identity primary key, code uuid default gen_random_uuid(), name varchar not null, description varchar(255), price bigint not null, category_id integer references categories.category(id), total bigint default 0, shop_id bigint references shops.shop(id), is_deleted boolean default false, create_by bigint, updated_by bigint, updated_at timestamp with time zone, create_at timestamp with time zone, image varchar, quantity_of_purchases bigint default 0, grade bigint default 0 );

Endi unga ma`lumot kirtib ko`raylik:



Endi mavzudan chetlashmagan holda shu jadvalimiz uchun funksiya va protseduralar yaratamiz:
Masalan, biz json berganimizda productni ozi avtomatom tablega saqlasin.

create function create_product(dataparam text, sesid bigint) returns bigint language plpgsql as $$ declare new_id bigint; dto products.create_product_dto; begin dto = mappers.to_create_product_dto(dataparam::json); insert into products.product(name, description, price, category_id, total, shop_id,create_by, create_at,image) values (dto.p_name, dto.description, dto.price, dto.category_id, dto.total, dto.shop_id,sesid,now(),dto.image) returning id into new_id; return new_id; end $$;

create_product() dega funksiya yaratdik. Ushbu funksiyaga json korinishidagi text turida product ma`lumotlarini bersak, jadvalga osha productni saqlab qoyadi.
Funksiyalar select so`rovi orqali chaqirladi.
select products.create_product(
'{' ||
'"code": "5a6b7a0d-a9bc-4cfe-b99b2931a7370bc5", ' ||
'"name": "MacBook air 2020", ' ||
'"image":
"https://www.cnet.com/a/img/resize/af0b56bbac3ef1c46689f2
0b88b3air-2017-05.jpg?auto=webp&width=768", ' ||
'"price": 10, ' ||
'"total": 5, ' ||
'"shop_id": 1, ' ||
'"category_id": 6, ' ||
'"description":' ||
' "Zor kompyuter"}',
1);
Funksiyani ishlatamiz.
Korib turganimizdek ma`lumot jadvalga qoshildi.

Endi jadvaldagi ma`lumotni o`chiradigan delete_product degan protsedura yaratib ko`raylik.Unga maxsulot idsini berganimizda uni o`chirishi kerak. Biz shuni etiborga olishimiz kerakki hech qaysi dasturda biron bir data hard delete qilinmaydi. Shunchaki uning isdeleted degan fildini true qilib qoyiladi. Shu narsa data o`chdi degan manoni beradi. Shu sababdan deleted_product protseduramiz osha productni isdeleted degan fildini true qilib update qiladigan qilib yaratamiz.

create procedure delete_product(product_id bigint, session_id bigint) language plpgsql as $$ begin update products.product set is_deleted = true where

not is_deleted and id = product_id; end $$;

Endi protseduramizani test qilib ko`raylik.

Keling jadvaldagi 1-IDdagi productni o`chiraylik:
Protseduralar call so`rovi orqali chaqirladi.

Bu yerda sessionId ushbu tranzaksiyani amalga oshiryapkan userni id si.
Natija:

E`tibor beradigan bolsak 1-IDdagi productni is deleted degan fildi true bolib qoldi.
Endi yaratgan protsedura va funksiyamizni o`chirish qanday bo`lishini k`oraylik:
Bu juda oson. Shunchaki drop komandasi orqali o`chiramiz:

Xulosa

Funksiya va protsedura deyarli bir xil narsa, ularning bitta farqi shundaki, funksiya qiymat qaytaradi, protsedura esa qiymat qaytarmaydi. Biz funcsiya va protseduralar bizning DT loyihamizni developer uchun qulaylik va kodni soddalashtirishga yordam beradi.