实验五存储过程和触发器地定义和使用
触发器与存储过程的建立与使用
whereemp_name='刘刚')
命令已成功完成。
10．
createproceduresearchman
as
begin
declaresearchcursorfor
selectemp_no
fromemployee
whereemp_namelike'李%'andtitle='职员'
go
命令已成功完成。
execproaddemployee'E0022','罗刚','M','业务','经理','12/7/2009','01/01/1987',15000,'都匀市'
select*
fromemployee
8．
createprocedureproesc
as
begin
declare@emp_nochar(5),@emp_namechar(10),@ccust_idchar(5),@cust_namechar(20),@scust_idchar(5),@sale_idchar(5),@tot_amtnumeric(9,2)
begin
fetchnextfromcur_cus_new
select"fecth_status值"=@@FETCH_STATUS
end
select"cursor读取状态"=cursor_status('variable','@cur_cus_new')
3．
createtriggerdelelteemployee
存储过程和触发器(数据库实验5)
数据库基础与实践实验报告实验五存储过程和触发器班级：惠普测试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数据库中，存储过程和触发器是两种非常有用的特性。
它们可以帮助开发人员简化数据库管理和提高应用程序的效率。
本文将深入探讨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语句。
第5讲 存储过程和触发器
在调用存储过程时，有两种数据传递的方法： （1）在传递参数时，使实参的顺序和定义时的参数顺序一 致。 （2）可以采用“参数=值”的形式，此时，各个参数的顺 序可以任意排列。
【例4-66】在School中创建一个存储过程proc_UpdateGrade,修 改指定学号（StudentCode)和课程代号(CourseCode)的成绩 (Grade)。
（3） 执行存储过程
存储过程定义后，可以通过EXECTE语句来执行该存储过程。 语法格式： EXEC[UTE] 存储过程名[@形参=]实参值 说明： 1)“@形参”是创建存储过程时定义的形参名； 2)“实参值”是输入参数的值； 3)“@变量”表示用来保存参数或者返回参数的变量； OUTPUT表示指定参数为返回参数； 4)DEFAULT表示使用该参数的默认值作为实参。 5)如果有多个参数，可以依次按以上参数定义规则列出， 用逗号“，”隔开。
说明： “形式参数”名称必须符合标识符规则；OUTPUT表示该参 数是可以返回的，可将信息返回调用者；如果有多个参数， 可以依次按以上参数定义规则列出，用逗号“，”隔开。
CREATE PROC proc_Course AS
SELECT * FROM T_Course
调用创建的存储过程proc_Course的语句为：
EXEC proc_Course
【例2】在School中创建一个的存储过程 proc_Se程带一个输入参数) CREATE PROC proc_SearchStudent @stcode char(8) AS SELECT StudentName, CourseName FROM T_Student JOIN T_Grade JOIN T_Course ON T_Grade.CourseCode=T_Course.CourseCode ON T_Student.StudentCode=T_Grade.StudentCode WHERE T_Student.StudentCode=@stcode
实验五 存储过程和触发器的使用
实验五存储过程和触发器的使用【目的要求】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是一种广泛使用的开源关系型数据库管理系统，它提供了存储过程和触发器功能，可以帮助开发者更好地管理和操作数据库。
本文将介绍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、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验五存储过程和触发器的定义和使用一、实验目的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）循环语句的使用。
①用WHILE语句编程计算1－100之间所有能被3整除的数的个数及总和。
DECLARE S SMALLINT,I SMALLINT,NUMS SMALLINTSET S=0SET I=1SET NUMS=0WHILE(I<=100)BEGINIF(I%3=0)BEGINSET S=S+ISET NUMS=NUMS+1ENDSET I=I+1ENDPRINT SPRINT NUMS②利用GOTO语句求出从1加到5的总和。
DECLARE S SMALLINT,I SMALLINTSET I=1SET S=0BEG:IF(I<=5)BEGINSET S=S+ISET I=I+1GOTO BEGENDPRINT S（4）W AITFOR语句的使用。
①等待5秒后执行查询Student学生信息waitfor delay '00:00:05'select*from Student②等到晚上10:20执行存储过程update_all_stats 。
BEGINWAITFOR TIME'10:20'EXECUTE update_all_statsEND（5）CASE语句的使用。
①查询Student的SNO，SEX，如果SEX为“1”则输出“男”，如果为“0”输出“女”。
SELECT学号,性别=CASE性别WHEN'1'THEN'男'WHEN'0'THEN'女'ENDFROM Student②从SC表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。
SELECT学号,课程号,成绩=CASEWHEN成绩IS NULL THEN'未考'WHEN成绩<60 THEN'不及格'WHEN成绩>=60 AND成绩<70 THEN'及格'WHEN成绩>=70 AND成绩<90 THEN'良好'WHEN成绩>=90 THEN'优秀'ENDFROM SC3、存储过程的的使用。
在上面学生成绩库中完成如下操作：（1）创建如下不带参数的简单存储过程：查询成绩在60至80分之间的学生的学号和课程号。
create procedure SCListasselect学号,课程号from SCwhere成绩>60 and成绩<80（2）分别创建如下的带输入参数的存储过程：①根据用户输入的学号，删除该学生选课成绩记录；create procedure studentqk1(学号char(10))asdelete成绩from SCwhere学号=学号②向SC表中插入数据。
create procedure sccharu(学号char(10),课程号char(10),成绩int)asinsert into SC values(学号,课程号,成绩)（3）创建如下的带输出参数的存储过程，并写出一个执行该过程的例子：根据用户输入的学号，课程号，输出其成绩。
create procedure grade(学号char(10),课程号char(10)output,成绩int output)asselect成绩=成绩from SCwhere学号=学号and课程号=课程号declare成绩intexecute grade '020101','101',成绩outputselect'成绩'=成绩（4）创建存储过程proc_t1，要现如下功能：输入专业名称，产生该专业学生的选课情况列表，其中包括专业、学号、、课程号、课程名、成绩、学分等。
并调用此存储过程，显示“信息管理”专业学生的选课情况列表。
create procedure proc_t1 (专业char(10))asselect Student.学号,Student.专业,Student.,Course.课程号,Course.课程名,Course.学分,成绩from Student,Course,SCwhere Student.学号=SC.学号and Course.课程号=SC.课程号and Student.专业=专业exec proc_t1 '信息管理'（5）对学生成绩库中已创建的存储过程proc_t1进行修改，要现如下功能：输入专业名称，产生该专业所有男生的选课情况列表，其中包括专业、学号、、课程号、课程名、成绩、学分等。
并调用修改后的存储过程，显示“信息管理”专业男生的选课情况列表。
alter procedure proc_t1 (专业char(10))asselect Student.学号,Student.专业,Student.,Course.课程号,Course.课程名,Course.学分,成绩from Student,Course,SCwhere Student.学号=SC.学号and Course.课程号=SC.课程号and Student.专业=专业and性别=1exec proc_t1 '信息管理'（6）删除学生成绩库中的存储过程proc_t2。
drop procedure proc_t1（7）编写存储过程，要现如下功能：输入课程名称，产生该课程各分数段及其相应人数的成绩分布情况统计。
create procedure scqk (课程名char(10))asbeginselect课程名,sum (case when成绩between 0 and 59 then 1 else 0 end ) as'不及格',sum (case when成绩between 60 and 69 then 1 else 0 end ) as'60-69',sum (case when成绩between 70 and 89 then 1 else 0 end ) as'70-89',sum (case when成绩between 90 and 100 then 1 else 0 end ) as'90-100' from SC,Coursewhere SC.课程号=Course.课程号and课程名=课程名group by课程名endexec scqk '计算机原理'4、触发器的使用。
在上面学生成绩库中完成如下操作：（1）DML触发器的使用①创建一个INSERT触发器tri_sc_insert，当向sc表中添加数据时，如果添加的数据与Student表中的数据不匹配（没有对应的学号），则将此数据删除。
create trigger tri_sc_insert on SCfor insertasbegindeclare bh char(6)select bh=inserted.学号from insertedif not exists(select学号from Student where Student.学号=bh)delete SC where学号=bhend②创建一个UPDATE触发器tri_sc_upd，用来防止用户修改SC表的成绩。
create trigger tri_sc_upd on SCfor updateasif update(成绩)beginprint'修改失败。
'raiserror('不能修改SC表的成绩',16,10)rollback transactionend③创建一个级联修改触发器trigger_1，即当修改学生课程表Course中的某门课的课程号时，对应学生的选课表SC中的课程号也作修改create trigger trigger_1 on Coursefor updateasif update(课程号)begin update SCset课程号=(select课程号from inserted)from SC,deletedwhere SC.课程号=deleted.课程号end④建立一个级联删除触发器trigger_2，实现，即当删除表Student中的记录时，自动删除表sc中对应选课记录。