MySQL存储过程
准备工作,新建名为students的数据,三张表分别是student,courses,stu_cou,并创建外键约束,级联删除更新,插入数据。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/*创建数据库*/ 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