Hancurkan garis dengan pemisah. Sedikit tentang CONNECT BY

Halo

Saya bekerja sebagai pengembang PL / SQL. Ada tugas untuk mengumpulkan beberapa data untuk metrik untuk melacak beban sistem. Ada beberapa fungsi yang disebut dengan parameter yang terdiri dari daftar ID.

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

Tugasnya adalah sebagai berikut. Adalah perlu untuk memecah baris tersebut menjadi elemen-elemen dan menuliskannya ke dalam koleksi integer.

Mari kita mulai.

Pertama, Anda perlu data untuk bekerja. Mari kita menulis fungsi yang menghasilkan string dengan angka yang dipisahkan oleh koma. Kami akan memberikan argumen integer N ke fungsi - jumlah angka dalam urutan.

Kami tidak akan pintar, kami akan melakukan urutan dengan tipe VARCHAR2, bukan CLOB. Selanjutnya, saya akan menjelaskan mengapa VARCHAR2.

Kode fungsi untuk menghasilkan urutan:

 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; 

Mari kita kembali ke tugas kita.

Hal pertama yang terlintas dalam pikiran adalah untuk loop melalui garis, mengurangi panjang garis di setiap iterasi. Karena, sesuai dengan kondisi masalah, hasilnya harus ditempatkan dalam koleksi, kami akan membuat koleksi yang sesuai.

 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; 

Hasil:
0
1
2
…
421
422
423
…

Fungsi createNumber () mengambil argumen v_N = 1000. Dalam fungsi createNumber (), Anda dapat melihat penanganan overflow dari variabel v_str. Dengan perhitungan sederhana, Anda dapat mengetahui bahwa 4000 byte cukup untuk 1021 angka. 1000 kami cocok dengan ukuran ini tanpa masalah.

Seperti yang Anda lihat, hasilnya adalah yang dibutuhkan. String terpecah.

Meskipun Oracle tidak memiliki fungsi built-in split (), seperti Java atau Python, opsi ini tidak cocok untuk saya, karena saya percaya bahwa terlalu banyak kode yang ditulis untuk tugas sederhana seperti memisahkan string.

Pada tahap ini, saya pikir, apakah mungkin untuk membagi string hanya menggunakan SQL? Maksud saya bukan SQL klasik, tetapi SQL yang ditawarkan Oracle.

Saya ingat desain untuk membangun hierarkis CONNECT BY query.



Pernyataan START WITH opsional memberi tahu Oracle tempat memulai loop, mis. baris mana yang akan menjadi root. Kondisinya bisa apa saja. Kondisi setelah CONNECT BY harus ditentukan. Di sini perlu untuk memberi tahu Oracle berapa lama untuk melanjutkan siklus.

Dapat dilihat bahwa satu-satunya syarat penting untuk membangun kueri hierarkis adalah operator CONNECT BY, sisanya "dirangkai" sesuai kebutuhan.

Juga, konstruksi ini memiliki tingkat pseudo-kolom, yang mengembalikan tingkat bersarang pada iterasi saat ini.

Pada pandangan pertama, mungkin terlihat bahwa konstruksi ini tidak cocok untuk memutus garis. Ini tidak sepenuhnya benar. Jika kondisi diatur dengan benar, maka traversal rekursif dapat diubah menjadi siklus, seperti pada saat atau untuk loop.

Sebelum menulis kueri, pertimbangkan algoritma garis-traversal. Mulai dari awal baris, perlu untuk memotong sejumlah karakter, menjadi karakter pemisah. Di atas, saya menulis tentang level pseudo-kolom. Kami akan menggunakannya sebagai nomor iterasi saat ini.
Ternyata sesuatu seperti ini:

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

Tetapi jika Anda melihat lebih dekat, Anda dapat melihat bahwa algoritma ini tidak akan bekerja pada iterasi pertama, karena argumen ketiga ke fungsi INSTR () tidak boleh 0.

Oleh karena itu, kami menambahkan kondisi kecil menggunakan fungsi 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; 

Sekarang iterasi pertama akan bekerja dengan benar.

Sudah waktunya untuk menerapkan CONNECT BY. Ditambah lagi, kami mengambil baris kami.

 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; 

Saya sudah menulis bahwa, di bawah kondisi yang tepat, CONNECT BY dapat berperilaku seperti loop. Kondisi terpenuhi sampai fungsi INSTR () dapat menemukan posisi ke-n dari karakter pembatas, di mana n adalah jumlah iterasi saat ini, dan seperti yang kita ingat, level pseudo-kolom bertanggung jawab atas nomor iterasi.

Tampaknya masalah terpecahkan? Tidak.

Kode mungkin berfungsi, tetapi keterbacaannya nol. Saya sudah berpikir untuk kembali ke opsi loop, tetapi saya menemukan cara untuk meningkatkan opsi CONNECT BY.

Oracle memiliki alat yang sangat kuat sebagai ekspresi reguler. Secara khusus, fungsi regexp_instr () dan regexp_substr ().

regexp_instr(_, [, _ [, ] ]) - fungsi mengembalikan posisi karakter pada awal atau akhir pertandingan untuk pola, serta INSTR analognya ().

regexp_substr(_, [, [, ]]) - fungsi mengembalikan substring yang cocok dengan pola.

Tulis ulang kueri menggunakan ekspresi reguler:

 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; 

Kode membaca jauh lebih baik daripada pada contoh sebelumnya. Opsi ini cocok untuk saya.

Pada akhirnya, akan lebih logis untuk membandingkan perbandingan parsing runtime untuk ketiga opsi. Di atas, saya berjanji untuk menjelaskan mengapa alih-alih tipe CLOB kita akan menggunakan tipe VARCHAR2. Ini hanya untuk membandingkan runtime. Karena Oracle menangani tipe CLOB secara berbeda dari VARCHAR2, yang dapat mendistorsi hasil.


Hasilnya ditabulasikan:

Opsi Istirahat / Jumlah Angka	10	100	1000
Siklus	0,05 ms	0,15 ms	1,52 ms
HUBUNGI OLEH	0,18 ms	0,68 ms	18.1ms
TERHUBUNG DENGAN + ekspresi reguler	0,25 ms	12.1 ms	1s 137ms

Jujur saja, hasilnya tidak terduga untuk saya. Saya berasumsi bahwa opsi ketiga adalah yang tercepat. Nah, itu akan menjadi pelajaran.

Terima kasih sudah membaca!

Daftar sumber yang digunakan:

1. Pertanyaan hierarkis (rekursif)