MySQL stored procedure solves the problem of array parameters and cursors cursor
The project encountered batch data processing, using hibernate to come out too slowly, modified as a stored procedure.
The first version was modified to apply cyclic-tuning stored procedures, and found that the effect was not satisfactory. It is possible to open the session frequently and close it.
The second version of the idea is that all parameters are passed to the stored procedure, the loop parameter is an array, there is no split function in the MySQL stored procedure,
The values in the array are truncated by substring, and the data can be queried by the ID value of the Intercept.
Multiple records, all loops that want to make a result set through a cursor, but
Cursor Properties
1, read-only, cannot be updated.
2, non-scrolling
3, insensitive, insensitive to server can live cannot copy its result table
The cursor must be declared before the handler is declared, and the variables and conditions must be declared before the cursor or handler is declared.
The third version of the idea is to establish two stored procedures, the first one to intercept and to tune up the second actual processed stored procedure.
The intercepted stored procedure is from the network:
drop procedure if exists pro_deletelog; --exist or delete
CREATE PROCEDURE Pro_deletelog (qualification varchar (100))--Define parameters
Begin
declare i int;
declare Start1 int;
declare Length int;
declare totallenght int;
DECLARE filed varchar (100);
DECLARE sqlstr varchar (2000);
DECLARE stmtnovelsearch varchar (2000);
Set I=1;
Set start1=1;
Set length=0;
Set Totallenght=length (qualification); --Calculate input parameter length
Select Totallenght;
While I <=totallenght do--i=1 start
--Select SUBSTRING (qualification,i,1);
if (SUBSTRING (qualification,i,1) = ', ')--note that MySQL's SUBSTRING function intercepts strings starting with 1 instead of 0, unlike Java JavaScript.
--to intercept a character from the first bit to see if it equals,
Then
Set filed=substring (qualification,start1,length);--intercept length characters from Start1
Select filed;
Call PRO_DO (filed);
Set start1=i+1;
Set length=0;
Else
Set length=length+1; --if not, the length of the Intercept plus 1
End If;
Set i=i+1;
End while;
End
--Call the Stored procedure field name to, separating attention to the last, end
Call Pro_deletelog (' Payload,backresult,dataset,effectrow,clientuser,loginuser,clientprg, ')
Reprint: http://www.cnblogs.com/bigcelestial/archive/2013/09/05/3303329.html
MySQL stored procedure, descendant string array