第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 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 乡, jd 街道, z 镇, qt 其他;
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多分支语句
CASEWHEN <条件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 jg int;
select sum(sjfk) from xsd join gzry on gzry.gyh = xsd.gyh where year(xsrq) = nf and month(xsrq) = yf and gyxm = xm into jg;
case
when isnull(jg) then set pj = "无业绩";
when jg < 5000 then set pj = "不达标";
when jg < 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和月份yf和姓名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
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;
1983
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
