Seringkali, ketika memecahkan masalah integrasi sistem, perlu untuk menyajikan sejumlah data dalam satu format atau lainnya. Pada saat yang sama, siapa pun dapat menjadi konsumen data, tetapi sumbernya hampir selalu berupa basis data perusahaan. Misalnya, produsen dapat meminta pemasok untuk melaporkan secara berkala pergerakan barang dalam format XLSX atau XML, dll.
Ada banyak alat untuk mengubah data menjadi berbagai format, dan kemungkinan penggunaannya bergantung pada tumpukan teknologi yang diadopsi di perusahaan dan arsitektur perangkat lunak. Pada saat yang sama, Anda selalu ingin rantai yang terdiri dari berbagai pustaka, kerangka kerja, lapisan sistem yang digunakan untuk mengonversi data sumber menjadi sesingkat mungkin. Ini akan mengurangi waktu yang dihabiskan untuk mengembangkan solusi dan meningkatkan efisiensi produksinya.
Jika kita mengasumsikan bahwa, pada kenyataannya, kueri SQL adalah akar dari proses pemilihan data, maka idealnya rantai transformasi akan diinginkan untuk melihat ini:
d′=f(SQL(d))
dimana
d - sumber data,
SQL(d) - Permintaan SQL untuk mengambil data,
f - fungsi yang mengubah pemilihan ke format yang diinginkan,
d′ - data dalam format yang diperlukan.
Untuk Oracle PL / SQL, ada sejumlah paket bawaan dan pihak ketiga yang mengimplementasikan fungsi ini. Ini adalah DBMS_XMLGEN, DBMS_XMLQUERY, AS_XLSX, PL / JSON dan lainnya.
Namun, ketika muncul pertanyaan tentang mengkonversi data ke format CSV, untuk beberapa alasan tidak ada solusi yang siap pakai. Saya harus melakukannya sendiri, maka akan ditunjukkan caranya.
Pernyataan masalah
Membuat alat (paket PL / SQL) yang menerima kueri SELECT sewenang-wenang sebagai string atau sebagai variabel kursor pada input, dan mengembalikan objek tipe CLOB yang mengenkapsulasi data dalam format CSV pada output. Dalam hal terjadi kesalahan, NULL harus dikembalikan. Format CSV itu sendiri tidak perlu diwakili - ini adalah string yang unsur-unsurnya dipisahkan oleh beberapa karakter, paling sering ";", tetapi dalam kasus umum karakter sewenang-wenang dapat bertindak sebagai pemisah. Asumsikan bahwa karakter 0x0D + 0x0A digunakan untuk memisahkan string. Baris pertama dalam file CSV biasanya adalah header dan mendefinisikan nama kolom.
Tentukan antarmuka paket
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;
Ada dua prosedur kelebihan beban, perbedaan di antara mereka adalah bahwa salah satu dari mereka menerima permintaan sebagai string, dan yang lainnya sebagai tautan ke kursor. Parameter kedua adalah output, ini adalah hasil yang diinginkan pada objek CLOB. Akhirnya, parameter ketiga adalah pemisah CSV.
Dalam implementasi prosedur ini, paket DBMS_SQL bawaan akan membantu kami, yang memungkinkan kami untuk bekerja dengan kursor dinamis ketika tidak diketahui sebelumnya (pada tahap kompilasi) persis berapa banyak kolom yang terlibat dalam pemilihan.
Fitur-fitur DBMS_SQL memungkinkan Anda untuk mengurai dan mengeksekusi permintaan arbitrer secara dinamis. Untuk prosedur yang menerima permintaan sebagai string, ini terjadi seperti ini:
AS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
Untuk prosedur yang mengambil variabel kursor, semuanya lebih sederhana - dimulai dengan Oracle versi ke-11, konversi "variabel kursor → nomor kursor SQL" telah tersedia.
Fungsi DBMS_SQL.TO_CURSOR_NUMBER mengubah variabel REFCURSOR (diketik dengan kuat atau lemah) menjadi nomor kursor SQL, yang kemudian dapat diteruskan ke rutinitas DBMS_SQL. Dalam hal ini, variabel kursor harus terbuka sebelum transfernya ke fungsi DBMS_SQL.TO_CURSOR_NUMBER.
cur PLS_INTEGER := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);
Dengan demikian, kedua opsi panggilan turun untuk mendapatkan
nomor kursor dinamis dan kumpulan data yang terkait. Langkah selanjutnya adalah mendapatkan informasi tentang kolom set ini dan mengekstrak data itu sendiri.
Paket DMBS_SQL memungkinkan Anda untuk mendeskripsikan kolom dari kursor dinamis, mengembalikan informasi tentang setiap kolom dalam array catatan asosiatif.
Untuk melakukan ini, Anda harus mendeklarasikan koleksi PL / SQL berdasarkan jenis koleksi DBMS_SQL.DESC_TAB (atau DESC_TAB2 jika kueri dapat mengembalikan nama kolom yang lebih panjang dari 30 karakter). Setelah itu, Anda dapat menggunakan metode pengumpulan untuk beralih di atas tabel dan mengambil informasi kursor.
AS cols DBMS_SQL.DESC_TAB2; ncols NUMBER;
Selanjutnya, paket DBMS_SQL harus diberi tahu jenis setiap kolom yang dipilih menggunakan kueri dinamis. Ini dilakukan dengan memanggil DEFINE_COLUMN.
FOR i IN 1 .. ncols LOOP DBMS_SQL.DEFINE_COLUMN(cur, i, col_val_chr, 32767); END LOOP;
Argumen kedua DEFINE_COLUMN adalah angka - posisi berurutan dari kolom dalam daftar. Argumen ketiga menetapkan tipe data kolom kursor. Melewati ekspresi dari tipe yang sesuai. Dengan kata lain, DBMS_SQL.DEFINE_COLUMN dilewatkan bukan string dengan nama tipe (katakanlah, "VARCHAR2"), tetapi variabel yang didefinisikan dengan tipe VARCHAR2.
Saat mendefinisikan kolom tipe karakter dalam argumen keempat, Anda harus menentukan panjang maksimum dari nilai-nilai yang dapat dimuat ke dalam kursor.
Operasi persiapan selesai, sekarang Anda dapat membuat baris header dan mulai mengekstraksi data.
AS cap CLOB;
Data diambil baris demi baris menggunakan DBMS_SQL.FETCH_ROWS dan panggilan selanjutnya ke DBMS_SQL.COLUMN_VALUE untuk mendapatkan nilai dari masing-masing kolom.
AS bod CLOB;
Maka tinggal mengumpulkan CSV yang dihasilkan
DBMS_LOB.APPEND(sheet, cap); DBMS_LOB.APPEND(sheet, c_line_break); DBMS_LOB.APPEND(sheet, bod);
Tangani kesalahan
EXCEPTION WHEN OTHERS THEN sheet := NULL;
Dan tutup kursor
DBMS_SQL.CLOSE_CURSOR(cur);
Opsi Penggunaan Paket
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;
Itu, pada kenyataannya, semua,
kode sumber terlampir .
Buku ini membantu dalam pengembangan
Feuerstein S., Tiba B. - Oracle PL / SQL. Untuk para profesional.