『SQLServer系列教程』——存储过程的创建与使用

📣读完这篇文章里你能收获到

• 了解SQLServer存储过程的优点、分类、定义及使用
• 提供实际操作的案例SQL脚本

一、概念篇

1 什么是存储过程？

2 存储过程的优点

二、存储过程的定义及使用

三、存储过程的分类

1 系统存储过程

• 系统存储过程的介绍

• 系统存储过程明细

• 系统存储过程的调用

2 带参数的存储过程

3 有输出返回的存储过程

四、案例代码

示例1：常用系统存储过程的使用

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  

示例2：常用扩展存储过程的使用

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\'   -- 查看文件

示例3：创建、执行无参的存储过程

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  --调用存储过程

示例4：创建、执行有参的存储过程

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 --参数顺序可以调换

示例5：创建、执行有默认值参数的存储过程

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   --两个参数都是用默认参数

示例6：创建带输出参数的存储过程

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

示例7：调用带输出参数的存储过程

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