数据库实验九 存储过程（新）

use province;
 #代码开始
 #定义过程
DELIMITER //
CREATE PROCEDURE qxxx(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 qxxx("长沙市","开福区");
 call qxxx("厦门市","同安区");

 #代码结束

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;

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 as 姓名,gkjb(2015,name) as 等级 from gk;

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;

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("安徽省");