存储过程、触发器和函数实验
计算机软件及应用数据库函数存储过程触发器数据库安全的实验
实验十函数一、实验目的与要求⏹理解什么是标量函数、内嵌表值函数及多语句表值函数。
⏹熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调用。
⏹基表如下所示：⏹二、实验内容和步骤1、函数的定义对于CPXS数据库，定义完成如下功能的函数：（1）据产品名称，查询该产品的相关信息；（函数名为FU_CP）use cpxs1gocreate function FU_CP(@productname char(30))returns tableasreturn(select * from CPXSB right join XSS on CPXSB.客户编号=XSS.客户编号right join CP on CPXSB.产品编号=CP.产品编号where 产品名称=@productname)Gouse cpxs1goselect * from FU_CP('彩色电视机')go（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。
use cpxs1gocreate function FU12_CPXS(@year int ,@quarter int, @cpmc char(10))returns tableasreturn(select 产品名称, sum(数量) as 销售数量, sum(销售额) as 销售总额from cpxsb,cpwhere cpxsb.产品编号=cp.产品编号and 产品名称=@cpmcand datepart(year ,销售日期)=@yearand datepart(quarter,销售日期)=@quarter)Gouse cpxs1gocreate function FU2_SPXS(@year int ,@quarter int ,@cpmc char(10)) returns @xsqk table(产品名称char (10),销售数量int ,销售金额int)asbegin insert into @xsqkselect 产品名称,sum(销售额)as 销售总额,sum(数量)as 销售数量from cpxsb,cpwhere cpxsb.产品编号=cp.产品编号and 产品名称=@cpmcand datepart (year ,销售日期)=@yearand datepart (quarter, 销售日期)=@quartergroup by 产品名称returnendgo（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。
实验五：触发器和存储过程
实验五：触发器和存储过程一．实验目的：理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过程的编写，并初步掌握什么情况下使用事务。
二．实验内容：有一个小型的图书管理数据库，包含的表为：bookstore（bookid，bookname，bookauthor，purchasedate，state）；--图书库存表borrowcard（cardid，ownername）；--借书证表borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表写一个存储过程，实现借书操作，要求有事务处理。
（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。
（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。
（3）要求用触发器实现表的完整性控制。
三、操作与运行1.创建图书数据库：create table bookstore(bookid int not null primary key,bookname char(20),bookauthor char(20),purchasedate datetime,state char(4))create table borrowcard(cardid int not null primary key,ownername char(20))create table borrowlog(cardid int not null,bookid int not null,borrowdate datetime,returndate datetime,primary key(cardid,bookid),---foreign key(cardid)references borrowcard(cardid), ---foreign key(bookid)references bookstore(bookid) )通过以上语句，可以看到数据库中的表建立成功。
实验5 存储过程,函数,触发器操作
实验5 存储过程、函数、触发器操作
已知一个已经建好的数据库factory,现在该数据库上存在三个表
1.职工表worker,其结构为 (职工号 numnber ,姓名 char(8), 性别 char(2) ,
出生日期 date, 党员否 char, 参加工作时间 date , 部门号 number ),其中职工号为主键
2.部门表 department 结构为 ( 部门号 number , 部门名称 varchar(20) ) ,
其中部门号为主键. 通常的部门信息有人事部,市场部,财务处等等
3.职工工资表 salary 其结构为 ( 职工号 number ,姓名 char(10) , 日期
date , 工资 number(10,2) ) . 其中职工号和日期为关键字
在以上的数据库上完成如下操作：
1.创建一个为worker表添加职工记录的存储过程addworker
2.创建一个为给定职工号,查询职工信息及部门名称的存储过程query_worker
3.创建一个为给定职工号,删除worker表中记录的存储过程delete_worker
4.显示存储过程
5.创建一个函数，完成给定职工号返回职工所在部门的名称的函数
6.创建一个函数，完成给定部门号返回该部门的最大年龄的函数
7.在表department上创建一个触发器deaprt_update，当更改部门号时同步更
改worker表中对应的部门号
8.在表worker上创建一个触发器worker_delete，当删除职工记录同步删除对
应职工的工资记录
9.删除两个刚刚建立触发器
以上内容请书写实验报告
余下时间请完成数据库的预备试验。
实验6：存储过程和触发器
序号：云南大学软件学院实验报告课程：数据库原理与实用技术实验学期：2015-2016学年第二学期任课教师：专业：学号：姓名：成绩：实验6 存储过程和触发器存储过程1、使用不带参数的存储过程（1）创建一个存储过程my_proc，查询“学生表”中所有计算机系女生的学号、姓名、性别、年龄和所在院系（2）执行存储过程2、带输入参数的存储过程（1）创建一个存储过程my_procsex，使其能够查询“学生表”中男学生或女学生（输入参数从这考虑）的学号、姓名、性别、年龄和所在院系（2）执行存储过程3、带输入/输出参数的存储过程（1）创建一个存储过程my_procage，使其能够根据学生姓名（输入），查询学生年龄（输出）。
（考虑当学生不存在时给出提示信息）（2）执行存储过程4、返回状态值的存储过程（1）创建一个存储过程my_procstatus，使其能够根据学生姓名，查询学生的选课信息。
（如果没有输入学生姓名，返回状态码55；如果输入的学生姓名不存在，则返回状态码-155）（2）执行存储过程：接收存储过程返回的状态码，如果返回的状态码为55则输出提示信息“没有输入名字！！”；如果返回的状态码为-155，则输出“没找到！！”。
（3）删除存储过程触发器1、使用触发器（1）创建一个触发器trig_update，返回对“学生表”进行更新操作后，被更新的记录条数（2）执行触发器（3）修改触发器trig_update，除返回被更新的记录条数外，再返回学生的所有基本信息2、使用触发器的两个特殊表：插入表（inserted）和删除表（deleted）。
（1）在“学生表”上创建触发器ins_del_sample，在对学生表进行插入、删除或更新操作后，分别从inserted表和deleted表中查询学生学号、姓名、性别、年龄和所在院系。
（请同学们在做删除操作时，注意备份）（2）执行插入、删除和更新操作后返回的表有什么区别？3、使用系统存储过程查看触发器（1）显示触发器trig_update的一般信息（2）显示触发器trig_update的源代码（3）显示“学生表”上所有的依赖关系（4）显示触发器trig_update所引用的对象4、难题（注意inserted表和deleted表的使用，并请自己修改数据表）（1）为“成绩表”创建一个触发器，当向表中插入数据时，如果成绩大于等于60分，该学生就能得到相应的学分，否则，该学生不能得到学分。
存储过程和触发器(实验报告)
WITH ENCRYPTION AS
SELECT*
FROM student_info
WHERE性别='男'
EXEC stu_en
DROP PROCEDURE stu_en
4．使用grade表。
（1）创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。
CREATE PROC stu_g_r @stu_no varchar(8)=NULL,
@stu_score real OUTPUT
AS
SELECT@stu_score=AVG(分数)
FROM grade
WHERE (学号=@stu_no)
（2）执行存储过程stu_g_r，输入学号0002。
DECLARE @score real
WHERE (a.姓名=@stu_name)
EXEC stu_g_p ‘刘卫平’
sp_helptext stu_g_p
3．使用student_info表。
（1）创建一个加密的存储过程stu_en，查询所有男学生的信息。
（2）执行存储过程stu_en，查看返回学生的情况。
（3）使用Transact-SQL语句DROP PROCEDURE删除存储过程stu_en。
（3）掌握通过SQL Server管理平台和Transact-SQL语句Alter procedure修改存储过程的方法；
（4）掌握通过SQL Server管理平台和Transact-SQL语句Drop procedure删除存储过程的方法；
（5）掌握通过SQL Server管理平台和Transact-SQL语句Create trigger创建触发器的方法和步骤；
实验九 存储过程和触发器
实验九存储过程和触发器〖仪器设备〗每位学生一台PC机（标准配制，1G内存），装有SQL Server 2005或者其他DBMS〖目的要求〗1.了解几个常用的系统存储过程。
2.掌握如何使用T-SQL语句创建、执行和重编译存储过程。
3.了解如何使用企业管理器管理存储过程。
4.掌握如何使用T-SQL语句创建触发器。
5.了解如何使用企业管理器创建触发器。
6.掌握AFTER触发器的用法，并了解INSTEAD OF触发器的用法。
〖实验内容〗1.创建存储过程。
(1)创建一个存储过程，查看“2”号课程的选修情况，包括选修该课程的学号、姓名和成绩。
use student_coursegocreate procedure ssc_3asselect student.sno,sname,gradefrom student join sc on student.sno=sc.snowhere o='2'go(2)执行存储过程execute ssc_32.使用输入参数(1)上面所建立的存储过程只能对“2”号课程的选修情况进行查看，要想对所有课程进行随机查看，需要进行参数的传递。
use student_coursegocreate procedure ssc_4@cnumber char(5)asselect student.sno,sname,gradefrom student join sc on student.sno=sc.snowhere o=@cnumbergo(2)按位置传递参数execute ssc_4 '3'(3)通过参数名传递参数execute ssc_4@cnumber='3'3.使用默认参数值(1)执行存储过程ssc_4时，如果没有给出参数，系统会报错。
如果希望不给参数时，能查询所有课程的进修情况，则可以使用默认参数值来实现。
use student_coursegocreate procedure ssc_5@cnumber char(5)=nullasif @cnumber is nullbeginselect student.sno,sname,gradefrom student join sc on student.sno=sc.snoendelsebeginselect student.sno,sname,gradefrom student join sc on student.sno=sc.snowhere o=@cnumberendgo(2)执行下面两条语句，比较执行结果。
实验六-存储过程与触发器
实验六存储过程和触发器１．实验目的(1) 掌握存储过程和触发器的基本概念和功能(2) 掌握创建,管理存储过程的方法(3) 掌握创建,管理触发器的方法２．实验内容及步骤(1) 利用SQL Server Management Studio创建一个存储过程ProcNum,查询每个班级中学生的人数,按班级号升序排序.在查询编辑器的存储过程模板中输入如下创建存储过程的代码并执行.USE teachingGOCREATE PROCEDURE ProcNum ASSELECT classno,COUNT(*)AS number FROM studentGROUP BY classno ORDER BY classno ASCGOEXEC ProcNum(2) 利用Transact-SQL语句创建一个带有参数的存储过程ProcInsert,向score 表插入一条选课记录,并查询该学生的姓名,选课的所有课程名称,平时成绩和期末成绩.<1> 在查询编辑器输入如下创建存储过程的代码并执行.USE teachingGOCREATE PROCEDURE ProcInsert(@sno NCHAR(10),@cno NCHAR(6),@usually NUMERIC(6,2),@final NUMERIC(6,2))ASINSERT INTO score VALUES (@sno,@cno,@usually,@final)SELECT sname,cname,usually,finalFROM student s,course c,score scWHERE s.studentno=sc.studentno and c.courseno=sc.courseno and s.studentno=@sno<2> 调用存储过程ProcInsert,向score表插入一条选课记录.DECLARE@AVERAGE NUMERIC(6,2)EXEC ProcInsert'16135222201','c05103',88,90(3) 利用Transact-SQL语句创建一个存储过程ProcAvg，查询指定班级指定课程的平均分。
实训4：存储过程和触发器
实训4：存储过程和触发器一、实训目的1、了解存储过程、触发器的概念。
2、会运用T-SQL语句创建存储过程、触发器。
2、掌握如何调用存储过程。
二、实训工具及设备1、实训软件：SQL Server。
2、实训设备：安装Windows系统计算机一台。
三、实训预备知识1、存储过程我们曾经学习过C语言的函数，使用函数时需要两步。
第一步：定义函数，它允许包含参数和返回值。
第二步：调用函数。
函数可以反复的调用，它方便了程序的模块化设计，大大提高了执行效率。
存储过程类似于C语言中的函数，它是存储在SQL SERVER服务器中的一组预编译过的SQL语句，当第一次调用以后，就驻留在内存中，以后调用时不必再进行编译，因此它的运行速度比独立运行同样的程序要快。
在SQL SERVER中存储过程分为两类：系统存储过程和用户自定义存储过程。
系统存储过程存储在master数据库中并以sp_为前缀（用户创建存储过程的时候不要以sp_为前缀），在任何数据库中都可以调用系统存储过程。
除了使用系统存储过程，用户还可以创建自己的存储过程。
当创建存储过程时，需要确定存储过程的三个组成部分：所有的输入参数及执行后的输出结果（返回值）；∙返回给调用者的状态值，以指明调用是否成功。
使用T-SQL语句创建存储过程的语法格式如下：CREATE PROCEDUER 存储过程名[@参数1 参数的数据类型][=默认值] [OUTPUT],……[@参数n 参数的数据类型][=默认值] [OUTPUT]ASSQL语句其中：使用OUTPUT选项可将@参数的值返回给调用语句。
（1）创建不带参数的存储过程代码清单4-1：在选课系统（ElectiveSystem）数据库中创建每门选修课的平均分use ElectiveSystemgocreate procedure proc_courseAvgasselect courseName,avg(grade) as '平均成绩'from course,scwhere course.courseNo=sc.courseNogroup by course.courseNamego在查询分析器中调用存储过程proc_courseAvg，结果如下图4-1所示。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
存储过程、触发器和用户自定义函数实验实验内容一
练习教材中存储过程、触发器和用户自定义函数的例子。
教材中的BookSales数据库，在群共享中，文件名为。
实验内容二
针对附件1中的教学活动数据库，完成下面的实验内容。
1、存储过程
（1）创建一个存储过程，该存储过程统计“高等数学”的成绩分布情况，即按照各分数段统计人数。
（2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。
（3）创建一个存储过程，该存储过程将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。
（4）创建一个存储过程，该存储过程有一个参数用来接收学生姓名，该存储过程查询该学生的学号以及选修课程的门数。
（5）创建一个存储过程，该存储过程有两个输入参数用来接收学号和课程号，一个输出参数用于获取相应学号和课程号对应的成绩。
2、触发器
（1）为study表创建一个UPDATE触发器，当更新成绩时，要求更新后的成绩不能低于原来的成绩。
（2）为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。
（3）为course表创建一个INSERT触发器，要求插入的课程记录中任课教师不能为空。
3、用户自定义函数
（1）创建一个返回标量值的用户定义函数RectangleArea：输入矩形的长和宽就能计算矩形的面积。
create function RectangleArea(@a int,@b int)returns int
as
begin
return@a*@b
end
（2）创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。
该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。
调用这个函数，生成相应的报表并给用户浏览。
create function student_table()returns table
as
return(
select课程号,课程名,COUNT选修人数,max最高分,min最低分,avg平均分
from student_course,course
where=
group by,
)
实验数据库说明
教学活动数据库包括student、course和study三个基本表，三个基本表的结构说明和数据如下：
（1）学生表(student)
学生表的结构
列名数据类型长度是否允许为空值字段说明
sno char5NO学号
sname char8NO姓名
age smallint年龄
sex nchar1性别
说明：sno为主键，age的范围为15~35之间，sex只能为“男”或“女”。
学生表的记录
sno sname age sex
（2）课程表(course)
课程表的结构
说明：cno为主键。
课程表的记录
（3）选课表(study)
选课表的结构
说明：sno和cno为主键，sno为外键（参照student表的sno），cno为外键（参照course 表的cno），score的范围为0~100之间。
选课表的记录。