我想将数据从表1整理到表2
表格1
id grp_name
-----------------------------
1 A@erf,[email protected]
2 [email protected],[email protected]/[email protected]
3 [email protected],[email protected],[email protected]
4 [email protected]#[email protected]
5 [email protected]
表2
code name
-----------------------------------
1 [email protected],[email protected]
2 [email protected]
3 [email protected]
4 [email protected]
5 [email protected]
6 [email protected]
我想将grp_name数据与名称数据(例如,使用逗号(',')concanate)连接起来,而grp_name数据具有不需要的符号,例如,“#”,“ /”,我也希望消除它们。
我创建了以下过程,但我不知道我是否有效。如果仅使用简单的update语句或merge语句是可能的,请告诉我。
异常结果
code name
1 [email protected],[email protected],A@erf,[email protected]
2 [email protected],[email protected],[email protected],[email protected]
3 [email protected],[email protected],[email protected],[email protected]
4 [email protected],[email protected],[email protected]
5 [email protected],[email protected]
6 [email protected]
CREATE OR REPLACE PROCEDURE procedure1
AS
CURSOR cur
IS
SELECT id, grp_name
FROM TABLE 1;
CURSOR cur2
IS
SELECT code, name
FROM TABLE 2;
v_a VARCHAR2(300);
v_b VARCHAR2(25);
v_c VARCHAR2(4000);
v_d VARCHAR2(250);
BEGIN
FOR i IN cur
LOOP
v_a := ','||i.grp_name;
v_b := i.id;
FOR e IN cur2
LOOP
v_c := e.name || v_a ;
v_d := i.code;
UPDATE schema_name.TABLE 2
SET name = v_c
WHERE v_d = v_b;
END LOOP;
END LOOP;
-- COMMIT;
END;
在最简单的情况下,如@jarlh所说,您可以进行更新:
update table2
set name = name
|| (select ',' || regexp_replace(grp_name, '[/#]', ',') -- replace / and # with ,
from table1
where table1.id = table2.code)
where code in (select id from table1); -- only update matching rows
最后一行不是严格必要的,但是最好避免不必要的更新。
如果在TABLE1中还有要添加的表2中不匹配的行,请使用插入:
insert into table2 (code, name)
select id, regexp_replace(grp_name, '[/#]', ',')
from table1
where id not in (select code from table2);
编辑:就像Gordon提到的那样,如果您这样存储数据,则使用SQL会更轻松:
code name
1 [email protected]
1 [email protected]
1 A@erf
1 [email protected]
2 [email protected]
2 [email protected]
2 [email protected]
2 [email protected]
3 [email protected]
3 [email protected]
3 [email protected]
3 [email protected]
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句