SQL Server 存储过程

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: SQL Server 存储过程

                     SQL Server 存储过程


什么是存储过程

 

存储过程是数据库中,一组为了完成特定功能,预编译的SQL语句集

1. 存储过程类似于C语言中的函数,可以带参数,可返回结果

int sum(int a, int b)

{

 int s;

 s = a + b;

 return s;

}

 

2. 用来执行管理任务或应用复杂的业务规则

 

存储过程的分类

1. 系统存储过程

a)  由系统定义,存放在master数据库中

b)  类似C语言中的系统函数

c)  系统存储过程中的名称都以“sp_”“xp_”开头

d)  返回0(表成功)1(表失败)

2. 用户自定义存储过程

a)  由用户在自己的数据库中创建的存储过程

   b)  类似C语言中的用户自定义函数

 

常用的系统存储过程

 

举例:

#列出当前系统中的数据库

exec sp_databases

 

#修改数据库的名称(单用户访问)

exec sp_renamedb ‘Northwind’,’Northwind1’

 

#查看当前数据库中查询的对象的列表

exec sp_tables

 

#返回某个表列的信息

exec sp_columns stuinfo

 

#查看表stuinfo的信息

exec sp_help stuinfo

 

#查看表stuinfo的约束

exec sp_helpconstraint stuinfo

 

#查看表stuMarks的索引

exec sp_helpindex stuMarks

 

#查看视图的语句文本

exec help_text ‘view_stuinfo_stuMarks’

 

#查看当前数据库中的存储过程

exec sp_stored_procedures

 

常用的存储过程扩展:xp_cmdshell

1.可以执行dos命令下的一些操作

2.以文本方式返回任何输出

3.调用语法:exec xp_cmdshell Dos命令[NO_OUTPUT]

例子:查看数据库文件(先创建数据库bankDB,保存在D:\bank

exec xp_cmdshell ‘dir d:\bank\’

 


如何创建存储过程

定义存储过程

create proc[edure]存储过程名

@参数1数据类型=默认值output,

……,

@参数n数据类型=默认值output

as

sql语句

go

 

说明:

1.参数分为输入参数input,输出参数output,参数可选

2.参数允许有默认值

 

例子:创建存储过程,查看本次考试平均分以及未通过考试的学员名单

create procedure proc_stu

AS

#声明平均分和机试平均分变量

DECLARE @wirttenAvg float,@labAvg flot

SELECT @writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam) FROM stuMarks

print ‘笔试平均分:’ + convert(varchar(5),@writtenAvg)

print ’机试平均分:’ + convert(varchar(5),@labAvg)

IF(@writtenAvg>70 AND @labAvg>70)

print ‘本班考试成绩:优秀

ELSE

print’本班考试成绩:较差

print’-------------------------------------------------’

print ‘参加本次考试没有通过的学员:

SELECT stuName,stuInfo.stuNo,writtenExam,labExam

FROM

FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNO

WHERE writtenExam<60 OR labExam<60

 

调用存储过程

execute语句来执行存储过程

语法:exec过程名[参数]

exec proc_stu;

 

 


存储过程的参数

1. 输入参数用于向存储过程传入值,类似C语言的按值传递,如图a,b

2. 输出参数用于在调用存储过程后,返回结果,类似C语言的按引用传递,如图中s

 

 

 

例子:修改上例,由于每次考试的难易程度不一样,每次笔试和机试的及格线可能随时变化(不再是60分),这导致考试的评判结果也相应变化

分析:

在述存储过程添加2个输入参数:

@writtenPass  笔试及格线

@labPass 机试及格线

 

CREATE PROCEDURE proc_stu

@writtenPass int, #输入参数:笔试及格线

@labPass int    #输入参数:机试及格线

AS

print ‘------------------------------------------------’

print ‘参加本次考试没通过的学员

SELECT stuName,stuInfo.stuNo,WrittenExam,

labExam FROM stuInfo

INNER JOIN stuMarks ON

stuInofo.stuNo=stuMarks.stuNo

WHERE writtenExam<@writtenPass OR labExam<@labPass

GO

 

调用带参数的存储过程

假定本次考试机试偏难,机试的及格线定为55分,笔试及格线定为60

EXEC proc_stu 60,55

EXEC proc_stu @labPass=55,@wirttenPass=60

 


创建输入参数带默认值的存储过程

如果试卷的难易程度合适,则调用者还是必须如此调用:EXEC  proc_stu 60,60,比较麻烦

这样调用就比较合理:

EXEC  proc_stu  55 #笔试及格线55分,机试及格线默认为60

EXEC  proc_stu #笔试和机试及格线都默认为标准的60

 

CREATE PROCEDURE proc_stu

@writtenPass int=60,

@labPass int=60

AS

print’------------------------------------------’

print’参加本次考试没通过的学员

SELECT stuName,stuInfo.stuNO,writtenExam,labExam

FROM stuInfo

INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNO

WHERE writtenExam<@writtenPass OR labExam<@labExam

GO

说明:inner join等同于等值连接=,,仅返回符合join on后面的条件的记录

 

调用输入参数带默认值的存储过程

EXEC proc_stu   --都采用默认值

EXEC proc_stu 64  --机试采用默认值

EXEC proc_stu 60,55  --都不采用默认值

注意这里有个参数的先后顺序:也就是说,带有默认值参数默认放在最后面

 

--错误的调用方式:希望笔试采用默认值,机试及格线55

EXEC proc_stu  ,55

--正确的方法:

EXEC proc_stu @labPass=55

 

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

如果希望调用存储过程后,返回一个或多个值,这时就需要使用输出(output)参数了

例子:修改上例,返回未通过考试的学员人数

create procedure proc_stu

@notpassSum int output #输出(返回)参数,表示没有通过的人数

@writtenPass int=60,

@labPass int=60   #推荐将默认参数放后

as

……

select stuName,stuInfo.stuNo,writtenExam,labExam

from stuInfo INNER JOIN stuMarks

on writtenExam<@writtenPass

or labExam<@labPass

select @notpassSum=count(stuNo)

FROM stuMarks where writtenExam<@writtenPass or labExam<@labPass

go

 

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

declare @sum int

exec proc_stu @sum output,64 #调用时必须带output关键词,返回结构存放在变量@sum

print’-----------------------------------------------’

if @sum>=3

print’未通过人数’+convert(varchar(5),@sum)+’’,’超过60%,及格分数线还应该下调

else

print’未通过人数:’+convert(varchar(5),@sum)+’’,’已控制在60%以下,及格分数线适中

go

 

 

处理存储过程中的错误

1,可以使用print语句显示错误信息,但这些信息是临时的,只能显示给用户

2,可以使用raiserror显示用户定义的错误信息时

a)  可以指定严重级别

b)  设置系统变量@@error

c)  记录所反生的错误

 

raiserror用法

raiserror(msg_id|msg_str,severity,state with option[,…n]])

说明:

msg_id:sysmessages系统表中指定用户定义的错误信息

msg_str:用户定义的特定信息,最长255个字符

severity:定义严重性级别,用户可以使用的级别为0-18

state:表示错误状态,1-127之间的值

option:指示是否将错误记录到服务器错误日志中

 

例子:完善上述例子,当用户调用存储过程时,传入的及格参数不在0-100之间时,将弹出错误警告,终止存储过程的执行。

create procedure proc_stu

@notpassSum int output,

@writtenPass int=60,

@labPass int=60

as

if(not @writtenPass between 0 and 100) or(not @labPass between 0 and 100)

begin

 

raiserror(‘及格线错误,请指定0-100之间的分数,统计中断退出’,16,1)

return –立即返回,退出存储过程

end

…… --其它语句

go

 

declare @sum int,@t int

exec proc_stu @sum output,604

set @t=@@ERROR

print ‘错误号:’+convert(varcha(5),@t)

if @t<>0

return

print’--------------------------------------------------------’

if @sum>=3

print ‘未通过人数:’+convert(varchar(5),@sum)+’,超过60%,及格分数线还应下调'

else

print '未通过人数:'+convert(varchar(5),@sum)+ ',已控制在60%以下,及格分数线适中'

go

 

存储过程的优点

1.执行速度更快

存储过程创建是就已经通过语法检查和性能优化,在执行时无需每次编译。

存储在数据库服务器,性能高。

2.允许模块化程序设计

只需创建存储过程一次并将其存储在数据库中,以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建,并可独立于程序源代码而单独修改

3.提高系统安全性

可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权限,建立特定的存储过程供用户使用,完成对数据的访问。

存储过程的定义文本可以被加密,使用户不能查看其内容

4.减少网络流量

一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现,而不需要在网络中发送数百行代码。

 

相关实践学习
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
26天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
252 1
|
4天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
10天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
21 2
|
15天前
|
SQL 存储 测试技术
|
14天前
|
SQL 机器学习/深度学习 搜索推荐
SQL SERVER 转换失败
【6月更文挑战第25天】
|
19天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何迁移SQL Server
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
27天前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
55 3
|
26天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
8天前
|
SQL 存储 数据库
SQL Server性能优化策略与实践
在使用Microsoft SQL Server(简称MSSQL)进行数据库管理时,性能优化是确保系统高效运行、提升用户体验的关键环节