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.

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?”