数据库的函数、存储过程、触发器详解

Dawn黎明开始

已于 2024-05-14 22:25:53 修改

阅读量847

点赞数 11

文章标签： mysql 数据库

于 2024-05-14 12:34:04 首次发布

本文链接：https://blog.csdn.net/2301_80760873/article/details/138807983

版权

一.变量

1.系统变量 global session

2.用户自定义变量

1.不用提前声明，使用时直接'' @变量名 ''

2.set @name='lisi';

set 字段名 into @name from 表名

3.局部变量

1.需要用declare声明

2.declare 变量名类型

3.set 变量名=值

二.触发器

.1函数

DELIMITER $$

USE `shujuku`$$

DROP FUNCTION IF EXISTS `PysxCx`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `PysxCx`(zw VARCHAR(20)) RETURNS VARCHAR(20) CHARSET utf8mb4
    READS SQL DATA
BEGIN
       SET @pysx='';
       SET @l=CHAR_LENGTH(zw);
       SET @i=1;
    WHILE (@i<=@l) DO
          SELECT jp INTO @jp FROM hzpyb  WHERE hz=SUBSTR(zw,@i,1);
          SET @pysx=CONCAT(@pysx,@jp);
          SET @i=@i+1;
           END WHILE;    
    RETURN @pysx;
    END$$

DELIMITER ;

触发器1

DELIMITER $$

USE `shujuku`$$

DROP TRIGGER `Update_mcsx_before_insert_goods`$$

CREATE
    TRIGGER `Update_mcsx_before_insert_goods` BEFORE INSERT ON `goods` 
    FOR EACH ROW BEGIN   
     SET new.abbreviations=pysxcx(new.gname);    
END;
$$

DELIMITER ;

触发器2

DELIMITER $$

USE `shujuku`$$

DROP TRIGGER `Update_mcsx_before_update_goods_gname`$$

CREATE
    TRIGGER `Update_mcsx_before_update_goods_gname` BEFORE UPDATE ON `goods` 
    FOR EACH ROW BEGIN    
      IF new.gname<>old.gname THEN      
            SET new.abbreviations=pysxcx(new.gname);     
      END IF;    
END;
$$

DELIMITER ;

2超市购买物品

goodswh

DELIMITER $$

USE `shujuku`$$

DROP PROCEDURE IF EXISTS `goodswh`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `goodswh`(gid INT,gname VARCHAR(20),
    unit VARCHAR(20),barcode VARCHAR(20),
    retail_Price DECIMAL(10,2),
    promotional_Price DECIMAL(10,2),
    STATUS TINYINT
    )
BEGIN
       IF gid=0 THEN
        INSERT INTO goods
        (gname,unit,barcode,retail_Price,promotional_Price,STATUS) 
        VALUES (gname,unit,barcode,retail_Price,promotional_Price,STATUS);
        ELSEIF gid>0 THEN
        UPDATE  goods SET goods.gname=gname, goods.unit=unit, 
         goods.barcode=barcode, goods.retail_Price=retail_Price, goods.promotional_Price=promotional_Price, 
                 goods.STATUS=STATUS 
        WHERE  goods.gid=gid;
    ELSE
        DELETE FROM goods WHERE  goods.gid=-gid;
    END IF;
    END$$

DELIMITER ;

goodscx

DELIMITER $$

USE `shujuku`$$

DROP PROCEDURE IF EXISTS `goodscx`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `goodscx`(gid INT)
BEGIN
  IF gid=0 THEN
    SELECT * FROM goods;
  ELSE
    SELECT * FROM goods  WHERE goods.gid=gid;
  END IF;
END$$

DELIMITER ;

删除触发器

DELIMITER $$

USE `shujuku`$$

DROP TRIGGER /*!50032 IF EXISTS */ `delete_from_stock_after_delete_goods`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `delete_from_stock_after_delete_goods` BEFORE DELETE ON `goods` 
    FOR EACH ROW BEGIN	
    DELETE FROM stock WHERE gid=old.gid;   
END;
$$

DELIMITER ;

11.超市存储过程

DELIMITER $$

USE `shujuku`$$

DROP PROCEDURE IF EXISTS `Xsjlcp_new`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Xsjlcp_new`(cashierid INT, uid INT, payment TINYINT, xsmx VARCHAR(8000))
BEGIN
    DECLARE amount_money DECIMAL(18,3);
    DECLARE actual_money DECIMAL(18,3);
    DECLARE discount_money DECIMAL(18,3);
    
    -- 如果存在名为 lsb 的表，则删除它
    DROP TEMPORARY TABLE IF EXISTS lsb;
    
    -- 创建临时表 lsb，包含 gid 和 xssl 两个字段
    CREATE TEMPORARY TABLE lsb (gid INT, xssl NUMERIC(18,3));
    
    -- 使用 WHILE 循环解析 xsmx 中的数据并插入到 lsb 表中
    WHILE (xsmx > '') DO
        SET @k = POSITION(',' IN xsmx);
        SET @gid = LEFT(xsmx, @k - 1);
        SET xsmx = SUBSTR(xsmx, @k + 1);
        SET @k = POSITION(',' IN xsmx);
        SET @xssl = LEFT(xsmx, @k - 1);
        INSERT INTO lsb(gid, xssl) VALUES(@gid, @xssl);
        SET xsmx = SUBSTR(xsmx, @k + 1);
    END WHILE;
    
    -- 计算总金额和实际金额
    SELECT SUM(xssl * retail_Price) INTO amount_money FROM lsb, goods WHERE lsb.gid = goods.gid;
    SELECT SUM(xssl * promotional_Price) INTO actual_money FROM lsb, goods WHERE lsb.gid = goods.gid;
    SET discount_money = amount_money - actual_money;
    
    -- 插入收银记录到 cashaccount 表
    INSERT INTO cashaccount(cashierid, uid, payment, amount_money, actual_money, discount_money)
    VALUES (cashierid, uid, payment, amount_money, actual_money, discount_money);
    
    -- 插入销售明细到 cashaccount_detail 表
    INSERT INTO cashaccount_detail(cashaccountid, gid, salesquantity, retailprice, promotionalprice)
    SELECT LAST_INSERT_ID(), lsb.gid, xssl, retail_Price, promotional_Price FROM lsb, goods WHERE lsb.gid = goods.gid;
    
    -- 更新库存
    UPDATE stock s JOIN lsb ON s.gid = lsb.gid SET s.quantity = s.quantity - lsb.xssl;
    
    -- 如果 uid 大于等于 10000，则更新用户的卡余额和积分
    IF uid >= 10000 THEN
        UPDATE `user` SET card_balance = card_balance - actual_money, points = points + FLOOR(actual_money)
        WHERE uid = uid;
    END IF;
    
    -- 删除临时表 lsb
    DROP TEMPORARY TABLE IF EXISTS lsb;
    
END$$

DELIMITER ;

3.xkglxt

1.实现学生表的插入，需要判断当前学号学生是否存在

DELIMITER $$

USE `myspj`$$

DROP PROCEDURE IF EXISTS `insert_Student`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_Student`(IN Studentid INT ,IN Studentname VARCHAR(10))
BEGIN
    DECLARE StudentExists INT ;
    SELECT COUNT(*) INTO StudentExists
    FROM Student WHERE id=Studentid;
    
    IF StudentExists > 0 THEN 
       SELECT '学号存在，学生信息插入失败' ;
    ELSE
       INSERT INTO Student(id,`name`) VALUES (Studentid,Studentname);
       SELECT '学生信息插入成功';
    END IF;
    END$$

DELIMITER ;

2.实现选课表的插入，需要判断当前课程id和学生id是否存在

DELIMITER $$

USE `myspj`$$

DROP PROCEDURE IF EXISTS `insert_xuanke`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_xuanke`(IN _xid INT, IN _id INT, IN _kid INT, IN _chengji INT)
BEGIN    
   DECLARE studentExists INT DEFAULT 0;    
   DECLARE courseExists INT DEFAULT 0;    
    
   
   SELECT COUNT(*) INTO studentExists   
   FROM Student WHERE id = _id;    
     
   IF studentExists = 0 THEN    
      SELECT '学生id不存在' AS result;  
     
   END IF;  
     
    
   SELECT COUNT(*) INTO courseExists   
   FROM kecheng WHERE kid = _kid;  
     
   IF courseExists = 0 THEN    
      SELECT '课程id不存在' AS result;  
      
   ELSE    
      -- 检查学生是否已经选了这门课程  
      IF NOT EXISTS (SELECT 1 FROM xuanke WHERE kid = _kid AND id = _id) THEN    
         INSERT INTO xuanke (xid, id, kid, chengji) VALUES (_xid, _id, _kid, _chengji);    
         SELECT '插入成功' AS result;  
      ELSE    
         SELECT '学生已选该课程';    
      END IF;    
   END IF;  
END$$

DELIMITER ;

4.订单延期

将顾客表上余额不足1000元的，将订单日期延后10天

UPDATE orders
SET DATE = DATE_ADD(DATE, INTERVAL 10 DAY)
WHERE orders.gid IN (
    SELECT gid
    FROM customers
    WHERE balance < 1000
);

将商品表中没有顾客订购的商品信息删除

DELETE FROM goods
WHERE goods.`sid` NOT IN (
SELECT DISTINCT orders.`sid` FROM orders
)

5.建立索引

 CREATE INDEX sname ON `student`(`studentname`);
 CREATE INDEX qanswer  ON `questionbank`(`questionAnswer`(1000));

6.当插入、删除试题时，修改章节表的试题数量

DELIMITER $$

CREATE
    TRIGGER `network_course`.`insert_from_questionbank_after_add_chapter` 
    AFTER UPDATE
    ON `network_course`.`questionbank`
    FOR EACH ROW BEGIN
	UPDATE chapter 
	SET `questioncount`=`questioncount`+1
	WHERE `chapterid`=new.`chapterid`;
    END$$

DELIMITER ;

7.写出插入一条学生数据的存储过程

DELIMITER $$

CREATE
    PROCEDURE `network_course`.`insert_one_student`(
	IN snum VARCHAR(20),
	IN sname VARCHAR(20),
	IN spw VARCHAR(20),
	IN cid INT
    )
    BEGIN
	DECLARE count1 INT;
	SELECT COUNT(*) INTO count1
	FROM student 
	WHERE `studentnum`=snum;
	IF count1=0 THEN
	INSERT INTO student(`studentnum`,`studentname`,`studentpw`,`classid`)
	VALUES(snum,sname,spw,cid);
	END IF;
    END$$

DELIMITER ;

8.写出一次插入多条学生数据的存储过程

学生数据的格式为：

“学号1，姓名1，密码1，班级号1；学号2，姓名2，密码2，班级号2；”

例如：

"201451080102,张三,NULL,1;201451080103,李四,NULL,1;"

CALL insert_many_student("221451080608,张1,NULL,1;221451080609,李1,NULL,1;");

CAST( 字符串 AS SIGNED); 将字符串类型的数字转化为数字类型的数字

在MySQL中，MID()函数用于返回一个字符串中从指定位置开始的指定长度的子字符串。其语法为：
MID(str, start, length)
str为要从中提取子字符串的原始字符串。
start为要开始提取子字符串的位置（位置从1开始）。
length为要提取的子字符串的长度
在MySQL中，LOCATE()函数用于在一个字符串中查找子字符串，并返回子字符串第一次出现的位置。其语法为：
LOCATE(substring, string, start)
substring为要查找的子字符串。
string为要在其中查找子字符串的原始字符串。
start为可选参数，表示从原始字符串的某个位置开始查找。如果不指定start，则默认从字符串的第一个字符开始查找。

DELIMITER $$

CREATE
    PROCEDURE `network_course`.`insert_many_student`(
    IN stustr VARCHAR(5000)
    )
    
    BEGIN
	DECLARE str1 VARCHAR(200);
	DECLARE num0 VARCHAR(20);
	DECLARE name0 VARCHAR(20);
	DECLARE PW0 VARCHAR(20);
	DECLARE classid0 INT ;
	DECLARE flag INT;
	WHILE stustr>' ' DO
		SET str1 = MID(stustr,1,LOCATE(';',stustr)-1);
		SET num0 = MID(str1,1,LOCATE(',',str1)-1);
		SET str1 = MID(str1,LOCATE(',',str1)+1);
		SET name0 = MID(str1,1,LOCATE(',',str1)-1);
		SET str1 = MID(str1,LOCATE(',',str1)+1);
		SET pw0 = MID(str1,1,LOCATE(',',str1)-1);
		SET classid0 =CAST(MID(str1,LOCATE(',',str1)+1) AS SIGNED);
		CALL `insert_one_student`(num0,name0,pw0,classid0);
		SET stustr= MID(stustr,LOCATE(';',stustr)+1);
	END WHILE ;
    END$$

DELIMITER ;

9.生成学生试卷

输入参数：学生帐号，按照规则生成学生试卷总表以及试卷明细表

DELIMITER $$

USE `network_course`$$

DROP PROCEDURE IF EXISTS `insert_exam`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_exam`(
    IN studentid0 INT
    )
BEGIN
	DECLARE examid0 INT;
	DECLARE fromc0 INT;
	DECLARE toc0 INT;
	DECLARE type0 INT;
	DECLARE  diff0 INT;
	DECLARE c0 INT;
	DECLARE done INT DEFAULT FALSE;
	DECLARE cur CURSOR FOR SELECT `fromchapterid`,`tochapterid`,`type`, `difficulty`, `count`
	FROM exam_rule ORDER BY exam_ruleid;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done= TRUE;
	SELECT COUNT(*)INTO c0 FROM exam WHERE studentid=studentid0;
	IF c0=0 THEN
		INSERT INTO exam(studentid,state,score)VALUES(studentid0,0,0);
		SELECT LAST_INSERT_ID()INTO examid0;
		OPEN cur;
		FETCH cur INTO fromc0,toc0,type0,diff0,c0;
		WHILE(NOT done) DO 
			INSERT INTO exam_detail(examid,questionid,answer ,grade)
			SELECT examid0 AS examid,questionid,'' AS answer,0 AS grade
			FROM questionbank WHERE chapterid>=fromc0 AND chapterid<=toc0
			AND `type`=type0 AND difficulty=diff0 ORDER BY RAND() LIMIT c0;
			FETCH cur INTO fromc0,toc0,type0,diff0,c0;
		END WHILE;
		CLOSE cur;
	END IF;
    END$$

DELIMITER ;

10.生成班级试卷

输入参数：班级ID，按照规则生成班级所有学生的试卷总表以及试卷明细表

DELIMITER $$

CREATE
    PROCEDURE `network_course`.`insert_exam0`(
	IN classid0 INT
    )
    
    BEGIN
	DECLARE examid0 INT;
	DECLARE stuid0 INT;
	DECLARE done INT DEFAULT FALSE;
	DECLARE	cur CURSOR FOR 
	SELECT studentid FROM student
	WHERE classid=classid0;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done= TRUE;
	OPEN cur;
	FETCH cur INTO stuid0;
	WHILE(NOT done)DO
		CALL insert_exam (stuid0);
		FETCH cur INTO stuid0;
	END WHILE;
	CLOSE cur;
    END$$

DELIMITER ;