سلسلة sql للمحترفين الاستعلامات الفرعية والإجراءات في SQL

تستعرض هذه المقالة بعض المواضيع المتقدمة عن تنفيذ الشيفرات في SQL، مثل الاستعلامات الفرعية، وكتل التنفيذ، والإجراءات المُخزّنة، والزنادات، والمُعاملات.

الاستعلامات الفرعية Subqueries

الاستعلامات الفرعية هي استعلامات داخلية أو متشعّبة داخل استعلام آخر في SQL. يُمكن أن تُضمّن الاستعلامات الفرعية داخل ‎FROM‎ أو SELECT أو WHERE.

الاستعلامات الفرعية في عبارة FROM

تتصرّف الاستعلامات الفرعية في عبارة ‎FROM‎ بشكل مشابه للجداول المؤقتة المُنشأة أثناء تنفيذ استعلام، والمفقودة إثر ذلك.

SELECT Managers.Id, Employees.Salary
FROM (
    SELECT Id
    FROM Employees
    WHERE ManagerId IS NULL
) AS Managers
JOIN Employees ON Managers.Id = Employees.Id

الاستعلامات الفرعية في عبارة SELECT

إليك مثال على استعلام فرعي في SELECT:

SELECT
     Id,
     FName,
     LName,
    (SELECT COUNT(*) FROM Cars WHERE Cars.CustomerId = Customers.Id) AS NumberOfCars
FROM Customers

الاستعلامات الفرعية في عبارة WHERE

يمكنك استخدام استعلام فرعي لترشيح مجموعة النتائج. على سبيل المثال، تعيد الشيفرة التالية الموظفين الأعلى أجرًا فقط:

SELECT *
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees)

الاستعلامات الفرعية المرتبطة Correlated Subqueries

الاستعلامات الفرعية المرتبطة (والمعروفة أيضًا باسم المتزامنة أو المتّسقة) هي استعلامات متشعّبة تحتوي مرجعًا يشير إلى الصفّ الحالي في الاستعلام الخارجي:

SELECT EmployeeId
     FROM Employee AS eOuter
     WHERE Salary > (
       SELECT AVG(Salary)
       FROM Employee eInner
       WHERE eInner.DepartmentId = eOuter.DepartmentId
 )

الاستعلام الفرعي ‎SELECT AVG(Salary) ...‎ مرتبط لأنّه يشير إلى الصفّ ‎Employee‎ من الجدول ‎eOuter‎ من الاستعلام الخارجي.

ترشيح نتائج الاستعلام باستخدام استعلام مُنفَّذ على جدول آخر

يختار الاستعلام التالي جميع الموظفين غير الموجودين في جدول المشرفين Supervisors:

SELECT *
FROM Employees
WHERE EmployeeID not in (SELECT EmployeeID
 FROM Supervisors)

يمكن تحقيق النتائج نفسها باستخدام الضم اليساري LEFT JOIN:

SELECT *
FROM Employees AS e
LEFT JOIN Supervisors AS s ON s.EmployeeID=e.EmployeeID
WHERE s.EmployeeID is NULL

الاستعلامات الفرعية في عبارة FROM

يمكنك استخدام الاستعلامات الفرعية لتعريف جدول مؤقّت واستخدامه في عبارة FROM الخاصّة بالاستعلام الخارجي.

تبحث الشيفرة التالية عن المدن في جدول الطقس weather التي تتغيّر درجات الحرارة اليومية الخاصّة بها بأكثر من 20 درجة:

SELECT * FROM (SELECT city, temp_hi - temp_lo AS temp_var FROM weather) AS w
WHERE temp_var > 20;

النتيجة:

الاستعلامات الفرعية في عبارة WHERE

يبحث المثال التالي عن المدن (من مثال المدن) التي يقل تعداد سكانها عن متوسط درجة الحرارة فيها (يتم الحصول عليها عن طريق استعلام فرعي):

SELECT name, pop2000 FROM cities
WHERE pop2000 < (SELECT avg(pop2000) FROM cities);

في المثال أعلاه، يحدّد الاستعلام الفرعي SELECT avg(pop2000) FROM شرط عبارة WHERE.

النتيجة:

كتل التنفيذ Execution blocks

تُستخدم الكلمتان المفتاحيتان BEGIN و END لبدء كتلة تنفيذ (Execution block) وإنهائها على التوالي، كما يوضح المثال التالي:

BEGIN
      UPDATE Employees SET PhoneNumber = '5551234567' WHERE Id = 1;
      UPDATE Employees SET Salary = 650 WHERE Id = 3;
END

الإجراءات المخزّنة Stored Procedures

يمكن إنشاء الإجراءات المخزّنة عبر واجهة المستخدم الرسومية الخاصة ببرنامج إدارة قاعدة البيانات (مثال SQL Server)، أو من خلال عبارة SQL كما يلي:

-- تحديد الاسم والمعاملات
CREATE PROCEDURE Northwind.getEmployee
 @LastName nvarchar(50), 
 @FirstName nvarchar(50) 
AS 
-- تحديد الاستعلام المراد تنفيذه
SELECT FirstName, LastName, Department  
FROM Northwind.vEmployeeDepartment
WHERE FirstName = @FirstName AND LastName = @LastName 
AND EndDate IS NULL; 

يمكن استدعاء الإجراء على النحو التالي:

EXECUTE Northwind.getEmployee N'Ackerman', N'Pilar';
-- أو 
EXEC Northwind.getEmployee @LastName = N'Ackerman', @FirstName = N'Pilar'; 
GO 
-- أو 
EXECUTE Northwind.getEmployee @FirstName = N'Pilar', @LastName = N'Ackerman'; 
GO 

الزنادات Triggers

الزنادات هي إجراءات مخزّنة تُستدعى تلقائيًا عند وقوع أحداث معينة، مثل، إدراج صفّ في عمود، أو تحديث عمود ما، أو غيرها من الأحداث.

إنشاء زناد

ينشئ هذا المثال زنادًا يُدرج سجلًا في جدول ثانٍ (MyAudit) عند إدراج سجل ما في الجدول الذي عُرِّف الزناد عليه (MyTable). في هذا المثال، الجدول "inserted" هو جدول خاص تستخدمه Microsoft SQL Server لتخزين الصفوف المتأثِّرة (affected rows) خلال عبارتي INSERT و UPDATE؛ يوجد أيضًا جدول "deleted" خاصّ يؤدي نفس الوظيفة في عبارات DELETE.

CREATE TRIGGER MyTrigger
     ON MyTable
     AFTER INSERT
AS
BEGIN
     -- MyAudit إضافة سجل إلى الجدول
     INSERT INTO MyAudit(MyTableId, User)
     (SELECT MyTableId, CURRENT_USER FROM inserted)
END

المثال التالي يستخدم زنادًا لإدارة سلة المحذوفات عبر الجدول "deleted":

CREATE TRIGGER BooksDeleteTrigger
         ON MyBooksDB.Books
         AFTER DELETE
AS
     INSERT INTO BooksRecycleBin
         SELECT *
         FROM deleted;
GO

المعامَلات Transactions

المعامَلات (Transactions) هي سلسلة من عمليات SQL تُجرى على قاعدة بيانات، هذه السلسلة تُعامل كما لو كانت عملية واحدة، بحيث إما أن تُنفَّذ جميعا، ونقول أنّه تمّ الالتزام بها (committed)، أو عدم تنفيذ أيّ منها، ونقول أنّه تمّ التراجع عنها (rolled back).

المثال التالي يوضّح معاملة بسيطة:

BEGIN TRANSACTION
    INSERT INTO DeletedEmployees(EmployeeID, DateDeleted, User)
    (SELECT 123, GetDate(), CURRENT_USER);
    DELETE FROM Employees WHERE EmployeeID = 123;
COMMIT TRANSACTION

يمكنك التراجع عن المعاملة في حال حدث خطأ في الشيفرة:

BEGIN TRY
    BEGIN TRANSACTION
       INSERT INTO Users(ID, Name, Age)
       VALUES(1, 'Bob', 24)

       DELETE FROM Users WHERE Name = 'Todd'
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

ترجمة -وبتصرّف- للفصول من 52 إلى 56 من الكتاب SQL Notes for Professionals

اقرأ المقال:

• المقال التالي: تصميم الجداول ومعلومات المخطط وترتيب تنفيذ الاستعلامات في SQL
• المقال السابق: مواضيع متفرقة في SQL
• النسخة العربية الكاملة من كتاب ملاحظات للعاملين بلغة SQL 1.0.0