Home > database > How to implement when two tables have a common field, insert two tables corresponding content in the
How to implement when two tables have a common field, insert two tables corresponding content in the
Time:09-18
Recently in contact with the oracle database, to achieve effect as shown in figure,
Forgot to supplement, the SGID in table 2 for the foreign key key, is the external key table 3, the other MING field constraint condition is the only value, Through the Internet information, he had to write a stored procedure + dynamic cursors way to implement, the results showed that not only the value of the new insert will only repeat insert the first column of table 1, table 2 existed before even content also repeat with synchronization, My code is as follows, the great god gives directions on how to improve, we should be grateful,
CREATE OR REPLACE PROCEDURE P_TEST IS
- 1. The cursor table_1 table data obtained
CURSOR T1_CURSOR IS the SELECT T.M EMBER, T.M ING the FROM table_1 T;
- 2. The cursor receive table_2 table data
CURSOR T2_CURSOR IS the SELECT T.S GID, T.M ING the FROM table_2 T;
The BEGIN
-- 3. Nested loop gain to deal with the data
FOR FOR_T2_CURSOR T2_CURSOR IN
LOOP
FOR FOR_T1_CURSOR T1_CURSOR IN
LOOP
4 - judgment
IF FOR_T1_CURSOR. MING=FOR_T2_CURSOR. MING
THEN
-- 5. Insert the data
INSERT INTO table_3 (MEMBER, MING, SGID)
VALUES (FOR_T1_CURSOR MEMBER, FOR_T1_CURSOR. MING, FOR_T2_CURSOR. The SGID);
END IF;
END LOOP;
END LOOP;
end of 6.END P_TEST;
CodePudding user response:
1, create a and the structure of table 3 4 of the same table, the table 3 old data replication to table 4; 2, delete table three data; 3, Insert into table 3 (SGID, MING, MEMBER) select b.S GID, B.m ing, B.m EMBER from table 1 a, table 2 b where arjun ing=B.m ing; 4, make a copy of the data in table 4 to table 3
CodePudding user response:
Stored procedure is not do such things
Throw two of the following statements in a transaction execution
insert into tab2 (sgid, Ming) values (' ab ', '1'); Insert into tab3 (sgid, Ming, member) Select t2 sgid, t2. Ming, t1) member from tab1 t1, tab2 t2 Where a t1. Ming=t2. Ming And not the exists (select * from tab3 t3 where t3. The sgid='ab' and t3. Ming='1') ;
Repetitive didn't understand you say, for instance,