【问题】遇到逗号分隔的字符串转为嵌套表，
【分析】
ORACLE提供了类似的过程来实现这个功能
| dbms_utility.comma_to_table( list IN VARCHAR2, tablen OUT BINARY_INTEGER, tab OUT lname_array); |
| CREATETABLEc2t_test ( readlineVARCHAR2(200)); INSERTINTOc2t_testVALUES('"1","Mainframe","31-DEC-2001"'); VALUES('"2","MPP","01-JAN-2002"'); VALUES('"3","Mid-Size","02-FEB-2003"'); VALUES('"4","PC","03-MAR-2004"'); VALUES('"5","Macintosh","04-APR-2005"'); COMMIT; SELECT*FROMc2t_test; TABLEtest_import ( src_noNUMBER(5), src_descVARCHAR2(20), load_dateDATE); CREATEORREPLACEPROCEDUREload_c2t_test IS c_stringVARCHAR2(250); cntBINARY_INTEGER; my_tabledbms_utility.lname_array; BEGIN FORt_recIN(FROMc2t_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'); my_table(3) :=TRANSLATE(my_table(3),'A'); INTOtest_import (src_no,src_desc,load_date) VALUES (TO_NUMBER(my_table(1)),my_table(2),TO_DATE(my_table(3))); ENDLOOP; COMMIT; ENDload_c2t_test; / exec load_c2t_test; FROMtest_import; |
【结论】
comma_to_table
table_to_comma 都是对应函数 字符串与内存表互转
原文链接：https://www.f2er.com/oracle/209949.html