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

数据库基础与实践实验报告实验五存储过程和触发器班级：惠普测试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）循环语句的使用。

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的功能，既可以传入数据，也可以传出数据。

实验五存储过程和触发器的定义和使用实验内容：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提供支持。

MySQL中的触发器与存储过程使用方法MySQL是一种开源的关系型数据库管理系统，广泛应用于各种类型的应用程序中。

在MySQL中，触发器和存储过程是两个非常重要的特性，它们能够帮助我们更好地管理和处理数据。

本文将探讨MySQL中的触发器和存储过程的使用方法，并介绍一些实际应用的案例。

一、触发器的概念与使用方法1. 触发器的概念触发器是一种与表相关联的特殊类型的存储过程，它在表中发生特定事件时被自动执行。

这些特定事件可以是INSERT、UPDATE或DELETE操作。

通过使用触发器，我们可以在数据发生变化时自动执行一些操作，如数据验证、数据更新等。

2. 创建触发器要创建一个触发器，我们需要使用CREATE TRIGGER语句。

其基本语法如下：CREATE TRIGGER trigger_name{BEFORE | AFTER} {INSERT | UPDATE | DELETE}ON table_nameFOR EACH ROWtrigger_body其中，trigger_name是触发器的名称，可以自定义；BEFORE或AFTER用于指定触发器是在操作之前还是之后执行；INSERT、UPDATE或DELETE用于指定触发器要触发的事件；table_name是触发器所属的表名；trigger_body是触发器的具体操作。

3. 触发器的具体应用触发器在数据库管理中有很多实际应用场景。

比如，可以使用触发器来实现数据完整性约束，通过在INSERT、UPDATE或DELETE操作之前进行数据验证，确保数据的准确性。

另外，触发器还可以用来自动更新一些计算字段，或者将一些操作日志写入其他表。

二、存储过程的概念与使用方法1. 存储过程的概念存储过程是一组在数据库服务器上预先编译过的SQL语句，它们按照特定的顺序组合在一起，形成一个可执行的过程。

存储过程类似于程序中的函数，可以接受参数、执行一系列SQL语句，并返回结果。

存储过程和触发器存储过程和触发器是SQL Server的数据库对象。

使用存储过程可以提高应用程序的效率。

触发器可以大大增强应用程序的健壮性、数据库的可恢复性和可管理性。

一、存储过程存储过程是一组T-SQL语句，它们只需编译一次，以后即可多次执行。

存储过程是在SQL Server中定义的子过程，是数据库对象之一。

存储过程可以执行范围很宽的各种操作与业务功能。

比如可以插入、更新或删除表中的数据。

通过传递参数值，存储过程可以判断是选择数据还是执行其他操作。

由于存储过程可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；存储过程是包含用于在数据库中执行操作（包括调用其他过程）的编程语句。

此外存储过程可以向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。

故SQL Server 中的存储过程与其他语言中的过程（有时也称：函数）类似。

可以使用 T-SQL EXECUTE 语句来运行存储过程。

存储过程作为 SQL Server 数据库系统中很重要的概念之一，合理的使用存储过程，可以有效地提高程序的性能；并且将商业逻辑封装在数据库系统中的存储过程中，可以大大提高整个软件系统的维护性。

当商业逻辑发生了改变的时候，不再需要修改并编译客户端的应用程序以及重新分发它们到为数众多的用户手中，只需要修改位于服务器端的实现相应商业逻辑的存储过程即可。

使用 SQL Server 创建应用程序时，T-SQL编程语言是应用程序和 SQL Server 数据库之间的主要编程接口。

使用T-SQL程序时，可用两种方法存储和执行程序；一种是将程序存储在本地，然后创建向SQL Server发送命令并处理结果的应用程序；另一种是将程序作为存储过程存储在SQL Server中，然后创建执行过程并处理结果的应用程序。

在SQL Server中使用存储过程而不使用存储在客户端计算机本地的T-SQL程序，原因在于存储过程具有以下的好处：✓存储过程已在服务器注册。