数据库原理实验五——触发器与存储过程(函数)

最新推荐文章于 2023-03-23 21:41:47 发布

Alfred young

最新推荐文章于 2023-03-23 21:41:47 发布

阅读量4.4k

点赞数 5

分类专栏：数据库文章标签：数据库 sql database

本文链接：https://blog.csdn.net/qq_45757722/article/details/121022134

版权

数据库专栏收录该内容

7 篇文章 11 订阅

订阅专栏

实验目的

熟练掌握存储过程/函数的创建和执行方法。
熟练掌握触发器的创建和执行方法。

实验内容

针对SPJ_MNG数据库，创建并执行如下存储过程。（共计40分）

(1) 创建一个没有参数的存储过程—jsearch1。该存储过程的作用是：当执行该存储过程时，将返回S表中北京供应商的所有信息。调用该存储过程并验证结果。（5分）

DELIMITER $$
CREATE PROCEDURE jsearch1()
BEGIN
	SELECT * 
    FROM S
    WHERE CITY = '北京';
END $$

验证

CALL jsearch1;

(2) 创建带输入参数的存储过程—jsearch2。该存储过程的作用是：当输入一个供应商所在城市名时（如北京），将返回该供应商的所有信息。调用存储过程并验证结果。（5分）

DELIMITER $$
CREATE PROCEDURE jsearch2(in city_in CHAR(45))
BEGIN
	SELECT * 
    FROM S
    WHERE CITY = city_in;
END $$

验证

CALL jsearch2('北京');

(3) 创建带输入参数和输出参数的存储过程(函数)—jsearch3。该存储过程的作用是：当输入一个供应商编号(输入参数SNO)时，将返回该供应商的名称(输出参数SNAME)。调用存储过程并验证结果。（5分）

DELIMITER $$
CREATE PROCEDURE jsearch3(in sno_in CHAR(45),out sname_out CHAR(45))
BEGIN
	SELECT SNAME
    INTO sname_out
    FROM S
    WHERE SNO = sno_in;
END $$

验证

CALL jsearch3('S1',@name);
SELECT @name;

(4) 创建一个使用游标的存储过程jsearch4，创建成功后调用该存储过程并验证结果。该存储过程的功能：当输入一个工程号JNO时，将返回供应该工程零件的所有供应商的名称(SNAME)，这些供应商名拼接成一个字符串，并用逗号’,’分隔。

DELIMITER $$
CREATE PROCEDURE jsearch4(in jno_in CHAR(45))
BEGIN
	DECLARE TEMP CHAR(100) DEFAULT '''';
    DECLARE NAME CHAR(10);
    DECLARE DONE INT DEFAULT 0;
	DECLARE C_NAME CURSOR FOR
		SELECT DISTINCT S.SNAME
		FROM S,SPJ
		WHERE S.SNO = SPJ.SNO
		AND SPJ.JNO = jno_in;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1;
    OPEN C_NAME;
    FETCH C_NAME INTO NAME;
    REPEAT
		SET TEMP =CONCAT(TEMP,NAME);
        SET TEMP =CONCAT(TEMP,',');
        FETCH C_NAME INTO NAME;
    UNTIL DONE
    END REPEAT;
    SET TEMP =CONCAT(TEMP,'''');
    CLOSE C_NAME;
    SELECT TEMP;
END $$

验证

CALL jsearch4('J2');

(5) 查看存储过程jsearch1和jsearch2的文本信息。（5分）

show create procedure jsearch1 \G;
show create procedure jsearch2 \G;

(6) 查看存储过程jsearch1基本状态信息。（5分）

show procedure status like 'jsearch1';

(7) 删除jsearch1存储过程。（5分）

DROP PROCEDURE jsearch1;

针对Student数据库，创建和执行如下的触发器：（共计40分）

(1) 删除SC表上的外键约束，针对SC表创建一个名为insert_s的INSERT触发器。该触发器的功能：当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入C表中没有的数据”，并阻止该数据的插入；如果插入的sno值不是S表中的sno的已有值，则提示用户“不能插入S表中没有的数据”，并阻止该数据的插入。触发器创建成功之后，向SC表插入记录，验证触发器是否正常工作。(5分)

DELIMITER $$
CREATE TRIGGER insert_s BEFORE INSERT ON SC FOR EACH ROW
BEGIN
	IF NEW.SNO NOT IN (SELECT DISTINCT(SNO) FROM S)
		THEN SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = '不能插入S表没有的数据';
	END IF;
    IF NEW.CNO NOT IN (SELECT DISTINCT(CNO) FROM S)
		THEN SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = '不能插入C表没有的数据';
	END IF;
END $$

验证

INSERT 
INTO SC(ID,SNO,CNO)
VALUES(10000,'100001','5');

INSERT 
INTO SC(ID,SNO,CNO)
VALUES(10000,'100','7');

(2) 为S表创建一个名为dele_s1的DELETE触发器，该触发器的作用是提示用户“不能删除该表中的数据”并阻止用户删除S表中的数据。触发器创建成功之后，删除S表中记录，验证触发器是否正常工作。(5分)

DELIMITER $$
CREATE TRIGGER dele_s1 BEFORE DELETE ON S FOR EACH ROW
BEGIN
	SIGNAL SQLSTATE '03100'
    SET MESSAGE_TEXT = '不能删除该表中的数据';
END $$

验证：

DELETE 
FROM s
WHERE SNO = '10001';

(3) 为S表创建一个名为dele_s2的DELETE触发器，该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作（确认S表和SC表相关数据是否被删除）。(5分)

DELIMITER $$
CREATE TRIGGER dele_s2 AFTER DELETE ON S FOR EACH ROW
BEGIN
	SET @SNO = OLD.SNO;
    DELETE FROM SC WHERE SNO = @SNO;
END $$

验证：

DELETE 
FROM S
WHERE SNO = '10001';

(4) 为S表创建一个名为update_s的UPDATE触发器，该触发器的作用是禁止更新S表中“sdept”字段的内容（更新不成功，并且提示“不能更新sdept字段”）。触发器创建成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常工作。(5分)

DELIMITER $$
CREATE TRIGGER update_s BEFORE UPDATE ON S FOR EACH ROW
BEGIN
	IF NEW.SDEPT != OLD.SDEPT
    THEN 
		SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '不能更新sdept字段';
	END IF;
END $$

验证：

UPDATE S
SET SDEPT = 'CS'
WHERE SNO = '1';

(5) 删除update_s触发器。(5分)

DROP TRIGGER update_s;

(6) 自己设计一个before update触发器和after update触发器，比较before 和after触发器的区别。(5分)
before触发器

DELIMITER $$
CREATE TRIGGER update_s_before BEFORE UPDATE ON S FOR EACH ROW
BEGIN
	IF NEW.SDEPT = 'CS'
    THEN
		SET NEW.SDEPT = 'IS';
    END IF;
		
END $$

after触发器

DELIMITER $$
CREATE TRIGGER update_s_after after UPDATE ON S FOR EACH ROW
BEGIN
	IF NEW.SDEPT = 'CS'
    THEN
		SET NEW.SDEPT = 'IS';
    END IF;
		
END $$

after触发器在事务执行之后生效，上述代码中将学生表的系修改为IS，对于一个已经执行了update操作的事务，再将其修改回来系统会报错。故这种情况下的after触发器不能被创建。
验证：

UPDATE S
SET SDEPT = 'CS'
WHERE SNO = '1';

(7) 创建一个新的课程成绩统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade)，分别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。利用触发器实现如下的功能：当SC表中插入、删除或者更新某个人的成绩时，自动更新表CAvgGrade。注意SC表中的grade为NULL时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是grade为0即考试成绩为0时，计算平均成绩需要计算该学生成绩。(10分)

首先设计一个公共的存储过程，这个存储过程可供三个触发器调用，实现数据的插入CAvgGrade表中。

DELIMITER $$
CREATE  PROCEDURE update_cavggrade(in in_cno INT)
BEGIN
	DECLARE e_snum INT;
	DECLARE e_examsnum INT;
	DECLARE e_avggrade INT;
	SELECT COUNT(*) FROM sc WHERE cno = in_cno
	INTO e_snum;
	SELECT count(*) FROM sc WHERE cno = in_cno AND grade is not null
	INTO e_examsnum;
	SELECT AVG(grade) FROM sc WHERE cno = in_cno AND grade is not null
	INTO e_avggrade;
	IF in_cno IN (SELECT DISTINCT cno FROM cavggrade)
		THEN UPDATE cavggrade SET Snum = e_snum,examSNum = e_examsnum,avgGrade = e_avggrade
		WHERE cno = in_cno;
	ELSE 
		INSERT INTO cavggrade VALUES(in_cno,e_snum,e_examsnum,e_avggrade);
	end IF;
END$$

插入触发器：

DELIMITER $$
CREATE TRIGGER insert_sc AFTER INSERT ON SC FOR EACH ROW
BEGIN
	call update_cavggrade(new.cno);
END$$

更新触发器：

DELIMITER $$
CREATE TRIGGER update_sc AFTER UPDATE ON SC FOR EACH ROW
BEGIN
	call update_cavggrade(new.cno);
    call update_cavggrade(old.cno);
END$$

删除触发器

DELIMITER $$
CREATE TRIGGER delete_sc AFTER DELETE ON SC FOR EACH ROW
BEGIN
    call update_cavggrade(old.cno);
END$

创建一个员工表employee(eID, eName, salary)，假设该表中有1000条员工数据，完成下列要求。（总计20分，每题10分）

(1) 为了协助本题自动生成1000条员工数据，创建一个自动生成员工ID的用户自定义函数generateEID。其中员工ID要求是一个8位的数字，前四位表示插入员工数据的当前年份，后四位按照从0001到9999的顺序增长。例如2015年插入的第一条数据是20150001，所有1000条员工ID分别是20150001-20151000。调用该函数实现自动插入1000条数据。（注意插入数据的时候员工姓名可以为任意值，工资是2000-5000之间的数字）

DELIMITER $$
CREATE  PROCEDURE generateEID(in n int)
BEGIN
	SET @x = 0;
	REPEAT 
		SET @x = @x + 1;
		INSERT INTO employee VALUES(20210000 + @x,concat('ename',cast(@x as char(10))),floor(2000 + 3000*rand()));
	UNTIL @x >= n
	END REPEAT;
END$$

执行

1.	call generateEID(1000);

(2) 该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整。工资增长规则如下：

工资在3000元以下，每月涨300元；
工资在3000-4000元之间，每月涨200元；
工资大于或者等于4000元，每月涨50元；

DELIMITER $$
		CREATE PROCEDURE salary_increase ()
		BEGIN
			DECLARE e_salary int;
			DECLARE e_eid int;
  		  	DECLARE done int;
			DECLARE c_name cursor for 
				SELECT eID,salary from employee;
			DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
    		OPEN c_name;
			FETCH c_name INTO e_eid,e_salary;  
			REPEAT
				CASE 
        		WHEN e_salary < 3000
					THEN UPDATE employee SET salary = salary + 300 WHERE eid = e_eid;
				WHEN e_salary >= 3000 and e_salary < 4000
					THEN UPDATE employee SET salary = salary + 200 where eid = e_eid;
				ELSE	
					UPDATE employee SET salary = salary + 50 where eid = e_eid;
				END CASE;	
				FETCH c_name INTO e_eid,e_salary;
			UNTIL done
			END REPEAT;	
			CLOSE c_name;		
END$$

执行

call salary_increase();

Alfred young

关注

5
点赞
踩
37

收藏

觉得还不错? 一键收藏
打赏
0
评论
数据库原理实验五——触发器与存储过程(函数)

实验目的熟练掌握存储过程/函数的创建和执行方法。熟练掌握触发器的创建和执行方法。实验内容针对SPJ_MNG数据库，创建并执行如下存储过程。（共计40分）(1) 创建一个没有参数的存储过程—jsearch1。该存储过程的作用是：当执行该存储过程时，将返回S表中北京供应商的所有信息。调用该存储过程并验证结果。（5分）DELIMITER $$CREATE PROCEDURE jsearch1()BEGIN SELECT * FROM S WHERE CITY = '北京
复制链接

扫一扫