Oracle البرنامج التعليمي لـ PL/SQL Dynamic SQL: التنفيذ الفوري وDBMS_SQL

ما هو SQL الديناميكي؟

التفاعل SQL هي منهجية برمجة لإنشاء وتنفيذ عبارات SQL أثناء تشغيل البرنامج. تُستخدم بشكل أساسي لكتابة برامج عامة ومرنة، حيث تُنشأ عبارات SQL وتُنفذ أثناء التشغيل بناءً على المتطلبات، على سبيل المثال عندما لا تكون أسماء الجداول أو قوائم الأعمدة أو شروط WHERE معروفة حتى يتم تشغيل البرنامج.

طرق كتابة لغة SQL الديناميكية

توفر لغة PL/SQL طريقتين لكتابة استعلامات SQL الديناميكية:

1. NDS - SQL الديناميكي الأصلي (عبارات EXECUTE IMMEDIATE و OPEN-FOR)
2. DBMS_SQL (عبوة مرفقة)

القاعدة العامة بسيطة: إذا كان عدد وأنواع بيانات متغيرات الإدخال والإخراج معروفة وقت الترجمة، فاستخدم لغة SQL الديناميكية الأصلية لأنها أسرع وتتطلب كتابة كود أقل. أما إذا كانت هذه المعلومات معروفة فقط وقت التشغيل، فاستخدم حزمة DBMS_SQL.

NDS (SQL الديناميكي الأصلي) – تنفيذ فوري

يُعدّ SQL الديناميكي الأصلي الطريقة الأسهل لكتابة استعلامات SQL الديناميكية. فهو يستخدم الأمر EXECUTE IMMEDIATE لإنشاء وتنفيذ استعلام SQL أثناء التشغيل. لاستخدام هذه الطريقة، يجب معرفة نوع البيانات وعدد المتغيرات المستخدمة أثناء التشغيل مسبقًا. كما أنها توفر أداءً أفضل وتعقيدًا أقل مقارنةً بـ DBMS_SQL.

بناء الجملة

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• dynamic_sql_string: تعبير سلسلة نصية (VARCHAR2 أو CHAR، وليس NVARCHAR2/NCHAR) يحتوي على عبارة SQL واحدة أو كتلة PL/SQL.
• عبارة INTO: اختياري. يُستخدم فقط عندما يكون استعلام SQL الديناميكي عبارة عن استعلام SELECT لصف واحد؛ حيث يقوم بتخزين القيم المُعادة في متغيرات أو سجل. يحتاج كل عمود مُحدد إلى متغير متوافق مع نوع البيانات.
• باستخدام عبارة USING: اختياري. تُستخدم متغيرات الربط. الوضع الافتراضي هو IN؛ ويتم استخدام OUT و IN OUT لاستقبال القيم مرة أخرى.
• عبارة العودة إلى: يستخدم مع عبارات DML التي تحمل عبارة RETURNING، لالتقاط قيم الصف المتأثر في وسيطات الربط.

مثال 1: في هذا المثال، نقوم بجلب البيانات من جدول الموظفين للرقم الوظيفي '1001' باستخدام عبارة NDS مع متغير ربط.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

الناتج

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code التفسير:

• الخطوط 2-6: تعريف المتغيرات.
• خط 8: تحديد إطار استعلام SQL أثناء التشغيل. يحتوي استعلام SQL على متغير الربط ':empno' في شرط WHERE.
• الخطوط 9-11: يتم تنفيذ استعلام SQL المُؤطَّر باستخدام الأمر EXECUTE IMMEDIATE. تحتوي متغيرات عبارة INTO على القيم المُسترجعة، بينما تُزوِّد عبارة USING قيمة المتغير المُرتبط :empno.
• الخطوط 12-15: عرض القيم التي تم جلبها.

استخدام لغة SQL الديناميكية لتعريف البيانات

لا يمكن لـ PL/SQL الثابتة تنفيذ أوامر DDL مثل CREATE أو ALTER أو DROP مباشرةً. يحل الأمر EXECUTE IMMEDIATE هذه المشكلة عن طريق إنشاء العبارة كسلسلة نصية، وهو أمر مفيد أيضًا عند تحديد اسم الكائن أثناء التشغيل.

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

لا يمكن تمرير أسماء الكائنات (الجدول، العمود، المخطط) كمتغيرات ربط، لذا يجب دمجها في سلسلة نصية. يجب دائمًا التحقق من صحة هذا الإدخال، على سبيل المثال باستخدام DBMS_ASSERT.SIMPLE_SQL_NAME، لتجنب هجمات حقن SQL.

DBMS_SQL لـ Dynamic SQL

توفر لغة PL/SQL حزمة DBMS_SQL للتعامل مع استعلامات SQL الديناميكية عندما لا يكون هيكل العبارة معروفًا إلا في وقت التشغيل. تتضمن عملية إنشاء وتنفيذ استعلامات SQL الديناميكية الخطوات التالية:

• فتح المؤشر: يتم تنفيذ استعلام SQL الديناميكي مثل المؤشرلتنفيذ عبارة SQL، يجب علينا أولاً فتح المؤشر.
• تحليل SQL: قم بتحليل استعلام SQL الديناميكي. هذا يتحقق من بناء الجملة ويحافظ على جاهزية الاستعلام للتنفيذ.
• ربط قيم المتغيرات: قم بتعيين قيم المتغيرات المرتبطة، إن وجدت.
• تعريف العمود: حدد كل عمود باستخدام موقعه النسبي في عبارة التحديد.
• ينفذ: نفّذ الاستعلام الذي تم تحليله.
• جلب القيم: استرجع القيم المنفذة.
• إغلاق المؤشر: بمجرد جلب النتائج، أغلق المؤشر.

مثال 1: في هذا المثال، نسترجع البيانات من جدول الموظفين للموظف رقم '1001' باستخدام عبارة DBMS_SQL. يُغلق قسم الاستثناءات المؤشر حتى في حالة حدوث خطأ.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

الناتج

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code التفسير:

• الخطوط 1-8: تعريف المتغيرات.
• خط 10: صياغة عبارة SQL.
• خط 11: فتح المؤشر باستخدام DBMS_SQL.OPEN_CURSOR، والذي يُرجع معرف المؤشر المفتوح.
• خط 12: بعد فتح المؤشر، يتم تحليل استعلام SQL.
• خط 13: تم تعيين قيمة الربط '1001' بدلاً من ':empno'.
• الخطوط 14-17: تحديد الأعمدة حسب موقعها النسبي: (1) اسم الموظف، (2) رقم الموظف، (3) الراتب، (4) المدير.
• خط 18: تنفيذ الاستعلام باستخدام DBMS_SQL.EXECUTE، والذي يُرجع عدد السجلات التي تمت معالجتها.
• الخطوط 19-32: يتم جلب السجلات في حلقة تكرارية. تُرجع الدالة FETCH_ROWS القيمة 0 عندما لا يتبقى أي صفوف، مما يؤدي إلى الخروج من الحلقة.
• كتلة الاستثناء: يضمن إغلاق المؤشر حتى لا تتسرب المؤشرات المفتوحة في حالة حدوث خطأ.

NDS مقابل DBMS_SQL: متى يُستخدم كل منهما

كلا النهجين يقومان بتشغيل SQL في وقت التشغيل، لكنهما يناسبان مواقف مختلفة:

• استخدم لغة SQL الديناميكية الأصلية (EXECUTE IMMEDIATE / OPEN-FOR) عندما يكون عدد وأنواع بيانات المدخلات والمخرجات معروفة وقت الترجمة، يكون ذلك أسرع وأسهل في القراءة ويتطلب كتابة كود أقل.
• استخدم DBMS_SQL عندما يكون الهيكل غير معروف حتى وقت التشغيل، على سبيل المثال استعلام يختلف فيه عدد الأعمدة المحددة أو متغيرات الربط، والمعروف باسم طريقة SQL الديناميكية 4، أو عبارة كبيرة جدًا بحيث لا يمكن وضعها في متغير VARCHAR2 واحد بحجم 32 كيلوبايت.

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