معالجة الأخطاء والتعديل على قواعد البيانات في SQL

تستعرض هذه المقالة كيفية معالجة الأخطاء باستخدام العبارة TRY / CATCH، وكيفية حساب الاتحاد (UNION) وبعض العمليات الأخرى التي تمكّن من تعديل قواعد البيانات.

العبارة TRY / CATCH

تُستخد عبارة TRY / CATCH لإمساك الأخطاء في الشيفرات التي يُتوقع أن تطرح أخطاء.

ستفشل عمليتا الإدراج في المثال التالي بسبب خطأ في صياغة التاريخ:

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, GETDATE(), 1)
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, 'not a date', 1)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    THROW
    ROLLBACK TRANSACTION
END CATCH

في المثال التالي، ستكتمل عَمليتا الإدراج دائمًا:

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, GETDATE(), 1)
    INSERT INTO dbo.Sale(Price, SaleDate, Quantity)
    VALUES (5.2, GETDATE(), 1)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    THROW
    ROLLBACK TRANSACTION
END CATCH

الاتحاد UNION

تُستخدم الكلمة المفتاحية UNION في SQL لدمج نتائج عبارتي SELECT دون تكرار. أي أنّها تشبه عملية الاتحاد المعروفة في علم المجموعات.

من أجل استخدام UNION لدمج النتائج، يُشترط أن يكون لكلي عبارتي SELECT عدد الأعمدة ونوع البيانات نفسه، ووفق الترتيب نفسه، ولكن يجوز أن تختلف أطوال الأعمدة.

إليك الجدولين التاليين:

CREATE TABLE HR_EMPLOYEES
(
    PersonID int,
    LastName VARCHAR(30),
    FirstName VARCHAR(30),
    Position VARCHAR(30)
);
CREATE TABLE FINANCE_EMPLOYEES
(
    PersonID INT,
    LastName VARCHAR(30),
    FirstName VARCHAR(30),
    Position VARCHAR(30)
);

باستخدام ‎UNION‎، يمكننا الحصول على جميع المدراء (‎manager‎) العاملين في القسمين HR و Finance على النحو التالي:

SELECT
    FirstName, LastName  
FROM
    HR_EMPLOYEES  
WHERE
    Position = 'manager' 
UNION ALL 
SELECT
    FirstName, LastName  
FROM
    FINANCE_EMPLOYEES  
WHERE
    Position = 'manager'

تزيل العبارة ‎UNION‎ الصفوف المكرّرة من نتائج الاستعلام. لكن لمّا كان من الممكن أن يشترك عدّة أشخاص في نفس الاسم، ويحتلّون نفس الموقع في كلا القسمين، فسنستخدم عبارة ‎UNION ALL‎ التي لا تزيل التكرارات.

يمكنك تكنِية (aliasing) الأعمدة المُعادة عبر وضع الكُنى في أول عبارات select على النحو التالي:

SELECT
    FirstName as 'First Name', LastName as 'Last Name'
FROM
    HR_EMPLOYEES  
WHERE
    Position = 'manager' 
UNION ALL 
SELECT
    FirstName, LastName  
FROM
    FINANCE_EMPLOYEES  
WHERE
    Position = 'manager'

هناك فرق أساسي بين ‎UNION‎ و UNION ALL:

تضمّ ‎UNION‎ مجموعتي النتائج مع إزالة التكرارات من مجموعة النتائج
تضمّ UNION ALL مجموعتي النتائج دون إزالة التكرارات

من الأخطاء الشائعة استخدامُ ‎UNION‎ في المواضع التي لا تكون فيها حاجة إلى إزالة التكرار من النتائج، فالكلفة الإضافية على الأداء قد تكون أكبر من المكاسب الناجمة عن إزالة التكرارات.

متى تستخدم UNION

لنفترض أنك تريد تصفية الدول بحسب قيم سمتين (attributes) مختلفتين، وأنّك أنشأت فهارس منفصلة غير مجمّعة (non-clustered indexes) لكل عمود. في هذه الحالة، يمكنك استخدام ‎UNION‎، التي تتيح لك استخدام كلا الفهرسين مع تجنّب التكرارات.

SELECT C1, C2, C3 FROM Table1 WHERE C1 = @Param1
UNION
SELECT C1, C2, C3 FROM Table1 WHERE C2 = @Param2

لن تُستخدم إلا الفهارس البسيطة في تنفيذ الاستعلامات، كما يمكنك تقليل عدد الفهارس المنفصلة غير المجمّعة (separate non-clustered indexes)، وهذا سيؤدي إلى تحسين الأداء.

متى تستخدم UNION ALL

لنفترض أنك تريد تصفية جدول ما بحسب قيمتي سمتين، بيْد أنّك لا تحتاج إلى تصفية السجلات المكرّرة (إمّا لأنّ ذلك لن يضرّ، أو أنّك صمّمت قاعدة البيانات بحيث لن ينتج أيّ تكرارات عن عملية الاتحاد).

SELECT C1 FROM Table1
UNION ALL
SELECT C1 FROM Table2

يمكن أن يكون استخدام UNION ALL مفيدًا عند إنشاء معارض (Views) تضمّ (join) بيانات صُمِّمت لكي تُقسَّم وتُوزَّع عبر عدّة جداول (ربما لأسباب تتعلق بتحسين الأداء). ولمّا كانت البيانات مقُسمة سلفًا، فإنّ جعل محرّك قاعدة البيانات يزيل التكرارات لن يضيف أيّ قيمة، وسَيبطئ الاستعلام.

ALTER TABLE

تُستخدم العبارة ALTER في SQL لتعدِيل قيد (constraint) أو عمود من جدول.

إضافة عمود

يضيف المثال التالي عمودين إلى جدول الموظفين، الأول باسم ‎StartingDate‎، ولا يمكن أن يكون معدومًا (not NULLABLE)، وقيمته الافتراضية تساوي التاريخ الحالي، أمّا العمود الثاني، فيُسمّى ‎DateOfBirth‎، ويمكن أن يكون معدومًا.

ALTER TABLE Employees
ADD StartingDate date NOT NULL DEFAULT GetDate(),
    DateOfBirth date NULL

حذف عمود

تحذف الشيفرَة أدناه العمود salary من جدول الموظفين:

ALTER TABLE Employees
DROP COLUMN salary;

إضافة مفتاح رئيسي Primary Key

تضيف الشيفرة أدناه مفتاحًا رئيسيًا إلى جدول "الموظفين" في الحقل ‎ID‎:

ALTER TABLE EMPLOYEES ADD pk_EmployeeID PRIMARY KEY (ID)

يؤدّي تضمين عدّة أسماء أعمدة بين قوسين بعد العبارة PRIMARY KEY إلى إنشاء مفتاح رئيسي مُركّب (Composite Primary Key):

ALTER TABLE EMPLOYEES ADD pk_EmployeeID PRIMARY KEY (ID, FName)

تغيير عمود

يعدّل الاستعلام التالي نوع بيانات العمود ‎StartingDate‎، ويغيّره من النوع ‎date‎ إلى ‎datetime‎، كما يعيّن قيمته الافتراضية عند قيمة التاريخ الحالي.

ALTER TABLE Employees
ALTER COLUMN StartingDate DATETIME NOT NULL DEFAULT (GETDATE())

حذف القيود Drop Constraint

تحذف الشيفرة التالية قيدًا يُسمّى DefaultSalary من جدول الموظفين:

ALTER TABLE Employees
DROP CONSTRAINT DefaultSalary

تنبيه: عليك حذف قيود العمود قبل حذف العمود.

الإدراج عبر INSERT

إدراج البيانات من جدول آخر باستخدام SELECT

يدرج المثال التالي جميع الموظفين (Employees) في جدول العملاء (Customers). ونظرًا لأنّ الجدولين يحتويان على حقول مختلفة، وقد لا تريد نقل جميع الحقول، فستحتاج إلى تحديد الحقول التي ستُدرج القيم فيها، و الحقول التي يجب اختيارها.

لست مضطرّا للحفاظ على أسماء الحقول المترابطة (correlating ﬁeld)، بيْد أنّه ينبغي عدم تغيير نوع البيانات.

يفترض هذا المثال أنّ هويّة حقل المعرّف (Id) مُحدّدة، وأنه تلقائيّ التزايد (auto increment).

INSERT INTO Customers (FName, LName, PhoneNumber)
SELECT FName, LName, PhoneNumber FROM Employees

إن كان للجدولين نفس أسماء الحقول، وكنت تريد نقل جميع السجلات من جدول إلى آخر، فيمكنك استخدام الشيفرة التالية:

INSERT INTO Table1
SELECT * FROM Table2

إدراج صف جديد

تدرج الشيفرة التالية صفًّا جديدًا في الجدول ‎Customers‎. لاحظ أنّنا لم نحدّد قيمة العمود ‎Id‎، ذلك أنّ قيمته ستضاف تلقائيًا. أمّا الأعمدة الأخرى فينبغي أن تُعيّن قيمها.

INSERT INTO Customers
VALUES ('Zack', 'Smith', 'zack@example.com', '7049989942', 'EMAIL');

إدراج أعمدة معيّنة

تدرج الشيفرة التالية صفًّا جديدًا في الجدول ‎Customers‎، لاحظ أنّها لن تُدرج البيانات إلّا في الأعمدة المُحدّدة:

INSERT INTO Customers (FName, LName, Email, PreferredContact)
VALUES ('Zack', 'Smith', 'zack@example.com', 'EMAIL');

لاحظ أنه لم يتم تقديم أيّ قيمة للعمود ‎PhoneNumber‎. ولاحظ أيضًا أنّه ينبغي تضمين الأعمدة الموسومة بـ ‎not null‎.

إدراج عدّة صفوف دفعة واحدة

يمكن إدراج عدّة صفوف باستخدام أمر إدراج واحد على النحو التالي:

INSERT INTO tbl_name (field1, field2, field3)
VALUES (1,2,3), (4,5,6), (7,8,9);

قد تحتاج أحيانًا إلى إدراج كمّيات كبيرة من البيانات دُفعة واحدة، عادة ما توفّر أنظمة إدارة قواعد البيانات بعض التوصيات والميزات للمساعدة على ذلك، كما في حالة:

MySQL
MSSQL

الدمج عبر MERGE

تسمح العبارة MERGE (تُسمّى أيضًا UPSERT) بإدراج صفوف جديدة أو تحديثها. الهدف من هذه العملية هو إجراء مجموعة كاملة من العمليات تلقائيًا (لضمان بقاء البيانات متسقة)، ومنع حمل الاتصال الزائد (communication overhead) لعبارات SQL في نظام عميل / خادم (client/server system).

إجراء عملية الدمج لجعل المصدر يطابق الهدف

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

MERGE INTO targetTable t
    USING sourceTable s
        ON t.PKID = s.PKID
     WHEN MATCHED AND NOT EXISTS (
             SELECT s.ColumnA, s.ColumnB, s.ColumnC
             INTERSECT
             SELECT t.ColumnA, t.ColumnB, s.ColumnC
             )
     THEN UPDATE SET
            t.ColumnA = s.ColumnA
         ,t.ColumnB = s.ColumnB
         ,t.ColumnC = s.ColumnC
     WHEN NOT MATCHED BY TARGET
         THEN INSERT (PKID, ColumnA, ColumnB, ColumnC)
         VALUES (s.PKID, s.ColumnA, s.ColumnB, s.ColumnC)
     WHEN NOT MATCHED BY SOURCE
         THEN DELETE
     ;

ملاحظة: تمنع العبارة ‎AND NOT EXISTS‎ تحديث السجلات التي لم تتغير. ويتيح استخدام البنية ‎INTERSECT‎ موازنة الأعمدة المعدومة (nullable) بدون مشاكل.

MySQL: عدّ أسماء المستخدمين

لنفترض أنّنا نريد أن نحسب عدد المستخدمين الذين لهم نفس الاسم. سننشئ أولًا جدولًا ‎users‎ على النحو التالي:

create table users(
    id int primary key auto_increment,
    name varchar(8),
    count int,
    unique key name(name)
);

لنفترض الآن أنّنا اكتشفنا وجود مستخدم آخر جديدًا يُدعى Joe، ونودّ أن نأخذه في الحسبان. أولًا، سنتحقّق ممّا إذا كان هناك صفّ يحمل ذلك الاسم، فإن كان الأمر كذلك، حدّثناه وزدنا قيمته بواحد؛ خلاف ذلك، سننشئ صفًّا جديدًا.

تستخدم MySQL الصياغة التالية: insert … on duplicate key update …‎‎:

insert into users(name, count)
    values ('Joe', 1)
    on duplicate key update count=count+1;

PostgreSQL: عدّ أسماء المستخدمين

لنفترض أنّنا نريد أن نعرف عدد المستخدمين الذين لهم نفس الاسم. سننشئ جدولًا ‎users‎ على النحو التالي:

create table users(
    id serial,
    name varchar(8) unique,
    count int
);

كما في الفقرة السابقة، سنفترض أنّنا اكتشفنا للتو مستخدمًا جديدًا يُدعى Joe، ونودّ أن نأخذه في الحسبان:

تستخدم PostgreSQLالصياغة التالية: insert … on conflict … do update …‎‎:

insert into users(name, count)
    values('Joe', 1)
    on conflict (name) do update set count = users.count + 1;

التطبيق المتقاطع والتطبيق الخارجي cross apply, outer apply

تُستخدم العبارة Apply لتطبيق دالة على جدول.

سننشئ جدولًا Department لتخزين المعلومات الخاصّة بالأقسام. ثم ننشئ جدولًا Employee يحتوي معلومات حول الموظفين. ينتمي كلّ موظّف إلى قسم معيّن، وبالتالي، فإنّ لجدول الموظّفين مرجعًا متكاملًا (referential integrity) مع جدول الأقسام.

يختار الاستعلام الأول البيانات من جدول الأقسام، ثمّ يستخدم العبارة CROSS APPLY لتقييم جدول الموظفين نسبة إلى سجلّات جدول الأقسام. أمّا الاستعلام التالي، [فيضمّ](رابط الفصل 6) جدول الأقسام إلى جدول الموظفين، ثم يعيد جميع السجلات التي تحقّق شرط الضمّ.

SELECT *
FROM Department D
CROSS APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
INNER JOIN Employee E
    ON D.DepartmentID = E.DepartmentID

لو نظرت إلى النتائج المُعادة، فستلاحظ أنّ الاستعلامين يعيدان نفس النتائج؛ قد تسأل إذن: كيف تختلف CROSS APPLY عن JOIN، وهل أداؤها أحسن.

يختار الاستعلام الأول في الشيفرة التالية البيانات من جدول الأقسام، ثمّ يستخدم OUTER APPLY لتقييم جدول الموظفين نسبة إلى كلّ سجل من سجلّات جدول الأقسام. تُعطى قيم الصفوف التي ليس لها مُطابق في جدول الموظفين القيمة المعدومة NULL، كما هو حال الصفّين 5 و 6.

يستخدم الاستعلام الثاني الضم الخارجي اليساري LEFT OUTER JOIN بين جدول الأقسام وجدول الموظفين. وكما هو متوقع، يعيد الاستعلام كافّة الصفوف من جدول الأقسام؛ بما فيها الصفوف التي ليس لها مُطابق في جدول الموظفين.

SELECT *
FROM Department D
OUTER APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
LEFT OUTER JOIN Employee E
    ON D.DepartmentID = E.DepartmentID
GO

رغم أنّ الاستعلامين أعلاه يعيدان المعلومات نفسها، فإنّ خطة التنفيذ تختلف بعض الشيء. بيْد أنّه لن يكون هناك اختلاف يُذكر في الأداء.

في بعض الحالات، يكون استخدام المعامل APPLY ضروريًا. في المثال التالي، سننشئ دالة جدولية (table-valued function)، تقبل الحقل DepartmentID كمعامل، وتعيد جميع الموظفين المنتمين إلى القسم ذي المعرّف DepartmentID. يختار الاستعلام التالي البيانات من الجدول Department ويستخدم CROSS APPLY مع الدالة التي أنشأناها. يمرَّر الحقل DepartmentID من كل صفّ من الجدول الخارجي - outer table - (أي الجدول Department)، ثمّ يطبّق الدالة على كلّ صف بشكل يماثل الاستعلامات الفرعية المرتبطة (correlated subquery).

يستخدم الاستعلام الثاني OUTER APPLY بدلاً من CROSS APPLY، وبالتالي، فعلى عكس التطبيق المتقاطع CROSS APPLY الذي لا يعيد إلّا البيانات المرتبطة (correlated data)، يعيد التطبيق الخارجي OUTER APPLY البيانات غير المرتبطة أيضًا، مع وضع القيم المعدومة (NULLs) في الأعمدة غير الموجودة.

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment (@DeptID AS int)
RETURNS TABLE
AS
    RETURN
    (
    SELECT
       *
    FROM Employee E
    WHERE E.DepartmentID = @DeptID
    )
GO
SELECT
    *
FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO
SELECT
    *
FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO

قد يتبادر إلى ذهنك السؤال التالي: هل يمكننا استخدام عمليّة ضمّ بسيطة بدلاً من استخدام الاستعلامات أعلاه؟ الجواب هو "لا"، إذا استبدلت CROSS / OUTER APPLY في الاستعلامات أعلاه بـعمليّات الضمّ INNER JOIN أو LEFT OUTER JOIN، وحدّدت العبارة ON (مثلًا 1 = 1)، ثمّ نفّذت الاستعلام، فسيُطرح الخطأ:

The multi-part identifier "D.DepartmentID" could not be bound.

ذلك أنّه في عمليّات الضمّ، يكون سياق تنفيذ الاستعلام الخارجي مختلفًا عن سياق تنفيذ الدالة (أو الجدول المشتق - derived table)، ولا يمكنك تمرير قيمة أو متغيّر من الاستعلام الخارجي إلى الدالة كمعامل. لهذا يجب استخدام المعامل APPLY في مثل هذه الاستعلامات.

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