注册
登录
plsql
是否有在PL / SQL中拆分字符串的功能?
返回
是否有在PL / SQL中拆分字符串的功能?
作者:
狗头军师
发布时间:
2024-01-12 03:17:10 (6月前)
我需要编写一个过程来规范一个记录,该记录具有一个字符连接的多个令牌。我需要获得这些令牌来分割字符串,并将每个令牌作为新记录插入表中。Oracle是否具有类似“拆分”功能的功能?
收藏
举报
2 条回复
1#
回复此人
狗头军师
|
2020-07-30 16-02
你必须自己动手。例如, ``` /* from :http://www.builderau.com.au/architect/database/soa/Create-functions-to-join-and-split-strings-in-Oracle/0,339024547,339129882,00.htm select split('foo,bar,zoo') from dual; select * from table(split('foo,bar,zoo')); pipelined function is SQL only (no PL/SQL !) */ create or replace type split_tbl as table of varchar2(32767); / show errors create or replace function split ( p_list varchar2, p_del varchar2 := ',' ) return split_tbl pipelined is l_idx pls_integer; l_list varchar2(32767) := p_list; l_value varchar2(32767); begin loop l_idx := instr(l_list,p_del); if l_idx > 0 then pipe row(substr(l_list,1,l_idx-1)); l_list := substr(l_list,l_idx+length(p_del)); else pipe row(l_list); exit; end if; end loop; return; end split; / show errors; /* An own implementation. */ create or replace function split2( list in varchar2, delimiter in varchar2 default ',' ) return split_tbl as splitted split_tbl := split_tbl(); i pls_integer := 0; list_ varchar2(32767) := list; begin loop i := instr(list_, delimiter); if i > 0 then splitted.extend(1); splitted(splitted.last) := substr(list_, 1, i - 1); list_ := substr(list_, i + length(delimiter)); else splitted.extend(1); splitted(splitted.last) := list_; return splitted; end if; end loop; end; / show errors declare got split_tbl; procedure print(tbl in split_tbl) as begin for i in tbl.first .. tbl.last loop dbms_output.put_line(i || ' = ' || tbl(i)); end loop; end; begin got := split2('foo,bar,zoo'); print(got); print(split2('1 2 3 4 5', ' ')); end; / ```
编辑
登录
后才能参与评论