存储过程,触发器和函数实验
计算机软件及应用数据库函数存储过程触发器数据库安全的实验
实验十函数一、实验目的与要求⏹理解什么是标量函数、内嵌表值函数及多语句表值函数。
⏹熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调用。
⏹基表如下所示：⏹二、实验内容和步骤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）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。
实验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.删除两个刚刚建立触发器
以上内容请书写实验报告
余下时间请完成数据库的预备试验。
存储过程、函数与触发器操作答案
《存储过程、函数与触发器操作》实验一、实验目的与要求1、掌握存储过程的使用。
2、掌握函数的使用。
3、掌握触发器操作。
三、实验内容一、存储过程1、在“教务管理系统”数据库中创建一个名为ProcStudentInfo的存储过程，它返回学生的学号、姓名、性别、班级编号、年级和籍贯信息。
CREATE PROCEDURE Proc_StudentInfoASSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息2、用EXECUTE执行Proc_StudentInfo存储过程。
EXECUTE Proc_StudentInfo3、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息。
CREATE PROCEDURE Proc_GetClassStudent1@ClassID varchar(14)ASSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息WHERE 班级编号=@ClassID4、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息，默认班级编号为'20031340000102' 。
CREATE PROCEDURE Proc_GetClassStudent2@ClassID varchar(14)= '20031340000102'ASSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息WHERE 班级编号=@ClassIDEXECUTE Proc_GetClassStudent2 '20031340000103'5、创建一个返回执行代码为100的存储过程。
CREATE PROCEDURE Proc_GetClassStudent4@ClassID varchar(14)ASBEGINSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息WHERE 班级编号=@ClassIDRETURN 100END6、执行存储过程Proc_GetClassStudent2和Proc_GetClassStudent4，并定义两个个变量存储执行返回代码。
实验五存储过程和触发器（计科）
实验五存储过程和触发器（计科）实验五：触发器和存储过程一、实验目的：理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过程的编写，并初步掌握什么情况下使用事务。
二、预习要求：存储过程和触发器的相关概念，事务的相关概念，编写相应的SQL语句。
三、实验内容：有一个小型的图书管理数据库，包含的表为：bookstore（bookid，bookname，bookauthor，purchasedate，state）；--图书库存表borrowcard（cardid，ownername）；--借书证表borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表写一个存储过程，实现借书操作，要求有事务处理。
（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。
（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。
（3）要求用触发器实现表的完整性控制。
四、完成情况（附上设计的SQL语句）。
------触发器和存储过程------触发器和存储过程CREATE DATABASE BOOK1gouse BOOK1CREATE TABLE BOOKSTORE(Bookid nvarchar(10),Bookname nvarchar(10),Bookauthor nvarchar(10),purchasedate datetime,state Nvarchar(10),primary key(Bookid))Create table Borrowcard(Cardid int,ownernamenvarchar(10),primarykey(Cardid))Create table Borrowlog(Cardid int,Bookid nvarchar(10),borrowdate datetime,returndate datetime ,primary key(Cardid,Bookid))insert into Borrowcard values(12,'wyb')insert into Borrowcard values(123,'wyb')insert into Borrowcard values(1,'wyb')insert into Bookstore values(1,'数据库','王珊','2012-04-23','存在') insert into Bookstore values(2,'数据结构','珊','2012-11-23','存在') insert into Bookstore values(3,'数据结构','珊','2011-1-23','存在') insert into Bookstore values(11,'数据库','王珊','2009-10-23','存在') insert into Bookstore values(12,'数据结构','珊','2001-11-23','存在') insert into Bookstore values(13,'数据结构','珊','2013-12-23','存在')----借书存储过程create proc borrow1@bookid nvarchar(10),@cardid intasbegin transactioninsert into Borrowlog(Cardid,Bookid,borrowdate,returndate) values(@cardid,@bookid,getdate(),null)if exists(select*from bookstore,Borrowcard where bookid=@bookid and state='存在'and cardid=@cardid) beginupdate bookstore set state='不存在'where bookid=@bookid commit transactionendelsebeginif exists(select*from bookstore where bookid=@bookid and state='不存在')Print'不存在该书'if not exists(select*from borrowcard,bookstore where cardid=@cardid) Print'没有此用户'rollback transactionEndexec borrow1'3',12exec borrow1'3',123exec borrow1'2',123drop proc borrow1----删除存储过程Create trigger insert_borrowlogon Borrowlog after insertasdeclare@borrowdate datetime,@returndate datetimeselect@borrowdate=borrowdate,@returndate=returndate from insertedif(@borrowdate>@returndate)print'借书时间不可晚于还书时间'rollbackselect*from bookstoreinsert into Borrowlog values(12,'1','2013-06-06','2012-05-5')五、思考题：如何通过系统的设置实现类似的功能，而不需触发器？答：通过定义存储过程或者设置外键约束等方法。
存储过程和触发器(实验报告)
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创建触发器的方法和步骤；
实训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、掌握创建触发器的方法和步骤。
4、掌握触发器的使用方法。
实验内容、要求以教学管理系统为例，有Student、SC和Course表，根据数据库的完整性要求，自己设计一个存储过程和触发器，当删除SC表的某条记录时，在屏幕上给出提示，当删除该条记录后，若没有学生选择此门课时，要求将Course表中对应的记录删除。
实验环境Microsoft Word, SQL Server 2000环境。
实验过程一、存储过程的设计1、有关概念存储过程是由SQL语句及控制流语句组成的集合。
调用一个存储过程，可以一次性地执行过程中的所有语句。
从这一点来说，它类似于程序。
存储过程由用户建立，它作为数据库的一个成分，存在于数据库中。
存储过程类似VFP中的过程（函数、子程序），它可以接受参数，也可以返回参数。
存储过程可以被客户端、其他存储过程或触发器调用。
以SP_为前缀的存储过程是SQL提供的系统存储过程; 以XP_为前缀的存储过程是扩展的存储过程; 关联到表上的存储过程称为触发式存储过程。
2、设计存储过程使用SQL语句创建存储过程（或使用企业管理器创建存储过程）。
3、执行存储过程对存储在服务器上的存储过程，使用EXECUTE命令执行它。
4、操作存储过程查看、修改、删除存储过程。
二、触发器的设计触发器是一种特殊的存储过程, 用它来控制关联的表。
1、设计一个触发器使用SQL语句创建触发器（使用企业管理器创建触发器）。
2、查看、修改和删除触发器实验六图书管理系统设计及SQL编程实验目的初步掌握数据库系统的开发过程实验内容1.根据数据库设计的基本思想，设计出图书管理系统的概念结构（用E-R图表示）。
2.根据E-R图进行数据库的逻辑设计。
3.在逻辑设计的基础上，进行数据库的物理设计。
4.给出各设计阶段的完整文档（E-R图、逻辑模型、数据字典）。
数据库原理 实验四 存储过程与触发器、函数
《数据库原理》实验报告题目：实验四 存储过程与触发器、函数 学号： 成 绩班级： 计算12 日期：2014.05 姓名： 指导老师：林颖贤一、实验目的：1、掌握创建存储过程的方法和步骤；2、掌握创建触发器的方法和步骤；3、掌握用户自定义函数的类型及使用方法。
二、实验使用环境：Windows 7 x64、SQL Server 2005三、实验内容与完成情况：1、增加一张库存表Inventoy,包括：商品编号、价格、库存数量、入库时间（默认值为系统时间）。
create table Inventory (Goo_no char (8) not null, Inv_num int null, Inv_date datetime not null, primary key (Goo_no ,Inv_date ));ALTER TABLE Inventory ADD CONSTRAINT Inv_datedefault getdate () for Inv_date ;select * from Inventory2、从Purchase (进货表)和Sell(销售表)中备份空记录表: PurchaseBak 和Sell1Bak 。
if (not exists(select name from sysobjects where name ='PurchaseBak')) ( select * into PurchaseBak from Purchase where (1=0))if (not exists(select name from sysobjects where name ='SellBak')) ( select * into SellBak from Sellwhere (1=0))3、创建一个触发器。
向进货表中插入一条记录时，这个触发器都将更新库存表。
如果库存有该类商品时，那么该商品的进价即为两次进价的平均值（因为每次的进价可能会不相同），库存量为原有库存加该次进货数量；（算法为：（库存商品进价*库存量+进货价*进货量）/（库存量+进货量）；如果没有该商品，则插入到库存表中。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
存储过程、触发器和用户自定义函数实验实验内容一
练习教材中存储过程、触发器和用户自定义函数的例子。
教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。
实验内容二
针对附件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 student_course.tcid课程号,ame课程名,COUNT(student_course.sno)选修人数,max(student_course.score)最高分,min(student_course.score)最低
分,avg(student_course.score)平均分
from student_course,course
where student_course.tcid=o
group by student_course.tcid,ame
)
实验数据库说明
教学活动数据库包括student、course和study三个基本表，三个基本表的结构说明和数据如下：
（1）学生表(student)
学生表的结构
列名数据类型长度是否允许为空值字段说明
说明：sno为主键，age的范围为15~35之间，sex只能为“男”或“女”。
学生表的记录
（2）课程表(course)
课程表的结构
说明：cno为主键。
课程表的记录
（3）选课表(study)
选课表的结构
说明：sno和cno为主键，sno为外键（参照student表的sno），cno为外键（参照course 表的cno），score的范围为0~100之间。
选课表的记录。