Oracle مُشغّل PL/SQL: بدلاً من الأنواع المركبة &

ما هو الزناد في PL/SQL؟

يتم تخزين المحفزات PL / SQL البرامج التي يتم تشغيلها بواسطة Oracle المحرك يعمل تلقائيًا عند بيانات DML تُنفَّذ عمليات مثل الإضافة والتحديث والحذف على الجدول، أو عند وقوع أحداث معينة. ويمكن تحديد الكود المطلوب تنفيذه في حالة المُشغِّل وفقًا للمتطلبات. يمكنك اختيار الحدث الذي يُفعِّل المُشغِّل وتوقيت التنفيذ. والغرض من المُشغِّل هو الحفاظ على سلامة المعلومات في قاعدة البيانات.

فوائد المشغلات

وفيما يلي فوائد المحفزات:

• توليد بعض قيم الأعمدة المشتقة تلقائيًا
• فرض التكامل المرجعي
• تسجيل الأحداث وتخزين المعلومات عند الوصول إلى الجدول
• التدقيق
• Syncالنسخ المتماثل للجداول
• فرض التصاريح الأمنية
• منع المعاملات غير الصالحة

أنواع المحفزات في Oracle

يمكن تصنيف المحفزات بناءً على المعلمات التالية.

التصنيف بناءً على التوقيت

• قبل الزناد: يتم تشغيله قبل وقوع الحدث المحدد.
• بعد الزناد: يتم تشغيله بعد وقوع الحدث المحدد.
• بدلا من الزناد: نوع خاص. ستتعرف على المزيد في المواضيع التالية. (خاص بـ DML)

التصنيف بناءً على المستوى

• مستوى البيان: يتم تشغيله مرة واحدة فقط لبيان الحدث المحدد.
• مستوى التفعيل ROW: يتم تشغيله لكل سجل تأثر في الحدث المحدد. (خاص بـ DML فقط)

التصنيف بناءً على الحدث

• مُشغّل DML: يتم تشغيله عند تحديد حدث DML (INSERT/UPDATE/DELETE).
• مُشغّل DDL: يتم تشغيله عند تحديد حدث DDL (إنشاء/تعديل).
• مُشغّل قاعدة البيانات: يتم تشغيله عند تحديد حدث قاعدة البيانات (تسجيل الدخول/تسجيل الخروج/بدء التشغيل/إيقاف التشغيل).

لذا فإن كل محفز هو مزيج من المعايير المذكورة أعلاه.

كيفية إنشاء الزناد

فيما يلي صيغة إنشاء مُشغّل. تُظهر لقطة الشاشة أدناه صيغة إنشاء هذا المُشغّل. Oracle.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

شرح بناء الجملة:

• يُظهر بناء الجملة أعلاه العبارات الاختيارية المختلفة الموجودة في إنشاء المشغل.
• سيحدد خيار "قبل/بعد" توقيتات الحدث.
• إدراج/تحديث/تسجيل الدخول/إنشاء/إلخ. سيحدد الحدث الذي يجب إطلاق المشغل من أجله.
• ستحدد عبارة ON الكائن الذي يكون الحدث المذكور أعلاه صالحًا عليه. على سبيل المثال، سيكون هذا اسم الجدول الذي قد يحدث عليه حدث DML في حالة مُشغّل DML.
• سيحدد الأمر "FOR EACH ROW" مستوى تشغيل الصف.
• ستحدد عبارة WHEN الشرط الإضافي الذي يجب أن يتم فيه تشغيل المشغل.
• تتشابه أجزاء الإعلان والتنفيذ ومعالجة الاستثناءات مع تلك الموجودة في الأنواع الأخرى. كتل بل / SQLالجزء الخاص بالإعلان و معالجة الاستثناء الأجزاء اختيارية.

:جديد و:بند قديم

في المشغل على مستوى الصف، يتم تشغيل المشغل لكل صف ذي صلة. وأحيانًا يكون من الضروري معرفة القيمة قبل وبعد عبارة DML.

Oracle تم توفير بندين في مُشغّل مستوى الصف لتخزين هذه القيم. يمكننا استخدام هذين البندين للإشارة إلى القيم القديمة والجديدة داخل نص المُشغّل.

• :جديد – يحتوي على قيمة جديدة لأعمدة الجدول/العرض الأساسي أثناء تنفيذ المشغل.
• :قديم – يحتفظ بالقيمة القديمة لأعمدة الجدول/العرض الأساسي أثناء تنفيذ المشغل.

يجب استخدام هذا الشرط بناءً على حدث DML. يوضح الجدول أدناه الشرط المناسب لكل عبارة DML (إدراج/تحديث/حذف).

بدلا من الزناد

يُعدّ "المُشغّل البديل" نوعًا خاصًا من المُشغّلات، ويُستخدم فقط في مُشغّلات لغة معالجة البيانات (DML). ويُستخدم عندما يكون هناك حدث DML مُرتقب وقوعه على عرض مُعقّد.

لنفترض مثالاً حيث يتم إنشاء عرض من ثلاثة جداول أساسية. عند تنفيذ أي حدث DML على هذا العرض، سيصبح غير صالح لأن البيانات مأخوذة من ثلاثة جداول مختلفة. لذا، في هذه الحالة، يُستخدم مُشغّل INSTEAD OF. يُستخدم مُشغّل INSTEAD OF لتعديل الجداول الأساسية مباشرةً بدلاً من تعديل العرض للحدث المُحدد.

مثال 1: في هذا المثال، سنقوم بإنشاء عرض معقد من جدولين أساسيين، حيث يمثل الجدول 1 جدول الموظفين والجدول 2 جدول الأقسام.

سنرى بعد ذلك كيفية استخدام مُشغّل INSTEAD OF لتحديث تفاصيل الموقع في هذا العرض المُعقّد. كما سنرى كيف تُفيد :NEW و :OLD في المُشغّلات. يتم تنفيذ المثال في الخطوات التالية:

• الخطوة 1: إنشاء جدولين 'emp' و 'dept' مع الأعمدة المناسبة
• الخطوة الثانية: ملء الجداول بقيم نموذجية
• الخطوة 3: إنشاء عرض للجداول التي تم إنشاؤها أعلاه
• الخطوة 4: تحديث العرض قبل تشغيل مُشغّل INSTEAD OF
• الخطوة 5: إنشاء مُشغّل INSTEAD OF
• الخطوة 6: تحديث العرض بعد تشغيل مُشغّل INSTEAD OF

الخطوة 1) إنشاء جدولين 'emp' و 'dept' مع الأعمدة المناسبة.

تُظهر لقطة الشاشة أدناه إنشاء الجداول الأساسية "emp" و "dept" في Oracle.

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept(
Dept_no NUMBER,
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Code تفسير

• Code الأسطر 1-7: إنشاء جدول "emp".
• Code الأسطر 8-12: إنشاء جدول "القسم".

الإخراج:

Table Created

الخطوة 2) الآن، بما أننا أنشأنا الجداول، فسوف نملأها بقيم نموذجية.

تُظهر لقطة الشاشة أدناه الصفوف النموذجية التي يتم إدراجها في جدولي "dept" و "emp".

BEGIN
INSERT INTO DEPT VALUES(10,'HR','USA');
INSERT INTO DEPT VALUES(20,'SALES','UK');
INSERT INTO DEPT VALUES(30,'FINANCIAL','JAPAN');
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code تفسير

• Code الأسطر 13-19: إدخال البيانات في جدول "القسم".
• Code الأسطر 20-26: إدخال البيانات في جدول 'emp'.

الإخراج:

PL/SQL procedure completed

الخطوة 3) إنشاء عرض للجداول التي تم إنشاؤها أعلاه.

تُظهر لقطة الشاشة أدناه عملية إنشاء العرض المعقد ثم الاستعلام عنه.

CREATE VIEW guru99_emp_view(
Employee_name,dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Code تفسير

• Code الأسطر 27-32: إنشاء عرض 'guru99_emp_view'.
• Code السطر 33: الاستعلام عن guru99_emp_view.

الإخراج:

View created

الخطوة 4) تحديث العرض قبل تشغيل المشغل INSTEAD OF.

تُظهر لقطة الشاشة أدناه محاولة التحديث على العرض المعقد والخطأ الناتج.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

Code تفسير

• Code الأسطر 34-38: قم بتحديث موقع "XXX" إلى "فرنسا". وقد أدى ذلك إلى ظهور استثناء لأن عبارات DML غير مسموح بها مباشرة على العرض المركب.

الإخراج:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

الخطوة 5) لتجنب الخطأ الذي واجهناه أثناء تحديث العرض في الخطوة السابقة، سنستخدم في هذه الخطوة مشغل "INSTEAD OF".

تُظهر لقطة الشاشة أدناه عملية إنشاء مُشغّل INSTEAD OF.

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Code تفسير

• Code السطر 39: إنشاء مُشغّل INSTEAD OF لحدث 'UPDATE' في عرض 'guru99_emp_view' على مستوى الصف. يحتوي هذا المُشغّل على عبارة التحديث لتحديث الموقع في الجدول الأساسي 'dept'.
• Code السطر 44: تستخدم عبارة التحديث ':NEW' و ':OLD' للعثور على قيمة الأعمدة قبل التحديث وبعده.

الإخراج:

Trigger Created

الخطوة 6) تحديث العرض بعد تشغيل مُشغّل "INSTEAD OF". لن يظهر الخطأ الآن، حيث سيتولى مُشغّل "INSTEAD OF" عملية تحديث هذا العرض المُعقّد. عند تنفيذ الكود، سيتم تحديث موقع الموظف XXX من "اليابان" إلى "فرنسا".

تُظهر لقطة الشاشة أدناه عملية التحديث الناجحة من خلال مشغل INSTEAD OF والعرض المُحدث.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code التفسير:

• Code الأسطر 49-53: تم تحديث موقع "XXX" إلى "فرنسا". وقد نجحت العملية لأن مُشغّل "INSTEAD OF" أوقف عبارة التحديث الفعلية في العرض وقام بتحديث الجدول الأساسي.
• Code السطر 55: التحقق من السجل المحدث.

الإخراج:

PL/SQL procedure successfully completed

الزناد المركب

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

• قبل البيان - المستوى
• قبل الصف - المستوى
• بعد الصف - المستوى
• بعد البيان - المستوى

فهو يوفر إمكانية دمج الإجراءات لأوقات مختلفة في نفس المشغل.

تُظهر لقطة الشاشة أدناه صيغة المُشغّل المركب مع أقسام التوقيت الأربعة الخاصة به.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
ON <name of underlying object>
<Declarative part>
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

شرح بناء الجملة:

• يوضح التركيب النحوي أعلاه إنشاء مشغل "COMPOUND".
• يُعد القسم التصريحي مشتركًا بين جميع كتل التنفيذ في جسم المشغل.
• يمكن ترتيب هذه الكتل الزمنية الأربع بأي ترتيب. ليس من الضروري وجودها جميعًا. يمكننا إنشاء مُشغّل مُركّب فقط للأوقات المطلوبة.

مثال 1: في هذا المثال، سنقوم بإنشاء مشغل لتعبئة عمود الراتب تلقائيًا بالقيمة الافتراضية 5000.

تُظهر لقطة الشاشة أدناه مثالاً على المُشغّل المركب ومخرجاته.

CREATE TRIGGER emp_trig
FOR INSERT
ON emp
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,'CCC',15000,'AAA',30);
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Code التفسير:

• Code الأسطر 2-10: إنشاء مُشغّل مُركّب. يتم إنشاؤه لتوقيت مستوى الصف السابق لتعبئة خانة الراتب بالقيمة الافتراضية 5000. سيؤدي هذا إلى تغيير الراتب إلى القيمة الافتراضية '5000' قبل إدراج السجل في الجدول.
• Code الأسطر 11-14: أدخل السجل في جدول 'emp'.
• Code السطر 16: جارٍ التحقق من السجل المُدرج.

الإخراج:

Trigger created

PL/SQL procedure successfully completed.

تمكين وتعطيل المشغلات

يمكن تفعيل أو تعطيل المشغلات. لتفعيل أو تعطيل مشغل، يجب إدخال عبارة ALTER (DDL) خاصة بالمشغل الذي يقوم بتعطيله أو تفعيله.

فيما يلي صيغة تفعيل/تعطيل المشغلات.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

شرح بناء الجملة:

• يوضح الشكل الأول كيفية تمكين/تعطيل مُشغّل واحد.
• يوضح البيان الثاني كيفية تمكين/تعطيل جميع المشغلات في جدول معين.

الأسئلة الشائعة