MySQL stored procedure, descendant string array

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.