精华内容
下载资源
问答
  • 实验内容及程序代码 1sch数据表的表结构如表71所示,sch表的数据如表72 表71 sch 表结构 字段名 数据类型 主键 外键 非空 唯一 自增 id INT(10) 就是 否 就是 就是 否 n ame VARCHAR(50) 否 否 就是 否 否 class ...
  • 数据库实验报告 创建数据库数据库进行查询 T-SQL的创建 存储过程
  • 1. 针对SPJ_MNG数据库,创建并执行如下存储过程。(共计40分) (1) 创建一个没有参数的存储过程—jsearch1。该存储过程的作用是:当执行该存储过程时,将返回S表中北京供应商的所有信息。调用该存储过程并验证结果。...

    一. 实验内容、步骤以及结果

    1. 针对SPJ_MNG数据库,创建并执行如下存储过程。(共计40分)

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

    创建:

    DELIMITER $$
    USE `spj_ming`$$
    CREATE PROCEDURE `jsearch1` ()
    BEGIN
    select * from s
        where city = '北京';
    END$$
    

    验证:

    Call jsearch1;
    

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

    创建:

    DELIMITER $$
    USE `spj_ming`$$
    CREATE PROCEDURE `jsearch2` (in incity char(10))
    BEGIN
    select * from s
    where city = incity;
    END$$
    

    验证:

    call jsearch2('北京')

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

    创建:

    DELIMITER $$
    USE `spj_ming`$$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `jsearch3`(in snoo char(5),out snamee char(10))
    BEGIN
    select sname into snamee from s
        where sno = snoo;
    END$$
    

    验证:

    call jsearch3('s1',@name);
    select @name;
    

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

    创建:

    DELIMITER $$
    USE `spj_ming`$$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `jsearch4`(in in_jno char(10))
    BEGIN
    	declare name char(10);
    	declare done int;
    	declare tmp char(100) default '''';
    	declare c_name cursor for 
    		select distinct sname from s,spj
    		where spj.sno = s.sno and jno = in_jno;
    	declare continue handler for sqlstate '02000' set done = 1; 
        open c_name;
        fetch c_name into name;
    	repeat 
            set tmp = concat (tmp,name);
    		set tmp = concat (tmp,',');
            fetch c_name into name;
    	until done
    	end repeat;
    	set tmp = concat (tmp,'''');
    	close c_name;
    	select tmp;
    END$$
    

    验证:

    call jsearch4('J2');
    

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

    • 提示:show create procedure jsearch1。为方便查看,可在以上命令末尾加\G,使用两列文本形式显示。
    show create procedure jsearch1\G;
     
    show create procedure jsearch2\G;
    

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

    • 提示:show procedure status like 'jsearch1';
    show procedure status like 'jsearch1';
    

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

    drop procedure jsearch1;
    

    2. 针对Student数据库,创建和执行如下的触发器:(共计40分)

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

    创建:

    DELIMITER $$
    USE `student`$$
    CREATE DEFINER=`root`@`localhost` TRIGGER `insert_s` BEFORE INSERT ON `sc` FOR EACH ROW BEGIN
    if new.sno not in (select distinct(sno) from sc) then
    		signal sqlstate '45000'
    		set message_text = '不能插入S表中没有的数据';
    	end if;
    	if new.cno not in (select distinct(cno) from sc) then
    		signal sqlstate '45000'
    		set message_text = '不能插入C表中没有的数据';
            end if;
    END$$
    DELIMITER ;
    

    验证:

    insert into sc values(2004,1,96);//验证s表中没有的数据是否能插入
    insert into sc values(2002,4,90);//验证c表中没有的数据是否能插入
    insert into sc values(2003,1,90);//验证其他数据插入是否正常 
    

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

    创建:

    DELIMITER $$
    USE `student`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `student`.`delete_s1` BEFORE DELETE ON `s` FOR EACH ROW
    BEGIN
    	signal sqlstate '03100'
    	set message_text = '不能删除该表中的数据';
    END$$
    DELIMITER ;
    

    验证:

    delete from s where sno = '2002';
    

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

    创建:

    DELIMITER $$
    USE `student`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `student`.`delete_s2` AFTER DELETE ON `s` FOR EACH ROW
    BEGIN
    	set @sno = old.sno;
    	delete from sc where sno = @sno;
    END$$
    DELIMITER ;
    

    验证:

    delete from s where sno = '2002';
    

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

    创建:

    DELIMITER $$
    USE `student`$$
    CREATE DEFINER=`root`@`localhost` TRIGGER `update_s1` BEFORE UPDATE ON `s` FOR EACH ROW BEGIN
    	if new.sdept != old.sdept
        then
            signal sqlstate '45000'
    		set message_text = '不能更新sdept字段';
            end if;
    END$$
    DELIMITER ;
    

    验证:

    UPDATE s SET sdept = 'ma' WHERE sdept = 'CS';
    

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

    drop trigger update_s1;
    

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

    创建:

    • Before update触发器
    CREATE DEFINER=`root`@`localhost` TRIGGER `sc_BEFORE_UPDATE` BEFORE UPDATE ON `sc` FOR EACH ROW BEGIN
    	call update_cavggrade(new.cno);
        call update_cavggrade(old.cno);
    END
    
    • After update触发器
    CREATE DEFINER=`root`@`localhost` TRIGGER `sc_AFTER_UPDATE` AFTER UPDATE ON `sc` FOR EACH ROW BEGIN
    	call update_cavggrade(new.cno);
        call update_cavggrade(old.cno);
    END
    

    比较:

    • After update:
    UPDATE sc SET cno = 2 WHERE cno = 1 and sno = 2003;
    

    可以看到使用after触发器,数值改变符合要求,cno=1和cno=2,对应的各项数值改变均符合期望。

    • Before update:
    UPDATE sc SET cno = 5 WHERE cno = 2 and sno = 2001;
    

    可以看到使用before触发器,数值改变符合要求,cno=5和cno=2,对应的各项数值均未改变。

    before 和after触发器的区别:before触发器在update执行之前就执行了,所以所期望的更新cavggrade表中数据功能无法实现;after触发器在sc表数据更新后才触发,所以可以满足更新cavggrade表中数据的需求。

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

    • 提示:在MySQL中需要针对插入、更新、删除动作需分别创建3个触发器。可以先设计并实现一个公共的存储过程,然后在3个触发器中调用该存储过程。

    储存过程:

    DELIMITER $$
    USE `student`$$
    CREATE DEFINER=`root`@`localhost` 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$$
    

    触发器:

    • After insert触发器
    DELIMITER $$
    USE `student`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `student`.`sc_AFTER_INSERT` AFTER INSERT ON `sc` FOR EACH ROW
    BEGIN
      call update_cavggrade(new.cno);
    END$$
    DELIMITER ;
    
    • After update触发器
    DELIMITER $$
    USE `student`$$
    CREATE DEFINER=`root`@`localhost` TRIGGER `sc_AFTER_UPDATE` AFTER UPDATE ON `sc` FOR EACH ROW BEGIN
    	call update_cavggrade(new.cno);
        call update_cavggrade(old.cno);
    END$$
    DELIMITER ;
    
    • After delete触发器
    DELIMITER $$
    USE `student`$$
    CREATE DEFINER=`root`@`localhost` TRIGGER `sc_AFTER_DELETE` AFTER DELETE ON `sc` FOR EACH ROW BEGIN
    	call update_cavggrade(old.cno);
    END$$
    DELIMITER ;
    

    数据测试:
    (1)插入一个cavggrade表中已有cno的数据,可看到改变符合要求。

    insert into sc values(2005,1,96);
    

    (2)插入一个cavggrade表中没有cno的数据,可看到新增加了一行且改变符合要求。

    insert into sc values(2003,5,96);
    

    (3)删除一个数据,可看到对应的cno行改变符合要求。

    delete from sc where cno = 5;
    

    (4)改变一个sc表中的grade字段,可看到avggrade列的数值改变符合要求。

    UPDATE sc SET grade = 60 WHERE cno = 1 and sno = 2001;
    

    (5)改变一个sc表中的cno字段,可看到改变前改变后对应的cno行数值改变符合要求。

    UPDATE sc SET cno = 6 WHERE cno = 1 and sno = 2001;
    

    (6)改变一个sc表中的cno字段,使其变到cavggrade表中无其对应的cno,可看到新加入一行,且改变前改变后对应的cno行数值改变符合要求。

    UPDATE sc SET cno = 7 WHERE cno = 6 and sno = 2001;
    

    3. 创建一个员工表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 $$
    USE `shiyan5`$$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `generateEID`(in n int)
    BEGIN
    	set @x = 0;
    	repeat 
    		set @x = @x + 1;
    		insert into employee values(20200000 + @x,concat('ename',cast(@x as char(10))),floor(2000 + 3000*rand()));
    	until @x >= n
    	end repeat;
    END$$
    

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

    • 工资在3000元以下,每月涨300元;
    • 工资在3000-4000元之间,每月涨200元;
    • 工资大于或者等于4000元,每月涨50元;
    DELIMITER $$
    		USE `shiyan5`$$
    		CREATE PROCEDURE `update_salary` ()
    		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$$
    

    二. 实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)

    展开全文
  • 一、实验目的 1.了解存储过程的概念、优点 2.熟练掌握创建存储过程的方法 3.熟练掌握存储过程的调用方法 4.了解触发器的概念、优点 5.掌握触发器的方法和步骤 6.掌握触发器的使用
  • 了解基本的数据存储结构知识,掌握了对数据库的基本存储操作。熟悉sysobjects、Sysindexes、CREATE PROCEDURE创建存储过程存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。

空空如也

空空如也

1 2 3 4 5 ... 9
收藏数 171
精华内容 68
关键字:

数据库存储过程实验报告