Oracle 创建 split 和 splitstr 函数
Sql语句最好依次执行创建 /************************************** /* 创建 split 函数 */ is if l_idx > 0 /************************************** return t_str; 示例:split(字符串,标识) select split('a,b,c,e,d,f,g') arrData from dual; 默认使用逗号分割,可以自定义修改,如:select split('X-rapido & Lemon','&') arrData from dual; 点开集合 默认使用逗号分割,可以自定义修改,如:select split('X-rapido & Lemon','&') arrData from dual; 示例:splitstr(字符串,获取的节点下标,分隔符) select splitstr('X-rapido&Lemon&Jennifer','&') word from dual; -- X-rapido 转自:https://www.cnblogs.com/soundcode/p/6145216.html 测试: 1、建立测试表: SQL> create table test(A_URS_ID varchar2(100)); Table created. SQL> create table test1(URS_ID varchar2(100),SR_NAME VARCHAR2(100)); Table created. SQL> INSERT INTO TEST VALUES('123,125'); 1 row created. SQL> INSERT INTO TEST VALUES('123,124,125'); 1 row created. SQL> INSERT INTO TEST1 VALUES('123','name1'); 1 row created. SQL> INSERT INTO TEST1 VALUES('124','name2'); 1 row created. SQL> INSERT INTO TEST1 VALUES('125','name3'); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT * FROM TEST; A_URS_ID -------------------------------------------------------------------------------- 123,125 123,125 SQL> SELECT * FROM TEST1; URS_ID -------------------------------------------------------------------------------- SR_NAME -------------------------------------------------------------------------------- 123 name1 124 name2 125 name3 2、更改字段内容: set serveroutput on; 增加字段: SQL> alter table test add subjname varchar2(4000); declare subjname varchar2(4000) := ''; a_urs_id varchar2(4000) := ''; title varchar2(4000) := ''; v_count integer :=0; sqlstr varchar2(4000) := ''; begin for i in (select title,a_urs_id from test) loop subjname := ''; a_urs_id :=''; title := i.title; a_urs_id := i.a_urs_id; select wmsys.wm_concat(dict.sr_name) into subjname from table(split(a_urs_id,')) val,test1 dict where val.COLUMN_VALUE=dict.urs_id; dbms_output.put_line(subjname); v_count := v_count + 1; sqlstr :='update test set subjname =:1 where a_urs_id =:2'; execute immediate sqlstr using subjname,a_urs_id; if v_count mod 2 = 0 then commit; end if; end loop; commit; end; / (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |