oracle - tutorial - solidity en español

Función dividida en Oracle a valores separados por comas con secuencia automática

Question

Necesita la función Split que tomará dos parámetros, string para dividir y delimitador para dividir la cadena y devolver una tabla con columnas Id y Data. Y cómo llamar a la función Split que devolverá una tabla con columnas Id y Data. La columna de identificación contendrá la secuencia y la columna de datos contendrá los datos de la cadena. P.ej.

SELECT*FROM Split(''A,B,C,D'','','')

El resultado debe estar en el siguiente formato:

|Id | Data
-- ----
|1 | A |
|2 | B |
|3 | C |
|4 | D |

Answer 1

Así es como podría crear una tabla de este tipo:

 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;

Con un poco de ajustes (es decir, reemplazando , en [^,] con una variable), podría escribir una función para devolver una tabla.

Answer 2

Esta función devuelve la enésima parte de la cadena de entrada MYSTRING. El segundo parámetro de entrada es el separador, es decir, SEPARATOR_OF_SUBSTR y el tercer parámetro es la enésima parte que se requiere.

Nota: MYSTRING debería terminar con el separador.

create or replace FUNCTION PK_GET_NTH_PART(MYSTRING VARCHAR2,SEPARATOR_OF_SUBSTR VARCHAR2,NTH_PART NUMBER)
RETURN VARCHAR2
IS
NTH_SUBSTR VARCHAR2(500);
POS1 NUMBER(4);
POS2 NUMBER(4);
BEGIN
IF NTH_PART=1 THEN
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, 1) INTO POS1 FROM DUAL;
SELECT SUBSTR(MYSTRING,0,POS1-1) INTO NTH_SUBSTR FROM DUAL;
ELSE
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART-1) INTO POS1 FROM DUAL;
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART) INTO POS2 FROM DUAL;
SELECT SUBSTR(MYSTRING,POS1+1,(POS2-POS1-1)) INTO NTH_SUBSTR FROM DUAL;
END IF;
RETURN NTH_SUBSTR;
END;

Espero que esto ayude a algún cuerpo, puede usar esta función como esta en un bucle para separar todos los valores:

SELECT REGEXP_COUNT(MYSTRING, ''~'', 1, ''i'') INTO NO_OF_RECORDS FROM DUAL;
WHILE NO_OF_RECORDS>0
LOOP
PK_RECORD :=PK_GET_NTH_PART(MYSTRING,''~'',NO_OF_RECORDS);
-- do some thing
NO_OF_RECORDS :=NO_OF_RECORDS-1;
END LOOP;

Aquí NO_OF_RECORDS, PK_RECORD son variables temporales.

Espero que esto ayude.

Answer 3

Hay múltiples opciones Consulte División de una cadena delimitada por comas en filas en Oracle

Solo necesita agregar LEVEL en la lista de selección como una columna, para obtener el número de secuencia para cada fila devuelta. O, ROWNUM también sería suficiente.

Usando cualquiera de los siguientes SQL, puede incluirlos en una FUNCIÓN .

INSTR en la cláusula CONNECT BY :

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 ---------------------------------------- word1 word2 word3 word4 word5 word6 6 rows selected. SQL>

REGEXP_SUBSTR en la cláusula CONNECT BY :

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 / STR ---------------------------------------- word1 word2 word3 word4 word5 word6 6 rows selected. SQL>

REGEXP_COUNT en la cláusula CONNECT BY :

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

Usando 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 ------------------------------------------------------------------------ word1 word2 word3 word4 word5 word6 6 rows selected. SQL>

Usando la cláusula MODELO :

SQL> WITH t AS 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 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[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 ------------------------------------------ word1 word2 word3 word4 word5 word6 6 rows selected. SQL>

También puede usar el paquete DBMS_UTILITY proporcionado por Oracle. Proporciona varios subprogramas de utilidad. Una de estas utilidades útiles es el procedimiento COMMA_TO_TABLE , que convierte una lista de nombres delimitada por comas en una tabla de nombres PL / SQL.

Leer DBMS_UTILITY.COMMA_TO_TABLE

Answer 4

Intenta como a continuación

select
split.field(column_name,1,'','',''"'') name1,
split.field(column_name,2,'','',''"'') name2
from table_name

Answer 5

Si necesita una función intente esto.
Primero crearemos un tipo:

CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
Field1 int
, Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/

Luego crearemos la función:

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;
/

Ahora podemos seleccionar de él:

select * from table(TEST_RETURN_TABLE()); 

Salida:

SQL> select * from table(TEST_RETURN_TABLE());
FIELD1 FIELD2
---------- -------------------------
1001 A
1001 B
1001 C
1001 D
1001 E
1001 F
6 rows selected.

Obviamente, necesitaría reemplazar el bit WITH TAB AS... donde obtendría sus datos reales. Credit Credit

Answer 6

Use esta función ''Split'':

CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sys_refcursor is
v_res sys_refcursor;
begin
open v_res for
WITH TAB AS
(SELECT p_str STR FROM DUAL)
select substr(STR, instr(STR, '','', 1, lvl) + 1, instr(STR, '','', 1, lvl + 1) - instr(STR, '','', 1, lvl) - 1) name
from
( select '','' || STR || '','' as STR from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, '','')) - 1;
return v_res;
end;

No puede usar esta función en la declaración de selección como describió en la pregunta, pero espero que la encuentre útil.

EDITAR: Aquí hay pasos que debes seguir. 1. Crear objeto: crear o reemplazar el tipo empy_type como objeto (valor varchar2 (512)) 2. Crear tipo: crear o reemplazar el tipo t_empty_type como tabla de empy_type 3. Crear función:

CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sms.t_empty_type is
v_emptype t_empty_type := t_empty_type();
v_cnt number := 0;
v_res sys_refcursor;
v_value nvarchar2(128);
begin
open v_res for
WITH TAB AS
(SELECT p_str STR FROM DUAL)
select substr(STR, instr(STR, '','', 1, lvl) + 1, instr(STR, '','', 1, lvl + 1) - instr(STR, '','', 1, lvl) - 1) name
from
( select '','' || STR || '','' as STR from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, '','')) - 1;
loop
fetch v_res into v_value;
exit when v_res%NOTFOUND;
v_emptype.extend;
v_cnt := v_cnt + 1;
v_emptype(v_cnt) := empty_type(v_value);
end loop;
close v_res;
return v_emptype;
end;

Entonces solo llame así:

SELECT * FROM (TABLE(split(''a,b,c,d,g''))) 

Answer 7

Configuración de Oracle :

CREATE OR REPLACE FUNCTION split_String(
i_str IN VARCHAR2,
i_delim IN VARCHAR2 DEFAULT '',''
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
p_result SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/

Consulta

SELECT ROWNUM AS ID,
COLUMN_VALUE AS Data
FROM TABLE( split_String( ''A,B,C,D'' ) );

Salida :

ID DATA
-- ----
1 A
2 B
3 C
4 D