【数据库实验】存储过程

【数据库实验】存储过程

准备知识

存储过程概述

存储过程是一组为了完成特定功能的SQL语句的集合，它经编译后存储在数据库中，用户通过指定的调用方法执行之。存储过程具有名称，参数及返回值，并且可以嵌套调用。

1. 系统存储过程：系统存储过程主要存储在master数据库中，一般以“sp_”为前缀。
2. 用户自定义的存储：过程用户自定义存储过程是由用户创建并能够完成某些特定功能而编写的存储过程，它可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出参数。
3. 扩展存储过程：扩展存储过程通常以“xp_”为前缀。

基本SQL语句

创建存储过程：

CREATE PROCEDURE <存储过程名>
[ with encryption ] | [ with recompile ]
as
<T-SQL语句>

删除存储过程：

DROP PROCEDURE <存储过程名>

实验环境

使用SQL Server来完成以下操作

实验素材

下载地址：https://download.youkuaiyun.com/download/mc_dudu233/90076559

实验内容

先将tsgl.bak数据库还原到库中并进入数据库

手动恢复数据库，并运行以下sql：

use 图书管理;
go

1. 创建存储过程sp_tsprice,显示价格在10到18之间的图书信息。

使用语句create procedure来创建一个存储结构，选择价格即可。

create procedure sp_tsprice
as
select * from ts
where 单价 between 10 and 18;
go

然后尝试执行该存储过程，用exec语句即可。

execute sp_tsprice;
go

2. 创建存储过程sp_dzxm，根据姓名来查找该姓名的读者信息，如果找到显示该读者信息，并提示“找到该读者”，否则显示信息为“未找到该读者”。（用print输出要显示的信息）

运用if语句来进行判断，判断是否为空可以使用exists语句。

create procedure sp_dzxm
@name nvarchar(10)
as
if exists (select * from dz where 姓名=@name) -- 如果存在
begin
  select * from dz where 姓名=@name;
  print '找到该读者'
end
else  -- 如果不存在
begin
  print '未找到该读者'
end;
go

-- 尝试执行
execute sp_dzxm '王维利';
execute sp_dzxm '王维利xx';
go

3. 删除存储过程sp_dzxm。

drop procedure sp_dzxm;

4. 将存储过程sp_tsprice改名为sp_tsprice1。

这里使用SQL Server的内置函数sp_rename来重命名。

execute sp_rename sp_tsprice,sp_tspricel;

将成绩管理附加到数据库中并选中该数据库

use 成绩管理;
go

5. 创建存储过程cx_cj,根据所输入学号和课程号查询相应的成绩

如果没有相应的学生，打印“查无此人”
如果没有相应的课程，打印“没有课程号为XXX的课程”
如果有学生但没有相应课程成绩则打印，“XXX同学没有选修《XXX》课”此处XXX为相应姓名和课程名
如果查询到学生成绩，则显示“XXX同学《XXX》课程成绩为XX，优秀”(用print语句)，评价依据如下：
90－100 优秀
80－89 良好
70－79 中等
60－69 及格
<60 不及格

create procedure cx_cj
@sno nvarchar(9),@cno nvarchar(4)
as
if not exists (select * from 学生 where 学号=@sno)
begin
  print '查无此人'
  return
end
else if not exists (select * from 课程 where 课程号=@cno)
begin
  print '没有课程号为' + @cno + '的课程'
  return
end
declare @sname nvarchar(10)
declare @cname nvarchar(10)
set @sname=(select 姓名 from 学生 where 学号=@sno)
set @cname=(select 课程名 from 课程 where 课程号=@cno)
if not exists (select * from 成绩 where 学号=@sno and 课程号=@cno)
begin
  print @sname + '同学没有选修《' + @cname + '》课'
  return
end
else
begin
  declare @grade tinyint
  set @grade=(select 成绩 from 成绩 where 学号=@sno and 课程号=@cno)
  declare @level nvarchar(3)
  set @level=
  case
    when @grade between 90 and 100 then '优秀'
    when @grade between 80 and 89 then '良好'
    when @grade between 70 and 79 then '中等'
    when @grade between 60 and 69 then '及格'
    when @grade between 0 and 60 then '不及格'
    else '未知'
  end
  print @sname + '同学《' + @cname + '》课程成绩为' + convert(nvarchar(10),@grade) + '，' + @level
end;
go

尝试执行一下吧。

execute cx_cj '1111','1111';
execute cx_cj '993501122','1111';
execute cx_cj '993501122','0005';
execute cx_cj '993501122','0004';

6. 试编写一个存储过程cx_xs，根据所输入的学号查询该学生的成绩，要求显示如下(可以用select语句)：

课程名 成绩 绩点
0001 90 4
其中的绩点是根据成绩给出
>=90 4
80-89 3.5
60-79 3
<60 0

create procedure cx_xs
@sno nvarchar(10)
as
select 课程名,成绩,
case
  when 成绩 >= 90 then '4'
  when 成绩 between 80 and 89 then '3.5'
  when 成绩 between 60 and 79 then '3'
  else '0'
end
as '绩点'
from 成绩,课程
where 成绩.课程号=课程.课程号 and 学号=@sno;
go

execute cx_xs '993501122';

7. 编写代码，延时5秒显示没有成绩的学生信息

create procedure delay_information
as
waitfor delay '0:0:5'
select *
from 学生
where 学号 not in (select 学号 from 成绩);
go

execute delay_information;

8. 在“成绩管理”数据库创建一个存储过程cj_proc，实现按照输入的课程名打印此门课程的成绩报表（如执行时没有课程名称，则输入《基础会计》的成绩），输出结果按照分数降序排列

《数据结构》成绩表

---

名次 学号 姓名 成绩
1 01 张三 99
2 08 李四 90
3 06 王五 87

---

create procedure cj_proc
@cname nvarchar(10) = '基础会计'
as
print '********************************************'
print '名次    学号     姓名    成绩'

declare @rank int = 1
declare cur cursor for
  select 学生.学号,姓名,成绩 from 成绩,学生
  where 成绩.学号=学生.学号 and 课程号=(select 课程号 from 课程 where 课程名=@cname)
  order by 成绩 desc

declare @sno nvarchar(10),@sname nvarchar(10),@grade tinyint
open cur
fetch next from cur into @sno,@sname,@grade
while @@FETCH_STATUS = 0
begin
  print cast(@rank as nvarchar(10)) + '    ' + @sno + '    ' + @sname + '    ' + cast(@grade as nvarchar(10))
  set @rank = @rank + 1
  fetch next from cur into @sno,@sname,@grade
end
close cur
deallocate cur

print '********************************************';
go

execute cj_proc;