كسر خط بفاصل. القليل عن CONNECT BY

مرحبًا

أنا أعمل كمطور PL / SQL. هناك مهمة لجمع بعض البيانات للمقاييس من أجل تتبع تحميل النظام. هناك بعض الوظائف التي يتم استدعاؤها بمعلمة تتكون من قائمة معرفات.

1,2,3,4…1001,1002,1003…

المهمة كما يلي. من الضروري تقسيم هذا الخط إلى عناصر وكتابتها في مجموعة صحيحة.

دعنا نبدأ.

تحتاج أولاً إلى بيانات للعمل. دعنا نكتب دالة تولد سلسلة بأرقام مفصولة بفواصل. سنقوم بتمرير وسيطة عدد صحيح N إلى الدالة - عدد الأرقام في التسلسل.

لن نكون أذكياء ، سنقوم بالتسلسل بالنوع VARCHAR2 ، وليس CLOB. بعد ذلك ، سأشرح لماذا VARCHAR2.

رمز الوظيفة لإنشاء التسلسل:

 CREATE OR REPLACE FUNCTION createNumbers(v_N IN PLS_INTEGER) RETURN VARCHAR2 IS v_str VARCHAR2(4000) := '0'; too_small EXCEPTION; PRAGMA EXCEPTION_INIT(too_small, -6502); BEGIN FOR i IN 1..v_N LOOP BEGIN v_str := v_str || ',' || TO_CHAR(i); EXCEPTION WHEN too_small THEN EXIT; END; END LOOP; RETURN v_str || ','; END; 

دعنا نعود إلى مهمتنا.

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

 DECLARE v_N PLS_INTEGER := 1000; v_str VARCHAR2(4000) := createNumbers(v_N => v_N); TYPE tab_number IS TABLE OF NUMBER(5); t_str tab_number := tab_number(); BEGIN WHILE v_str IS NOT NULL LOOP t_str.EXTEND; t_str(t_str.COUNT) := SUBSTR(v_str, 1, INSTR(v_str, ',', 1) - 1); v_str := SUBSTR(v_str, INSTR(v_str, ',', 1) + 1); END LOOP; FOR i IN t_str.FIRST..t_str.LAST LOOP dbms_output.put_line(t_str(i)); END LOOP; t_str.DELETE; END; 

النتيجة:
0
1
2
…
421
422
423
…

تأخذ الدالة createNumber () الوسيطة v_N = 1000. في دالة createNumber () ، يمكنك رؤية معالجة تجاوز المتغير v_str. مع حساب بسيط ، يمكنك معرفة أن 4000 بايت كافية لأرقام 1021. يناسبنا 1000 في هذا الحجم دون أي مشاكل.

كما ترون ، فإن النتيجة هي تلك التي كانت مطلوبة. تم تقسيم السلسلة.

على الرغم من أن Oracle لا تحتوي على وظيفة تقسيم () مضمنة ، كما هو الحال في Java أو Python ، فإن هذا الخيار لا يناسبني ، لأنني أعتقد أن الكثير من التعليمات البرمجية مكتوبة لمهمة بسيطة مثل تقسيم سلسلة.

في هذه المرحلة ، اعتقدت ، هل من الممكن تقسيم سلسلة باستخدام SQL فقط؟ لا أقصد لغة SQL الكلاسيكية ، ولكن لغة SQL التي توفرها Oracle.

تذكرت تصميم بناء الاستفسارات الهرمية CONNECT BY.



تخبر عبارة START WITH الاختيارية Oracle من أين تبدأ الحلقة ، أي أي صف سيكون الجذر. يمكن أن يكون الشرط تقريبا. يجب تحديد الشرط بعد CONNECT BY. هنا من الضروري إخبار أوراكل بمدى استمرار الدورة.

يمكن ملاحظة أن الشرط الوحيد المهم لبناء استعلام هرمي هو العامل CONNECT BY ، والباقي "متدرج" حسب الحاجة.

أيضًا ، يحتوي هذا البناء على مستوى عمود زائف ، والذي يعيد مستوى التداخل في التكرار الحالي.

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

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

 SELECT SUBSTR(str, INSTR(str, ',', 1, level - 1) + 1, INSTR(str, ',', 1, level) - INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

ولكن إذا نظرت عن كثب ، يمكنك أن ترى أن هذه الخوارزمية لن تعمل على التكرار الأول ، لأن الوسيطة الثالثة للدالة INSTR () لا يمكن أن تكون 0.

لذلك ، نضيف حالة صغيرة باستخدام دالة DECODE ().

 SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM DUAL; 

الآن سوف يعمل التكرار الأول بشكل صحيح.

حان الوقت لتطبيق بنية CONNECT BY. بالإضافة إلى أننا نأخذ خطنا.

 WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT SUBSTR(str, DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1), INSTR(str, ',', 1, level) - DECODE(level, 1, 1, INSTR(str, ',', 1, level - 1) + 1)) FROM TMain CONNECT BY NVL(INSTR(str, ',', 1, level), 0) <> 0; 

لقد كتبت بالفعل أنه ، تحت الحالة الصحيحة ، يمكن أن تتصرف بنية CONNECT BY مثل الحلقة. يتم استيفاء الشرط حتى تتمكن الدالة INSTR () من العثور على الموضع n لحرف المحدد ، حيث n هو رقم التكرار الحالي ، وكما نتذكر ، فإن مستوى العمود الزائف مسؤول عن رقم التكرار.

يبدو أن المشكلة تم حلها؟ لا.

قد يعمل الرمز ، ولكن قابلية قراءته تساوي صفر. كنت أفكر بالفعل في العودة إلى خيار التكرار ، لكنني اكتشفت كيفية تحسين خيار CONNECT BY.

تمتلك أوراكل أداة قوية مثل التعبيرات العادية. وبشكل خاص ، الدالتان regexp_instr () و regexp_substr ().

regexp_instr(_, [, _ [, ] ]) - تُرجع الدالة موضع الحرف في بداية أو نهاية المباراة للنمط ، بالإضافة إلى INSTR () التناظري الخاص به.

regexp_substr(_, [, [, ]]) - تُرجع الدالة سلسلة فرعية تطابق النمط.

أعد كتابة الاستعلام باستخدام التعابير العادية:

 WITH TMain AS (SELECT '100,200,300,400,500' || ',' AS str FROM DUAL) SELECT regexp_substr(str, '[^,]+', 1, level) FROM TMain CONNECT BY NVL(regexp_instr(str, '[^,]+', 1, level), 0) <> 0; 

يقرأ رمز أفضل بكثير مما كان عليه في المثال السابق. هذا الخيار يناسبني.

في النهاية ، سيكون من المنطقي مقارنة مقارنات تحليل وقت التشغيل للخيارات الثلاثة. أعلاه ، لقد وعدت بشرح سبب استخدام نوع VARCHAR2 بدلاً من نوع CLOB. هذا فقط لمقارنة وقت التشغيل. نظرًا لأن Oracle تتعامل مع نوع CLOB بشكل مختلف عن VARCHAR2 ، مما قد يؤدي إلى تشويه النتائج.


النتائج مجدولة:

خيار الاستراحة / عدد الأرقام	10	100	1000
دورة	0.05 مللي ثانية	0.15 مللي ثانية	1.52 مللي ثانية
الاتصال بـ	0.18 مللي ثانية	0.68 مللي ثانية	18.1 مللي ثانية
CONNECT BY + التعبيرات العادية	0.25 مللي ثانية	12.1 مللي ثانية	1 ثانية 137 مللي ثانية

بصراحة ، النتيجة غير متوقعة بالنسبة لي. افترضت أن الخيار الثالث سيكون الأسرع. حسنا ، هذا سيكون درسا.

شكرا للقراءة!

قائمة المصادر المستخدمة:

1. الاستعلامات الهرمية (العودية)