mysql 存储过程和触发器综合例题

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.20-log : Database - lianxi
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`lianxi` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `lianxi`;

/*Table structure for table `rizhi` */

DROP TABLE IF EXISTS `rizhi`;

CREATE TABLE `rizhi` (
  `sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志编号',
  `biao` varchar(100) DEFAULT NULL COMMENT '修改了那个表',
  `stime` datetime DEFAULT NULL COMMENT '时间',
  `leixing` varchar(100) DEFAULT NULL COMMENT '执行了什么',
  `new` varchar(100) DEFAULT NULL COMMENT '修改之前的存款',
  `old` varchar(100) DEFAULT NULL COMMENT '修改之后的存款',
  `yinhangid` varchar(100) DEFAULT NULL COMMENT '银行ID',
  `username` varchar(100) DEFAULT NULL COMMENT '谁使用了',
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

/*Data for the table `rizhi` */

insert  into `rizhi`(`sid`,`biao`,`stime`,`leixing`,`new`,`old`,`yinhangid`,`username`) values (1,'zhanghu','2018-01-22 14:49:02','insert','1002',NULL,NULL,'root@'),(2,'zhanghu','2018-01-22 14:49:24','insert','1003',NULL,NULL,'root@'),(3,'zhanghu','2018-01-22 15:08:41','insert','1004',NULL,NULL,'root@'),(4,'zhanghu','2018-01-22 15:53:10','insert',NULL,NULL,'1005','root@'),(5,'zhanghu','2018-01-22 15:55:21','insert',NULL,NULL,'新增用户1006','root@'),(6,'zhanghu','2018-01-22 16:05:27','insert','1458','1335','1000','root@'),(7,'zhanghu','2018-01-22 16:05:28','insert','1581','1458','1000','root@'),(8,'zhanghu','2018-01-22 16:05:28','insert','1704','1581','1000','root@'),(9,'zhanghu','2018-01-22 16:05:28','insert','1827','1704','1000','root@');

/*Table structure for table `zhanghu` */

DROP TABLE IF EXISTS `zhanghu`;

CREATE TABLE `zhanghu` (
  `sid` int(11) NOT NULL AUTO_INCREMENT COMMENT '账户编号',
  `sname` varchar(100) DEFAULT NULL COMMENT '用户名字',
  `yhid` int(11) DEFAULT NULL COMMENT '用户ID',
  `mima` int(11) DEFAULT NULL COMMENT '用户密码',
  `dianhua` int(11) DEFAULT NULL COMMENT '电话',
  `yue` int(11) DEFAULT NULL COMMENT '用户余额',
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

/*Data for the table `zhanghu` */

insert  into `zhanghu`(`sid`,`sname`,`yhid`,`mima`,`dianhua`,`yue`) values (13,'11111aa',1000,123,123,1827),(14,'111111aa',1001,123,123,13),(15,'1111111aa',1002,123,123,13),(16,'111111561aa',1003,123,123,13),(17,'1121',1004,123,123,13),(18,'111111561aa1',1005,123,123,13),(19,'1111115611aa1',1006,123,123,13);

/* Trigger structure for table `zhanghu` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `insert` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'skip-grants user'@'skip-grants host' */ /*!50003 TRIGGER `insert` BEFORE INSERT ON `zhanghu` FOR EACH ROW 
    BEGIN
    insert into rizhi(`biao`,`stime`,`leixing`,`yinhangid`,`username`)
    values('zhanghu',sysdate(),'insert',concat('新增用户',new.`yhid`),user());
    END */$$


DELIMITER ;

/* Trigger structure for table `zhanghu` */

DELIMITER $$

/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `cunqu` */$$

/*!50003 CREATE */ /*!50017 DEFINER = 'skip-grants user'@'skip-grants host' */ /*!50003 TRIGGER `cunqu` AFTER UPDATE ON `zhanghu` FOR EACH ROW 
    BEGIN
    INSERT INTO rizhi(`biao`,`stime`,`leixing`,`new`,`old`,`yinhangid`,`username`)
    VALUES('zhanghu',SYSDATE(),'insert',new.`yue`,old.`yue`,CONCAT(new.`yhid`),USER());
    END */$$


DELIMITER ;

/* Procedure structure for procedure `chongzhi` */

/*!50003 DROP PROCEDURE IF EXISTS  `chongzhi` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `chongzhi`(in id int,in mima int ,in jine int)
BEGIN
    DECLARE cs1 int;
    DECLARE cs2 INT;
    /*判断一下银行ID存在不*/
    if exists(select * from zhanghu where id=yhid)then
    select z.mima into cs1 from zhanghu z where id=z.yhid;
    /*判断密码正确不*/
    if cs1=mima then
      update zhanghu set yue=jine+yue where id=yhid;
      select yue into cs2 from zhanghu where  id=yhid;
      select concat('充值成功,余额为：',cs2);
    else
      select '密码错误';
    end if;
    else
    select '没有此用户，请先创建账户';
    end if;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `chuangjian` */

/*!50003 DROP PROCEDURE IF EXISTS  `chuangjian` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `chuangjian`(in `kname` varchar(100),in `mima` int,in `dianhua` int,in `yue` int)
BEGIN
        DECLARE sc int;
        /*先判断账户是否重复*/
        if EXISTS(select sname from zhanghu z where kname=z.sname) then
        select '此用户以存在';         
        else
        if exists(SELECT yhid FROM zhanghu z ORDER BY yhid DESC LIMIT 1 ) then
        select yhid into sc from zhanghu z order by yhid desc limit 1;
        set sc=sc+1;
        INSERT INTO `zhanghu`(`sname`,`yhid`,`mima`,`dianhua`,`yue`)VALUES(kname,sc,mima,dianhua,yue);
        select concat('银行ID为：',sc);
        else
        /*定制初始*/
        insert into `zhanghu`(`sname`,`yhid`,`mima`,`dianhua`,`yue`)values(kname,1000,mima,dianhua,yue);
        SELECT CONCAT('银行ID为：',1000);
        END IF;
        end if;
    END */$$
DELIMITER ;

/* Procedure structure for procedure `quqian` */

/*!50003 DROP PROCEDURE IF EXISTS  `quqian` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `quqian`(IN id INT,IN mima INT ,IN jine INT)
BEGIN
    DECLARE cs1 INT;
    DECLARE cs2 INT;
    DECLARE cs3 INT;
    DECLARE cs4 INT;
    /*先判断银行ID存在不*/
    IF EXISTS(SELECT * FROM zhanghu WHERE id=yhid)THEN
    SELECT z.mima INTO cs1 FROM zhanghu z WHERE id=z.yhid;
    /*判断密码正确不*/
    IF cs1=mima THEN
      /*取钱金额大小*/
      if jine>1 then
      SELECT c.yue INTO cs3 FROM zhanghu c WHERE id=c.yhid;
      IF cs3>jine THEN
      UPDATE zhanghu SET yue=yue-jine WHERE id=yhid;
      SELECT yue INTO cs4 FROM zhanghu WHERE  id=yhid;
      SELECT CONCAT('充值成功,余额为：',cs4);
      ELSE
      SELECT '余额不足';
      END IF;
      else
      select '取钱金额过少，最低2块';
      end if;
    ELSE
      SELECT '密码错误';
    END IF;
    ELSE
    SELECT '没有此用户，请先创建账户';
    END IF;
    END */$$
DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;