【问题】遇到逗号分隔的字符串转为嵌套表,
如果采用自定义的方法,需要写个循环,比较麻烦
【分析】
ORACLE提供了类似的过程来实现这个功能
dbms_utility.comma_to_table( list IN VARCHAR2, tablen OUT BINARY_INTEGER, tab OUT lname_array); |
CREATE TABLE c2t_test ( readline VARCHAR2(200)); INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"'); INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"'); INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"'); INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"'); INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"'); COMMIT; SELECT * FROM c2t_test; CREATE TABLE test_import ( src_no NUMBER(5), src_desc VARCHAR2(20), load_date DATE); CREATE OR REPLACE PROCEDURE load_c2t_test IS c_string VARCHAR2(250); cnt BINARY_INTEGER; my_table dbms_utility.lname_array; BEGIN FOR t_rec IN (SELECT * FROM c2t_test) LOOP dbms_utility.comma_to_table(t_rec.readline, cnt, my_table); my_table(1) := TRANSLATE(my_table(1), 'A"','A'); my_table(2) := TRANSLATE(my_table(2), 'A"','A'); my_table(3) := TRANSLATE(my_table(3), 'A"','A'); INSERT INTO test_import (src_no, src_desc, load_date) VALUES (TO_NUMBER(my_table(1)), my_table(2),TO_DATE(my_table(3))); END LOOP; COMMIT; END load_c2t_test; / exec load_c2t_test; SELECT * FROM test_import; |
【结论】
comma_to_table
table_to_comma 都是对应函数 字符串与内存表互转