1-mavzu. Ma’lumotlar bazasi obyektlarini yaratish

Protsedurani ishga tushirish
Saqlangan protsedurani ishga tushirish uchun EXEC yoki EXECUTE buyrug'i chaqiriladi:
EXEC ProductSummary



Protsedurani o’chirish
Protsedurani o’chirish uchun DROP PROCEDURE buyrug'idan foydalaniladi:
DROP PROCEDURE ProductSummary

Protseduradagi parametrlar

Protsedura parametrlarni qabul qilishi mumkin. Parametrlar yordamida protseduraga ba'zi qiymatlarni uzatish mumkin. Chiqish parametrlari ham bo’ladi, ular protseduradan biron bir qiymatni qaytarishga imkon beradi.
Masalan, ma`lumotlar bazasida quyidagi Products jadvali mavjud deylik:
USE productsdb;
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price MONEY NOT NULL
);
Ushbu jadvalga ma`lumotlar qo'shadigan protsedurani yaratamiz:
USE productsdb;
GO
CREATE PROCEDURE AddProduct
@name NVARCHAR(20),
@manufacturer NVARCHAR(20),
@count INT,
@price MONEY
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES(@name, @manufacturer, @count, @price)
Protsedura nomidan so'ng, parametrlarning ro`yxati keladi, ular o`zgaruvchilar bilan bir xil tarzda aniqlanadi. Parametr nomi @ belgisi bilan boshlanadi va nomdan keyin o`zgaruvchining tipi keladi. INSERT buyrug'i yordamida ushbu parametrlarning qiymatlari Products jadvaliga o`tkaziladi.
Ushbu protseduradan ishga tushirish:
USE productsdb;
DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20);
DECLARE @prodCount INT, @price MONEY
SET @prodName = 'Galaxy C7'
SET @company = 'Samsung'
SET @price = 22000
SET @prodCount = 5
EXEC AddProduct @prodName, @company, @prodCount, @price
SELECT * FROM Products

Bu yerda protseduraga berilgan qiymatlar o`zgaruvchilar orqali aniqlanadi. Protsedura chaqirilganda, qiymatlar unga vergul bilan ajratilib uzatiladi. Bunday holda, qiymatlar protsedura parametrlariga pozitsiya bo'yicha mos kelishi kerak. Dastlab @name parametri yozilganligi sababli, unga birinchi qiymat - @prodName o`zgaruvchisining qiymati beriladi. Ikkinchi parametr - @manufacturer ga ikkinchi qiymat - @company o`zgaruvchisining qiymati va h.k uzatiladi. Eng asosiysi, qiymatlarning tipi va protsedura parametrlari tipi bilan mos tushishi kerak.

To'g'ridan-to'g'ri qiymatlarni ham uzatish mumkin:

EXEC AddProduct 'Galaxy C7', 'Samsung', 5, 22000

Shuningdek, protsedura parametrlariga qiymatlarni nom bo'yicha berish mumkin:

USE productsdb;
DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20);
SET @prodName = 'Honor 9'
SET @company = 'Huawei'
EXEC AddProduct @name = @prodName,
@manufacturer=@company,
@count = 3,
@price = 18000

Qiymat uzatish shart bo’lmagan parametrlar

Parametrlarni Qiymat uzatish shart emas qilib aniqlash mumkin, Bunda holda ularga standart qiymat belgilanadi. Masalan, yuqoridagi holatda, agar tovar miqdori uzatilmagan bo’lsa, unga avtomatik ravishda 1 ni o'rnatishimiz mumkin:
USE productsdb;
GO
CREATE PROCEDURE AddProductWithOptionalCount
@name NVARCHAR(20),
@manufacturer NVARCHAR(20),
@price MONEY,
@count INT = 1
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES(@name, @manufacturer, @count, @price)
Qiymat uzatish shart bo’lmagan parametrlarni protsedura parametrlari ro`yxati oxirida joylashtirish ma’qul.
DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20), @price MONEY
SET @prodName = 'Redmi Note 5A'
SET @company = 'Xiaomi'
SET @price = 22000
EXEC AddProductWithOptionalCount @prodName, @company, @price
SELECT * FROM Products
Bu holda, @count parametri uchun protseduraga qiymat uzatish shart emas.

Chiqish parametrlari

Chiqish parametrlari protseduradan ba'zi natijalarni qaytarishga imkon beradi. Chiqish parametrlari OUTPUT kalit so`zi yordamida aniqlanadi:
USE productsdb;
GO
CREATE PROCEDURE GetPriceStats
@minPrice MONEY OUTPUT,
@maxPrice MONEY OUTPUT
AS
SELECT @minPrice = MIN(Price), @maxPrice = MAX(Price)
FROM Products

Chiqish parametrlari protsedurasini chaqirganda o`zgaruvchilar OUTPUT kalit so`zi bilan uzatiladi:

USE productsdb;
DECLARE @minPrice MONEY, @maxPrice MONEY
EXEC GetPriceStats @minPrice OUTPUT, @maxPrice OUTPUT
PRINT minimal narx ' + CONVERT(VARCHAR, @minPrice)
PRINT 'maksimal narx ' + CONVERT(VARCHAR, @maxPrice)

Kirish va chiqish parametrlarini birlashtirish mumkin. Masalan, jadvalga yangi qator qo'shadigan va uning id sini qaytaradigan protsedurani aniqlaymiz:

USE productsdb;
GO
CREATE PROCEDURE CreateProduct
@name NVARCHAR(20),
@manufacturer NVARCHAR(20),
@count INT,
@price MONEY,
@id INT OUTPUT
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price)
VALUES(@name, @manufacturer, @count, @price)
SET @id = @@IDENTITY
@@ IDENTITY global o`zgaruvchisi qo'shilgan yozuvning identifikatorini olish uchun ishlatiladi.
Ushbu protsedura chaqirilganda barcha kirish va chiqish parametrlari unga pozitsiya bo'yicha uzatiladi:
USE productsdb;
DECLARE @id INT
EXEC CreateProduct 'LG V30', 'LG', 3, 28000, @id OUTPUT
PRINT @id
Qiymatni qaytarish
Saqlangan protsedura natijani chiqish parametrlari orqali qaytarishdan tashqari, RETURN operatori yordamida ham qiymat qaytarishi mumkin. Garchi bu imkoniyat, chiqish parametrlaridan foydalanib natijani qaytarish holatiga tenglashtirilsa-da, protseduradan bitta qiymatni qaytarish zarur bo'lsa, RETURN operatoridan foydalaniladi.
Masalan, tovarlarning o'rtacha narxini qaytaramiz:
USE productsdb;
GO
CREATE PROCEDURE GetAvgPrice AS
DECLARE @avgPrice MONEY
SELECT @avgPrice = AVG(Price)
FROM Products
RETURN @avgPrice;

Qaytariladigan qiymat RETURN operatoridan keyin ko`rsatiladi. Yuqoridagi misolda, bu @avgPrice o`zgaruvchisining qiymati.

Ushbu protsedurani chaqirish:
USE productsdb;
DECLARE @result MONEY
EXEC @result = GetAvgPrice
PRINT @result

Protsedura natijasini olish uchun uning qiymati o`zgaruvchida saqlanadi (misolda @result o`zgaruvchisida):

9-mavzu. Funksiyalar.

Reja:

1. 
   Foydalanuvchi tomonidan e`lon qilinadigan funksiyalarni(UDF) yaratish Foydalanuvchi tomonidan e`lon qilingan funksiyani chaqirish
   
2. 
   Jadval funksiyalar
   
3. 
   Ma`lumotni bir tipdan boshqa tipga o`tkazish
   

Foydalanuvchi tomonidan e`lon qilinadigan funksiyalarni(UDF) yaratish

Foydalanuvchi tomonidan aniqlanadigan funksiyalar quyidagi sintaksisga ega bo'lgan CREATE FUNCTION buyrug’i yordamida yaratiladi:
CREATE FUNCTION [schema_name.]function_name
[( {@param } type [= default]) {,...}
RETURNS {scalar_type | [@variable] TABLE}
[WITH {ENCRYPTION | SCHEMABINDING}
[AS] {block | RETURN (select_statement)}

Schema_name parametri UDF egasi tomonidan tayinlangan sxema nomini va function_name parametri esa ushbu yaratiladigan funksiya nomini bildiradi. @Param parametri - bu funksiya uchun kirish parametri, uning qiymati tipi parametr tipi bilan aniqlanadi. Funksiya parametrlari - bu UDF chaqiruvchisi tomonidan uni ishlatish uchun uzatadigan qiymatlar. default ushbu funksiya parametri uchun mos keladigan standart qiymatni belgilaydi. (standart qiymat NULL ham bo'lishi mumkin.)

RETURNS bandi UDF tomonidan qaytarilgan qiymatning ma`lumotlar tipini bildiradi. Bu ma`lumotlar bazasi tizimi tomonidan qo`llab-quvvatlanadigan deyarli har qanday standart ma`lumotlar tipi, shu jumladan TABLE ma`lumotlar tipi ham bo'lishi mumkin. Mumkin bo'lmagan yagona ma`lumot tipi - bu timestamp ma`lumotlar tipi.
Foydalanuvchi tomonidan e`lon qilingan funksiyalar skalyar funksiyalar yoki jadval funksiyalar bo'lishi mumkin. Skalyar funksiyalar skalyar qiymatni(aniq bitta qiymatni) qaytaradi. Bu shuni anglatadiki, standart ma`lumotlar tiplaridan biri skalyar funksiyasining RETURNS bandida ko`rsatilgan. Agar RETURNS bandi jadvalni qaytarsa, jadval funksiya bo'ladi.
Tizim katalogidagi WITH ENCRYPTION xossasi CREATE FUNCTION kodi matnini o`z ichiga oladi. Bu funksiyani yaratish uchun ishlatilgan kod matnini ruxsatsiz ko`rishni oldini oladi. Ushbu parametr ma`lumotlar bazasi tizimining xavfsizligini oshiradi.
WITH SCHEMABINDING muqobil varianti UDF-ni ushbu funksiya tomonidan foydalaniladigan ma`lumotlar bazasi ob`yektlariga bog'laydi. Shundan so'ng, funksiya tomonidan ishlatilgan ma`lumotlar bazasi ob`yektini o`zgartirishga ruxsat bo’lmaydi. (Funksiyaning u ishlatadigan ma`lumotlar bazasi ob`yektlari bilan bog'lanishini faqat funksiya o`zgarganda olib tashlanadi.)
Funksiyani yaratishda SCHEMABINDING variantini ishlatish uchun funksiya tomonidan sihlatiladigan ma`lumotlar bazasi ob`yektlari quyidagi shartlarga javob berishi kerak:

• 
  UDF tomonidan foydalaniladigan barcha ko`rinishlar va boshqa obyektlar sxemaga bog`langan bo'lishi kerak;
  
• 
  ma`lumotlar bazasining barcha ob`yektlari (jadvallar, ko`rinishlar va UDF) UDF bilan bitta ma`lumotlar bazasida bo'lishi kerak.
  

Blok bandi funksiya realizasiyasini o`z ichiga olgan BEGIN / END blokini belgilaydi. Blokdagi so'nggi instruksiya RETURN instruksiyasi bo'lishi kerak. (RETURN instruksiyasining qiymati funksiyaning qaytarish qiymati.) BEGIN / END blokida faqat quyidagi instruksiyalarga ruxsat beriladi:

• 
  SET kabi qiymat o`zlashtirish bo'yicha instruksiyalar;
  
• 
  WHILE va IF kabi boshqarish bo'yicha ko`rsatmalar;
  
• 
  lokal o`zgaruvchilarni e`lon qiladigan DECLARE instruksiyasi;
  
• 
  Ushbu funksiya uchun lokal bo'lgan o`zgaruvchilarga qiymatlari beriladigan ustunlar ro`yxatini o`z ichiga olgan SELECT operatorlari;
  
• 
  Funksiya uchun lokal bo'lgan TABLE o`zgaruvchilarini o`zgartiradigan INSERT, UPDATE va DELETE operatorlari.
  

Quyidagi misol ComputeCosts funksiyasini yaratilishini ko`rsatadi:
USE SampleDb;
GO
CREATE FUNCTION ComputeCosts (@percent INT = 10)
RETURNS DECIMAL(16, 2)
BEGIN
DECLARE @addCosts DEC (14,2), @sumBudget DEC(16,2)
SELECT @sumBudget = SUM (Budget) FROM Project
SET @addCosts = @sumBudget * @percent/100
RETURN @addCosts
END;
ComputeCosts funksiyasi loyiha byudjetlari ko'payishi bilan bog'liq bo'lgan qo'shimcha xarajatlarni hisoblab chiqadi. Yagona kirish parametri @percent byudjetlarning o'sish foizini belgilaydi. BEGIN / END bloki avval ikkita lokal o`zgaruvchini e`lon qiladi: @addCosts va @sumBudget, keyin esa @sumBudget o`zgaruvchisiga SELECT ifodasi yordamida barcha byudjetlar yig'indisiga uzatiladi. Keyinchalik, bu funksiya umumiy tushgan xarajatlarni hisoblab chiqadi va RETURN operatori yordamida ushbu qiymatni qaytaradi.

Foydalanuvchi tomonidan e`lon qilingan funksiyani ishga tushirish

Foydalanuvchi tomonidan aniqlangan funksiyani SELECT, INSERT, UPDATE yoki DELETE kabi Transact-SQL operatorlari yordamida chaqirish mumkin. Funksiya uning nomini ko`rsatib, so'ngra bir yoki bir nechta parametrlarni ko`rsatishi mumkin bo'lgan oxirida qavslar jufti bilan chaqiriladi. Parametrlar - bu funksiya nomidan keyin darhol aniqlanadigan, kirish parametrlariga uzatiladigan qiymatlar yoki ifodalar.
Quyidagi misolda SELECT operatorida ComputeCosts funksiyasini chaqirish ko`rsatilgan:
USE SampleDb;

SELECT Number, ProjectName

FROM Project
WHERE Budget < dbo.ComputeCosts(25);

Misoldagi SELECT operatorida byudjeti qo'shimcha xarajatlar miqdoridan kichik bo'lgan loyihalarning nomlari va raqamlarini chiqaradi.

T-SQL da funksiya nomlari ikki qism: sxema nomi va funksiya nomi yordamida ko`rsatilishi kerak. Shuning uchun biz misolda dbo sxema prefiksidan foydalanganmiz.