实验五存储过程和触发器地定义和使用

数据库基础与实践实验报告实验五存储过程和触发器班级：惠普测试142学号：**********姓名：***日期：2016-11-141 实验目的：1)掌握SQL进行存储过程创建和调用的方法；2)掌握SQL进行触发器定义的方法，理解触发器的工作原理；3)掌握触发器禁用和重新启用的方法。

2 实验平台：操作系统：Windows xp。

实验环境：SQL Server 2000以上版本。

3 实验内容与步骤利用实验一创建的sch_id数据库完成下列实验内容。

1.创建存储过程JSXX_PROC，调用该存储过程时可显示各任课教师姓名及其所教课程名称。

存储过程定义代码：CREATE PROCEDURE JSXX_PROCASSELECT tn 教师姓名,cn 所教课程FROM T,TC,C WHERE T.tno=TC.tno AND o=o存储过程执行语句与执行结果截图：EXECUTE JSXX_PROC2.创建存储过程XM_PROC，该存储过程可根据输入参数（学生姓名）查询并显示该学生的学号、所学课程名称和成绩；如果没有该姓名学生，则提示“无该姓名的同学”。

存储过程定义代码：CREATE PROCEDURE XM_PROC @sname VARCHAR(100)ASBEGINIF EXISTS(SELECT NULL FROM S WHERE sn=@sname)SELECT S.sno 学号,cn 课程,score 成绩FROM S,SC,C WHERE o=o AND SC.sno=S.sno ANDS.sn=@snameELSEPRINT'无该姓名的同学。

'END运行截图：3.创建存储过程XBNL_PROC，该存储过程可根据输入参数（专业名词，默认值为计算机专业），统计并显示该专业各年龄段男、女生人数。

如果没有该专业，则显示“无此专业”。

存储过程定义代码：CREATE PROCEDURE XBNL_PROC@departName VARCHAR(30)='计算机',@begin INT,@end INTASDECLARE @numOfBoys INTDECLARE @numOfGirls INTDECLARE @d# VARCHAR(3)DECLARE @result VARCHAR(50)BEGINSELECT @d# = dno FROM D WHERE dn=@departNameIF @d# IS NOT NULLBEGINSELECT @numOfBoys =COUNT(sno)FROM S WHERE age BETWEEN @begin AND @end AND dno=@d# AND sex='男'SELECT@numOfGirls =COUNT(sno)FROM S WHERE age BETWEEN@begin AND@end AND dno=@d# AND sex='女'SET @result = @departName+'专业年龄在'+CAST(@begin AS VARCHAR(3))+'-'+CAST(@end AS VARCHAR(3))+'之间的男生有'+CAST(@numOfBoys AS VARCHAR(3))+'人，'+'女生有'+CAST(@numOfGirls AS VARCHAR(3))+'人'ENDELSESET @result='无此专业。

MySQL中的存储过程和触发器在MySQL数据库中，存储过程和触发器是两种非常有用的特性。

它们可以帮助开发人员简化数据库管理和提高应用程序的效率。

本文将深入探讨MySQL中存储过程和触发器的概念、用途以及如何创建和使用。

一、存储过程存储过程是一组预编译SQL语句的集合，可以使用输入参数，输出参数和返回值。

它们可以用于执行常见的数据库操作，如插入、更新和删除数据。

存储过程还可以用于实现业务逻辑，从而减少在应用程序中执行的SQL语句数量。

1.1 存储过程的优点- 提高性能：存储过程经过编译，可以使用缓存以提高查询性能。

- 简化管理：存储过程可以简化复杂的数据库管理操作，如维护数据库结构和数据安全。

- 实现业务逻辑：存储过程可以在数据库中实现业务逻辑，从而减少应用程序代码中执行的SQL语句的数量和复杂性。

1.2 存储过程的创建和使用在MySQL中创建存储过程需要使用CREATE PROCEDURE语句。

以下是一个简单的示例：```CREATE PROCEDURE get_customer(IN customer_id INT)BEGINSELECT * FROM customers WHERE id = customer_id;END;```在执行上述存储过程后，可以使用以下语句查询：```CALL get_customer(1);```1.3 存储过程的注意事项- 存储过程是在数据库服务器上执行的，因此需要一定的安全性考虑。

- 由于存储过程可以在数据库中执行大量计算，因此可能会影响服务器性能。

二、触发器触发器是一种特殊的存储过程，当特定事件发生时自动触发执行。

可以根据需要定义触发器在INSERT、UPDATE或DELETE操作之前或之后执行。

2.1 触发器的优点- 数据完整性：触发器可以防止对数据库中重要数据的误更改或误删除。

- 降低复杂性：使用触发器可以避免在应用程序中编写对数据库的复杂操作。

2.2 触发器的创建和使用在MySQL中创建触发器需要使用CREATE TRIGGER语句。

实验五存储过程和触发器的使用【目的要求】1、了解存储过程的基本概念和类型。

2、了解创建存储过程的T-SQL语句的基本语法。

3、了解查看、执行、修改和删除存储过程的T-SQL命令的用法。

4、了解触发器的基本概念和类型。

5、了解创建触发器的T-SQL语句的基本语法。

6、了解查看、修改和删除存储过程的T-SQL命令的用法。

【实验内容】内容一：存储过程的使用一、数据需求分析存储过程是一种数据库对象，为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用，自动完成需要预先执行的任务。

存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中便于以后调用，提高了代码的执行效率。

二、内容要点分析1、SQL SERVER支持五种类型的存储过程：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。

其中，系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。

系统存储过程定义在系统数据库master中，其前缀是sp_。

本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，不能以sp_为前缀。

2、只能在当前数据库中创建存储过程。

3、创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。

4、创建存储过程的T-SQL语句CREATE PROC[EDURE] 存储过程名称[{ @参数名称数据类型 }] [,…n][WITH{ RECOMPILE|ENCRYPTION }]ASSQL语句序列说明：（1）RECOMPILE表明每次运行该过程时，将其重新编译。

（2）ENCRYPTION表示 SQL SERVER 加密SYSCOMMENTS表中包含CREATE PROCEDURE语句文本的条目。

注：必须将CREATE PROCEDURE语句放在单个批处理中。

实验五存储过程和触发器的定义和使用一、实验目的1、掌握局部变量、全局变量、流程控制语句的使用方法2、了解存储过程的类型和作用，并掌握使用对象资源管理器和Transact-SQL语句创建存储过程的方法及使用方法。

3、理解触发器的特点和作用，并掌握使用Transact-SQL语言创建触发器的方法二、实验内容1. 在学生成绩库中中有如下各表：学生表（Student）create database学生成绩数据库create table Student(学号Char(6)not null,姓名Char(8)not null,性别Bit not null,出生日期smalldatetime,专业Char(10),所在系Char(10),联系电话Char(11)null)课程表（Course）create table Course(课程号Char(3)not null,课程名Char(20)not null,教师Char(10),开课学期Tinyint,学时Tinyint,学分Tinyint not null,)学生选课成绩表（SC）create table SC(学号Char(6)not null,课程号Char(3)not null,成绩Smallint,)对三个表格分别导入，截图如下：2、T-SQL语句中流程控制语句的使用（1）全局变量的使用。

显示到当前日期和时间为止试图登录SQL Server的次数。

select getdate()as'当前的日期和时间',@@connections as'试图登陆的次数'（2）IF语句的使用。

①在Student表中，若存在学号“020205”的学生，则显示该学生的信息，否则插入该学生的记录（020205, 李萍,0, 1983-7-20, 电子商务, 经济系, 88297171）If exists(select*from Student where学号='020205')select*from Student where学号='020205'Elseinsert into Student values('020205','李萍','0','1983-7-20','电子商务','经济系','88297171')②查询杨颖有没有选课，若选了课，则统计其平均成绩，若没有选课，则输出“杨颖没有选课”If exists(select姓名from Student,SC where姓名='杨颖'and Student.学号=SC.学号)select avg(成绩)from SC,Student where Student.姓名='杨颖'and Student.学号=SC.学号Elseprint'杨颖没有选课!'（3）循环语句的使用。

实验存储过程和触发器实验一存储过程的创建和使用【实验目的】1.掌握存储过程的概念，了解存储过程的类型2.掌握创建各种存储过程的方法3.掌握执行存储过程的方法。

4.掌握查看，修改，删除存储过程的方法【实验内容】1.在SSMS图形化界面下创建对表Customers进行插入，修改和删除的3个存储过程:insertCustomers、updateCustomers、deleteCustomers.2.在查询分析其中创建一个存储过程，要求输入作者的姓和名，如果存在，则返回这个作者以及作者所出版的书的信息，否则给出相应的提示信息。

3.用系统存储过程查看刚创建的存储过程的信息。

4.删除存储过程【实验主要步骤】1.在SSMS图形化界面下创建对表Customers进行插入，修改和删除的3个存储过程:insertCustomers、updateCustomers、deleteCustomers.2.在查询分析其中创建一个存储过程，要求输入作者的姓和名，如果存在，则返回这个作者以及作者所出版的书的信息，否则给出相应的提示信息。

3.用系统存储过程查看刚创建的存储过程的信息。

4.删除存储过程实验二触发器的创建和使用【实验目的】1.理解触发器的概念与类型。

2.理解触发器的功能及工作原理。

3.掌握创建、修改和删除触发器的方法。

4.掌握利用触发器维护数据完整性的方法。

【实验内容】触发器是一种实施复杂数据完整性的特殊存储过程，在对表或视图执行UPDATE、INSERT、DELETE语句时自动触发执行，以防止对数据不正确、未授权或不一致的修改。

1.使用T-SQL语句创建一个DELETE触发器，完成的功能是当在Categories表中删除记录时，检测Products表中是否存在相关记录，如果存在，则给出提示信息“不能删除该条记录”；如果不存在，则删除该条记录。

2.基于Sales表创建一个触发器，针对INSERT、DELETE、UPDATE操作。

MySQL存储过程与触发器的使用技巧引言：MySQL是一种广泛使用的开源关系型数据库管理系统，它提供了存储过程和触发器功能，可以帮助开发者更好地管理和操作数据库。

本文将介绍MySQL存储过程和触发器的使用技巧，以帮助读者更好地利用这些功能提升数据库的性能和灵活性。

一、存储过程的概念和作用存储过程是一组预编译的SQL语句，可以在数据库中进行存储和重复执行。

它具有以下的几个主要作用：1. 提高数据库性能：存储过程在执行过程中不需要每次都解析SQL语句，可以减少数据库服务器的负载，提升查询性能。

2. 简化应用程序开发：通过存储过程，可以将复杂的数据操作逻辑封装为一个可供应用程序调用的接口，简化了应用程序的开发过程。

3. 提高安全性：通过存储过程，可以将敏感的数据操作逻辑封装在数据库中，减少了对数据库直接操作时的安全隐患。

二、存储过程的语法和使用方法1. 存储过程的创建：CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] parameter_name parameter_type)BEGIN-- 存储过程的具体实现逻辑END;2. 存储过程的调用：CALL procedure_name(parameter_value);3. 存储过程的注释：DELIMITER // -- 修改定界符CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] parameter_name parameter_type)BEGIN-- 存储过程的具体实现逻辑END //DELIMITER ; -- 恢复定界符4. 存储过程的参数：- IN参数：用于传入存储过程的输入值，仅在存储过程内部使用，不影响原始变量的值。

- OUT参数：用于传出存储过程的输出值，返回给调用者使用。

- INOUT参数：兼具IN和OUT的功能，既可以传入数据，也可以传出数据。

存储过程、触发器和用户自定义函数实验实验内容一练习教材中存储过程、触发器和用户自定义函数的例子。

教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。

实验内容二针对附件1中的教学活动数据库，完成下面的实验内容。

1、存储过程（1）创建一个存储过程，该存储过程统计“高等数学”的成绩分布情况，即按照各分数段统计人数。

执行存储过程：exec countpeople（2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。

执行存储过程：exec avg_score'C602'（3）创建一个存储过程，该存储过程将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

执行存储过程：exec alterscore（4）创建一个存储过程，该存储过程有一个参数用来接收学生姓名，该存储过程查询该学生的学号以及选修课程的门数。

执行存储过程：exec select_courses'李强'（5）创建一个存储过程，该存储过程有两个输入参数用来接收学号和课程号，一个输出参数用于获取相应学号和课程号对应的成绩。

执行存储过程:declare@score smallintexec select_socre'98601','C602',@score outputprint'成绩是'+convert(char(2),@score)+'分'2、触发器（1）为study表创建一个UPDATE触发器，当更新成绩时，要求更新后的成绩不能低于原来的成绩。

创建完触发器尝试进行更新数据：update study set score=60 where sno='98601'and cno='C601'执行完之后查询结果发现成绩仍然是90select score from study where sno='98601'and cno='C601'再更新一个高于90分的成绩则可以成功update study set score=91 where sno='98601'and cno='C601'select score from study where sno='98601'and cno='C601'（2）为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。

实验五存储过程和触发器的定义和使用实验内容：1.在学生成绩数据库中建立表，并相应的导入数据create database学生成绩use学生成绩create table student(学号char(6)not null,姓名char(8)not null,性别bit not null,出生日期smalldatetime,专业名char(10),所在系char(10),联系电话char(11)null)create table course(课程号char(3)not null,课程名char(20)not null,教师char(10),开课学期tinyint,学时tinyint,学分tinyint not null)create table sc(学号char(6)not null,课程号char(3)not null,成绩smallint)2.（1）全局变量（2）if语句①②（3）循环语句①declare@i int,@sum int,@count intset@i=1set@sum=0set@count=0while (@i<100)beginif(@i%3=0)beginset@sum=@sum+@iset@count=@count+1endset@i=@i+1endprint'总和为:'+convert(varchar(10),@sum)print'个数为:'+convert(varchar(10),@count)②declare@i1int,@s1intset@i1=1set@s1=0beg:if(@i1<=5)beginset@s1=@s1+@i1set@i1=@i1+1goto begendprint@s1（4）waitfor 语句①waitfor delay'00:00:05'select*from studentgo②waitfor time'10:20'exec update_all_stats（5）case语句①select学号,性别=case性别when'1'then'男'when'0'then'女'endfrom studentgo②select学号,成绩=casewhen成绩IS NULL then'未考'when成绩<60 then'不及格'when成绩>=60 and成绩<70 then'及格'when成绩>=70 and成绩<90then'良好'when成绩>=90 then'优秀'endfrom sc3．存储过程（1）create proc proc1asselect学号,课程号from scwhere成绩between 60 and 80goexec proc1（2）①create proc proc21(@学号char(10))asdelete成绩from scwhere学号=@学号go②create proc proc22(@学号char(6),@课程号char(3),@成绩smallint)asinsert into scvalues(@学号,@课程号,@成绩)go（3）create proc proc3(@学号char(6),@课程号char(3),@成绩smallint output)asselect@成绩=成绩from scwhere学号=@学号and课程号=@课程号godeclare@成绩smallintexec proc3'020101','101',@成绩output select'成绩'=@成绩（4）create proc proc_t1(@专业名称char(10))asselect专业名,student.学号,姓名,course.课程号,course.课程名,成绩,学分from student,sc,coursewhere专业名=@专业名称and student.学号=sc.学号and sc.课程号=course.课程号goexec proc_t1'信息管理'go（5）alter proc proc_t1(@专业名称char(10))asselect专业名,student.学号,姓名,course.课程号,course.课程名,成绩,学分from student,sc,coursewhere专业名=@专业名称and student.学号=sc.学号and sc.课程号=course.课程号and student.性别=1goexec proc_t1'信息管理'go（6）drop proc proc_t1（7）create proc proc7(@课程名char(10))asbeginselect课程名,sum(case when成绩between 0 and 59 then 1 else 0 end)as'0-60分', sum(case when成绩between 60 and 79 then 1 else 0 end)as'60-79分', sum(case when成绩between 80 and 89 then 1 else 0 end)as'80-89分', sum(case when成绩between 90 and 100 then 1 else 0 end)as'90-100分' from sc,coursewhere课程名=@课程名and sc.课程号=course.课程号group by课程名endgoexec proc7'英语'go4.（1）create trigger tri_sc_insert on scafter insertasbegindeclare@sno char(6)select@sno=inserted.学号from insertedif not exists(select学号from student where学号=@sno) delete scwhere sc.学号=@snoendgo（2）create trigger tri_sc_upd on scafter updateasif UPDATE(成绩)beginprint'修改失败!!'raiserror ('不能修改SC表的成绩',16,10)rollback transactionendgo③create trigger trigger_1on Coursefor updateasif update(课程号)begin update SCset课程号=(select课程号from inserted)from SC,deletedwhere SC.课程号=deleted.课程号end④create trigger trigger_2on Studentfor deleteasbegindelete SCfrom SC,deletedwhere SC.学号=deleted.学号endgo⑤create trigger trigger_3on Studentfor updateasprint'学生表被修改了！'go⑥alter trigger trigger_3on Studentfor updateasdeclare@kch char(6)select@kch=学号from deletedprint'学生表中学号为'+@kch+'的记录被修改了' go⑦drop trigger trigger_3三、思考题1.存储过程的类型有哪些？分别有什么特征？类型：系统存储过程、扩展存储过程、用户定义存储过程特征：（1）系统存储过程：由系统创建、管理和使用；主要存储在master数据库，以sp_ 为前缀；用户只能对其调用，不能修改或删除；通过系统存储过程能够得到系统信息或为数据库系统管理员管理SQL Server提供支持。