مزود إلى CSV باستخدام DBMS_SQL

غالبًا ، عند حل مشكلات تكامل النظام ، من الضروري تقديم قدر معين من البيانات بتنسيق أو آخر. في الوقت نفسه ، يمكن لأي شخص أن يكون مستهلكًا للبيانات ، لكن المصدر دائمًا ما يكون قاعدة بيانات للشركات. على سبيل المثال ، قد يطلب الصانع من المورد الإبلاغ دوريًا عن حركة البضائع الخاصة به بتنسيق XLSX أو XML ، إلخ.

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

إذا افترضنا أن استعلام SQL يكمن في الواقع في جذر عملية اختيار البيانات ، فمن الأفضل أن تكون سلسلة التحويلات مناسبة لرؤية هذا:

$$

$$d '= f (SQL (d))$$

حيث
$$$د$- مصدر البيانات ،
$$ - استعلام SQL لاسترداد البيانات ،
$$$و$- وظيفة تحول الاختيار إلى التنسيق المطلوب ،
$$$d '$- البيانات بالتنسيق المطلوب.

بالنسبة إلى Oracle PL / SQL ، يوجد عدد من الحزم المضمنة والجهات الخارجية التي تنفذ هذه الوظيفة. هذه هي DBMS_XMLGEN و DBMS_XMLQUERY و AS_XLSX و PL / JSON وغيرها.

ومع ذلك ، عند ظهور سؤال حول تحويل البيانات إلى تنسيق CSV ، لسبب ما لم تكن هناك حلول جاهزة. كان علي أن أفعل ذلك بنفسي ، ثم سيظهر كيف.

بيان المشكلة

قم بإنشاء أداة (حزمة PL / SQL) تستقبل استعلام SELECT التعسفي كسلسلة أو كمتغير مؤشر عند الإدخال ، وتقوم بإرجاع كائن من نوع CLOB لتغليف البيانات بتنسيق CSV عند الإخراج. في حال وجود أي خطأ ، يجب إرجاع NULL. لا يلزم تمثيل تنسيق ملف CSV نفسه - فهذه عبارة عن سلاسل يتم فصل عناصرها بشخص ما ، وغالبًا "؛" ، ولكن في الحالة العامة ، يمكن أن تعمل الشخصية التعسفية كفاصل. افترض أنه يتم استخدام الأحرف 0x0D + 0x0A لفصل السلاسل. عادةً ما يكون السطر الأول في ملف CSV هو العنوان ويحدد أسماء الأعمدة.

تحديد واجهة الحزمة

CREATE OR REPLACE PACKAGE pp_csv AS PROCEDURE query2sheet( stmt IN VARCHAR2, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); PROCEDURE query2sheet( ref_cursor IN OUT SYS_REFCURSOR, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); END; 

يوجد إجراءان زائدان ، والفرق بينهما هو أن أحدهما يتلقى الطلب كسلسلة ، والآخر كارتباط بالمؤشر. المعلمة الثانية هي الإخراج ؛ هذه هي النتيجة المرغوبة في كائن CLOB. أخيرًا ، المعلمة الثالثة هي فاصل CSV.

في تنفيذ هذه الإجراءات ، سوف تساعدنا حزمة DBMS_SQL المدمجة ، والتي تتيح لنا العمل مع المؤشرات الديناميكية عندما يكون غير معروف مقدمًا (في مرحلة الترجمة) بالضبط عدد الأعمدة المشاركة في التحديد.

تتيح لك ميزات DBMS_SQL تحليل الاستعلامات التعسفية وتنفيذها ديناميكيًا. لإجراء يقبل طلب كسلسلة ، يحدث هذا مثل هذا:

 AS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; --     DBMS_SQL ignore INTEGER; BEGIN DBMS_SQL.PARSE(cur, stmt, DBMS_SQL.NATIVE); ignore := DBMS_SQL.EXECUTE(cur); 

لإجراء يتطلب متغير المؤشر ، كل شيء أبسط - بدءًا من الإصدار الحادي عشر من Oracle ، أصبح تحويل "متغير المؤشر → رقم مؤشر SQL" متاحًا.

تقوم دالة DBMS_SQL.TO_CURSOR_NUMBER بتحويل متغير REFCURSOR (مكتوب بقوة أو ضعيف) إلى رقم مؤشر SQL ، والذي يمكن بعد ذلك تمريره إلى إجراءات DBMS_SQL. في هذه الحالة ، يجب أن يكون متغير المؤشر مفتوحًا قبل تمريره إلى الدالة DBMS_SQL.TO_CURSOR_NUMBER.

 cur PLS_INTEGER := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor); 

وهكذا ، حصل كلا الإصدارين من المكالمة على عدد المؤشرات الديناميكية ومجموعة البيانات المرتبطة. الخطوة التالية هي الحصول على معلومات حول أعمدة هذه المجموعة واستخراج البيانات نفسها.

تسمح لك حزمة DMBS_SQL بوصف أعمدة المؤشر الديناميكي ، مع إرجاع المعلومات حول كل عمود في صفيف اقتران من السجلات.

للقيام بذلك ، يجب أن تعلن مجموعة PL / SQL بناءً على نوع المجموعة DBMS_SQL.DESC_TAB (أو DESC_TAB2 إذا كان الاستعلام يمكن أن يعرض أسماء الأعمدة التي تزيد عن 30 حرفًا). بعد ذلك ، يمكنك استخدام طرق التجميع للتكرار عبر الجدول واسترداد معلومات المؤشر.

 AS cols DBMS_SQL.DESC_TAB2; ncols NUMBER; --     col_val_chr VARCHAR2(32767); BEGIN DBMS_SQL.DESCRIBE_COLUMNS2(cur, ncols, cols); 

بعد ذلك ، يجب إبلاغ حزمة DBMS_SQL بنوع كل عمود محدد باستخدام الاستعلام الديناميكي. يتم ذلك عن طريق استدعاء DEFINE_COLUMN.

 FOR i IN 1 .. ncols LOOP DBMS_SQL.DEFINE_COLUMN(cur, i, col_val_chr, 32767); END LOOP; 

في الوسيطة الثانية DEFINE_COLUMN ، يتم تمرير رقم - الموضع التسلسلي للعمود في القائمة. تقوم الوسيطة الثالثة بتعيين نوع بيانات عمود المؤشر. يمر تعبير من النوع المناسب. بمعنى آخر ، يتم تمرير DBMS_SQL.DEFINE_COLUMN ليس سلسلة باسم اسم النوع (على سبيل المثال ، "VARCHAR2") ، ولكن متغير تم تعريفه بالنوع VARCHAR2.

عند تحديد عمود نوع الحرف في الوسيطة الرابعة ، يجب عليك تحديد الحد الأقصى لطول القيم التي يمكن تحميلها في المؤشر.

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

 AS cap CLOB; --    CSV- BEGIN DBMS_LOB.CREATETEMPORARY(cap, TRUE, DBMS_LOB.SESSION); FOR i IN 1 .. ncols LOOP DBMS_LOB.APPEND(cap, cols(i).col_name || delimeter); END LOOP; 

يتم استرداد البيانات صفًا تلو الآخر باستخدام DBMS_SQL.FETCH_ROWS والمكالمات اللاحقة إلى DBMS_SQL.COLUMN_VALUE للحصول على قيم الأعمدة الفردية.

 AS bod CLOB; --   CSV- c_line_break CONSTANT VARCHAR2(2) := chr(13) || chr(10); BEGIN DBMS_LOB.CREATETEMPORARY(bod, TRUE, DBMS_LOB.SESSION); WHILE DBMS_SQL.FETCH_ROWS(ur) > 0 LOOP FOR i IN 1 .. ncols LOOP DBMS_SQL.COLUMN_VALUE(cur, i, col_val_chr); DBMS_LOB.APPEND(bod, col_val_chr || delimeter); END LOOP; DBMS_LOB.APPEND(bod, c_line_break); END LOOP; 

ثم يبقى فقط لجمع CSV الناتجة

 DBMS_LOB.APPEND(sheet, cap); DBMS_LOB.APPEND(sheet, c_line_break); DBMS_LOB.APPEND(sheet, bod); 

معالجة الأخطاء

 EXCEPTION WHEN OTHERS THEN sheet := NULL; 

وأغلق المؤشر

 DBMS_SQL.CLOSE_CURSOR(cur); 

حزمة استخدام الخيارات

 DECLARE csv CLOB; BEGIN DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet('SELECT empcode, fio FROM employee WHERE ROWNUM < 10', csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END; 

 DECLARE csv CLOB; cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT empcode, fio FROM employee WHERE ROWNUM < 10; DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet(cur, csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END; 

هذا ، في الواقع ، هو كل شيء ، يتم إرفاق التعليمات البرمجية المصدر .

ساعد الكتاب في التطوير
Feuerstein S. ، وصل B. - Oracle PL / SQL. للمحترفين.