MYSQL存储过程基础入门教程

#创建一个简单的存储过程
 
DELIMITER $$
CREATE PROCEDURE selectStu()
BEGIN 
   SELECT * FROM stu WHERE id = 3;
END ;
 
$$
 
DELIMITER;   
 
-- 调用存储过程
CALL selectStu();
 
#存储过程多个 查询
DELIMITER $$ 
CREATE PROCEDURE selectStuAndTeaAll()
BEGIN 
   SELECT * FROM stu WHERE id = 8;
   SELECT * FROM tea WHERE id = 2;
END ;
$$
DELIMITER;
-- 调用存储过程会出现 两个结果集
CALL selectStuAndTeaAll();
 
 
# 存储过程的 变量 使用
DELIMITER $$
CREATE PROCEDURE finalSelectStu()
BEGIN 
   DECLARE name_stu VARCHAR(20) DEFAULT '';
   SET name_stu = '默认值';
   SELECT `name` INTO name_stu FROM stu WHERE id = 1;
   SELECT name_stu;
END ;
$$
DELIMITER;  
-- 调用存储过程  以前一直出错  可能出现字符集问题。
CALL    finalSelectStu();
 
   
# 存储过程出现多个代码块
 
DELIMITER $$
CREATE PROCEDURE selectStuAndTeaMinMax()
BEGIN
   BEGIN
      DECLARE sum_stu INT DEFAULT 0;
      DECLARE sum_tea INT DEFAULT 0;
      SELECT COUNT(*) INTO sum_stu FROM stu ;
      SELECT COUNT(*) INTO sum_tea FROM tea;
      SELECT sum_stu,sum_tea;
   END; 
   BEGIN 
      -- 记得数据类型要一直 我这里出现的错误
      DECLARE regStuMax_time  TIMESTAMP;
      DECLARE regStuMin_time  TIMESTAMP;
      SELECT MAX(TIMESTAMP) ,MIN(TIMESTAMP) INTO regStuMax_time,regStuMin_time FROM stu;
   END ;   
END;
$$
DELIMITER;
-- 调用存储过程
CALL selectStuAndTeaMinMax()
 
# 存储过程传入参数 IN
 
DELIMITER $$
-- 传参 ( IN stuId INT) in不写默认
CREATE PROCEDURE parameterSelectStu(stuId INT)
BEGIN 
   
   DECLARE name_stu VARCHAR(20) DEFAULT '';
   SELECT `name` INTO name_stu FROM stu WHERE id = stuId;
   SELECT name_stu;
END;
$$
DELIMITER;   
-- 调用存储过程
CALL parameterSelectStu(5);
 
#存储过程传出参数 OUT
DELIMITER $$
CREATE PROCEDURE outStuName(IN stuId INT,OUT stuName VARCHAR(20))
BEGIN 
   SELECT `name` INTO stuName FROM stu WHERE id = stuId;
   SELECT stuName;
END;
$$
DELIMITER;   
-- 调用存储过程，注意参数
CALL outStuName(2,@stuName);
 
# 存储过程 可变参数 INOUT 的使用,即是传入参数又是传出参数
DELIMITER $$
CREATE PROCEDURE inoutStuIdName(INOUT stuId INT ,INOUT stuName VARCHAR(20))
BEGIN 
   SELECT id,`name` INTO stuId,stuName FROM stu WHERE id = stuId;
   SELECT stuId,stuName;
END;
$$
DELIMITER;   
-- INOUT 不会调用啊 (还是百度的啊)
SET @stuIds = 8;
SET @stuNames = '';
CALL inoutStuIdName(@stuIds,@stuNames);
 
 
# 存储过程的条件语句
DELIMITER $$
CREATE PROCEDURE ifElseStu(IN stuId INT)
BEGIN 
   DECLARE stuName VARCHAR(20) DEFAULT '';
   -- 注意 这里不是 ==  是 = 在这里错过
   IF(stuId%2=0)   
   THEN 
     SELECT `name` INTO stuName FROM stu WHERE id = stuId;
     SELECT stuName;
   ELSE
     SELECT stuId;
   -- 注意在这里是 end if 在这里错过  
   END IF;
END;
$$
DELIMITER;       
-- 调用存储过程   
CALL  ifElseStu(2);  
   
# 案例  如果 = 5 字段age + 10 ;如果id = 9 字段 age - 10 ；其他都+100   
 
-- 修改前  id= 5 age = 33 ，id=9 age= 53
DELIMITER $$
CREATE PROCEDURE topIfThen(IN stuId INT)
BEGIN 
   DECLARE stuAge INT DEFAULT 0;
   SELECT age INTO stuAge FROM stu WHERE id = stuId;
   --  如果age = 55 加10 
   IF(stuAge = 55)
   THEN
     UPDATE stu SET age=stuAge + 10 WHERE id = stuId;
   -- 如果 age = 33 减 10；  
   ELSEIF (stuAge = 33)
     UPDATE stu SET age=stuAge - 10 WHERE id = stuId; 
   -- 其他情况加  30  
   ELSE    
     UPDATE stu SET age = stuAge + 30 WHERE id = stuId;
   END IF ;
END;
$$
DELIMITER; 
 
 
#WHILE 循环语句
DELIMITER $$
CREATE PROCEDURE whileStu()
BEGIN 
   DECLARE i INT DEFAULT 0;
   WHILE (i < = 10) DO
     BEGIN 
       SELECT i;
       SET i = i+1;
       INSERT INTO stu (age) VALUES(2); 
       END;
     END WHILE;
END;
$$
DELIMITER;       
 

/*
SQLyog Ultimate v11.27 (32 bit)
MySQL - 5.5.56 : Database - test
*********************************************************************
*/
 
 
/*!40101 SET NAMES utf8 */;
 
/*!40101 SET SQL_MODE=''*/;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;
 
USE `test`;
 
/*Table structure for table `stu` */
 
DROP TABLE IF EXISTS `stu`;
 
CREATE TABLE `stu` (
  `id` int(36) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `reg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
 
/*Data for the table `stu` */
 
insert  into `stu`(`id`,`name`,`age`,`reg`) values (1,'荀攸',10,'2017-10-26 15:16:58'),(2,'郭嘉',10,'2017-10-26 16:07:26'),(3,'庞统',10,'2017-10-26 16:07:26'),(4,'刘备',10,'2017-10-26 16:07:26'),(5,'曹操',10,'2017-10-26 16:07:26'),(6,'孙权',10,'2017-10-26 16:07:26'),(7,'陆逊',10,'2017-10-26 16:07:26'),(8,'孙浩',10,'2017-10-26 16:07:26'),(9,'周瑜',10,'2017-10-26 16:07:26'),(10,'张飞',10,'2017-10-26 15:23:38'),(11,'张苞',10,'2017-10-26 16:07:26'),(12,'关羽',10,'2017-10-26 16:07:26'),(13,'赵子龙',10,'2017-10-26 16:07:26');
 
/*Table structure for table `tea` */
 
DROP TABLE IF EXISTS `tea`;
 
CREATE TABLE `tea` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `name` varchar(6) DEFAULT NULL,
  `age` int(5) DEFAULT NULL,
  `reg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
 
/*Data for the table `tea` */
 
insert  into `tea`(`id`,`name`,`age`,`reg`) values (1,'李斯',23,'2017-07-31 09:05:50'),(2,'蒙恬',85,'2017-09-12 09:05:56'),(3,'嬴政',45,'2017-09-18 09:06:00'),(4,'吕不韦',56,'2017-07-12 09:06:04'),(5,'嫪毐',75,'2017-10-09 09:06:09'),(6,'吴广',63,'2017-10-18 09:06:12'),(7,'项羽',78,'2017-12-08 09:06:15'),(8,'刘邦',22,'2018-01-19 09:06:19'),(9,'萧何',33,'2018-01-19 09:06:23'),(10,'张良',55,'2017-05-16 09:06:27'),(11,'赵高',68,'2017-07-03 09:06:32'),(12,'韩信',67,'2017-10-09 09:06:36'),(13,'吕后',13,'2017-10-02 09:06:39');
 
/* Procedure structure for procedure `finalSelectStu` */
 
/*!50003 DROP PROCEDURE IF EXISTS  `finalSelectStu` */;
 
DELIMITER $$
 
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `finalSelectStu`()
begin 
   declare name_stu varchar(20) default '';
   set name_stu = '默认值';
   select `name` into name_stu from stu where id = 1;
   select name_stu;
end */$$
DELIMITER ;
 
/* Procedure structure for procedure `ifElseStu` */
 
/*!50003 DROP PROCEDURE IF EXISTS  `ifElseStu` */;
 
DELIMITER $$
 
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `ifElseStu`(in stuId int)
begin 
   declare stuName varchar(20) default '';
   if(stuId%2=0)   
   then 
     select `name` into stuName from stu where id = stuId;
     select stuName;
   else
     select stuId;
   end if;
end */$$
DELIMITER ;
 
/* Procedure structure for procedure `inoutStuIdName` */
 
/*!50003 DROP PROCEDURE IF EXISTS  `inoutStuIdName` */;
 
DELIMITER $$
 
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `inoutStuIdName`(inout stuId int ,inout stuName varchar(20))
begin 
   select id,`name` into stuId,stuName from stu where id = stuId;
   select stuId,stuName;
end */$$
DELIMITER ;
 
/* Procedure structure for procedure `outStuName` */
 
/*!50003 DROP PROCEDURE IF EXISTS  `outStuName` */;
 
DELIMITER $$
 
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `outStuName`(in stuId int,out stuName varchar(20))
begin 
   select `name` into stuName from stu where id = stuId;
   select stuName;
end */$$
DELIMITER ;
 
/* Procedure structure for procedure `parameterSelectStu` */
 
/*!50003 DROP PROCEDURE IF EXISTS  `parameterSelectStu` */;
 
DELIMITER $$
 
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `parameterSelectStu`(stuId int)
begin 
   
   declare name_stu varchar(20) default '';
   select `name` into name_stu from stu where id = stuId;
   select name_stu;
end */$$
DELIMITER ;
 
/* Procedure structure for procedure `selectStu` */
 
/*!50003 DROP PROCEDURE IF EXISTS  `selectStu` */;
 
DELIMITER $$
 
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `selectStu`()
begin 
   select * from stu where id = 3;
end */$$
DELIMITER ;
 
/* Procedure structure for procedure `selectStuAndTeaAll` */
 
/*!50003 DROP PROCEDURE IF EXISTS  `selectStuAndTeaAll` */;
 
DELIMITER $$
 
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `selectStuAndTeaAll`()
begin 
   select * from stu where id = 8;
   select * from tea where id = 2;
end */$$
DELIMITER ;
 
/* Procedure structure for procedure `selectStuAndTeaMinMax` */
 
/*!50003 DROP PROCEDURE IF EXISTS  `selectStuAndTeaMinMax` */;
 
DELIMITER $$
 
/*!50003 CREATE DEFINER=`root`@`localhost` PROCEDURE `selectStuAndTeaMinMax`()
begin
   begin
      declare sum_stu int default 0;
      declare sum_tea int default 0;
      select count(*) into sum_stu from stu ;
      select count(*) into sum_tea from tea;
      select sum_stu,sum_tea;
   end; 
   begin 
      declare regStuMax_time  timestamp;
      declare regStuMin_time  timestamp;
      select max(timestamp) ,min(timestamp) into regStuMax_time,regStuMin_time from stu;
   end ;   
end */$$
DELIMITER ;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;