mysql 调用存储过程

2017-01-13 19:11:00来源:CSDN作者:sinat_37109615人点击

一/项目描述:

根据authors表查询所有的作者,并在authortitle表中根据作者查询出单独的著书数量和合作著书数量,并存储到count表中


二/sql查询语句

存储过程p7:

DELIMITER $$USE `pubs`$$DROP PROCEDURE IF EXISTS `p7`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`()BEGIN        DECLARE n1 INT DEFAULT 0;    DECLARE aid VARCHAR(20);    DECLARE tyb CURSOR FOR        SELECT au_id FROM AUTHORS;       DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET n1=1;       OPEN tyb;       WHILE n1=0 DO       FETCH tyb INTO aid;       CALL p6(aid);       END WHILE;    CLOSE tyb;    END$$DELIMITER ;


存储过程p6:

DELIMITER $$USE `pubs`$$DROP PROCEDURE IF EXISTS `p6`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `p6`(tid varchar(20))BEGIN    DECLARE n INT DEFAULT 0;    DECLARE tied VARCHAR(20);    declare total_num int DEFAULT 0;    declare single_num int DEFAULT 0;    declare co_num int DEFAULT 0;    declare stid int;    declare  yb cursor for          select title_id ,count(1) as total from titleauthor as t where t.au_id=tid;         -- sql状态为'02000' 则设置n=1         declare continue handler for sqlstate '02000' set n=1;         -- 开启游标         open yb;         -- repeat 循环         repeat          fetch yb into tied,total_num;         select count(1) into stid from titleauthor where title_id=tid;         -- 进行if判断         if stid>1 then         -- 合作作者数量+1         set co_num =co_num+1;         end if;                  until n=1 end repeat;         -- 计算的单个作者的数量         set single_num=total_num-co_num;         -- 插入count表         insert into count values(tid,total_num,co_num,single_num);         -- 关闭游标         close yb;    END$$DELIMITER ;


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台