18-dars. Temp Table va Stored Procedure

Temp Table nima?

Temp Table — ma'lumotlarni vaqtincha saqlash uchun yaratiladigan jadval.

SQL Server'da local temporary table nomi # bilan boshlanadi. U joriy session doirasida ishlaydi (ya'ni SSMS dasturini yopishingiz bilan jadval o'chib ketadi).

Masalan:

CREATE TABLE #HighSales
(
    OrderID INT,
    Sales INT
);

Ma'lumot qo‘shamiz:

INSERT INTO #HighSales
VALUES
(1001, 1500),
(1002, 2000);

Ko‘ramiz:

SELECT * FROM #HighSales;

Yana osonroq usul

SELECT INTO orqali ham vaqtinchalik jadval yaratish mumkin:

SELECT *
INTO #HighSales
FROM Orders
WHERE Sales > 1000;

Natijada:

Orders
   ↓
SELECT
   ↓
#HighSales

Oddiy tushuncha

Temp Table — katta query davomida kerak bo‘ladigan ma'lumotlarni vaqtincha saqlab turadigan jadval.

Masalan:

Table
  ↓
katta query
  ↓
Temp Table
  ↓
keyingi query

Stored Procedure nima?

Stored Procedure — database ichida saqlab qo‘yilgan SQL buyruqlari to‘plami bo‘lib, kerak bo‘lganda uni nomi orqali ishga tushirish mumkin.

Yana sodda:

Stored Procedure — oldindan yozib, database'ga saqlab qo‘yilgan SQL kod.

Masalan, har kuni:

SELECT * FROM Orders
WHERE Status = 'Delivered';

query'sini ishlatamiz.

Uni qayta-qayta yozish o‘rniga:

Stored Procedure
      ↓
GetDeliveredOrders
      ↓
EXEC
      ↓
natija

qilishimiz mumkin.

SQL Server'da procedure CREATE PROCEDURE orqali yaratiladi va EXEC/EXECUTE orqali ishga tushiriladi.

View'dan farqi nima?

Eng yaxshi analogiya:

View — tayyor ko‘rinish.
Stored Procedure — tayyor buyruq.

Masalan:

VIEW
"Mana shu ma'lumotlarni ko'rsat"

STORED PROCEDURE
"Mana shu ishni bajar"

Stored procedure parametr qabul qilishi va database'dagi bir nechta SQL amallarini bajarishi mumkin.

Stored Procedure yozamiz

Masalan, barcha orderlarni chiqaradigan procedure:

CREATE PROCEDURE GetAllOrders
AS
SELECT *
FROM Orders;

Endi uni ishlatamiz:

EXEC GetAllOrders;

yoki:

EXECUTE GetAllOrders;

Natijada Orders jadvalidagi ma'lumotlar chiqadi.

Procedure + WHERE

Endi realroq misol.

Faqat Delivered orderlarni chiqaradigan procedure:

CREATE PROCEDURE GetDeliveredOrders
AS
SELECT *
FROM Orders
WHERE Status = 'Delivered';

Ishga tushiramiz:

EXEC GetDeliveredOrders;

Oddiy query:

SELECT *
FROM Orders
WHERE Status = 'Delivered';

Stored Procedure:

CREATE PROCEDURE GetDeliveredOrders
AS
SELECT *
FROM Orders
WHERE Status = 'Delivered';

Keyin:

EXEC GetDeliveredOrders;

Ya'ni query'ni bir marta yozib, saqlab qo‘ydik.

Eng muhim qism — SP Parameters

Stored Procedure'ning eng foydali tomonlaridan biri — parametr qabul qilishi.

Masalan, biz:

“Menga faqat 2-raqamli customer'ning orderlarini ber”

demoqchimiz.

Buning uchun:

CREATE PROCEDURE GetOrdersByCustomer
    @CustomerID INT
AS
SELECT *
FROM Orders
WHERE CustomerID = @CustomerID;

Endi:

EXEC GetOrdersByCustomer @CustomerID = 2;

Natijada CustomerID = 2 bo‘lgan orderlar chiqadi.

Yana:

EXEC GetOrdersByCustomer @CustomerID = 6;

Bu safar Ali'ning orderlari chiqadi.

Juda yaxshi analogiya:

Procedure
    ↓
@CustomerID
    ↓
"Qaysi customer kerak?"
    ↓
2
    ↓
John'ning orderlari

SP'da Parameter nima uchun kerak?

Agar parametr bo‘lmasa:

CREATE PROCEDURE GetJohnOrders
AS
SELECT * FROM Orders
WHERE CustomerID = 2;

Faqat John uchun ishlaydi.

Parametr bilan:

CREATE PROCEDURE GetOrdersByCustomer
    @CustomerID INT
AS
SELECT *
FROM Orders
WHERE CustomerID = @CustomerID;

Endi bitta procedure'ni barcha customerlar uchun ishlatish mumkin.

EXEC GetOrdersByCustomer 1;
EXEC GetOrdersByCustomer 2;
EXEC GetOrdersByCustomer 3;
EXEC GetOrdersByCustomer 6;

Mana shu joyni darsda alohida ta'kidlash kerak:

Parameter Stored Procedure'ni universal va qayta ishlatiladigan qiladi.

SQL Server procedure parametrlari orqali chaqiruvchi dastur procedure ichidagi kodga qiymat uzatishi mumkin.

Bir nechta parametr

Endi:

“Menga Germany'dagi customerlarning 1000 dan katta orderlarini ko‘rsat.”

Procedure:

CREATE PROCEDURE GetLargeOrders
    @Country VARCHAR(50),
    @MinSales INT
AS
SELECT
    c.FirstName,
    c.Country,
    o.OrderID,
    o.Sales
FROM Customers c
JOIN Orders o
    ON c.CustomerID = o.CustomerID
WHERE c.Country = @Country
  AND o.Sales >= @MinSales;

Ishlatish:

EXEC GetLargeOrders
    @Country = 'Germany',
    @MinSales = 1000;

Yana:

EXEC GetLargeOrders
    @Country = 'USA',
    @MinSales = 500;

Default parameter

Endi ozgina yangi tushuncha.

Parameterga default qiymat berish mumkin:

CREATE PROCEDURE GetOrdersByStatus
    @Status VARCHAR(20) = 'Delivered'
AS
SELECT *
FROM Orders
WHERE Status = @Status;

Agar parametr bermasak:

EXEC GetOrdersByStatus;

Delivered ishlatiladi.

Agar:

EXEC GetOrdersByStatus 'Shipped';

desak, Shipped bo‘yicha qidiradi.

Stored Procedure + Aggregate Functions

Bu qism juda foydali.

Oldingi darslarda:

SUM()
GROUP BY

o‘rganilgan.

Endi ularni procedure ichida ishlatamiz.

CREATE PROCEDURE GetCustomerSales
AS
SELECT
    c.CustomerID,
    c.FirstName,
    SUM(o.Sales) AS TotalSales
FROM Customers c
JOIN Orders o
    ON c.CustomerID = o.CustomerID
GROUP BY
    c.CustomerID,
    c.FirstName;

Ishga tushirish:

EXEC GetCustomerSales;

Natijada:

Procedure'ni o‘zgartirish

Masalan, GetAllOrders procedure'ni o‘zgartirmoqchimiz.

SQL Server'da ALTER PROCEDURE ishlatish mumkin. CREATE OR ALTER PROCEDURE ham mavjud.

ALTER PROCEDURE GetAllOrders
AS
SELECT
    OrderID,
    OrderDate,
    Sales,
    Status
FROM Orders;

Endi:

EXEC GetAllOrders;

oldingi SELECT * emas, faqat ko‘rsatilgan ustunlarni qaytaradi.

CREATE OR ALTER— qulay variant

CREATE OR ALTER PROCEDURE GetAllOrders
AS
SELECT
    OrderID,
    OrderDate,
    Sales,
    Status
FROM Orders;

Agar procedure mavjud bo‘lmasa → yaratadi.

Agar mavjud bo‘lsa → o‘zgartiradi.

Procedure'ni o‘chirish

Agar kerak bo‘lmasa:

DROP PROCEDURE GetAllOrders;

Procedure database'dan o‘chiriladi.

View vs Stored Procedure

Eng qisqa eslab qolish:

View — “Nimani ko‘rsatamiz?”
Stored Procedure — “Nima ish bajaramiz?”

Darsga tegishli SQL fayllar

⬇️ Darsda ishlatilgan .sql fayli:

⬇️ Uyga vazifa .sql fayli: