视图与存储过程

视图的创建与使用

为什么需要视图？不同人员关注不同的数据，保证信息的安全性

视图

• 是存储在服务器端的一个查询块，是一张虚拟表。
• 表示一张表的部分数据或多张表的综合数据。
• 其结构和数据是建立在对表的查询基础上。
• 视图的使用，跟对普通的表的查询使用完全一样。
• 视图中不存放数据（数据存放在视图所引用的原始表中）

视图的用途

• 筛选表中的行。
• 防止未经许可的用户访问敏感数据。
• 降低数据库的复杂程度。
• 将多个物理数据库抽象为一个逻辑数据库。

如何创建视图？

--使用T-SQL语句创建视图
create view view_StuInfo
as
<select 语句>

--使用T-SQL语句删除视图
if exists(select * from sysobjects where name=view_StuInfo)
drop view view_StuInfo

--使用T-SQL语句查看视图
select * from view_StuInfo

系统与扩展存储过程

什么是存储过程

• 预先存储好的SQL程序
• 保存在SQL Server中（跟视图的存储方式一样）
• 通过名称和参数执行
• 可带参数，也可返回结果
• 可包含数据操纵语句、变量、逻辑控制语句等

存储过程的优点

• 执行速度更快
• 允许模块化程序设计
• 提高系统安全性
• 减少网络流通量
• 视图和存储过程的重要优点：安全且执行速度快

应用程序发送SQL的过程：传输语句→语法检查→语句优化→语句编译→语句执行
应用程序调用存储过程或视图的过程：传输参数→语句执行

存储过程的分类

• 系统存储过程：以sp_开头，由SQLServer创建、管理和使用，存放在Master数据库中

  
  

• 扩展存储过程：xp_开头，使用编程语言创建的外部存储过程，以DLL形式单独存在

• 用户自定义存储过程

自定义无参数存储过程

--创建、执行无参的存储过程
--创建存储过程usp_ScoreQuery，查询考试成绩，显示：学号、姓名、班级、总成绩，并按成绩的总分高低排序；统计分析考试成绩，显示班级名称、C#平均分、数据库平均分，按班级分组实现。
use StudentManageDB
go
if exists(select * from sysobjects where name='usp_ScoreQuery')
    drop procedure usp_ScoreQuery
go
create procedure usp_ScoreQuery
as
    --查询考试成绩
    select Students.StudentId,StudentName,ClassName,ScoreSum=(CSharp+SQLServerDB)
    from Students
    inner join StudentClass on StudentClass.ClassId=Students.ClassId
    inner join ScoreList on Students.StudentId=ScoreList.StudentId
    order by ScoreSum desc
    --分析考试信息
    --select StudentClass.ClassName,C#Avg=AVG(CSharp),DBAvg=AVG(SQLServerDB)
    --from ScoreList
    --inner join Students on Students.StudentId=ScoreList.StudentId
    --inner join StudentClass on StudentClass.ClassId=Students.ClassId
    --group by ClassName
    --order by ClassName
    
    select StudentClass.ClassId,C#Avg=AVG(CSharp),DBAvg=AVG(SQLServerDB)
    into #scoreTemp --将查询结果放入临时表
    from ScoreList
    inner join Students on Students.StudentId=ScoreList.StudentId
    inner join StudentClass on StudentClass.ClassId=Students.ClassId
    group by StudentClass.ClassId
    order by StudentClass.ClassId
    --将临时表和班级表关联查询
    select ClassName,C#Avg,DBAvg
    from #scoreTemp
    inner join StudentClass on StudentClass.ClassId=#scoreTemp.ClassId
go
exec usp_ScoreQuery

自定义带输入参数的存储过程

--查询考试成绩，要求能够按照自定义的及格线查询结果
use StudentManageDB
go
if exists(select * from sysobjects where name='usp_ScoreQuery2')
    drop procedure usp_ScoreQuery2
go
--创建带参数的存储过程
create procedure usp_ScoreQuery2
@CSharp int=60,
@DB int=60
as
    select Students.StudentId,StudentName,C#=CSharp,DB=SQLServerDB
    from Students
    inner join ScoreList on Students.StudentId=ScoreList.StudentId
    where CSharp<@CSharp or SQLServerDB<@DB
go

--调用带参数的存储过程
exec usp_ScoreQuery2 60,65 --按照参数顺序赋值
exec usp_ScoreQuery2 @DB=65,@CSharp=60 --参数顺序可以调换
exec usp_ScoreQuery2

自定义带输出参数的存储过程

-查询考试成绩，要求自定义分数线，显示查询列表，并输出缺考总人数、不及格总人数
use StudentManageDB
go
if exists(select * from sysobjects where name='usp_ScoreQuery3')
    drop procedure usp_ScoreQuery3
go
--创建带输出参数的存储过程
create procedure usp_ScoreQuery3
@AsentCount int output,--缺考的总人数
@FailedCount int output,--不及格总人数
@CSharp int=60,
@DB int=60
as
    --查询统计结果
    select @AsentCount=COUNT(*) from Students
    where StudentId not in(select StudentId from ScoreList)--查询缺考的总人数
    
    select @FailedCount=COUNT(*) from ScoreList
    where CSharp<@CSharp or SQLServerDB<@DB --查询不及格的总人数
go
--调用带输出参数的存储过程
declare @ASentCount int,@FailedCount int --定义输出参数
exec usp_ScoreQuery3 @ASentCount output,@FailedCount output,65,70
select 缺考总人数=@ASentCount,不及格总人数=@FailedCount