MySQL存储过程

 准备工作，新建名为students的数据，三张表分别是student，courses，stu_cou，并创建外键约束，级联删除更新，插入数据。

/*创建数据库*/
create database if not EXISTS students character set utf8 collate utf8_general_ci;
/*创建表*/
use students;
create table if not EXISTS student
(
    stuID  int(5)  not null primary key,
    stuName  varchar(50)  not null,
    stuSex CHAR(10),
    stuAge smallint
);

CREATE TABLE if not EXISTS courses(
         couID int  not null primary key auto_increment COMMENT '学号',
         couName varchar(50) not null DEFAULT('大学英语'),
         couHours  smallint UNSIGNED COMMENT '学时',
         couCredit  float DEFAULT(2) COMMENT '学分'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE  utf8mb4_0900_ai_ci;

CREATE TABLE if not EXISTS stu_cou(
         ID int not null primary key auto_increment,
         stuID int(5)  not null  COMMENT '学号',
         couID int  not null  COMMENT '课程编号',
         time timestamp not null DEFAULT(now())
);

 /*添加外键约束*/
 alter table stu_cou add CONSTRAINT fk_stuid foreign key(stuID) REFERENCES student(stuID) ON UPDATE CASCADE ON DELETE CASCADE ;
 alter table stu_cou add CONSTRAINT fk_couid foreign key(couID) REFERENCES courses(couID) ON UPDATE CASCADE ON DELETE CASCADE ;

  /*插入数据*/
 insert into student(stuID,stuName,stuSex,stuAge) values(1001,'张三','男',19),(1002,'李四','男',18),(1003,'王五','男',18),(1004,'黄丽丽','女',18),(1005,'李晓辉','女',19),(1006,'张敏','女',18); insert into student VALUES(1007,'五条人','男',20),(1008,'胡五伍','女',19);
 insert into courses(couID,couName,couHours,couCredit) values(50,'大学英语',64,2),(60,'计算机基础',78,2.5),(70,'Java程序设计',108,6),(80,'数据库应用',48,2.5);
 insert into stu_cou(stuID,couID) values(1001,50),(1001,60),(1001,70),(1001,80),(1002,50),(1002,60),(1002,70),(1002,80),(1003,50),(1003,60),(1003,70),(1003,80),(1004,50),(1004,60),(1004,70),(1004,80),(1005,50),(1005,60),(1005,70),(1005,80),(1006,50),(1006,60),(1006,70),(1006,80);

alter table stu_cou add COLUMN grade FLOAT null;
UPDATE stu_cou set grade=(SELECT FLOOR(50 +RAND() * 50));

alter table student add COLUMN stuColleage varchar(100) null;
update student set stuColleage='大数据学院' where stuID BETWEEN 1001 and  1003;
update student set stuColleage='物流学院' where stuID BETWEEN 1004 and  1006;
update student set stuColleage='康养学院' where stuID BETWEEN 1007 and  1008;

alter table courses add COLUMN couColleage varchar(50) null;

update courses set couColleage='通识教育学院' where couName='大学英语';
update courses set couColleage='通识教育学院' where couName='计算机基础';
update courses set couColleage='大数据学院' where couName='Java程序设计';
update courses set couColleage='大数据学院' where couName='数据库应用';

insert into courses values(90,'大学体育',56,1.5,'通识教育学院'),(100,'Android程序设计',92,5,'大数据学院'),(101,'大学物理',48,2,'通识教育学院');

insert into stu_cou(stuID,couID,grade) values(1007,50,86),(1007,60,71),(1008,70,56),(1008,80,63);
 insert into student VALUES(1009,'曾小小','男',17,'物流学院'),(1010,'项XXX','女',21,'大数据学院');


/*视图部分*/
create view `查询所有大数据学院学生`
as
select * from student where stuColleage='大数据学院'
with check option;


create view `查询学生姓名,课程名称和分数`
as
SELECT stuName as '姓名',couName as '课程名称',grade as '分数' from student s INNER JOIN stu_cou sc on s.stuID=sc.stuID INNER JOIN courses c on c.couID=sc.couID
with check option;


alter view `查询所有大数据学院学生`(`学号`,`姓名`,`性别`,`学院`)
as
select stuID,stuName,stuSex,stuColleage from student 
where stuColleage='大数据学院';

create view `查询大数据学院学生的姓名，课程名称，分数和学院名称`
as
select a.`姓名`,b.`课程名称`,b.`分数`,a.`学院` from `查询所有大数据学院学生` a inner join 
`查询学生姓名,课程名称和分数` b on a.`姓名`=b.`姓名`
with check option;



alter view `查询所有大数据学院学生`(`学号`,`姓名`,`性别`,`学院`)
as
select stuID,stuName,stuSex,stuColleage from student 
where stuColleage='大数据学院'
with check option;

create view `查询学生表中男女同学的人数`(`性别`,`人数`)
as
select stuSex,count(stuID)  from student 
GROUP BY stuSex;


create view `查询大数据学院男同学的年龄`(`学号`,`姓名`,`性别`,`年龄`,`学院`)
as
select stuID,stuName,stuSex,stuAge,stuColleage from student where 
stuSex='男' and stuColleage='大数据学院';

1.存储过程的语法（转自：http://c.biancheng.net/view/2593.html）

可以使用 CREATE PROCEDURE 语句创建存储过程，语法格式如下：

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

语法说明如下：

1) 过程名

存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。
需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

2) 过程参数

存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。
MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。
需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。

3) 过程体

存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。
在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。
在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。
为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下：

DELIMITER $$

语法说明如下：

• $$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
• 当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。

注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。

例1：创建一个名为ShowStu的存储过程，查询student表的内容。

delimiter // 
create PROCEDURE ShowStu()
begin
select * from student;
end//
delimiter ;

2.存储过程中变量的使用

存储过程中的变量分为两种:局部变量和用户变量。

（1）局部变量

定义在存储过程中的变量，只能在存储过程内部使用，定义在存储过程开始的位置，语法：declare 变量名称  数据类型  [default 默认值]。

给变量赋值，set 变量名=表达式值[,变量名=表达式...];

如：declare sname varchar(50)  default '张三';    set sname='李四';

例2：创建一个名为 proc_test2存储过程:计算输入参数的平方与输入参数/2之和。

create procedure proc_test2(in a int,out r int)
begin
DECLARE X INT DEFAULT 0;  #定义X int类型 默认值为0
DECLARE Y INT DEFAULT 0;   #定义Y int类型 默认值为0
SET X=a*a;
SET Y=a/2;
SET r=X+Y; 
end;

call proc_test2(5,@r);
select @r;

 （2）用户变量

用户变量相当于全局变量，定义的用户变量可以通过"select @attrname from dual"进行查询。

在存储过程中使用select.....INTO.....给变量赋值。

注意：因为用户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，在开发中建议尽量少使用用户变量。用户变量过多会导致程序不易理解，难以维护。

3.存储过程的参数

MySQL中存储过程的参数一共有三种：in/out/inout

（1）IN输入参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量），默认是IN输入参数，如果不填写，就是默认的IN输入参数。

例3：创建一个名为GetNameByStu的存储过程，用于查询指定姓名的学生信息。

delimiter // 
create PROCEDURE GetNameByStu(in name varchar(50))
begin
select * from student where stuName=name;
end//
delimiter ;

 调用存储过程：call sp_name[(传参)];

call GetScoreByStu('李四');

 （2）OUT输出参数（转自：https://www.jb51.net/article/255003.htm）

OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）。

例4：创建一个名为GetIDByName的存储过程，用于查询指定姓名的学生学号。

delimiter // 
create PROCEDURE GetIDByName(in name varchar(50),out id int)
begin
select stuID into id from student where stuName=name;
end//
delimiter ;

call GetIDByName('王五',@sid);
select @sid;

输出的时候，需要在存储方法中指定，并@变量名，最后才能让这个变量接收到数值

其次输出参数的定义的时候，也和输入参数一样的，out 参数名 类型（长度）

 例5：创建一个名为GetIDAndHoursByName的存储过程，用于查询指定课程名称的课程编号和课程学时。

delimiter // 
create PROCEDURE GetIDAndHoursByName(in name varchar(50),out id int,out hours int)
begin
select couID,couHours into id,hours from courses where couName=name;
end//
delimiter ;

call GetIDAndHoursByName('大学英语',@id,@hours);
select @id;
select @hours;

 内部接收的时候就需要使用 into 变量名，变量名……

注意：也就是说，在存储过程中使用out声明输出参数，在内部视图into 进行赋值之后，最后在调用存储的时候需要在里面自定义一个变量进行接收 但是必须要 使用@变量名

（3）INOUT修改传入参数值

inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

例6：创建一个名为GetSalaryByName的存储过程，传入学生名字，使用“-”拼接学号，并传入一个月的薪水值，输出年薪。

delimiter //
create PROCEDURE GetSalaryByName(inout sname varchar(50) ,inout salary float)
begin
select concat(stuID,"-",stuName) into sname from student where stuName=sname;
set salary=salary*12;
end//
delimiter ;

set @sname='王五';
set @salary=4000.5;
call GetSalaryByName(@sname,@salary);
select @sname;
select @salary;

相对于，如果我们使用inout关键词，这个参数可以作为输入参数，也可以作为输出参数，输出参数时候，可以在内部的语句进行修改，然后覆盖原来的值，最后调用的时候可以定义一样的变量名，也可以不一样。

• in 输入参数，意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回
• out 输出参数:该值可在存储过程内部被改变，并向外输出
• inout 输入输出参数，既能输入一个值又能传出来一个值

4.存储过程中的流程控制

 （1）分支控制语句

单分支控制语句：if 条件 then 语句列表 end if ; 

 多分支控制语句:

if 条件 then 语句列表

      else if 条件 then 语句列表

      else if 条件 then 语句列表 

     ...

     else 语句列表 

end if ;

例7：创建一个名weekchoose的存储过程，使用多分支控制语句实现传入整型数字0,1,2，输出周日，周一，周二，如果传入其他数字则输出无效日期。

delimiter //
create PROCEDURE `weekchoose`(in `day` int)
BEGIN
if `day`=0 THEN
  select '星期天';
elseif `day`=1 THEN
  select '星期一';
elseif `day`=2 THEN
  select '星期二';
ELSE
  select '无效日期';
end if;
end//
delimiter ;
call weekchoose(2);

例：向student表中插入一条信息，判断学号是否存在，如果存在则提示“学号已存在，不能添加”，否则向数据库添加一条数据，提示“数据添加成功”

DELIMITER $$
CREATE PROCEDURE `InsertTostudent`(IN stu_id int,IN stu_name varchar(20),OUT s_result VARCHAR(20))
    BEGIN
       -- 声明一个变量 用来决定学号是否已经存在
       DECLARE s_count INT DEFAULT 0;
       -- 验证这么名字是否已经存在
       SELECT COUNT(*) INTO s_count FROM student WHERE `stuID` = stu_id;    
       IF s_count = 0 THEN
            INSERT INTO student(`stuID`, `stuName`) VALUES(stu_id, stu_name);
         SET s_result = '数据添加成功';
       ELSE
                SET s_result = '学号已存在，不能添加';
                SELECT s_result;
       END IF;
    END$$
DELIMITER;

call InsertTostudent(1010,'李晓红',@result);
SELECT @result;

call InsertTostudent(1011,'李晓红',@result);
SELECT @result;

条件控制语句case：

case

     when 条件值  then 语句列表

    [when 条件值  then 语句列表]

    [else 语句列表]

end case;

 例8：同例7，使用case条件控制语句实现。

delimiter //
create PROCEDURE `weekchoose2`(in `day` int)
BEGIN
case `day`
when 0 THEN
  select '星期天';
when 1 THEN
  select '星期一';
when 2 THEN
  select '星期二';
ELSE
  select '无效日期';
end case;
end//
delimiter ;

call weekchoose2(2);

 （2）while循环语句

首先判断条件是否成立。如果成立，则执行循环体

[label:] WHILE 条件 DO
语句列表
END WHILE [label]

label为标号，用于区分不同的循环，可省略,用在begin、repeat、while 或者loop 语句前。

例9：使用while循环实现传入小于0的整数i，输出i+1到10的和。

delimiter //
CREATE PROCEDURE `sum`(in i int,out sum int)
BEGIN
  set sum=0;
while i<10 DO
  set i=i+1;
  set sum=sum+i;
end while;
end//
delimiter ;

call `sum`(0,@s);
select @s;

例10：在students数据库中创建一个名为test的表，字段为id int PRIMARY key，tname varchar(50) not null，time TIMESTAMP DEFAULT now()，使用while循环向test表中插入10万条数据。

use students;
create table test
(
id int PRIMARY key,
tname varchar(50) not null,
time TIMESTAMP DEFAULT now()
);

delimiter //
create PROCEDURE `InsertTotest`()
BEGIN
DECLARE i int DEFAULT 1;
while i<=100000 DO
   insert into `students`.`test`(id,tname) values(i,'张三');
   set i=i+1;
end while;
end//
delimiter ;

call InsertTotest();

 （3）LOOP循环语句

[label:] LOOP
语句列表
END LOOP [label] ;

执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环，也可以嵌套IF等判断语句。

LEAVE 语句效果对于Java中的break，用来终止循环；

ITERATE语句效果相当于Java中的continue，用来跳过此次循环。进入下一次循环。且ITERATE之下的语句将不在进行。

（4）REPEAT循环语句

先执行循环操作再判断循环条件

#与Java的do-while循环语句类似

[label:] REPEAT 

   语句列表

UNTIL 条件

END REPEAT [label]

5.存储过程的管理

（1）显示存储过程

show PROCEDURE STATUS;

 

 显示特定的存储过程

show PROCEDURE STATUS where db='students' and name like '%Get%';

 

 （2）显示存储过程的源码

show create PROCEDURE `GetIDByName`;

（3）删除存储过程

drop procedure 存储过程名称；

总结（转自：https://blog.csdn.net/ReincarnationArm/article/details/124572991）：

 

 

参考文章：

https://zhuanlan.zhihu.com/p/557690630

https://blog.csdn.net/ReincarnationArm/article/details/124572991

https://www.jb51.net/article/253031.htm

1.