oracle - salida - División de cadena separada por comas en un proceso almacenado PL/SQL

procedimiento almacenado oracle select

Question

Tengo una cadena CSV 100.01,200.02,300.03 que necesito pasar a un procedimiento almacenado PL / SQL en Oracle. Dentro del proceso, necesito insertar estos valores en una columna de Número en la tabla.

Para esto, tengo un enfoque de trabajo desde aquí:

Cómo dividir mejor las cadenas de CSV en Oracle 9i

[2] Usando SQL conecta por nivel.].

Ahora, tengo otro requisito. Necesito pasar 2 cadenas CSV [igual de longitud] como entrada al proceso almacenado PL / SQL. Y necesito romper esta cadena e insertar cada valor de dos cadenas CSV en dos columnas diferentes en la tabla. ¿Podrían por favor dejarme ¿Sabes cómo hacerlo?

Ejemplo de entradas CSV: mystring varchar2 (2000): = ''0.75, 0.64, 0.56, 0.45'';

myAmount varchar2 (2000): = ''0.25, 0.5, 0.65, 0.8'';

Los valores de myString entrarían en los valores de Columna A y myAmount en la Columna B de la tabla.

¿Podrías decirme cómo lograr esto?

Gracias.

Answer 1

Aquí hay una buena solución:

FUNCTION comma_to_table(iv_raw IN VARCHAR2) RETURN dbms_utility.lname_array IS
ltab_lname dbms_utility.lname_array;
ln_len BINARY_INTEGER;
BEGIN
dbms_utility.comma_to_table(list => iv_raw
,tablen => ln_len
,tab => ltab_lname);
FOR i IN 1 .. ln_len LOOP
dbms_output.put_line(''element '' || i || '' is '' || ltab_lname(i));
END LOOP;
RETURN ltab_lname;
END;

Fuente: CSV - valores separados por comas - y PL / SQL (el enlace ya no es válido)

Answer 2

En cuanto al caso de connect by uso, este enfoque debería funcionar para usted:

select regexp_substr(''SMITH,ALLEN,WARD,JONES'',''[^,]+'', 1, level)
from dual
connect by regexp_substr(''SMITH,ALLEN,WARD,JONES'', ''[^,]+'', 1, level) is not null;

Answer 3

Esto debería hacer lo que estás buscando. Supone que tu lista siempre será solo números. Si ese no es el caso, simplemente cambie las referencias a DBMS_SQL.NUMBER_TABLE por un tipo de tabla que funcione para todos sus datos:

CREATE OR REPLACE PROCEDURE insert_from_lists(
list1_in IN VARCHAR2,
list2_in IN VARCHAR2,
delimiter_in IN VARCHAR2 := '',''
)
IS
v_tbl1 DBMS_SQL.NUMBER_TABLE;
v_tbl2 DBMS_SQL.NUMBER_TABLE;
FUNCTION list_to_tbl
(
list_in IN VARCHAR2
)
RETURN DBMS_SQL.NUMBER_TABLE
IS
v_retval DBMS_SQL.NUMBER_TABLE;
BEGIN
IF list_in is not null
THEN
/*
|| Use lengths loop through the list the correct amount of times,
|| and substr to get only the correct item for that row
*/
FOR i in 1 .. length(list_in)-length(replace(list_in,delimiter_in,''''))+1
LOOP
/*
|| Set the row = next item in the list
*/
v_retval(i) :=
substr (
delimiter_in||list_in||delimiter_in,
instr(delimiter_in||list_in||delimiter_in, delimiter_in, 1, i ) + 1,
instr (delimiter_in||list_in||delimiter_in, delimiter_in, 1, i+1) - instr (delimiter_in||list_in||delimiter_in, delimiter_in, 1, i) -1
);
END LOOP;
END IF;
RETURN v_retval;
END list_to_tbl;
BEGIN
-- Put lists into collections
v_tbl1 := list_to_tbl(list1_in);
v_tbl2 := list_to_tbl(list2_in);
IF v_tbl1.COUNT <> v_tbl2.COUNT
THEN
raise_application_error(num => -20001, msg => ''Length of lists do not match'');
END IF;
-- Bulk insert from collections
FORALL i IN INDICES OF v_tbl1
insert into tmp (a, b)
values (v_tbl1(i), v_tbl2(i));
END insert_from_lists;

Answer 4

No estoy seguro de si esto se ajusta a su versión de Oracle. En mi 10g puedo usar funciones de tabla canalizadas:

set serveroutput on
create type number_list as table of number;
-- since you want this solution
create or replace function split_csv (i_csv varchar2) return number_list pipelined
is
mystring varchar2(2000):= i_csv;
begin
for r in
( select regexp_substr(mystring,''[^,]+'',1,level) element
from dual
connect by level <= length(regexp_replace(mystring,''[^,]+'')) + 1
)
loop
--dbms_output.put_line(r.element);
pipe row(to_number(r.element, ''999999.99''));
end loop;
end;
/
insert into foo
select column_a,column_b from
(select column_value column_a, rownum rn from table(split_csv(''0.75, 0.64, 0.56, 0.45''))) a
,(select column_value column_b, rownum rn from table(split_csv(''0.25, 0.5, 0.65, 0.8''))) b
where a.rn = b.rn
;

Answer 5

Ya se han proporcionado muchas buenas soluciones. Sin embargo, si el texto se proporciona en un formato delimitado por comas simple o similar, y la velocidad es importante, entonces tengo para usted una solución con una función TABLE (en PL / SQL). También proporcioné un resumen de algunas otras soluciones.

Por favor, consulte más en Blog Entry on Parsing CSV en varias columnas .

Answer 6

Yo uso apex_util.string_to_table para analizar cadenas, pero puedes usar un analizador diferente si lo deseas. Luego puede insertar los datos como en este ejemplo:

declare
myString varchar2(2000) :=''0.75, 0.64, 0.56, 0.45'';
myAmount varchar2(2000) :=''0.25, 0.5, 0.65, 0.8'';
v_array1 apex_application_global.vc_arr2;
v_array2 apex_application_global.vc_arr2;
begin
v_array1 := apex_util.string_to_table(myString, '', '');
v_array2 := apex_util.string_to_table(myAmount, '', '');
forall i in 1..v_array1.count
insert into mytable (a, b) values (v_array1(i), v_array2(i));
end;

Apex_util está disponible desde Oracle 10G en adelante. Antes de esto, se llamaba htmldb_util y no se instalaba de manera predeterminada. Si no puede usar eso, podría usar el analizador de cadenas que escribí hace muchos años y lo publiqué here .

Answer 7

crear o reemplazar el procedure pro_ss(v_str varchar2) como

v_str1 varchar2(100);
v_comma_pos number := 0;
v_start_pos number := 1;
begin
loop
v_comma_pos := instr(v_str,'','',v_start_pos);
if v_comma_pos = 0 then
v_str1 := substr(v_str,v_start_pos);
dbms_output.put_line(v_str1);
exit;
end if;
v_str1 := substr(v_str,v_start_pos,(v_comma_pos - v_start_pos));
dbms_output.put_line(v_str1);
v_start_pos := v_comma_pos + 1;
end loop;
end;
/
call pro_ss(''aa,bb,cc,dd,ee,ff,gg,hh,ii,jj'');

outout: aa bb cc dd ee ff gg hh ii jj

Answer 8

CREATE OR REPLACE PROCEDURE insert_into (
p_errcode OUT NUMBER,
p_errmesg OUT VARCHAR2,
p_rowsaffected OUT INTEGER
)
AS
v_param0 VARCHAR2 (30) := ''0.25,2.25,33.689, abc, 99'';
v_param1 VARCHAR2 (30) := ''2.65,66.32, abc-def, 21.5'';
BEGIN
FOR i IN (SELECT COLUMN_VALUE
FROM TABLE (SPLIT (v_param0, '','')))
LOOP
INSERT INTO tempo
(col1
)
VALUES (i.COLUMN_VALUE
);
END LOOP;
FOR i IN (SELECT COLUMN_VALUE
FROM TABLE (SPLIT (v_param1, '','')))
LOOP
INSERT INTO tempo
(col2
)
VALUES (i.COLUMN_VALUE
);
END LOOP;
END;