MySQL Crash Course #16 # Chapter 24. Using Cursors + MySQL loop

MySQL Midstream use case Study (note) This is quite straightforward understood.

Index:

1. Cursor Basics Explained
2. MySQL Loop
3. The integration code in the book

Cursor Basics Explained

The cursor is a bit similar to ResultSet in JDBC, allowing us to FETCH data one line after the SELECT is executed.

It can only be used in stored procedures! If the stored procedure is likened to a function, the cursor can only be defined, opened, and closed within the body of the function (inside the stored procedure), and once the stored procedure is executed, it will no longer exist (you can interpret the cursor as a local variable).

Defines a cursor that does not execute a SELECT statement at this time:

CREATE PROCEDURE processorders () BEGIN    DECLARECURSORfor      SELECT from orders;  END;

Open the Cursor,select statement is executed at this time, the data is cached into the cursor, after which you can FETCH the data:

OPEN ordernumbers;

Close the cursor, releasing all memory and resources occupied by the cursor, and of course, the cursor will automatically close at the end of the stored procedure (execution to end), even if it is not manually closed:

CLOSE ordernumbers;

Next is the fetch data, each fetch is the next line! Can be combined with the above several knowledge points to do a small experiment, the above is the experimental table:

DROP PROCEDURE IF EXISTSHello;delimiter//CREATE PROCEDUREHello ()BEGIN   --declaring local variables for temporary storage of FETCH results   DECLAREOINT; --declaring Cursors   DECLAREOrdernumbersCURSOR    for   SELECTOrder_num fromorders; --Open Cursors (execute Select, cache data)   OPENordernumbers; --Take out the first order number and put it in O, and print   FETCHOrdernumbers intoo; SELECTo; --Take out the second order number and put it in O, and print   FETCHOrdernumbers intoo; SELECTo; --Close cursors (frees memory, resources)   CLOSEordernumbers; --Reopen cursor (re-execute Select, cache data)   OPENordernumbers; --Take out the first order number and put it in O, and print   FETCHOrdernumbers intoo; SELECTO--As you can guess, it's still the first order number.END //DELIMITER; Call Hello ();

MySQL Loop

The cycle of MySQL itself is actually quite simple, but the circulation in the book is a bit special.

Basic Loop Example:

While↓

DROP PROCEDURE IF EXISTSHello;delimiter//CREATE PROCEDUREHello ()BEGIN    DECLAREV1INT DEFAULT 5;  whileV1> 0 DoSETV1=V1- 1; SELECTv1; END  while;END //DELIMITER; Call Hello ();

Repeat↓

DROP PROCEDURE IF EXISTSHello;delimiter//CREATE PROCEDUREHello ()BEGIN    DECLAREV1INT DEFAULT 5; REPEATSETV1=V1- 1; SELECTv1; UNTIL v1= 0 ENDREPEAT;END //DELIMITER; Call Hello ();

Loop↓

DROP PROCEDURE IF EXISTSHello;delimiter//CREATE PROCEDUREHello ()BEGIN    DECLAREV1INT DEFAULT 5; Label1:loopSETV1=V1- 1; SELECTv1; IFV1> 0  Theniterate Label1;--The continuation cycle is equivalent to continue    END IF; LEAVE Label1; --equivalent to break        ENDLOOP;END //DELIMITER; Call Hello ();

PS. In order to ensure that you can directly COPY to the command line execution, the above code indentation a bit of a problem ...

Examples of loops on a book:

CREATE PROCEDUREprocessorders ()BEGIN   --Declare Local Variables   DECLAREDone BOOLEANDEFAULT 0; DECLAREOINT; --Declare the cursor   DECLAREOrdernumbersCURSOR    for   SELECTOrder_num fromorders; --Declare Continue handler   DECLARE CONTINUEHANDLER forSQLSTATE'02000' SETDone=1; --Open the cursor   OPENordernumbers; --Loop through all rowsREPEAT--Get Order number      FETCHOrdernumbers intoo; --End of LoopUNTIL DoneENDREPEAT; --Close the cursor   CLOSEordernumbers;END;

The first thing to do is to read this sentence.

DECLARE CONTINUE HANDLER for SQLSTATE ' 02000 ' SET done=1;

This means that when the SQLSTATE ' 02000 ' is present, the SET done=1 is executed.

and SQLSTATE ' 02000 ' is equivalent to not found, which occurs when the data is not fetch, so when the data is not taken, done becomes true and ends the loop ...

The integration code in the book

CREATE PROCEDUREprocessorders ()BEGIN   --Declare Local Variables   DECLAREDone BOOLEANDEFAULT 0; DECLAREOINT; DECLARETDECIMAL(8,2); --Declare the cursor   DECLAREOrdernumbersCURSOR    for   SELECTOrder_num fromorders; --Declare Continue handler   DECLARE CONTINUEHANDLER forSQLSTATE'02000' SETDone=1; --Create a table to store the results   CREATE TABLE IF  not EXISTSOrderTotals (Order_numINT, totalDECIMAL(8,2)); --Open the cursor   OPENordernumbers; --Loop through all rowsREPEAT--Get Order number      FETCHOrdernumbers intoo; --Get The total for this orderCall OrderTotal (O,1, T); --Insert order and total into ordertotals      INSERT  intoordertotals (Order_num, total)VALUES(o, t); --End of LoopUNTIL DoneENDREPEAT; --Close the cursor   CLOSEordernumbers;END;

Who's going to do all this work? Is it a top-level application or a database? Pending Update:

MySQL Crash Course #16 # Chapter 24. Using Cursors + MySQL loop