视图与存储过程

什么是视图

 视图的概念

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

视图中不存放数据

• 数据存放在视图所引用的原始表中。

表视图的多样性

• 一个或多个原始表，根据不同用户的不同需求，可以创建不同的视图。

视图的用途

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

如何创建视图

使用管理器创建视图

T-SQL语句管理视图

使用T-SQL语句创建视图

CREATE VIEW view_Stulnfo 
    AS
    <SELECT语句> 

 1 use StudentManager
 2 go
 3 --判断视图是否存在
 4 if exists(select * from sysobjects where name='view_ScoreQuery')
 5 drop view view_ScoreQuery
 6 go
 7 --创建视图
 8 create view view_ScoreQuery
 9 as
10     select top 1000 Students.StudentId,StudentName,ClassName,
11              C#=CSharp,SQLDB=SQLServerDB,
12              ScoreSum=(CSharp+SQLServerDB) from Students
13              inner join ScoreList on Students.StudentId=ScoreList.StudentId
14              inner join StudentClass on Students.ClassId=StudentClass.ClassId
15              order by StudentClass.ClassId
16 go
17 --使用视图查询
18 select * from view_ScoreQuery

使用T-SQL语句删除视图

IF EXISTS (SELECT*FROM sysobjects WHERE name = view_Stulnfo)
    DROP VIEW view_Stulnfo

使用T-SQL语句查看视图

SELECT* FROM view Stulnfo

创建成绩查询视图

使用视图注意事项

• 视图中可以使用多个表
• 一个视图可以嵌套另一个视图（尽量少套用）
• 视图定义中的SELECT语句不能包括下列内容：
• ORDER BY子句，除非在SELECT语句的选择列表中也有一个 TOP子句。 
• INTO关键字。 
• 引用临时表或表变量。

什么是存储过程

概念

• 预先存储好的SQL程序
• 保存在SQLServer中（跟视图的存储方式一样）
• 通过名称和参数执行
  • 在数据库服务器端直接调用（DBA）
  • 供应用程序调用（软件开发工程师）

类似于Java和C#语言中的方法

• 可带参数，也可返回结果
• 可包含数据操纵语句、变量、逻辑控制语句等

存储过程的优点

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

应用程序发送SQL的过程

<传输语句> <语法检查> < 语句优化> <语句编译> <语句执行>

应用程序调用存储过程或视图的过程

<传输参数> <语句执行>

存储过程的分类

系统存储过程

• 系统存储过程的名称一般以“sp_”开头。
• 由SQLServer创建、管理和使用。
• 存放在Master数据库中。
• 目类似Java和C#语言类库中的方法。

扩展存储过程

• 扩展存储过程的名称通常以“xp_”开头。
• 使用编程语言（如C#）创建的外部存储过程。
• 以DLL形式单独存在。

用户自定义存储过程

• 由用户在自己的数据库中创建的存储过程。
• 类似C#语言中用户自定义的方法。

调用存储过程

调用存储讨程的语法

EXECUTE 过程名[参数]

EXEC 过程名[参数]

如果执行存储过程的语句是批处理中的第一个语句，则可以不指定EXECUTE关键字

常用的系统存储过程

常用系统存储过程调用示例

 1 sp_databases
 2 
 3 EXEC  sp_renamedb 'ProductDB','pDB'
 4 
 5 USE StudentManager
 6 GO
 7 sp_tables
 8 
 9 EXEC sp_columns Students 
10 
11 EXEC sp_help Students 
12 
13 EXEC sp_helpconstraint Students
14 
15 EXEC sp_stored_procedures  

常用的系统存储过程

常用扩展存储过程

xp_cmdshell

• 可以执行DOS命令下的一些的操作。
• 以文本行方式返回任何输出。

 1 USE master
 2 GO
 3 EXEC xp_cmdshell 'mkdir D:\ProductDB', NO_OUTPUT
 4 IF EXISTS(SELECT * FROM sysdatabases  WHERE name='ProductDB')
 5    DROP DATABASE ProductDB
 6 GO
 7 --CREATE DATABASE ProductDB
 8 -- (
 9 --  …
10 --)
11 --GO
12 EXEC xp_cmdshell 'dir D:\ProductDB\'   -- 查看文件

如何创建存储过程

定义存储过程的语法

存储过程的参数

• 和C#语言的方法一样，参数可选。
• 参数分为输入参数、输出参数。
• 输入参数允许有默认值。

创建、执行无参的存储过程

创建存储过程usp_ScoreQuery

• 查询考试成绩，显示：学号、姓名、班级、总成绩，并按成绩的总分高低排序。
• 统计分析考试成绩，显示班级名称、C#平均分、数据库平均分，按照班级分组实现。

 代码编写分析

 1 use StudentManager
 2 go
 3 if exists(select * from sysobjects where name='usp_ScoreQuery')
 4 drop procedure usp_ScoreQuery
 5 go
 6 create procedure usp_ScoreQuery --创建存储过程
 7 as
 8     --查询考试信息
 9     select Students.StudentId,StudentName,ClassName,
10               ScoreSum=(CSharp+SQLServerDB) from Students
11     inner join StudentClass on StudentClass.ClassId=Students.ClassId
12     inner join ScoreList on Students.StudentId=ScoreList.StudentId
13     order by ScoreSum DESC
14     --统计分析考试信息
15     select StudentClass.ClassId,C#Avg=avg(CSharp),DBAvg=avg(SQLServerDB)  into #scoreTemp
16     from StudentClass 
17     inner join Students on StudentClass.ClassId=Students.ClassId
18     inner join ScoreList on ScoreList.StudentId=Students.StudentId
19     group by StudentClass.ClassId order by ClassId
20     select ClassName,C#Avg,DBAvg from #scoreTemp
21     inner join StudentClass on StudentClass.ClassId=#scoreTemp.ClassId
22 go
23 exec usp_ScoreQuery  --调用存储过程

存储过程参数

存储过程的参数分两种：

• 输入参数：向存储过程传入值
• 输出参数：调用存储过程后，传出执行结果

带输入参数的存储过程

查询考试成绩，要求能够按照自定义的及格线查询结果？

 1 use StudentManager
 2 go
 3 if exists(select * from sysobjects where name='usp_ScoreQuery2')
 4 drop procedure usp_ScoreQuery2
 5 go
 6 --创建带参数的存储过程
 7 create procedure usp_ScoreQuery2 
 8 @CSharp int,
 9 @DB int
10 as
11     select Students.StudentId,StudentName,C#=CSharp,DB=SQLServerDB
12     from Students
13     inner join ScoreList on Students.StudentId=ScoreList.StudentId
14     where CSharp<@CSharp or SQLServerDB<@DB
15 go
16 --调用带参数的存储过程
17 exec usp_ScoreQuery2 60,65 --按照参数顺序赋值
18 exec usp_ScoreQuery2 @DB=65,@CSharp=60 --参数顺序可以调换

输入参数的默认值

查询中，如果用户没有输入及格线参数，则默认60

 1 use StudentManager
 2 go
 3 if exists(select * from sysobjects where name='usp_ScoreQuery3')
 4 drop procedure usp_ScoreQuery3
 5 go
 6 --创建带参数的存储过程
 7 create procedure usp_ScoreQuery3 
 8 @CSharp int=60,
 9 @DB int=60
10 as
11     select Students.StudentId,StudentName,C#=CSharp,DB=SQLServerDB
12     from Students
13     inner join ScoreList on Students.StudentId=ScoreList.StudentId
14     where CSharp<@CSharp or SQLServerDB<@DB
15 go
16 --调用带参数的存储过程
17 exec usp_ScoreQuery3 65 --第二个参数没有赋值，则默认
18 exec usp_ScoreQuery3 @DB=65
19 exec usp_ScoreQuery3 default,65 --不使用显示方式赋值
20 exec usp_ScoreQuery3   --两个参数都是用默认参数

创建带输出参数的存储过程

问题：查询考试成绩，要求自定义分数线，显示查询列表，并输出缺考总人数、不及格总人数？

 1 use StudentManager
 2 go
 3 if exists(select * from sysobjects where name='usp_ScoreQuery4')
 4 drop procedure usp_ScoreQuery4
 5 go
 6 create procedure usp_ScoreQuery4 --创建带参数的存储过程
 7 @AbsentCount int output,--缺考总人数
 8 @FailedCount int output,--不及格总人数
 9 @CSharp int=60,
10 @DB int=60
11 as
12     select Students.StudentId,StudentName,C#=CSharp,DB=SQLServerDB
13      from Students
14      inner join ScoreList on Students.StudentId=ScoreList.StudentId
15      where CSharp<@CSharp or SQLServerDB<@DB        --显示结果列表 
16     select @AbsentCount=count(*) from Students 
17     where StudentId not in(select StudentId from ScoreList) --查询缺考总人数
18     select @FailedCount=count(*) from ScoreList
19      where CSharp<@CSharp or SQLServerDB<@DB      --查询不及格总人数
20 go

调用带输出参数的存储过程

输出参数在使用前要首先定义，调用时也要使用output

1 use StudentManager
2 go
3 --调用带参数的存储过程
4 declare @AbsentCount int,@FailedCount int --首先定义输出参数
5 exec usp_ScoreQuery4 @AbsentCount output,@FailedCount output
6 --使用反馈的结果
7 select 缺考总数=@AbsentCount,不及格总数=@FailedCount

自定义存储过程

 1 use StudentManager
 2 go
 3 if exists(select * from sysobjects where name='usp_ScoreQuery')
 4 drop procedure usp_ScoreQuery
 5 go
 6 create procedure usp_ScoreQuery
 7 
 8 as
 9     --查询考试信息
10     select Students.StudentId,StudentName,ClassName,ScoreSum=(SQLServerDB+CSharp) from Students
11     inner join StudentClass on Students.ClassId=StudentClass.ClassId
12     inner join ScoreList on Students.StudentId=ScoreList.StudentId
13     order by ScoreSum DESC
14     --统计分析考试信息
15     select ClassName,C#Avg=AVG(CSharp),DBAvg=AVG(SQLServerDB) 
16     from StudentClass
17     inner join Students on StudentClass.ClassId=Students.ClassId
18     inner join ScoreList on ScoreList.StudentId=Students.StudentId
19     group by ClassName 
20 go
21 
22 exec usp_ScoreQuery  --调用存储过程
23 
24 
25 --if exists(select * from sysobjects where name='usp_ScoreQuery2')
26 --drop procedure usp_ScoreQuery2
27 --go
28 --create procedure  usp_ScoreQuery2
29 --@CSharp int,  --参数定义
30 --@DB int
31 --as
32 --     select Students.StudentId,StudentName,C#=CSharp,  DB=SQLServerDB from Students
33 --    inner join ScoreList on Students.StudentId=ScoreList.StudentId 
34 --    where CSharp<@CSharp or SQLServerDB<@DB
35 --go
36 ----调用带参数的SQL语句
37 --exec usp_ScoreQuery2 60,65  --按照参数顺序赋值
38 --exec usp_ScoreQuery2 @DB=65,@CSharp=60
39 
40 
41 if exists(select * from sysobjects where name='usp_ScoreQuery2')
42 drop procedure usp_ScoreQuery2
43 go
44 create procedure  usp_ScoreQuery2
45 @CSharp int=60, 
46 @DB int=60
47 as
48      select Students.StudentId,StudentName,C#=CSharp,  DB=SQLServerDB from Students
49     inner join ScoreList on Students.StudentId=ScoreList.StudentId 
50     where CSharp<@CSharp or SQLServerDB<@DB
51 go
52 --调用带参数的SQL语句
53 exec usp_ScoreQuery2 65
54 exec usp_ScoreQuery2 @DB=65
55 
56 exec usp_ScoreQuery2 default,65
57 exec usp_ScoreQuery2 
58 
59 
60 if exists(select * from sysobjects where name='usp_ScoreQuery4')
61 drop procedure usp_ScoreQuery4
62 go
63 create procedure  usp_ScoreQuery4
64 @AbsentCount int output,--缺考总人数
65 @FailedCount int output,--不及格总人数
66 @CSharp int=60, 
67 @DB int=60
68 as
69      select Students.StudentId,StudentName,C#=CSharp,  DB=SQLServerDB from Students
70     inner join ScoreList on Students.StudentId=ScoreList.StudentId 
71     where CSharp<@CSharp or SQLServerDB<@DB
72     
73     select @AbsentCount=COUNT(*) from Students 
74     where StudentId not in(Select StudentId from ScoreList)
75     select @FailedCount=COUNT(*) from ScoreList
76     where CSharp<@CSharp or SQLServerDB<@DB
77 go
78 --调用带输出参数的存储过程
79 declare @AbsentCount int,@FailedCount int --首先定义输出参数
80 exec usp_ScoreQuery4 @AbsentCount output,@FailedCount output
81 --读取输出参数
82 select 缺考总数=@AbsentCount,不及格总数=@FailedCount

END