实验七 存储过程及应用完整版含截图
实验7_存储过程应用初步
《数据库原理及应用》课程实验报告实验名称存储过程应用初步实验序号实验日期班级学号姓名院系计算机科学与信息工程学院专业计算机科学与技术指导教师成绩一、实验目的和要求1. 理解存储过程的作用；2．初步掌握存储过程的创建方法；3．初步掌握存储过程的执行方法。
4. 这个实验所涉及的容不属于《数据库原理及应用》课知识，是扩充知识，主要是为即将到来的课程设计做部分准备工作。
二、实验预习容1．上网查找资料---存储过程是数据库中的一个怎样的对象；主要应用在哪些方面；存储过程的类型等基本知识。
2．创建存储过程的方法，包括有参数和无参数的存储过程。
其中可能涉及到变量、系统函数SQL控制流语句等容。
三、实验项目摘要1．针对“图书-借阅”涉及的5表，自行设计3个存储过程（要有实际意义），执行它们，看结果是否是你所需要的；2．针对“供应”涉及的4表，设计3个存储过程（要有实际意义），执行它们，看结果是否是你所需要的。
说明：设计的存储过程不可以太过简单（如只有一个简单查询），要会设计带参数的存储过程。
同学们可以根据自己对知识的掌握情况，自行决定如何设计。
四、实验结果与分析1．（1）创建(无参数)查询‘文学’类图书中出版时间最早的最早的图书信息的存储过程并执行：1、书写T-SQL语句，创建过程一：CREATE PROCEDURE GUOCHENG_ONE AS/*新建过程一*/SELECT图书名,作者,FROM BOOKS,BOOKCATEGORYWHERE出版日期IN(SELECT MIN(出版日期)FROM BOOKSWHERE BOOKS.种类编号=BOOKCATEGORY.种类编号AND种类名称='文学')/*T-SQL语句*/GOII、执行过程一：EXECUTE GUOCHENG_ONE /*执行该存储过程功能*/(或者：EXEC GUOCHENG_ONE )（2）（单个参数）查询：设置一个存储过程GUOCHENG_TWO，通过指定可变的读者，显示其所借阅的图书的图书名：I、先以一个“具体的”书写T-SQL语句，检验语法错误；无误后以变量代替“具体的”，进而创建过程二：SELECT图书名FROM BOOKSWHERE图书编号IN(SELECT图书编号FROM BORROWWHERE读者编号IN(SELECT读者编号FROM READERSWHERE='于志强'))CREATE PROCEDURE GUOCHENG_TWO NAME varchar(10)AS/*新建过程二*/SELECT图书名FROM BOOKSWHERE图书编号IN (SELECT图书编号FROM BORROWWHERE读者编号IN (SELECT读者编号FROM READERSWHERE=NAME ))GO/*过程功能*/II、执行上述过程，即随意指定读者，查询其借阅图书的图书名：EXECUTE GUOCHENG_TWO NAME='明'/*执行该存储过程功能*/EXECUTE GUOCHENG_TWO NAME='明'/*执行该存储过程功能*/（3）设置（多个参数）插入一条记录的过程；完成后并执行该过程将：“读者编号：1243；：翟萌；种类编号：2；工作单位：交通大学；住址：桃苑小区3-415；联系：83839228；登记日期：2014-04-15；性别：男”这些容添加到到表READERS中：I、创建向表READER插入记录的过程如下：CREATE PROCEDURE GUOCHENG_THREEREADERS_NUMBER nchar(10),NAME varchar(20),RANGE_NUMBER int,COMPANY varchar(50), ADD varchar(100),TEL varchar(20),DATE datetime,SEX char(2)AS/*新建过程三*/INSERT INTO READERS VALUES(READERS_NUMBER,NAME,RANGE_NUMBER ,COMPANY,ADD,TEL,DATE,SEX)/*过程功能*/GOII、执行该过程，将记录插入到表中：EXECUTE GUOCHENG_THREEREADERS_NUMBER='1243',NAME='翟萌',RANGE_NUMBER='2',COMPANY='交通大学', ADD='桃苑小区-415',TEL='83839228',DATE='2014-04-15',SEX='男'/*执行该存储过程功能*/2．（1）创建带输出参数的存储过程，要求求出零件表P中所有蓝色零件重量的平均值,并将结果通过输出变量返回给用户：I、创建具有上述功能的带输出参数的存储过程：CREATE PROCEDURE GUOCHENG_FOUR PJZ INT OUTPUTAS/*创建过程四*/SELECT PJZ=AVG(WEIGHT)FROM零件表PWHERE COLOR ='蓝'/*过程功能*/GOII、执行该过程：DECLARE PJZ INTEXECUTE GUOCHENG_FOUR PJZ OUTPUTPRINT'零件表中所有蓝色零件的平均重量为'+STR(PJZ)GO（2）创建带输入输出参数的存储过程，要求求出“用户指定的供应商”提供给“用户指定的工程”的”所有零件重量”的平均值,并将结果通过输出变量返回给用户：I、创建具有上述功能的带输入输出参数的存储过程：CREATE PROCEDURE GUOCHENG_FIVE GYSN NCHAR(10),GCN NCHAR(10),PJZ INT OUTPUTAS/*创建过程四*/SELECT PJZ=AVG(WEIGHT)FROM零件表PWHERE PN IN(SELECT PNFROM供应关系表SPJWHERE SN IN(SELECT SNFROM供应关系表SPJWHERE SN=GYSN)AND JN IN(SELECT JNFROM供应关系表SPJWHERE JN=GCN))/*过程功能*/GOII、假设用户指定供应商为S4，工程为J4，执行上述过程：DECLARE PJZ INTEXECUTE GUOCHENG_FIVE 'S5','J4',PJZ OUTPUTPRINT'零件的平均值为'+STR(PJZ)GO（3）（无参数）更新“供应商表”用户指定值的供应表信息：I、创建具有上述功能的存储过程：CREATE PROCEDURE GUOCHENG_SIX GYSN NCHAR(10),GYSNAME NCHAR(10),CSM VARCHAR(50)AS/*创建过程六*/UPDA TE供应商表SSET SNAME=GYSNAME,CITY=CSMWHERE SN=GYSN /*过程功能*/GOII、指定S4，修改名称为“SN8”，城市为“土耳其”：附：之前实验报告修改实验五1.1）查询“计算机科学”这类图书中的定价最高的图书书名和定价；SELECT图书名,价格AS定价FROM Books,BookCategoryWHERE价格=(SELECT MAX(价格)FROM BooksWHERE Books.种类编号=BookCategory.种类编号AND种类名称='计算机科学')实验六3．导入“图书借阅”5表的关系图；导入供应商等四表的关系图。
实验七 存储过程及应用完整版含截图
实验七存储过程及应用1．实验目的1、理解存储过程的概念。
2、掌握存储过程的使用方法。
2．实验要求1．建立如下的存储过程（基于前面实验建立的表和插入的数据，并为每个存储过程设计返回的状态值）：（1）按要求设计完成如下功能的存储过程。
①查询平均分数在x到y范围内的学生信息。
说明：该存储过程有两个参数；要求查询的学生信息包括学号、姓名、院系名称和平均分数。
②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。
(2)自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。
（3）在客户端以存储过程和输入SQL语句的方式分别执行相同的查询或操作，比较使用和不使用存储过程的区别。
3、实验过程（1）①查询平均分数在x到y范围内的学生信息create procedure cc711@x smallint,@y smallintasselect学生.学号,学生.姓名,院系.名称,学生.平均成绩from学生join院系on学生.院系=院系.编号where学生.平均成绩between@x and@yEXECUTE cc71160,90②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩create procedure mcc712@xh nchar(8),@kcbh nchar(8),@cj intasupdate选课set成绩=@cj where学号=@xh and课程编号=@kcbhdeclare@pjcj intselect@pjcj=AVG(成绩)from选课where学号=@xhreturn@pjcjdeclare@avg intexecute@avg=mcc7122,5,98print'更新后平均成绩：'+str(@avg,6)(2)自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。
数据库应用基础实验报告7
数据库应用基础实验报告
实验七存储过程创建与应用
班级 2009123 学号 2009 姓名
一、实验目的
使学生理解存储过程的概念, 掌握创建存储过程的使用、执行存储过程和查看、修改、删除存储过程的方法。
二、实验内容
（1）利用SQL Server ManagementStudio创建存储过程book_db, 要求实现如下功能: 在“图书信息系统”数据库中查询书名中包含“数据库”3个字的图书的图书编号和名称；调用存储过程book_db;
（2）利用SQL命令窗口创建名为proc_exp的带参存储过程, 要求实现: 从图书表中返回指定图书编号的图书的所有信息。
调用存储过程proc_exp, 输入图书编号, 显示图书的所有信息。
（3）修改存储过程proc_exp, 为按照图书的书名精确匹配查找图书信息。
（4）删除刚刚创建的book_db和proc_exp两个存储过程。
三、问题讨论
1. 相比在本地存储SQL程序, 使用存储过程有什么优点？
答：1, 允许标准组件式编程；2, 能够实现较快的执行速度；3, 减少网络流量；4, 可以作为一种安全机制来充分利用。
2. 触发器的作用是什么？
答: 触发器的作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。
存储过程的使用 实验报告
USEypp7
--声明四个变量，用于保存输入和输出参数
DECLARE@KECHENGMINGvarchar(20)
DECLARE@AVGCHENGJI1tinyint
DECLARE@MAXCHENGJI1tinyint
DECLARE@MINCHENGJI1tinyint
--为输入参数赋值
SELECT@KECHENGMING='高等数学'
--声明四个变量，用于保存输入和输出参数
DECLARE@KECHENGMINGvarchar(20)
DECLARE@AVGCHENGJI1tinyint
DECLARE@MAXCHENGJI1tinyint
DECLARE@MINCHENGJI1tinyint
--为输入参数赋值
SELECT@KECHENGMING='计算机基础'
1.使用if exists语句，如果存储过程“单科成绩分析”存在，就将其删除；
2.使用create proc语句创建存储过程；
3.定义所需要的输入参数和输出参数；
4.声明4个变量来保存输入和输出参数；
5.执行存储过程并显示结果。
同时，值得注意的是，在创建存储过程时，应该注意一些细节，如单词的拼写要准确无误，程序中用的是单引号而不是双引号等等。
--执行存储过程
EXEC单科成绩分析@KECHENGMING,
@AVGCHENGJI1OUTPUT,
@MAXCHENGJI1OUTPUT,
@MINCHENGJI1OUTPUT
--显示结果
SELECT@KECHENGMINGAS课程名,@AVGCHENGJI1AS平均成绩,@MAXCHENGJI1AS最高成绩,
实验07_使用T-SQL编写存储过程和函数实验报告
北京理工大学珠海学院实验报告ZHUHAI CAMPAUS OF BEIJING INSTITUTE OF TECHNOLOGY 班级学号姓名指导教师成绩实验题目使用T-SQL编写存储过程和函数实验时间一实验目的1. 理解存储过程的概念、使用方式；2. 熟悉使用T-SQL编写存储过程来进行数据库应用程序的设计；3. 掌握SQL Server中自定义函数的概念，熟悉自定义函数的类型；4. 能创建相关的自定义函数，解决T-SQL程序设计中的相关问题。
二实验工具SQL Server 2008利用SQL Server 2008 SSMS及其SQL新建查询编辑器，使用T-SQL编写存储过程和函数。
三实验内容和要求1.建立学生-课程数据库，其中包含学生表Student(Sno,Sname,Ssex,Sage,Sdept)、课程表：Course(Cno,Cname,Cpno,Ccredit)和学生选课表：SC(Sno,Cno,Grade)；编写相应的存储过程，完成下面的功能：（1）编写一个存储过程，可以查询指定系的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。
调用该存储过程，测试执行结果。
（2）编写一个存储过程，返回指定课程的平均分。
调用该存储过程，测试执行结果。
（3）编写一个存储过程，可以查询指定系指定成绩等级的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。
调用该存储过程，测试执行结果。
（成绩等级为优、良、中、及格、不及格，其中成绩在90分到100分之间为‘优’，在80分到89分之间为‘良’，在70分到79分之间为‘中’，在60分到69分之间为‘及格’，在0分到59分之间为‘不及格’。
）要求：提交创建存储过程的SQL脚本，并标注必要的注释。
保证程序能够正确编译和运行，并有相应的测试代码。
2. 自定义标量函数假设有一家生产的集装箱的公司，生产一些不同型号的集装箱，存储集装箱信息的表如下所示。
实验7 存储过程和触发器
实验7 存储过程和触发器二、背景知识（1）存储过程概述：存储过程是SQL语句和可选控制流程语句的预编译集合，它以一个名称存储并作为一个单元处理。
存储过程存储在数据库内，可由应用程序通过一个调用执行。
存储过程可以接受参数，输出参数，返回单个或多个结果集，也可以返回一个值。
存储过程具有以下优点：●可以在一个存储过程中，执行一系列SQL语句。
●存储过程可以嵌套调用，以简化语句的描述●存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，并且能减少网络通信的负担。
●存储过程中的参数可以具有默认值，默认值必须为常量或者NULL。
（2）触发器概述：触发器是一种特殊类型的存储过程，常用作数据完整性约束。
一个系统有三类触发器，分别为：INSERT触发器、UPDATE触发器和DELETE触发器。
通常INSERT、UPDATE触发器被用来检查插入或者修改后的数据是否满足要求。
DELETE触发器一般用作级联删除或记录外键的删除操作。
当对指定表执行INSERT、UPDATE、DELETE时，相应的触发器会自动执行。
触发器可以包含复杂的T-SQL语句。
一个表可以有多个触发器。
在触发器执行的时候，会产生两个临时表：INSERTed表和deleted 表。
它们的结构和触发器所在的表的结构相同，SQLSERVER自动创建和管理这些表。
在对触发器的表进行操作时，系统执行过程如下：●执行INSERT操作，插入到触发器表中的新行同时被插入到INSERTed表中。
●执行DELETE操作，从触发器表中删除的行同时被插入到deleted 表中。
●执行UPDATE操作，先从触发器表中删除旧行，然后再插入新行。
其中，被删除的旧行，同时被插入到deleted表中；插入的新行，同时被插入到INSERTed表中。
触发器可以使用这两个临时表测试数据修改的效果及设置触发器操作的条件。
触发器具有以下优点：●触发器可通过数据库中的相关表实现级联更改。
数据库原理与应用(存储过程与触发器的应用)
《数据库原理与应用》实验报告题目：实验七：存储过程与触发器的应用学号：1148028姓名：沈宇杰日期：2013/5/30实验预习情况：一：实验目的：①：掌握创建存储过程的方法和步骤②：掌握存储过程的使用方法③：掌握创建触发器的方法和步骤④：掌握触发器的使用方法二：实验内容：①：存储过程的创建、执行和删除②：触发器的创建、执行和删除三：实验示例：一：创建触发器：对于STUDENT数据库，表STUDENT的CLASS_ID列与表CLASS的CLASS_ID满足下列参照完整性规则：7.1创建触发器A.向STUDENT插入或修改一记录时，该字段在STUDENT表中的对应值也做相应的修改B.修改CLASS表的CLASS_ID字段值时，该字段在STUDENT表中对应值也做相应的修改C.删除CLASS表中一记录的同时删除该记录CLASS_ID字段值在STUDENT表中对应的记录在查询分析器编辑窗口输入下列触发器的代码并执行。
①：向STUDENT表中插入或修改一记录时，通过触发器检查记录的CLASS_ID值在CLASS表中是否存在，若不存在，则取消插入或删除操CREATE TRIGGER STUDENTINS ON DBO.STUDENTFOR INSERT,UPDATEAS BEGINIF(SELECT INS.CLASS_ID FROM inserted INS)NOT IN(SELECT CLASS_ID FROM CLASS)ROLLBACKENDGOAFTER指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被触发。
所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
如果仅指定FOR关键字，则AFTER为默认值。
不能对视图定义AFTER触发器。
注意：书上的P162页的语法和SQL SERVER2008中的语法可能不大一样，SQL SERVER2008中并没有BEFORE、FOR EACH ROW等关键字。
007-实验七存储过程与触发器(实验报告内容)
实验七存储过程与触发器（实验报告）一、目的1．掌握存储过程和触发器的基本概念和功能。
2．掌握创建、管理存储过程的方法。
3．掌握创建、管理触发器的方法。
二、实验内容（1）完成下列操作1．利用SQL Server Management Studio创建一个查询过程ProNum,查询每个班级中学生的人数，按班级号升序排列。
2．利用Transact-SQL语句创建一个带有参数的存储过程ProInsert,向score表插入一条选课记录，并查询该学生的姓名、选修的所有课程名称、平时成绩和期末成绩。
3．利用Transact-SQL语句创建一个存储过程ProAvg，查询指定班级指定课程的平均分。
班级号和课程名称由输入参数给定，计算出的平均分通过输出参数返回。
若该存储过程存在，则删除后重建。
4．利用SQL Server Management Studio创建一个AFTER触发器trigsex,当插入或修改student表中性别字段sex时，检查数据是否只为‘男’或‘女’。
5．利用Transact-SQL语句创建一个AFTER数据库trigforeign,当向score表中插入或修改记录时，如果插入或修改的数据与student表中数据部匹配，即没有对应的学号存在，则将此记录删除。
6．利用Transact-SQL语句创建一个AFTER触发器trigclassname，当向class表中插入或修改数据时，如果出现班级名称重复则回滚事务。
若该触发器存在，则删除后重建。
（2）完成实验报告三、实验环境SQL2005。
四、实验原理1．理解存储过程。
2．掌握触发器的使用。
五、实验报告将实验内容作为实验报告完成。
六、实验小结。
存储过程及其实例演示
存储过程及其实例演⽰概念存储过程存储过程是为了完成特定功能的SQL语句的集合。
包括了⼀些流程控制语句及数据类型。
种类⾃定义存储过程创建1.使⽤CREATE PROCEDURE创建存储过程CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>-- Add the parameters for the stored procedure here<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>ENDGOps：procedure_name 存储过程的名称。
最长为128个字符sysname：存储过程的参数。
相当于数据类型nvarchar（128）。
相见：@param1：存储过程中使⽤到的参数。
@Datatype：参数的数据类型。
另外⼤家可以看到这⾥的SQL语句都是⼤写的。
这是因为SQL语⾔在执⾏的时候是先转化为⼤写再去执⾏，对于⼀些⼩的程序这些转化的时间可以忽略，但是当⼀些⼤型的数据处理的时候就会浪费许多时间。
SQL实验7：使用存储过程
The Price of toy : 8.99
cOrderNo cToyId siQty cGiftWrap cWrapperId vMessage mToyCost
-----------------------------------------------------------------------------
createPROCproc8(@echar(6))
AS
BEGIN
IF(SELECTmTotalCostFROMOrdersWHEREcOrderNo=@e)>60BEGIN
SELECT*
FROMOrdersWHEREcOrderNo=@e
RETURN0
END
ELSE
BEGIN
PRINT'No more than 60'
ifexists(select*fromToyswherecToyId=@toyid)
begin
select@toyName=vToyName,@TOyDescription=vToyDescription,
@toyrate=mToyRate
fromToyswherecToyId=@toyid
return0
wherecToyId=@toyid
end
else
print'No record for the given Toyid'
end
指导教师
日期
注：请用A4纸书写，不够另附纸。第页，共页
end
else
return1
end
createprocproc11@toyidchar(6)='000001',
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验七存储过程及应用
1．实验目的
1、理解存储过程的概念。
2、掌握存储过程的使用方法。
2．实验要求
1．建立如下的存储过程（基于前面实验建立的表和插入的数据，并为每个存储过程设计返回的状态值）：
（1）按要求设计完成如下功能的存储过程。
①查询平均分数在x到y范围内的学生信息。
说明：
该存储过程有两个参数；
要求查询的学生信息包括学号、姓名、院系名称和平均分数。
②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。
(2)自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。
（3）在客户端以存储过程和输入SQL语句的方式分别执行相同的查询或操作，比较使用和不使用存储过程的区别。
3、实验过程
（1）①查询平均分数在x到y范围内的学生信息
create procedure cc711@x smallint,@y smallint
as
select学生.学号,学生.姓名,院系.名称,学生.平均成绩
from学生join院系on学生.院系=院系.编号
where学生.平均成绩between@x and@y
EXECUTE cc71160,90
②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩
create procedure mcc712@xh nchar(8),@kcbh nchar(8),@cj int
as
update选课set成绩=@cj where学号=@xh and课程编号=@kcbh
declare@pjcj int
select@pjcj=AVG(成绩)from选课where学号=@xh
return@pjcj
declare@avg int
execute@avg=mcc7122,5,98
print'更新后平均成绩：'+str(@avg,6)
(2)自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。
create procedure dcc721@kcmc nchar(20)
as
declare@cc721nchar(10)
select@cc721=学生.平均成绩
from学生join选课on学生.学号=选课.学号
where选课.成绩in(select MAX(成绩)
from选课where选课.课程编号in(
select课程编号from课程where学时=@kcmc))
return@cc721
declare@acc721nchar(10)
execute@acc721=dcc721'16'
print'最高分者的平均成绩'+str(@acc721)
（3）在客户端以存储过程和输入SQL语句的方式分别执行相同的查询或操作，比较使用和不使用存储过程的区别。
存储：
create procedure cc731@kcmc nchar(20)
as
select学生.学号,姓名,性别from学生join选课
on学生.学号=选课.学号
where选课.课程编号in(
select课程编号from课程
where课程名称=@kcmc)
execute cc731数据库
输入SQL语句：
select学生.学号,姓名,性别from学生join选课
on学生.学号=选课.学号where选课.课程编号in( select课程编号from课程where课程名称='数据库')
4、实验总结
存储过程比SQL的优势在于一次编译多次使用，因为在存储过程中的SQL语句是已经过了语法检查和编译的，可以直接执行；而SQL总要先进行语法解析和编译才能执行。
另外对于网络执行的话，执行相同的任务，客户端调用存储过程的代码传输量和直接执行SQL语句相比也要更少。
存储中参数的使用很重要，也容易出错。