MS SQL高级——视图与存储过程

视图与存储过程

什么是视图

 视图的概念

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

视图中不存放数据

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

表视图的多样性

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

视图的用途

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

如何创建视图

使用管理器创建视图

 

T-SQL语句管理视图

使用T-SQL语句创建视图

CREATE VIEW view_Stulnfo 
    AS
    <SELECT语句> 

use StudentManager
go
--判断视图是否存在
if exists(select * from sysobjects where name='view_ScoreQuery')
drop view view_ScoreQuery
go
--创建视图
create view view_ScoreQuery
as
    select top 1000 Students.StudentId,StudentName,ClassName,
             C#=CSharp,SQLDB=SQLServerDB,
             ScoreSum=(CSharp+SQLServerDB) from Students
             inner join ScoreList on Students.StudentId=ScoreList.StudentId
             inner join StudentClass on Students.ClassId=StudentClass.ClassId
             order by StudentClass.ClassId
go
--使用视图查询
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关键字

常用的系统存储过程

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

sp_databases

EXEC  sp_renamedb 'ProductDB','pDB'

USE StudentManager
GO
sp_tables

EXEC sp_columns Students 

EXEC sp_help Students 

EXEC sp_helpconstraint Students

EXEC sp_stored_procedures  

常用的系统存储过程

系统存储过程	说明
sp_databases	列出服务器上的所有数据库
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb	更改数据库的名称
sp_tables	返回当前环境下可查询的对象的列表
sp_columns	返回某个表列的信息
sp_help	查看某个表的所有信息
sp_helpconstraint	查看某个表的约束
sp_helpindex	查看某个表的索引
sp_stored_procedures	列出当前环境中的所有存储过程
sp_password	添加或修改登录帐户的密码
sp_helptext	显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

常用扩展存储过程

xp_cmdshell

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

USE master
GO
EXEC xp_cmdshell 'mkdir D:\ProductDB', NO_OUTPUT
IF EXISTS(SELECT * FROM sysdatabases  WHERE name='ProductDB')
   DROP DATABASE ProductDB
GO
--CREATE DATABASE ProductDB
-- (
--  …
--)
--GO
EXEC xp_cmdshell 'dir D:\ProductDB\'   -- 查看文件

如何创建存储过程

定义存储过程的语法

存储过程的参数

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

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

创建存储过程usp_ScoreQuery

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

 代码编写分析

use StudentManager
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.ClassId,C#Avg=avg(CSharp),DBAvg=avg(SQLServerDB)  into #scoreTemp
    from StudentClass 
    inner join Students on StudentClass.ClassId=Students.ClassId
    inner join ScoreList on ScoreList.StudentId=Students.StudentId
    group by StudentClass.ClassId order by ClassId
    select ClassName,C#Avg,DBAvg from #scoreTemp
    inner join StudentClass on StudentClass.ClassId=#scoreTemp.ClassId
go
exec usp_ScoreQuery  --调用存储过程

存储过程参数

存储过程的参数分两种：

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

带输入参数的存储过程

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

use StudentManager
go
if exists(select * from sysobjects where name='usp_ScoreQuery2')
drop procedure usp_ScoreQuery2
go
--创建带参数的存储过程
create procedure usp_ScoreQuery2 
@CSharp int,
@DB int
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 --参数顺序可以调换

输入参数的默认值

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

use StudentManager
go
if exists(select * from sysobjects where name='usp_ScoreQuery3')
drop procedure usp_ScoreQuery3
go
--创建带参数的存储过程
create procedure usp_ScoreQuery3 
@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_ScoreQuery3 65 --第二个参数没有赋值，则默认
exec usp_ScoreQuery3 @DB=65
exec usp_ScoreQuery3 default,65 --不使用显示方式赋值
exec usp_ScoreQuery3   --两个参数都是用默认参数

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

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

use StudentManager
go
if exists(select * from sysobjects where name='usp_ScoreQuery4')
drop procedure usp_ScoreQuery4
go
create procedure usp_ScoreQuery4 --创建带参数的存储过程
@AbsentCount int output,--缺考总人数
@FailedCount int output,--不及格总人数
@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        --显示结果列表 
    select @AbsentCount=count(*) from Students 
    where StudentId not in(select StudentId from ScoreList) --查询缺考总人数
    select @FailedCount=count(*) from ScoreList
     where CSharp<@CSharp or SQLServerDB<@DB      --查询不及格总人数
go

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

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

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

自定义存储过程

use StudentManager
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=(SQLServerDB+CSharp) from Students
    inner join StudentClass on Students.ClassId=StudentClass.ClassId
    inner join ScoreList on Students.StudentId=ScoreList.StudentId
    order by ScoreSum DESC
    --统计分析考试信息
    select ClassName,C#Avg=AVG(CSharp),DBAvg=AVG(SQLServerDB) 
    from StudentClass
    inner join Students on StudentClass.ClassId=Students.ClassId
    inner join ScoreList on ScoreList.StudentId=Students.StudentId
    group by ClassName 
go

exec usp_ScoreQuery  --调用存储过程


--if exists(select * from sysobjects where name='usp_ScoreQuery2')
--drop procedure usp_ScoreQuery2
--go
--create procedure  usp_ScoreQuery2
--@CSharp int,  --参数定义
--@DB int
--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
----调用带参数的SQL语句
--exec usp_ScoreQuery2 60,65  --按照参数顺序赋值
--exec usp_ScoreQuery2 @DB=65,@CSharp=60


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
--调用带参数的SQL语句
exec usp_ScoreQuery2 65
exec usp_ScoreQuery2 @DB=65

exec usp_ScoreQuery2 default,65
exec usp_ScoreQuery2 


if exists(select * from sysobjects where name='usp_ScoreQuery4')
drop procedure usp_ScoreQuery4
go
create procedure  usp_ScoreQuery4
@AbsentCount int output,--缺考总人数
@FailedCount int output,--不及格总人数
@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
    
    select @AbsentCount=COUNT(*) from Students 
    where StudentId not in(Select StudentId from ScoreList)
    select @FailedCount=COUNT(*) from ScoreList
    where CSharp<@CSharp or SQLServerDB<@DB
go
--调用带输出参数的存储过程
declare @AbsentCount int,@FailedCount int --首先定义输出参数
exec usp_ScoreQuery4 @AbsentCount output,@FailedCount output
--读取输出参数
select 缺考总数=@AbsentCount,不及格总数=@FailedCount

END