数据库上机实验题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
上机实验题7
相关知识：第14章。
目的：通过本实验，掌握使用函数和存储过程的设计和使用方法。
实验内容：编写满足如下各功能的程序：
（1）在Library数据库中创建一个标量值函数Sum(n)，求1+2+…+n之和。并用相关数据进行测试。
（2）在Library数据库中创建一个内联表值函数nbook，返回指定系的学号、姓名、班号、所借图书名和借书日期。并用相关数据进行测试。
（3）在Library数据库中创建一个多语句表值函数pbook，返回系名和该系所有学生所借图书的平均价格。并用相关数据进行测试。
（4）设计一个存储过程，查询每种图书品种的数目。并用相关数据进行测试。
（5）设计一个存储过程，采用模糊查询方式查找借阅指定书名的学生，输出学号、姓名、班号和书名。并用相关数据进行测试。
设计过程：
（1）对应的参考程序如下：
USE Library
GO
IF EXISTS(SELECT * FROM sysobjects
WHERE name='Sum' AND type='FN') --如果存在这样的函数则删除之
DROP FUNCTION Sum
GO
CREATE FUNCTION Sum(@n int) --输入参数
RETURNS int
AS
BEGIN
DECLARE @s int,@i int
SET @s=0
SET @i=1
WHILE @i<=@n
BEGIN
SET @s=@s+@i
SET @i=@i+1
END
RETURN(@s)
END
GO
PRINT '1+2+...+60='+CAST(60) AS char(10))
GO
（2）对应的参考程序如下：
USE Library
GO
IF EXISTS(SELECT * FROM sysobjects --如果存在这样的函数则删除之
WHERE name='nbook' AND (type='IF' OR type ='TF'))
DROP FUNCTION nbook
GO
CREATE FUNCTION nbook(@dname char(16))--建立函数nbook
RETURNS TABLE --返回表，没有指定表结构，这是内联表值函数的特征
AS
RETURN
(
SELECT s.学号,s.姓名,s.班号,b.图书名,bor.借书日期
FROM student s,depart sc,borrow bor,book b
WHERE s.班号=sc.班号 AND s.学号=bor.学号 AND b.图书编号=bor.图书编号 AND sc.系名=@dname
)
GO
SELECT *
FROM nbook('计算机系')
ORDER BY 学号
GO
（3）对应的参考程序如下：
USE Library
GO
IF EXISTS(SELECT * FROM sysobjects --如果存在这样的函数则删除之
WHERE name='pbook' AND (type='IF' OR type ='TF'))
DROP FUNCTION pbook
GO
CREATE FUNCTION pbook() --建立函数pbook
RETURNS @st TABLE
(
系名 char(16),
平均价格 decimal(4,1)
)
AS
BEGIN
INSERT @st --向@st中插入满足条件的记录
SELECT sc.系名 AS '系名',AVG(b.定价) AS '平均价格'
FROM depart sc,student s,book b,borrow bor
WHERE s.班号=sc.班号 AND s.学号=bor.学号 AND b.图书编号=bor.图书编号
GROUP BY sc.系名
RETURN
END
GO
SELECT * FROM pbook()
GO
（4）对应的参考程序如下：
USE Library
GO
--若存在存储过程proc1，则删除之
IF EXISTS(SELECT * FROM sysobjects WHERE name='proc1'
AND type='P')
DROP PROCEDURE proc1
GO
--创建存储过程proc1
CREATE PROCEDURE proc1
AS
SELECT 图书名 AS '书名',COUNT(*) AS '数目'
FROM book
GROUP BY 图书名
GO
EXEC proc1
（5）对应的参考程序如下：
USE Library
GO
--若存在存储过程proc2，则删除之
IF EXISTS(SELECT * FROM sysobjects WHERE name='proc2' AND type='P')
DROP PROCEDURE proc2
GO
--创建存储过程proc2
CREATE PROCEDURE proc2(@bn char(16))
AS
DECLARE @pt char(16)
SET @pt='%'+RTRIM(@bn)+'%'
SELECT s.学号,s.姓名,s.班号,b.图书名
FROM student s,book b,borrow bor
WHERE s.学号=bor.学号 AND b.图书编号=bor.图书编号
AND b.图书名 LIKE @pt
ORDER BY s.班号
GO
EXEC proc2 '数'