What is a stored procedure?

That is, you can write logical sql, such as judgment, loop, etc,,

What are the benefits?

Imagine that we want to insert 100 pieces of data. Compared with batch operation, it is sure that the efficiency of the stored procedure is high In addition, the running speed of stored procedures is very fast. You can send several sql to the database and one execution command to the database. It must be much faster to execute the command!!

Disadvantage

Portability,,, not good...

Database structure I tested

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50717
Source Host           : localhost:3306
Source Database       : pro_test

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001

Date: 2018-02-04 20:59:23
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_interest
-- ----------------------------
DROP TABLE IF EXISTS `t_interest`;
CREATE TABLE `t_interest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(11) DEFAULT NULL COMMENT 'Student id',
  `name` varchar(32) DEFAULT NULL COMMENT 'Interest name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for t_log
-- ----------------------------
DROP TABLE IF EXISTS `t_log`;
CREATE TABLE `t_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(32) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for t_student
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Procedure structure for ifAndWhileTest
-- ----------------------------
DROP PROCEDURE IF EXISTS `ifAndWhileTest`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ifAndWhileTest`(in c CHAR, in num int, out result int)
begin 
declare i int default 0;
declare sum int default 0;
IF c = '1'
THEN
while i <= num DO
set sum = sum + i;
set i = i + 1;
END WHILE;
set result = sum; 
ELSE
set result = 0;
END IF;
end
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for inTest
-- ----------------------------
DROP PROCEDURE IF EXISTS `inTest`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `inTest`(in a int)
begin 
set a = 6;
select a;
end
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for outTest
-- ----------------------------
DROP PROCEDURE IF EXISTS `outTest`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `outTest`(out a int)
begin 
set a = 6;
select a;
end
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for saveStudent
-- ----------------------------
DROP PROCEDURE IF EXISTS `saveStudent`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `saveStudent`(in stuName varchar(32), in interestName varchar(32))
begin 
declare stuId int;
insert into t_student(name) values(stuName);
select max(id) into stuId from t_student;
insert into t_interest(s_id, name) values(stuId, interestName);  
end
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `toStudentAdd`;
DELIMITER ;;
CREATE TRIGGER `toStudentAdd` AFTER INSERT ON `t_student` FOR EACH ROW begin 
insert into t_log(content, create_time) values(concat('A record is inserted in the student table,id yes', new.id), now());
end
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `toStudentUpdate`;
DELIMITER ;;
CREATE TRIGGER `toStudentUpdate` AFTER UPDATE ON `t_student` FOR EACH ROW begin 
insert into t_log(content, create_time) values(concat('Student table updated a record,primary id yes', old.id, 'The updated name is', new.name), now());
end
;;
DELIMITER ;

Definition

delimiter (Your custom symbol)
create procedure Stored procedure name(parameter) 
begin 
..............process
end (Your custom symbol)

About parameters

1. The in parameter can only be used as the accepted value and cannot be modified
2. out parameter, modifiable, unacceptable

3. inout parameter, combining the two..

   eg:

delimiter //
create procedure inTest(in a int) 
begin 
set a = 6;
select a;
end //

-- Here is the call
set  @s = 1;
call inTest(@s);
select @s;

-- ------------------Modified in the above stored procedure a The value is6And output after,Let's check again @s Value discovery or1，It has not changed.

delimiter //
create procedure outTest(out a int) 
begin 
set a = 6;
select a;
end //

set  @f = 1;
call outTest(@f);
select @f;
-- ------------------Modified in the above stored procedure a The value is6And output after,Let's check again @f Value discovery of6Yes

About selection structure and cycle

Just look at the code:

-- Enter a letter and a number,If the letter is a,Calculation0-The sum of this number returns,Otherwise return0
delimiter //
create procedure ifAndWhileTest(in c CHAR, in num int, out result int) 
begin 
declare i int default 0;
declare sum int default 0;
-- declare Is to define a local variable,It seems that it can only be defined at the front end of the code.. That's what I tried
IF c = '1'
THEN
while i <= num DO
set sum = sum + i;
set i = i + 1;
END WHILE;
set result = sum; 
ELSE
set result = 0;
END IF;
end //

call ifAndWhileTest('a', 100, @r);
select @r;

Example

-- Example,A stored procedure that accepts students' names and hobbies and inserts them(Operation of two tables)

delimiter //
create procedure saveStudent(in stuName varchar(32), in interestName varchar(32))
begin 
declare stuId int;
insert into t_student(name) values(stuName);
select max(id) into stuId from t_student;
insert into t_interest(s_id, name) values(stuId, interestName);  
end //

call saveStudent('Minor fertilizer', 'Bask in the sunshine')

trigger

It means that we want to perform an operation after performing an operation, such as logging when operating a student table

-- Implementation trigger,Record the operation of students(The syntax is like this)
create trigger toStudentAdd after insert on t_student for each row 
begin 
insert into t_log(content, create_time) values(concat('A record is inserted in the student table,id yes', new.id), now());
end

-- new Represents new data,old Represents old data
create trigger toStudentUpdate after update on t_student for each row 
begin 
insert into t_log(content, create_time) values(concat('Student table updated a record,primary id yes', old.id, 'The updated name is', new.name), now());
end

update t_student set name = 'Little hair' where id = 4