深圳全飞鸿

标题: oracle分割字符串后以单列多行展示 [打印本页]

---

作者: admin    时间: 2019-7-11 11:35
标题: oracle分割字符串后以单列多行展示
目前采用的方案：

1. create or replace type type_split as table of varchar2(4000);
   
2. 
   
3. create or replace function test_split(p_list VARCHAR2, p_sep VARCHAR2) return type_split
   
4.   PIPELINED IS
   
5.   l_idx  PLS_INTEGER;
   
6.   v_list VARCHAR2(32767) := p_list;
   
7. begin
   
8.   LOOP
   
9.     l_idx := instr(v_list, p_sep);
   
10.     IF l_idx > 0 THEN
    
11.       PIPE ROW(substr(v_list, 1, l_idx - 1));
    
12.       v_list := substr(v_list, l_idx + length(p_sep));
    
13.     ELSE
    
14.       PIPE ROW(v_list);
    
15.       EXIT;
    
16.     END IF;
    
17.   END LOOP;
    
18.   RETURN;
    
19. end test_split;
    

复制代码

SELECT * FROM table(test_split('P071813,P071814,P071815',','))

---

欢迎光临 深圳全飞鸿 (http://www.nagomes.com/disc/)

Powered by Discuz! X3.2