O‘zbеkiston rеspublikasi axborot tеxnologiyalari va kommunikatsiyalarini rivojlantirish vazirligi muhammad al-xorazmiy nomidagi toshkеnt axborot tеxnologiyalari univеrsitеti samarqand filiali

Tayanch so‘zlar va iboralar: ma’lumotlar himoyasi, paralel qayta ishlash, ma’lumotlarni mag‘fiyligi

Nazorat savollari

1. Ma’lumot bazasida saqlanadigan axborot ximoya nimani anglatadi.

2. Foydalanuvchini mualliflashtirish nimani anglatadi.
3.Axborot tizimlar xavfsizligiga tahdid qiladigan sabablarni aytib bering.

14-15- Mavzu: SQL tilida funksiyalar yaratish
Reja

1. 
   SQL tilida funksiyalar
   
2. 
   Skalar funktsiyalari
   
3. 
   SQL tilida funksiyalar yaratishga misollar
   

Kalit so'zlar: SQL, saqlanadigan protsedura, arxitektura, foydalanuvchi funktsiyasi, server, o'rnatilgan funksiya, qidirish, ma'lumotlar bazasi, foydalanuvchi skalasi funktsiyasi, jadval ma'lumotlari turi.
SQL tilida bir necha marotaba talab qilinishi mumkin bo'lgan murakkab algoritmlarni amalga oshirishda, darhol foydalanish uchun ishlab chiqilgan kodni saqlash haqida savol tug'iladi. Ushbu vazifani saqlangan protseduralar yordamida amalga oshirish mumkin, ammo ularning arxitekturasi protseduralarni to'g'ridan-to'g'ri iboralarda ishlatishga imkon bermaydi, chunki ular qaytarilgan qiymatning o'zgaruvchiga oraliq tayinlanishini talab qiladilar, shunda u ifodada ko'rsatiladi. Tabiiyki, dastur kodini qo'llashning bunday usuli juda qulay emas. Ko'pgina ishlab chiquvchilar uzoq vaqt davomida ishlab chiqilgan algoritmlarni to'g'ridan-to'g'ri iboralarda chaqirishni xohlashdi.
Maxsus funktsiyalarni yaratish qobiliyati MS SQL Server 2000 muhitida ta'minlandi, boshqa SQL dasturlarida foydalanuvchi faqat eng keng tarqalgan algoritmlarni ta'minlaydigan o'rnatilgan funktsiyalarga ega: maksimal yoki minimal qiymatni qidirish va h.k.
Foydalanuvchi funktsiyalari, masalan, saqlanadigan protseduralar yoki triggerlar kabi mustaqil ma'lumotlar bazasi ob'ektlari. Foydalanuvchi funktsiyasi ma'lum bir ma'lumotlar bazasida joylashgan va faqat uning kontekstida mavjud.
SQL Server quyidagi funktsiyalar sinflariga ega:
Scalar - funktsiyalar odatdagi skalyar qiymatni qaytaradi, ularning har biri bitta ... blokda birlashtirilgan ko'plab buyruqlarni o'z ichiga olishi mumkin BEGIN ... END konstruktsiyasi;
Inline - funktsiyalar faqat bitta SELECT buyrug'ini o'z ichiga oladi va foydalanuvchiga TABLE ma'lumotlar turining qiymati sifatida berilgan ma'lumotni qaytaradi;
Ko'p bayonot - funktsiyalar foydalanuvchiga ma'lumotlar to'plamini o'z ichiga olgan TABLE ma'lumotlar turi qiymatini qaytaradi, ammo funktsiyaning tarkibida SQL ko'p buyruqlar mavjud (INSERT, UPDATE va hk). Ularning yordami bilan ma'lumotlar to'plami shakllantiriladi, bu funktsiyani tugatgandan so'ng qaytarilishi kerak.
Ushbu bo'limda Transact-SQL-dan foydalanib SQL Serverda foydalanuvchi tomonidan belgilangan funktsiyani qanday yaratishni tasvirlaydi.
Boshlashdan oldin
Cheklovlar
• Foydalanuvchi tomonidan belgilangan funktsiyalar ma'lumotlar bazasining holatini o'zgartiradigan amallarni bajara olmaydi.
• Foydalanuvchi belgilagan funktsiyalar jadval bo'lgan OUTPUT INTO-ni o'z ichiga olmaydi.
• Foydalanuvchi tomonidan belgilangan vazifalar bir nechta natijalar to'plamini qaytarib berolmaydi. Agar bir nechta natijalar to'plamini qaytarishingiz kerak bo'lsa, saqlangan protseduradan foydalaning.
• Foydalanuvchi tomonidan belgilangan funktsiyalarni bajarishda xatolik cheklangan. UDF TRY ... CATCH, @ERROR va RAISERROR turlarini qo'llab-quvvatlamaydi.
• Foydalanuvchi tomonidan belgilangan funktsiyalar saqlangan protsedurani chaqira olmaydi, ammo kengaytirilgan saqlangan protsedurani chaqira oladi.
• Foydalanuvchi tomonidan belgilangan funktsiyalar dinamik SQL va vaqtinchalik jadvallardan foydalana olmaydi. Jadval o'zgaruvchilaridan foydalanishga ruxsat berilgan.
• SET yo'riqnomasi foydalanuvchi tomonidan belgilangan funktsiyalarda foydalanilmaydi.
• XML formatidagi bo'sh gapdan foydalanish mumkin emas.
• Foydalanuvchi tomonidan belgilangan funktsiyalar joylashtirilishi mumkin, ya'ni bitta funktsiya boshqasiga qo'ng'iroq qilishi mumkin. Ichki joylashish darajasi chaqirilayotgan funktsiyaning bajarilishi har safar boshlanib, uning ijrosi tugashi bilan bittaga kamayadi. Foydalanuvchi tomonidan belgilangan funktsiyalarning joylashtirilishi 32 darajadan oshmasligi kerak. O'rnatishning maksimal darajasidan oshish chaqirilayotgan funktsiyalarning butun zanjiri uchun bajarishda xatoga olib keladi. Foydalanuvchi tomonidan tayinlangan Transact-SQL funktsiyasidan har bir boshqariladigan kod qo'ng'irog'i kiritish mumkin bo'lgan 32 darajadan biri hisoblanadi. Boshqariladigan koddan chaqiriladigan usullar ushbu cheklovga duch kelmaydi.

• 
  Brokerning quyidagi yo'riqlari Transact-SQL foydalanuvchi tomonidan belgilangan funktsiyalar ta'rifiga kiritilishi mumkin emas:
  
  • 
    BEGIN DIALOG CONVERSATION
    
  • 
    END CONVERSATION
    
  • 
    GET CONVERSATION GROUP
    
  • 
    MOVE CONVERSATION
    
  • 
    RECEIVE
    
  • 
    SEND
    

Ruxsatnomalar
Funktsiya yaratilgan sxema uchun CREATE FUNCTION ma'lumotlar bazasi va ALTER ruxsatnomasini talab qiladi. Agar funktsiyada foydalanuvchi belgilagan tur ko'rsatilgan bo'lsa, ushbu turga EXECUTE ruxsati kerak.
Skalar funktsiyalari
Quyidagi misol AdventureWorks2012 ma'lumotlar bazasidagi bir nechta ko'rsatmalardan skalalar funktsiyasini (scalar UDF) yaratadi. Funktsiya bitta kiritish parametriga ega ProductID va bitta qiymatni qaytaradi - omborda ko'rsatilgan mahsulot miqdori.
SQL nusxasi
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
Quyidagi misolda ufnGetInventoryStock funktsiyasi ProductModelID 75 dan 80 gacha bo'lgan mahsulotlar soni to'g'risida ma'lumot olish uchun ishlatiladi.
SQL nusxasi
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
Jadval qiymatlari bilan funktsiyalar
Quyidagi misolning natijasi - AdventureWorks2012 ma'lumotlar bazasida jadval qiymatini (TVF) qaytaradigan o'rnatilgan funksiya. Funktsiya bitta kirish parametriga ega - mijoz (do'kon) identifikatori - va joriy yil uchun mahsulotni sotish to'g'risidagi ma'lumot bilan ProductID, Name va YTD Total ustunlarini qaytaradi.
SQL nusxasi
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
Quyidagi misolda, funktsiya 602 identifikatori bilan chaqiriladi.
SQL nusxasi
SELECT * FROM Sales.ufn_SalesByStore (602);
Quyidagi misolning natijasi - AdventureWorks2012 ma'lumotlar bazasida ko'paytmali jadvalga o'rnatilgan o'rnatilgan funksiya (MSTVF). Funktsiya bitta WorkeeID kirish parametriga ega va ko'rsatilgan xodimga bevosita yoki bilvosita hisobot beradigan barcha xodimlarning ro'yxatini qaytaradi. Keyin funktsiya xodimning identifikatori 109 bilan chaqiriladi.
SQL nusxasi
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
( EmployeeID int primary key NOT NULL,
FirstName nvarchar(255) NOT NULL,
LastName nvarchar(255) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
RecursionLevel int NOT NULL )
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
AS (
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID
UNION ALL
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID )
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM EMP_cte
RETURN
END;
GO
Quyidagi misolda, funktsiya 1-raqamli xodim bilan chaqiriladi.
SQL nusxasi
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);
Tavsiyalar
Agar foydalanuvchi tomonidan belgilangan funktsiya (UDF) SCHEMABINDING bandini ishlatmasdan yaratilgan bo'lsa, asosiy ob'ektlarning o'zgarishi funktsiyaning aniqlanishiga ta'sir qilishi va funktsiya chaqirilganda kutilmagan natijalarga olib kelishi mumkin. Funktsiyaning asosiy ob'ektlari o'zgarishi sababli muddati tugamasligini ta'minlash uchun quyidagi usullardan birini amalga oshirish tavsiya etiladi.
• UDF funktsiyasini yaratishda WITH SCHEMABINDING so'zini ko'rsating. Bu, agar funktsiyaning o'zi o'zgarmasa, funktsiyaning ta'rifi bilan murojaat qilingan ob'ektlarni o'zgartirish mumkin emasligini ta'minlaydi.
• UDF funktsiyasi ta'rifida ko'rsatilgan har qanday ob'ektni o'zgartirgandan so'ng, saqlangan protsedurani sp_refreshsqlmodule bilan ishga tushiring.
Agar siz ma'lumotlarga kirish huquqiga ega bo'lmagan foydalanuvchi tomonidan aniqlanadigan funktsiyani yaratayotgan bo'lsangiz, SCHEMABINDING parametrini ko'rsating. Bu so'rovlar optimallashtiruvchisiga bunday foydalanuvchi tomonidan belgilangan funktsiyalarni o'z ichiga olgan so'rov rejalari uchun keraksiz navbat operatorlarini yaratishni oldini oladi. Navbatlar haqida ko'proq ma'lumot olish uchun Showplan mantiqiy va jismoniy operatorlari ma'lumotnomasiga qarang. Sxemaga bog'liq funktsiyalarni yaratish haqida ko'proq ma'lumot olish uchun tegishli bo'limga qarang.
MSTVF-ni FROM gapiga qo'shilish mumkin, ammo ishlashning yomonlashishiga olib kelishi mumkin. SQL Server MSTVF funktsiyasiga qo'shilishi mumkin bo'lgan ba'zi bayonotlar uchun
barcha optimallashtirilgan usullarni ishlata olmaydi va natijada so'rovlar rejasi maqbul emas. Agar iloji bo'lsa, eng yaxshi ko'rsatkichga erishish uchun funktsiyalar orasida emas, balki tayanch jadvallar o'rtasida birlashma belgilang.
Muhim!
MSTVF funktsiyalari SQL Server 2014 (12.x) dan boshlangan va SQL Serverning oldingi versiyalarida 100 dan iborat bo'lgan ko'paytmaning belgilangan taxminiy qiymatiga ega.
SQL Server 2017 bilan boshlanadi (14.x); MSTVF funktsiyalaridan foydalanadigan ijro rejasini optimallashtirish uchun siz yuqoridagi evristikning o'rniga haqiqiy ko'plikni ta'minlaydigan interlovved ijrodan foydalanishingiz mumkin.
Qo'shimcha ma'lumot olish uchun bir nechta ko'rsatmalarga ega jadval qiymati bo'lgan funktsiyalar uchun Interleaving-ga qarang.
Izoh
ANSI_WARNINGS parametrlari saqlangan protseduralarga, foydalanuvchi funktsiyalariga o'tish uchun va to'plam ko'rsatmalarida o'zgaruvchilarni e'lon qilish va o'rnatish uchun mos emas. Masalan, agar siz o'zgaruvchini char (3) deb e'lon qilsangiz va unga uchta belgidan uzunroq qiymatni belgilab qo'ysangiz, ma'lumotlar o'zgaruvchining o'lchamiga bo'linadi va INSERT yoki UPDATE bayonoti xatosiz tugaydi.