The use of MySQL midstream target

Cursors are often used when using MySQL recently, so use the method to make a note for future viewing. (This is just to illustrate the use of cursors and not to involve complex business logic, so the example may not be appropriate.)

First, create two tables Departmentinfo (departmental information table) and EmployeeInfo (Employee Information table)

The table structure is as follows:

Table Departmentinfo Storage Department name and department total sales

Table EmployeeInfo store Employee name, employee Department ID and employee personal sales

The initial data in the table is as follows:

Total sales of each department are 0

Employees are in 3 departments and have their own sales

Then create the stored procedure P_changesales, with the following code

CREATEDefiner= 'Root'@'localhost'PROCEDURETest. P_changesales (out ExtreturnvalueINT)TOP:BEGIN  DECLARE EXITHANDLER forSQLEXCEPTIONBEGIN    ROLLBACK; SETExtreturnvalue= 0;--Failed  END; STARTTRANSACTION; BEGIN     DECLAREIsleaveINT;--do you want to jump out of the loop, 0: Continue 1: Jump    DECLAREDepidINT;--Department ID variable    DECLARESalesvalINT;--Sales Variables    DECLARECursalesCURSOR  for SELECTDepartmentid,sales fromEmployeeInfo;--declaring Cursors    DECLARE CONTINUEHANDLER for  notFOUNDSETIsleave= 1;--an overflow is assigned a value of 1, which is the judgment of jumping out of the loop    OPENCursales;--Open Cursor    SETIsleave= 0; Cursales_loop:loopFETCHCursales intoDepid, Salesval; IFIsleave= 1  Then --End of CycleLEAVE Cursales_loop; ELSE        UPDATEDepartmentinfoSETSales=Sales+SalesvalWHEREDepartmentID=Depid;--Increase Total Department sales      END IF; ENDLOOP Cursales_loop; CLOSECursales;--Close Cursors  END; UPDATEEmployeeInfoSETSales= 0;--Employee Personal Sales 0  SETExtreturnvalue= 1;--Success  COMMIT;END

Now we execute the above stored procedure

The stored procedure executes successfully, resulting in a return value of 1. View data in a table

Total department sales calculated in table Departmentinfo

Personal sales of employees in table EmployeeInfo changed to 0

At this point, we have completed the simple application of MySQL midstream standard.

It is also important to note that the definition of a variable in a stored procedure should not be the same as the field name in our table, otherwise there will be a problem. For example, we changed all "Salesval" in the stored procedure to "sales" the same as the field name "Sales" (case insensitive). Save the changes at this time, and will not error, stored procedures can also be executed, but will be the following return value

There was a problem with our stored procedure execution, and we did a rollback to get this return value.

Below we debug the stored procedure discovery, execute to "FETCH ... Into ..., the correct value is obtained in "Depid", but the value of "sales" is null.

Therefore, it is important to note that when a stored procedure executes to the following SQL statement, an error is rolled back.