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

任务描述

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

任务要求

建立存储过程

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

调用过程

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

例如，以长沙市和岳麓区为参数，调用过程显示结果如下
乡 街道 镇 其他
1 9 1 0

相关知识

建立存储过程

命令格式：

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 列名]
计算并显示表达式的值

---

开始你的任务吧，祝你成功！

代码参考

 use province;
 #代码开始
 #定义过程
delimiter //
create procedure dqxx(in city varchar(10) , in district varchar(10))
begin
declare x int;
declare z int;
declare jd int;
declare qt int;
select count(*) from jdxx where name like '%乡' and cs =city and qxmc=district into x;
 #调用过程
select count(*) into jd from jdxx where name like '%街道' and cs=city and qxmc=district;
select count(*) into z from jdxx where name like '%镇' and cs=city and qxmc=district;
select count(*) into qt from jdxx where name not like '%乡' and name not like '%街道' and name not like '%镇' and cs=city and qxmc=district;
select x as '乡',jd as '街道' , z as '镇' ,qt as '其他';
end 
//
delimiter ;
call dqxx('长沙市','开福区');
call dqxx('厦门市','同安区');

 #代码结束

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

任务描述

本关任务：
销售数据库有工作人员、销售单数据表
工作人员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多分支语句

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

---

开始你的任务吧，祝你成功！

参考代码

 use sale;
 #代码开始
 #定义过程
delimiter $
create procedure ygyj(in nf int,in yf int,in xm varchar(10) ,out pj  varchar(10))
begin
declare yj real;

select sum(sjfk) from xsd,gzry where xsd.gyh=gzry.gyh and year(xsrq)=nf and month(xsrq)=yf and gyxm=xm into yj;
case
when yj is null then set pj='无业绩';
when yj <5000 then set pj='不达标';
when yj <10000 then set pj='达标';
else set pj='优秀';
end case;

end 
$
delimiter ;

 #调用过程
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;

第3关：建立和调用存储函数

任务描述

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

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

任务要求

建立存储过程

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

调用过程

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

相关知识

存储函数定义

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

CREATE FUNCTION func_name ( [func_parameter] )
Returns <返回参数类型>
BEGIN    
函数体
return 返回值
END

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

调用存储函数

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

---

开始你的任务吧，祝你成功！

参考代码

use sale;
#代码开始
delimiter $$
create function gkjb(nf int, xm varchar(10))
    returns varchar(10)
DETERMINISTIC
begin
    declare jg int;
    declare pj varchar(10);
    select sum(sjfk)
    from xsd join gk on gk.hyh = xsd.hyh 
    where name = xm and year(xsrq) = nf into jg;
        case
        when isnull(jg) then
        set pj = "非会员";
        when jg < 5000 then
        set pj = "一般会员";
        when jg < 10000 then
        set pj = "vip";
        else set pj = "超级vip";
        end case;
    return pj;
end $$
delimiter ;
select name 姓名, gkjb(2015, name) 等级 from gk;
#代码结束

第4关：修改多个数据表的存储过程

任务描述

本关任务：
图书管理数据库有读者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

---

开始你的任务吧，祝你成功！

参考代码

use library;
#代码开始
delimiter $$
create procedure hs(in sh varchar(8), in dzbh varchar(3), in rq date, out zt varchar(12))
begin
    declare jywh int;
    select count(*) from borrow where txm=sh and dzzh=dzbh and isnull(hsrq) into jywh;
    if jywh=0 then
        set zt = "没有该借阅";
    else
        update borrow set hsrq=rq where dzzh=dzbh and txm=sh and isnull(hsrq);
        update book set zk=1 where txm=sh;
        set zt = "还书成功";
    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;

第5关：使用游标的存储过程

任务描述

本关任务：
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将指针指向下一条结果
变量必须在声明游标前定义

遍历游标

---

开始你的任务吧，祝你成功！

代码参考

 use province;
delimiter $$
 #代码开始
create procedure  tjdq(in sfm varchar(10)) 
begin 
    declare n,m,x1,jd1,z1,qt1 int;
	declare flag int default 1;
    declare cs1,qxmc1 char(30)  charset  utf8;
    declare  rs cursor  for
    select distinct cs,qxmc from jdxx where sf=sfm; /* 同一个省份中同一个市有很多不同的区和街道，当有很多相同的值时要取distinct */
    declare continue handler for not found set flag = 0;
    DELETE FROM dqtj;
	open  rs; /*按循环将数据插入表中。*/
    fetch rs into cs1,qxmc1;
    while flag=1 do
        select count(*) from jdxx  where cs=cs1 and qxmc=qxmc1 and name like '%乡' into x1;
        select count(*) from jdxx  where cs=cs1 and qxmc=qxmc1 and name like '%街道' into jd1;
        select count(*) from jdxx  where cs=cs1 and qxmc=qxmc1 and name like  '%镇' into z1;
        select count(*) from jdxx  where cs=cs1 and qxmc=qxmc1 and name not like '%乡' and name not like '%街道' and name not like '%镇' into qt1;
        insert into dqtj(cs,qxmc,x,jd,z,qt) values(cs1,qxmc1,x1,jd1,z1,qt1);
        fetch rs into cs1,qxmc1;
    end while;
    close rs;
end $$
delimiter ;
call tjdq('安徽省');
 #代码结束
 select * from dqtj;