Simple MySQL stored procedure

1. Why do I learn MySQL stored procedures?

Because I did not understand the stored procedure before, did not find the bug in time, then spent one months to learn the MySQL stored procedure, the company all the stored procedures and all the tables are understood. It was awkward, however, that the interviewer asked me to write a stored procedure at an interview, since I couldn't.

2. A lot of things learned from the usual easy to forget, suggesting that you learn something when you take out from time to practice practiced hand.

Here are the stored procedures I wrote to play

1) Create two tables

CREATE TABLE ' Mtfan ' (
' ID ' bigint (a) not NULL auto_increment COMMENT ' primary key ',
' Name ' varchar DEFAULT NULL COMMENT ' name ',
' Money ' varchar DEFAULT NULL COMMENT '
' Date_1 ' date DEFAULT NULL COMMENT ' time ',
PRIMARY KEY (' id ')
)；

CREATE TABLE ' Mtfan01 ' (
' ID ' bigint (a) not NULL auto_increment COMMENT ' primary key ',
' Name ' varchar DEFAULT NULL COMMENT ' name ',
' Money ' varchar DEFAULT NULL COMMENT '
' Date_1 ' date DEFAULT NULL COMMENT ' time ',
PRIMARY KEY (' id ')
)；

2) Create a stored procedure to insert data into the Mtfan table

CREATE PROCEDURE ' Inout_param ' ()
BEGIN
declare i int default 0;
declare money int;

Loop_label:loop
Set money = Floor (+ RAND () * (1000-500));

INSERT into Mtfan (name,money,date_1) VALUES (' Xiao Ming ', Money, ' 2018-07-30 ');
INSERT into Mtfan (name,money,date_1) VALUES (' Little Red ', money, ' 2018-07-30 ');
INSERT into Mtfan (name,money,date_1) VALUES (' Piglet ', Money, ' 2018-07-30 ');
INSERT into Mtfan (name,money,date_1) VALUES (' Xiao Li ', Money, ' 2018-07-30 ');
INSERT into Mtfan (name,money,date_1) VALUES (' small country ', money, ' 2018-07-30 ');

Set i=i+1;
If I>=5 Then
Leave Loop_label;
End If;
End Loop;
END;

3) Create a cursor stored procedure to pass data from the Mtfan table to the Mtfan01 tables

CREATE PROCEDURE ' cursor_example ' ()
BEGIN
#定义函数
DECLARE cnname VARCHAR (+) DEFAULT ';
DECLARE Cnmoney INT DEFAULT 0;
DECLARE date DATETIME DEFAULT ' 2018-08-01 ';
DECLARE done INT DEFAULT 0;
#创建一个游标
DECLARE cur1 CURSOR for SELECT name,money,date_1 from mtfan WHERE date_1 = ' 2018-07-10 ';
Jump out of the loop when #设置变量 done=1
DECLARE CONTINUE HANDLER for not FOUND SET done=1;
#打开游标
OPEN Cur1;
#创建一个loop循环
Emp_loop:loop
#把游标查询得到的值传给对应的函数
FETCH cur1 into Cnname,cnmoney,date;
#满足条件终止循环
IF Done=1 Then
LEAVE Emp_loop;
#if结束
END IF;
#sql插入语句
INSERT into Mtfan01 (name,money,date_1) VALUES (cnname,cnmoney,date);
#循环结束
END LOOP Emp_loop;
#关闭游标
CLOSE Cur1;
END;

Simple MySQL stored procedure