Oracle insert into-CodePudding

Write a stored procedure, oracle to add data to a table and part of the data fields from another table, there are some fields are involved in, how to write the procedure

CodePudding user response:

This can play out what flower yao...

Insert into the select yyy field 1 XXX, yyy field 2... Parameters 1, 2... The from yyy where z...

CodePudding user response:

reference 1st floor minsic78 response:

this can play out what flower yao...

Insert into the select yyy field 1 XXX, yyy field 2... Parameters 1, 2... The from yyy where z...

so wrong I asked is to use a cursor

CodePudding user response:

refer to the second floor qq_36943132 response:

Quote: refer to 1st floor minsic78 response:

This can play out what flower yao...

Insert into the select yyy field 1 XXX, yyy field 2... Parameters 1, 2... The from yyy where z...

so wrong I asked is to use a cursor

Insert like two: one is the front, one can insert into XXX values (... ); Do ~

CodePudding user response:

Declare
TYPE dq_row IS TABLE OF dq_tmbh % ROWTYPE;
Data1 dq_row;

Cursor dq_cs is
Select the id as TMBH from dqzb_20170810;

The begin
The open dq_cs;
Loop
The fetch dq_cs bulk collect into data1 limit 1000;
If data1. COUNT & gt; 0 then
Forall I in data1. First.. Data1. Last
Insert into dq_tmbh values data1 (I);
Commit;
end if;
Exit the when dq_cs % notfound;
End loop;
end;

Give you a insert templates, corresponding change can

CodePudding user response:

You give point things out,
R if part from other table, add a cursor,

 
The create or replace pro_t1 (in_1 varchar2, in_2 varchar2) is 
The begin 
Declare 
Cursor cur_t1 is the select m1, m2 from t2 where m1=1; 
Type_t1 cur_t1 % rowtype; 
The begin 
Open cursor cur_t1 
The fetch cur_t1 into type_t1; 
Insert into t2 (p1, p2, p3, p4) values (type_t1 type_t1. M1, m2, in_1, in_2); 
The close cur_t1; 
end; 
The end;

CodePudding user response:

Can batch, bulk insert, the oil mark is low efficiency