存储过程（二）

存储过程是一组为了完成某特定功能的SQL语句集，一段存放在数据库中的代码，由声明式的SQL语句(例如select update)和过程式的SQL语句(如if else then)组成
存储过程编译后直接存储在数据库中，用户可以通过call语句来调用及执行它。
系统无须重新编译程序，可提高执行效率。

建立和调用存储过程（不带输出参数的存储过程）

任务描述

本关任务：
该实验是针对数据表jdxx，该数据表有四个字段，分别是省份(sf)、城市(cs)、区县(qxmc)、街道(name)。
例如，查询天心区(qxmc)的所有字段的值结果如图所示

任务要求

建立存储过程

dqxx(in city varchar(10),in district varchar(10))
输入城市city和区县district的名称，显示乡x、镇z和街道jd、其他的个数
提示：即name字段含有乡、镇、街道及不包含上述选项的个数

调用过程

以长沙市和开福区为参数，调用dqxx过程
以厦门市和同安区为参数，调用dqxx过程

例如，以长沙市和岳麓区为参数，调用过程显示结果如下

1
2
乡 街道 镇    其他
1    9    1    0

相关知识

建立存储过程

命令格式：

1
2
3
4
CREATE PROCEDURE sp_name ( [proc_parameter] )
BEGIN    
过程体
END

设置语句结束符

注意：
DELIMITER //
在过程体前面使用DELIMITER 符号命令将其他符号（不是分号）作为语句结束符,从而使服务器可以完整地处理过程体中所有的sql语句。
DELIMITER ;
在过程结束后将语句结束符变为;

参数列表

([in|out|inout proc_name type][,in|out|inout proc_name type])
过程可以没有参数或多个参数，多个参数之间用逗号分隔
IN输入参数，使数据可以传递给存储过程
OUT输出参数，用于需要返回结果
INOUT输入输出参数 既可以充当输入参数也可以充当输出参数

声明局部变量

DECLARE 局部变量名 类型 [default 默认值]
过程体中使用的局部变量，必须在存储过程的开头处声明

显示表达式的值

SELECT 表达式 [as 列名]
计算并显示表达式的值

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 use province;
 #代码开始
 #定义过程

delimiter $$
CREATE PROCEDURE dqxx(in city varchar(10),in district varchar(10))
BEGIN
declare x int;
declare jd int;
declare z int;
declare qt int;
select count(name)  from jdxx  where cs=city and qxmc=district and name like '%乡' into x;
select count(name)  from jdxx  where cs=city and qxmc=district and name like '%街道' into jd;
select count(name)  from jdxx  where cs=city and qxmc=district and name like '%镇' into z;
select count(name)  from jdxx  where cs=city and qxmc=district and name not like '%镇' and name not like '%街道' and name not like '%乡'into qt;
select x as "乡",jd as "街道",z as "镇",qt as "其他";
end $$
delimiter ;

 #调用过程
call dqxx('长沙市','开福区');
call dqxx("厦门市","同安区");
 #代码结束

建立和调用存储过程（带输出参数）

任务描述

本关任务：
销售数据库有工作人员、销售单数据表
工作人员gzry数据表有雇员号gyh、姓名gyxm、出生日期csrq、学历xl、工资gz、部门bm、电话dh字段

销售单xsd数据表有销售单号xsdh、会员号hyh、雇员号gyh、销售日期xsrq、应付款yfk、实际付款sjfk字段

任务要求

建立存储过程

ygyj(in nf int,in yf int,in xm varchar(10) out pj varchar(10))

输入年份nf和月份yf和姓名xm ，输出对该工作人员的评价pj。
若该员工在指定年份和月份的销售业绩（销售单xsd的实际付款sjfk的合计）大于等于10000元，则返回优秀，大于等于5000元，则返回达标，大于0小于5000元返回不达标，若没有销售单则返回无业绩。

调用过程

以2015、7和王雅静为参数，调用ygyj过程，将结果存入@yj1
以2015、6和廖秉娴为参数，调用ygyj过程，将结果存入@yj2
以2015、7和赵敏为参数，调用ygyj过程，将结果存入@yj3
以2015、7和章伟为参数，调用ygyj过程，将结果存入@yj4

相关知识

参数列表

([in|out|inout proc_name type][,in|out|inout proc_name type])
过程可以没有参数或多个参数，多个参数之间用逗号分隔
IN输入参数，使数据可以传递给存储过程
OUT输出参数，用于需要返回结果
INOUT输入输出参数 既可以充当输入参数也可以充当输出参数

调用存储过程

CALL 存储过程（参数列表）
当有out参数时，可以用全局变量接收结果
全局变量以@开头，在mysql会话中一直有效

case多分支语句

1
2
3
4
5
6
CASE
WHEN   <条件1> THEN  <语句1>
WHEN   <条件2>   THEN <语句2>
……
[Else  <其他语句>]
END CASE

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 use sale;
 #代码开始
 #定义过程
 
delimiter $$
create procedure ygyj(in nf int,in yf int,in xm varchar(10) ,out pj  varchar(10))
begin
declare yj int;
select (select sum(sjfk) from xsd,gzry where xsd.gyh=gzry.gyh and year(xsrq)=nf and month(xsrq)=yf and gzry.gyxm=xm) into yj;
set pj=case 
when yj>=10000 then '优秀'
when yj<5000 then '不达标'
when (yj>=5000 and yj<10000) then '达标'
else '无业绩'
end;
end $$
delimiter ;

#select sum(sjfk) from xsd,gzry where xsd.gyh=gzry.gyh and year(xsrq)=2015 and month(xsrq)=7 and xsd.gyh='009'; 

 #调用过程
call ygyj(2015,7,'王雅静',@yj1);
call ygyj(2015,6,'廖秉娴',@yj2);
call ygyj(2015,7,'赵敏',@yj3);
call ygyj(2015,7,'章伟',@yj4);

 #代码结束
 select @yj1,@yj2,@yj3,@yj4;

建立和调用存储函数

任务描述

本关任务：
销售数据库有顾客、销售单数据表
顾客gk数据表有会员号hyh、姓名name、性别sex、电话tel、部门dept字段

销售单xsd数据表有销售单号xsdh、会员号hyh、雇员号gyh、销售日期xsrq、应付款yfk、实际付款sjfk字段

任务要求

建立存储过程

gkjb(nf int, xm varchar(10) )
输入年份nf和月份yf和姓名xm ，返回该会员的等级。
若该顾客在指定年份的消费金额（销售单xsd的实际付款sjfk的合计）大于等于10000元，则返回超级vip，大于等于5000元，则返回vip，大于0小于5000元返回一般会员，若没有销售数据则返回非会员。

调用过程

通过select查询所有顾客数据表的姓名和2015年的顾客级别，结果如下所示

相关知识

存储函数定义

存储函数由sql语句和过程语句组成的代码片段
可以定义输入参数（无需使用in)，没有输出参数。
在begin前通过returns <数据类型>语句指定返回值类型
在函数体中通过return返回值

1
2
3
4
5
6
CREATE FUNCTION func_name ( [func_parameter] )
Returns <返回参数类型>
BEGIN    
函数体
return 返回值
END

如果开启了 bin-log, 必须在begin之前指定一个参数DETERMINISTIC

调用存储函数

select 存储函数（参数列表）[as 列名]
可通过select语句调用并显示函数

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
use sale;
#代码开始
#函数定义
delimiter $$
create function gkjb(nf int,nm varchar(10))
Returns varchar(10)
begin
    declare dj varchar(10);
    declare total int;
    set total=0;
    select (select sum(sjfk) from xsd,gk where gk.name=nm and gk.hyh=xsd.hyh and nf=year(xsrq)) into total;
set dj = case
when total>=10000 then '超级vip'  
when total<10000 and total>=5000 then 'vip' 
when total<5000 then '一般会员'
else '非会员'
end;   

    return dj;
end $$
delimiter ;

#调用函数
select distinct name as 姓名 ,gkjb(2015,name) as 等级 from gk ;

 #代码结束

修改多个数据表的存储过程

任务描述

本关任务：
图书管理数据库有读者reader图书book借阅数据表
读者表reader有读者证号dzzh、姓名xm、性别xb、身份sf、电话号码dhhm字段

图书表book有条形码txm、书名sm、分类号flh,作者zz,出版社cbs,出版日期cbrq,售价sj,典藏类别dclb,在库zk,币种bz字段

借阅表borrow有借阅编号jyid、读者证号dzzh、条形码txm、借阅日期jyrq、还书日期hsrq五个字段。
当读者借阅图书时，在借阅数据表中添加一条记录，还书日期设置为NULL,图书的在库zk字段被设置为0
当读者归还图书时，将其对应借阅记录的还书日期设置为指定日期，并修改图书的在库zk字段为1.

任务要求

建立存储过程

hs(in sh varchar(8), in dzbh varchar(3) ,in rq date,out zt varchar(12) )
输入图书条形码sh、读者证编号dzbh和还书日期rq，办理还书。
检查借阅数据表是否有此记录（条形码txm为sh读者dzzh为dzbh，且还书日期hsrq是否为null，若没有则zt返回”没有该借阅”,否则在借阅表将该借阅记录的还书日期改为rq。
并将图书数据表该图书的在库zk改为1,zt返回”成功还书”.

调用过程

分别以下列参数调用过程，并将结果返回@zt1、@zt2
书号P0000001和读者证号001,还书日期2022-5-1
书号P0000001和读者证号002,还书日期2022-5-1

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 use library;
 #代码开始
 #定义过程
delimiter $$
create procedure hs(in sh varchar(8), in dzbh varchar(3) ,in rq date,out zt varchar(12) )
begin
declare flag varchar(10);
declare flag2 varchar(10);
set flag='111';
set flag2='111';
select hsrq from borrow where txm=sh and dzzh=dzbh and hsrq is null into flag;
if flag is null then set zt='还书成功';
else set zt='没有该借阅';
end if;

if flag is null then update book set zk=1 where txm=sh;
else set flag2='111';
end if;

if flag is null then update borrow set hsrq=rq where txm=sh and dzzh=dzbh;
else set flag2='111';
end if;

end $$
delimiter ;
 #调用过程
call hs('P0000001',001,2022-5-1,@zt1);
call hs('P0000001',002,'2022-5-1',@zt2);

 #代码结束
 select @zt1,@zt2;
 select txm,sm,zk from book;
 select * from borrow;

使用游标的存储过程

任务描述

本关任务：
jdxx数据表有四个字段，分别是省份(sf)、城市(cs)、区县(qxmc)、街道(name)。
例如，查询天心区(qxmc)的所有字段的值结果如图所示

任务要求

建立存储过程

tjdq(in sf varchar(10))
输入省份的名称，将该省份所有城市的所有地区的
乡x、镇z和街道jd和其他的个数插入到数据表dqtj中。
dqtj数据表已经建立在数据库中，有城市(cs)，区县名称(qxmc)、县（x)、街道（jd)、镇(z)、其他(qt)六个字段。分别存储指定省份的城市、区县名称和县、街道、镇、其他的个数。
以湖南省为参数，调用tjdq过程后
dqtj数据表部分数据如图所示

调用过程

以安徽省为参数，调用过程

提示：该过程先要删除dqtj数据表的所有数据
然后将指定省份的城市和区县的唯一信息值存入游标
然后将游标的各条信息依次取出循环，根据游标中的城市和区县名称，在jdxx数据表查出对应的乡、街道、镇和其他的个数，然后插入到dqtj数据表。

相关知识

声明游标

命令格式：
DECLARE 游标名 CURSOR FOR select语句

打开和关闭游标

命令格式：
open 游标名
close 游标名

读取游标信息

命令格式：
FETCH 游标名 INTO var1,var2[,…]
将游标声明时的查询结果逐条存放在变量中
每执行一次fetch将指针指向下一条结果
变量必须在声明游标前定义

遍历游标

我的代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 use province;
 #代码开始
DELIMITER ||
CREATE PROCEDURE tjdq(in sm varchar(10))
BEGIN
declare flag int default 1;
declare city varchar(10);
declare qx varchar(10);
declare jd int;
declare x int;
declare z int;
declare qt int;
DECLARE dq CURSOR FOR SELECT distinct cs,qxmc from jdxx where sf=sm;
declare continue handler for not found set flag = 0;
delete from dqtj;
OPEN dq;
FETCH dq INTO city,qx;
while flag=1 do
select   count(*) from jdxx where cs=city and qxmc=qx and name like "%街道"  into jd;
select   count(*) from jdxx where cs=city and qxmc=qx and name like "%乡"  into x;
select   count(*) from jdxx where cs=city and qxmc=qx and name like "%镇"  into z;   
select   count(*) from jdxx where cs=city and qxmc=qx and name not like "%镇"  and name not like "%街道" and name not like "%乡" into qt;     
insert into dqtj values(city,qx,x,jd,z,qt);
FETCH dq INTO city,qx;
END while;
CLOSE dq;
END
||
DELIMITER ;
call tjdq("安徽省");
select * from dqtj;