Perlu fungsi Split yang akan mengambil dua parameter, string untuk membagi dan pembatas untuk membagi string dan mengembalikan tabel dengan kolom Id dan Data. Kolom Id akan berisi urutan dan kolom data akan berisi data string. Misalnya.
SELECT*FROM Split('A,B,C,D',',')
Hasil harus dalam format di bawah ini:
|Id | Data
-- ----
|1 | A |
|2 | B |
|3 | C |
|4 | D |
Berikut ini adalah bagaimana Anda bisa membuat tabel semacam itu:
SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
FROM dual
CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;
Dengan sedikit perubahan (yaitu, mengganti ,
dalam [^,]
dengan variabel) Anda dapat menulis fungsi seperti itu untuk mengembalikan tabel.
Ada beberapa opsi. Lihat Memisahkan string tunggal yang dibatasi koma menjadi baris di Oracle
Anda hanya perlu menambahkan LEVEL dalam daftar pilih sebagai kolom, untuk mendapatkan nomor urutan ke setiap baris yang dikembalikan. Atau, ROWNUM juga sudah cukup.
Dengan menggunakan salah satu SQL di bawah ini, Anda dapat memasukkannya ke dalam FUNCTION.
INSTR dalam klausa CONNECT BY:
SQL> DENGAN DATA SEBAGAI 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 FROM DATA 6 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 7 /STR
kata1 kata2 kata3 kata4 kata5 kata6
6 baris yang dipilih.
SQL>
REGEXP_SUBSTR dalam klausa CONNECT BY:
SQL> DENGAN DATA SEBAGAI 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 FROM DATA 6 CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL 7 /STR
kata1 kata2 kata3 kata4 kata5 kata6
6 baris yang dipilih.
SQL>
REGEXP_COUNT dalam klausa CONNECT BY:
SQL> DENGAN DATA SEBAGAI 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 FROM DATA 6 CONNECT BY LEVEL <= regexp_count(str, ',')+1 7 / STR ---------------------------------------- kata1 kata2 kata3 kata4 kata5 kata66 baris yang dipilih.
SQL>
Menggunakan XMLTABLE
SQL> DENGAN DATA SEBAGAI 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(COLUMN_VALUE) str 5 FROM DATA, xmltable(('"' || REPLACE(str, ',', '","') || '"')) 6 / STR ------------------------------------------------------------------------ kata1 kata2 kata3 kata4 kata5 kata66 baris yang dipilih.
SQL>
Menggunakan klausa MODEL:
SQL> DENGAN t SEBAGAI 2 ( 3 SELECT 'word1, word2, word3, word4, word5, word6' str 4 FROM dual ) , 5 model_param AS 6 ( 7 SELECT str AS orig_str , 8 ',' 9 || str 10 || ',' AS mod_str , 11 1 SEBAGAI start_pos , 12 Length(str) AS end_pos , 13 (Length(str) - Length(Replace(str, ',')))) + 1 SEBAGAI element_count , 14 0 AS element_no , 15 ROWNUM SEBAGAI rn 16 FROM t ) 17 SELECT trim(Substr(mod_str, start_pos, end_pos-start_pos)) str 18 FROM ( 19 SELECT * 20 FROM model_param MODEL PARTITION BY (rn, orig_str, mod_str) 21 DIMENSI OLEH (element_no) 22 UKURAN (start_pos, end_pos, element_count) 23 ATURAN ITERASI (2000) 24 SAMPAI (ITERATION_NUMBER+1 = element_count[0]) 25 ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1, 26 end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) ) ) 27 WHERE element_no != 0 28 ORDER BY mod_str , 29 element_no 30 /STR
kata1 kata2 kata3 kata4 kata5 kata6
6 baris yang dipilih.
SQL>
Anda juga bisa menggunakan paket DBMS_UTILITY yang disediakan oleh Oracle. Paket ini menyediakan berbagai subprogram utilitas. Salah satu utilitas yang berguna adalah COMMA_TO_TABLE procedure, yang mengubah daftar nama yang dibatasi koma menjadi tabel nama PL/SQL.
Jika Anda memerlukan fungsi, cobalah ini.
Pertama kita akan membuat sebuah tipe:
CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
Field1 int
, Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/
Kemudian kita akan membuat fungsi:
CREATE OR REPLACE FUNCTION TEST_RETURN_TABLE
RETURN T_TABLE_COLL
IS
l_res_coll T_TABLE_COLL;
l_index number;
BEGIN
l_res_coll := T_TABLE_COLL();
FOR i IN (
WITH TAB AS
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
UNION
SELECT '1002' ID, 'D,E,F' STR FROM DUAL
UNION
SELECT '1003' ID, 'C,E,G' STR FROM DUAL
)
SELECT id,
SUBSTR(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
FROM
( SELECT ',' || STR || ',' AS STR, id FROM TAB
),
( SELECT level AS lvl FROM dual CONNECT BY level <= 100
)
WHERE lvl <= LENGTH(STR) - LENGTH(REPLACE(STR, ',')) - 1
ORDER BY ID, NAME)
LOOP
IF i.ID = 1001 THEN
l_res_coll.extend;
l_index := l_res_coll.count;
l_res_coll(l_index):= T_TABLE(i.ID, i.name);
END IF;
END LOOP;
RETURN l_res_coll;
END;
/
Sekarang kita bisa memilih darinya:
select * from table(TEST_RETURN_TABLE());
Keluaran:
SQL> select * from table(TEST_RETURN_TABLE());
FIELD1 FIELD2
---------- -------------------------
1001 A
1001 B
1001 C
1001 D
1001 E
1001 F
6 rows selected.
Tentunya Anda harus mengganti bit DENGAN TAB AS...
dengan dari mana Anda akan mendapatkan data aktual Anda.
Kredit Kredit