Trenger Split-funksjon som tar to parametere, streng som skal deles og skilletegn for å dele strengen og returnere en tabell med kolonnene Id og Data.and hvordan du kaller Split-funksjonen som returnerer en tabell med kolonnene Id og Data. Id-kolonnen inneholder sekvensen, og datakolonnen inneholder data fra strengen. F.eks.
SELECT*FROM Split('A,B,C,D',',')
Resultatet bør være i formatet nedenfor:
|Id | Data
-- ----
|1 | A |
|2 | B |
|3 | C |
|4 | D |
Slik kan du opprette en slik tabell:
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;
Med litt tilpasning (dvs. å erstatte ,
i [^,]
med en variabel) kan du skrive en slik funksjon som returnerer en tabell.
Det finnes flere alternativer. Se Split single comma delimited string into rows in Oracle.
Du trenger bare å legge til LEVEL i select-listen som en kolonne for å få sekvensnummeret til hver rad som returneres. Eller ROWNUM er også tilstrekkelig.
Hvis du bruker en av SQL-ene nedenfor, kan du inkludere dem i en FUNCTION.
INSTR i CONNECT BY-klausulen:
SQL> WITH DATA AS 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
ord1 ord2 ord3 ord4 ord5 ord6
6 rader valgt.
SQL>
REGEXP_SUBSTR i CONNECT BY klausul:
SQL> WITH DATA AS 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 /ord1 ord2 ord3 ord4 ord5 ord6
6 rader valgt.
SQL>
REGEXP_COUNT i CONNECT BY-klausulen:
SQL> WITH DATA AS 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 ---------------------------------------- ord1 ord2 ord3 ord4 ord5 ord66 rader valgt.
SQL>
Ved hjelp av XMLTABLE.
SQL> WITH DATA AS 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 ------------------------------------------------------------------------ ord1 ord2 ord3 ord4 ord5 ord66 rader valgt.
SQL>
Bruker MODEL-klausul:
SQL> WITH t AS 2 ( 3 SELECT 'ord1, ord2, ord3, ord4, ord5, ord6' str 4 FROM dual ) , 5 model_param AS 6 ( 7 SELECT str AS orig_str , 8 ',' 9 || str 10 || ',' AS mod_str , 11 1 AS start_pos , 12 Length(str) AS end_pos , 13 (Length(str) - Length(Replace(str, ',')))) + 1 AS element_count , 14 0 AS element_no , 15 ROWNUM AS 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 DIMENSION BY (element_no) 22 MEASURES (start_pos, end_pos, element_count) 23 RULES ITERATE (2000) 24 UNTIL (ITERATION_NUMBER+1 = element_count[0]) 25 ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1, 26 end_pos[iterasjonsnummer+1] = instr(cv(mod_str), ',', 1, cv(element_nr) + 1) ) ) 27 WHERE element_no != 0 28 ORDER BY mod_str , 29 element_no 30 /STR
ord1 ord2 ord3 ord4 ord5 ord6
6 rader valgt.
SQL>
Du kan også bruke DBMS_UTILITY-pakken fra Oracle. Den inneholder en rekke underprogrammer. Et nyttig verktøy er COMMA_TO_TABLE procedure, som konverterer en kommaseparert navneliste til en PL/SQL-tabell med navn.
Hvis du trenger en funksjon, kan du prøve denne.
Først oppretter vi en type:
CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
Field1 int
, Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/
Deretter oppretter vi funksjonen:
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;
/
Nå kan vi velge fra den:
select * from table(TEST_RETURN_TABLE());
Utdata:
SQL> select * from table(TEST_RETURN_TABLE());
FIELD1 FIELD2
---------- -------------------------
1001 A
1001 B
1001 C
1001 D
1001 E
1001 F
6 rows selected.
Du må selvsagt erstatte WITH TAB AS...
-biten med hvor du henter de faktiske dataene dine fra.
Kreditt Kreditt