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,

CodePudding user response:

refer to the second floor nayi_224 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') 
; 

Repeat didn't understand what you said, for example,

Such as table 1 MING has A value of 1, B, C, D four, after execution, columns are in table 3 MING value is 1 A,
This if the field values in table 2 is not fixed how to change, because the SGID is the primary key,

CodePudding user response:

reference szdl88 reply: 3/f

Quote: refer to the second floor nayi_224 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') 
; 

Repeat didn't understand what you said, for example,

Such as table 1 MING has A value of 1, B, C, D four, after execution, columns are in table 3 MING value is 1 A,
This if how change the field values in table 2 is not fixed, because the SGID is the primary key,

Look not to understand, the detailed data

CodePudding user response:

This is a result of my original stored procedure execution,
The SGID in table 2 for the table's primary key, not fixed values,

CodePudding user response:

reference 4 floor nayi_224 response:

don't understand, give specific data

Just found a mistake on the ground floor, but now can't change, you look at the upstairs, in fact, I want to achieve is based on table 1 and table 2 Ming relationship in field, the table 1 and table 2 a many-to-one relationship into a one-on-one, and then insert the results to table 3,

CodePudding user response:

refer to 6th floor szdl88 response:

Quote: refer to 4th floor nayi_224 response:

Look not to understand, give specific data

Just found a mistake on the ground floor, but now can't change, you look at the upstairs, in fact, I want to achieve is based on table 1 and table 2 Ming relationship in field, the table 1 and table 2 a many-to-one relationship into a one-on-one, and then insert the results to table 3,

See the stored procedure, can only say that you don't fully understand the SQL can do,,,

First of all, the fifth floor of the figure is the result you want?
Table3 logic specific to describe
According to your logic, now CURSOR T1_CURSOR
IS the SELECT T.M EMBER, T.M ING the FROM table_1 T; It must be combined with sorting, or even to enter the same data may also have different results,

You are now stored procedure is equivalent to the

 insert into tab3 (sgid, Ming, member) 
Select t2 sgid, t2. Ming, first_value (t1) member) over (order by t1. The rowid) 
The from tab1 t1, tab2 t2 
Where a t1. Ming=t2. Ming; 

But I don't think this is what you want