These days have a need to batch update operation requirements, let me also very headache, what a great god to help me, is like this:

 
Update user u set u.u sername='flowers' where u.i d=' 1 '; 
Update user u set u.u sername='Ming' where u.i d='2'. 
Update user u set u.u sername='xiao li' where u.i d='3'. 
Update user u set u.u sername='wang' where u.i d='4; 
. 

Such operations,
At present my solution is

 
The begin 
Update user u set u.u sername='flowers' where u.i d=' 1 '; 
Update user u set u.u sername='Ming' where u.i d='2'. 
Update user u set u.u sername='xiao li' where u.i d='3'. 
Update user u set u.u sername='wang' where u.i d='4; 
. ; 
The end; 

But there are a lot of question, efficiency doesn't know how to say, but do not return can affect the number of rows, do not know to have better SQL statements, oracle database,


If familiar with ibatis great god best help, think of ibatis inside should be how to write, I now is like this:

 
The begin 
Update user u set u.u sername=# [] username# where u.i d=# [] id#; 
 
The end; 
 

So can bulk changes, but the return value is always 1, and also don't know how to efficiency, efficiency must be considered because is the batch problem

CodePudding user response:

If we can use the stored procedure, the value of Name, ID | Name, ID , stored procedure for loop, record is changed

CodePudding user response:

reference 1st floor lovelj2012 response:

if we can use the stored procedure, the value of Name, ID Name, ID , record stored procedures for loop, modify the article success number

Stored procedure is how to implement, probably give a code fragment, and how to return to affect the number of rows, trouble a little specific

CodePudding user response:

refer to the second floor Java_zero response:

Quote: refer to 1st floor lovelj2012 response:

If we can use the stored procedure, the value of Name, ID Name, ID , record stored procedures for loop, modify the article success number

Stored procedure is how to implement, probably give a code fragment, and how to return to affect the number of rows, slightly more specific

1, baidu find a split function of Oracle implementation (Oracle didn't own split function, are realized through a custom function)
2, the for loop iterates through the split function, and then by number intercept, respectively to read every line of the Name and ID to the
3, perform the update operation, through the exception or SQL % rowcount determine whether implementation success

CodePudding user response:

Declare
I number;
J number;
The begin
I:=1;
J:=0;
Select count (*) into j from T1;
Loop
Exit the when I & gt; j;
The update T1 set T1. Subobject_Name=(. Select T2 Subobject_Name from T2 where T1. ID=T2. ID)
Where a T1. ID & gt;=I and T1. ID & lt;=(I + 1000);
I:=I + 1000;
commit;
end loop;
The end;

Don't know what's the matter with your original data, so write words, the original data need to save to a table, if you need to ensure that the data completely correctly inserted into the table, you need to add the exception module, how to determine if there is an error

CodePudding user response:

A, the use of union all
Insert into test (a, b)
Select 'a1', 'b1 from dual
Union all
Select 'a2', 'b2 from dual
;

Second, use the insert all insert into multiple data
Insert all into test (a, b) values (' a1 ', 'b1)
Into the test (a, b) values (' b1 ', 'b2)
Select 1 from dual;

CodePudding user response:

Parameter values in a bind variables to preach, should be faster