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='大数据学院';

View Code

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 语句列表

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

posted @ 2022-11-29 19:35 YorkShare 阅读(263) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

YorkShare_z

笨鸟先飞

MySQL存储过程

1) 过程名

2) 过程参数

3) 过程体

公告