DEMO: 操作前: A: A_ID A_Type ID Name ...... ---------------------------------- 1 AA 1 zhao 1 BB 2 qian 1 BB 3 sun 1 CC 3 liB: ID Name ...... ----------------------------------- 1 ZHOU 2 WU 3 ZHENG 4 WANG操作后: A: A_ID A_Type ID Name ...... ---------------------------------- 1 AA 1 zhao 1 BB 2 WU 1 BB 3 ZHENG 1 CC 3 liB: ID Name ...... ----------------------------------- 1 ZHOU 2 qian 3 sun 4 WANG
create PROCEDURE rep(m varchar2,n varchar2) IS x v1%rowtype; y v2%rowtype; BEGIN select * into x from a where A_ID = m and A_Type = n; select * into y from b where id = x.id; delete from a where A_ID = m and A_Type = m; delete from b where id = x.id; insert into a values(m,n,y.id,y...); insert into b values(x.id,x......); EXCEPTION WHEN OTHERS THEN Null; END;
B主键怎么可能重复?不知道楼主的意思 匹配A中的A_ID 和A_TYPE到B中: ... V_SQLSTRING:='insert into B(column list) select column list from A WHERE A_ID=:A_ID AND A_TYPE=:A_TYPE'; EXECUTE IMMEDIATE V_SQLSTRING USING YOUR_A_ID,YOUR_A_TYPE; COMMIT; ...
但是主键重复,A_ID和A_Type用传入的数据,不理解?
操作前:
A:
A_ID A_Type ID Name ......
----------------------------------
1 AA 1 zhao
1 BB 2 qian
1 BB 3 sun
1 CC 3 liB:
ID Name ......
-----------------------------------
1 ZHOU
2 WU
3 ZHENG
4 WANG操作后:
A:
A_ID A_Type ID Name ......
----------------------------------
1 AA 1 zhao
1 BB 2 WU
1 BB 3 ZHENG
1 CC 3 liB:
ID Name ......
-----------------------------------
1 ZHOU
2 qian
3 sun
4 WANG
x v1%rowtype;
y v2%rowtype;
BEGIN
select * into x from a where A_ID = m and A_Type = n;
select * into y from b where id = x.id;
delete from a where A_ID = m and A_Type = m;
delete from b where id = x.id;
insert into a values(m,n,y.id,y...);
insert into b values(x.id,x......);
EXCEPTION
WHEN OTHERS THEN
Null;
END;
匹配A中的A_ID 和A_TYPE到B中:
...
V_SQLSTRING:='insert into B(column list) select column list from A WHERE A_ID=:A_ID AND A_TYPE=:A_TYPE';
EXECUTE IMMEDIATE V_SQLSTRING USING YOUR_A_ID,YOUR_A_TYPE;
COMMIT;
...