Mysql Stored Procedure Application

MySQLCreate a stored procedure

(1). Format

Format of MySQL stored PROCEDURE creation: create procedure process name ([process parameter [,...])
[Features...] process body

Here is an example:

1. Mysql> DELIMITER //
2. Mysql> create procedure proc1 (OUT s int)
3. -> BEGIN
4. -> Select count (*) INTO s FROM user;
5. -> END
6. -> //
7. Mysql> DELIMITER;

   Note:

   (1) Here we need to note that DELIMITER // and DELIMITER; the DELIMITER means DELIMITER, because MySQL uses ";" as the Separator by default, if we do not declare a DELIMITER, the compiler treats the stored procedure as an SQL statement, and the compilation process of the stored procedure reports an error. Therefore, we must first use the DELIMITER keyword to declare the DELIMITER of the current segment, in this way, MySQL regards ";" as the code in the stored procedure and does not execute the code. After the code is used up, the separator should be restored.

   (2) The stored procedure may have input, output, and input and output parameters as needed. Here there is an output parameter s in the type of int. If there are multiple parameters, use ", "Split.

   (3) start and END of the Process body are identified by BEGIN and END.

   In this way, a MySQL stored procedure is completed, isn't it easy? It doesn't matter if you don't understand it. Next, Let's explain it in detail.

   The following example mainly uses

   I. if-then-else statement

   I. FOUND_ROWS () Statement

   # Record the Daily walking, sleep, weight, calories consumed, and other information # In the userRecordDetail table, if the data for the current day exists, modify it. Otherwise, add the # userRecord table. If so, accumulate, otherwise add # type: 1 walking 2 sleep 3 calories consumed 4 weight # CALL userRecord_create (1000,500,500, 45,); drop procedure if exists pro_userRecord_stepNum; DELIMITER // create procedure pro_userRecord_stepNum (IN p_userId INT, IN p_stepNum INT) begin declare RCount INT; -- check whether the user has a detailed record of SELECT id FROM userRecordDetail WHERE userId = p_userId AND DATE) = CURDATE () LIMIT 1; SELECT FOUND_ROWS () INTO RCount; IF (RCount = 0) THEN -- check whether userRecord has the user's total record information. IF not, Add, otherwise, modify SELECT id FROM userRecord WHERE userId = p_userId LIMIT 1; SELECT FOUND_ROWS () INTO RCount; IF (RCount = 0) then insert into 'userrecord' ('userid ', 'totalstep', 'updatetime', 'createtime') VALUES (p_userId, p_stepNum, NOW (), NOW ()); else update userRecord SET totalStep = totalStep + p_stepNum WHERE userId = p_userId; end if; -- END -- INSERT a user record details insert into 'userrecorddetail '('weight', 'calorie ', 'stepnum', 'userid', 'sleeptimes ', 'lightsleeptimes', 'heavysleeptimes ', 'wakupnum', 'updatetime', 'createtime') VALUES (, p_stepNum, p_userId, 0, 0, 0, NOW (), NOW (); ELSE -- check whether there is a total user record information, does not exist, then add, otherwise, modify SELECT id FROM userRecord WHERE userId = p_userId LIMIT 1; SELECT FOUND_ROWS () INTO RCount; IF (RCount = 0) then insert into 'userrecord' ('userid ', 'totalstep', 'updatetime', 'createtime') VALUES (p_userId, p_stepNum, NOW (), NOW ()); else update userRecord SET totalStep = totalStep + p_stepNum WHERE userId = p_userId; end if; -- modify userRecordDetail UPDATE userRecordDetail SET stepNum = stepNum + p_stepNum WHERE userId = p_userId; end if; END; // DELIMITER; show warnings; show create procedure pro_userRecord_stepNum; CALL pro_userRecord_stepNum (1009,111 );

   To be accurate to hours, the stored procedure statement is as follows:

   # Record the Daily walking, sleep, weight, calories consumed, and other information # In the userRecordDetail table, if the data for the current day exists, modify it. Otherwise, add the # userRecord table. If so, accumulate, otherwise add # type: 1 walking 2 sleep 3 Calories consumption 4 weight # CALL userRecord_create (1000,500,500, 45,); drop procedure if exists pro_userRecord_sleep; DELIMITER // create procedure partition (IN p_userId INT, IN p_sleepTimes INT, IN p_lightSleepTimes INT, IN p_heavySleepTimes INT, IN p_wakeupNum INT) begin declare RCount INT; DECLARE resultId INT; -- check whether the user has a detailed record SELECT id INTO resultId FROM userRecordDetail WHERE userId = p_userId AND DATE_FORMAT (createTime, '% Y % m % d % H') = DATE_FORMAT (NOW (), '% Y % m % d % H') LIMIT 1; SELECT FOUND_ROWS () INTO RCount; IF (RCount = 0) THEN -- check whether userRecord has the user's total record information, does not exist, otherwise, modify SELECT id FROM userRecord WHERE userId = p_userId LIMIT 1; SELECT FOUND_ROWS () INTO RCount; IF (RCount = 0) then insert into 'userrecord '('userid ', 'totalstep', 'updatetime', 'createtime') VALUES (p_userId, p_stepNum, NOW (), NOW ()); else update userRecord SET totalStep = totalStep + p_stepNum WHERE userId = p_userId; end if; -- END -- INSERT a user record details insert into 'userrecorddetail '('weight', 'calorie ', 'stepnum', 'userid', 'sleeptimes ', 'lightsleeptimes', 'heavysleeptimes ', 'wakupnum', 'updatetime', 'createtime') VALUES (, 0, p_userId, p_sleepTimes, p_lightSleepTimes, times, p_wakeupNum, NOW (), NOW (); ELSE -- modify reset UPDATE into SET sleepTimes = sleepTimes + p_sleepTimes, lightSleepTimes = lightSleepTimes + p_lightSleepTimes, heavySleepTimes = heavySleepTimes + average, wakeupNum = wakeupNum + p_wakeupNum WHERE id = resultId; end if; END; // DELIMITER; show warnings; show create procedure limit; CALL limit (1009,600,100,500, 2 );

   I. Statements for creating a table are as follows:

   Drop table if exists 'userrecord'; create table 'userrecord' ('id' int (11) not null AUTO_INCREMENT, 'userid' int (11) not null comment 'fk ', 'totalstep' int (11) DEFAULT '0' comment' total steps ', 'updatetime' datetime default null, 'createtime' datetime not null, primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 8 default charset = utf8 COLLATE = utf8_unicode_ci COMMENT = 'user record ';/* Data for the table 'userrecord' */lock tables 'userrecord' WRITE; insert into 'userrecord' ('id', 'userid', 'totalstep', 'updatetime', 'createtime') values (88000, '2017-05-16 14:16:50 ', '2017-05-13 14:16:52'), (2014, 2014, '2017-05-16 14:26:22 ', '2017-05-12 14:26:24 '), (2014, 95000, '2017-05-16 14:28:00', '2017-05-12 14:28:06 '), (2014, 2014, 150000, '2017-05-16 14:30:31 ', '2017-04-28 14:30:33'), (2014, 2014, '2017-05-19 16:24:26 ', '2017-05-19 16:24:26 '), (6,1010, 33, '2017-05-19 17:01:50', '2017-05-19 17:01:50 '), (7,1011, 33, '2017-05-19 17:03:31 ', '2017-05-19 17:03:31'); unlock tables; /* Table structure for table 'userrecorddetail '*/drop table if exists 'userrecorddetail'; create table 'userrecorddetail '('id' int (11) not null AUTO_INCREMENT, 'weight' double DEFAULT '0' comment' today's weight kg ', 'calorie' int (11) DEFAULT '0' comment' calories consumed today', 'stepnum' int (11) DEFAULT '0' comment' today's steps ', 'userid' int (11) not null comment 'fk', 'sleeptimes 'int (11) DEFAULT '0' comment' today's sleep time unit: min ', 'lightsleeptimes' int (11) DEFAULT '0' comment' today's mild sleep time unit: min ', 'heartsleeptimes 'int (11) DEFAULT '0' comment' today's sleep duration unit: mine', 'wakupnum' int (11) DEFAULT '0' comment' today's Wakeup Times ', 'updatetime' datetime default null, 'createtime' datetime not null, primary key ('id ')) ENGINE = MyISAM AUTO_INCREMENT = 26 default charset = utf8 COLLATE = utf8_unicode_ci COMMENT = 'user record detail information';/* Data for the table 'userrecorddetail '*/lock tables 'userrecorddetail' WRITE; insert into 'userrecorddetail '('id', 'weight', 'calorie', 'stepnum', 'userid', 'sleeptimes', 'lightsleeptimes ', 'heavysleeptimes ', 'wakeupnum', 'updatetime', 'createtime') values (2014, 0, 0, NULL, '2017-05-16 14:17:53, 2014, 0, 0, NULL, '2017-05-15 14:22:58 '), (, 0, 0, 0, NULL, '2017-05-14 14:23:56 '), (2014, 2014, 0, 0, NULL, '2017-05-13 14:24:10, 0, 0, 0, NULL, '2017-05-12 14:24:32 '), (6, 0, 2014, 2014, 0, 0, 0, NULL, '2017-05-11 14:24:51 '), (2014, 0, 0, NULL, '2017-05-09 14:25:02 '), (, 0, 0, NULL, '2017-05-16 14:26:50 '), (9, 0, 2014, 2014, 0, 0, NULL, '2017-05-15 14:26:58'), (, 0, 0, 0, 0, NULL, '2017-05-14 14:27:14 '), (11, 0, 2014, 2014, 0, 0, NULL, '2017-05-16 14:28:46 '), (2014, 0, 0, 0, NULL, '2017-05-15 14:28:54 '), (, 0, 0, 0, NULL, '2017-05-13 14:29:01 '), (2014, 0, 2014, 0, 0, NULL, '2017-05-12 14:29:07, 0, 0, 0, NULL, '2017-05-08 14:29:39 '), (16, 0, 2014, 2014, 0, 0, NULL, '2017-05-16 14:30:45 '), (17,0, 2014, 0, 0, NULL, '2017-05-15 14:30:54 '), (, 0, 0, 0, 0, 0, NULL, '2017-05-14 14:31:02 '), (2014, 0, 2014, 0, 0, NULL, '2017-05-13 14:31:10'), (, 0, 0, 0, 0, NULL, '2017-05-12 14:31:18 '), (2014, 2014, 0, 0, NULL, '2017-05-11 14:31:26 '), (2014, 0, 111,288,100, 1007,0, 2014, 0, 0, NULL, '2017-04-30 14:32:02 '), (, 45, 2, '2017-05-19 16:24:26 ', '2017-05-19 16:24:26 '), (24, 0, 2014, 33, 2014, 0, 0, '2017-05-19 17:01:50', '2017-05-19 17:01:50 '), (1011,600,100,500, 45, 2014, 2014, 0, '2017-05-19 17:03:31 ', '2017-05-19 17:03:31'); unlock tables;

   The following example mainly uses

   I. if-then-else statement