专栏首页授客的专栏SQLServer 存储过程

SQLServer 存储过程

SQL Server 存储过程

by:授客 QQ:1033553122

什么是存储过程

存储过程是数据库中,一组为了完成特定功能,预编译的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代码的操作由一条执行过程代码的单独语句就可实现,而不需要在网络中发送数百行代码。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Python 文件复制&按目录树结构拷贝&批量删除目录及其子目录下的文件

    #!/usr/bin/env/ python # -*- coding:utf-8 -*- __author__ = 'shouke' im...

    授客
  • Postman Postman接口测试工具使用简介

    注:url仅输入http://xx.xxx.xxxx.xxx/ timeline/postandcheck.do 部分,剩余部分是下面编辑参数时自动输入的

    授客
  • loadrunner 脚本开发-文件下载

    对 HTTP协议来说,无论是下载文件或者请求页面,对客户端来说,都只是发出一个GET请求,并不会记录点击后的“保存”、“另存为操作”。

    授客
  • 每天一道leetcode-80删除排序数组中的重复项 II

    给定一个排序数组,你需要在原地删除重复出现的元素,使得每个元素最多出现两次,返回移除后数组的新长度。

    乔戈里
  • 对象存储的一些理解

    对象存储怎么用,对于很多人来说,对象存储是什么都没听过。为什么觉得陌生,因为用得少。

    我是小护士的小亮子
  • 私有存储云如何构建?

    构建内部的云存储必须考虑到弹性、选择正确的平台、支持工作流,以及批量部署和跟公有云的集成。 随着时间的推移,存储即服务的交付进展惊人。如今,公有云,如Amazo...

    静一
  • 如何进入PageAdmin安装界面

    一般下面几个应用场景如第一次使用PageAdmin配置参数、服务器迁移、主域名更换、忘记超级管理员密码等都可以在安装界面进行设置,通过修改配置文件可以进入重装界...

    用户5258701
  • 如何进入PageAdmin安装界面

    一般下面几个应用场景如第一次使用PageAdmin配置参数、服务器迁移、主域名更换、忘记超级管理员密码等都可以在安装界面进行设置,通过修改配置文件可以进入重装界...

    用户5258701
  • 如何进入PageAdminCMS安装界面

    一般下面几个应用场景如第一次使用PageAdmin配置参数、服务器迁移、主域名更换、忘记超级管理员密码等都可以在安装界面进行设置。

    Almost Lover
  • 连续八个季度全球增速第一,华为要给智能存储打个样

    纵观全球存储市场过去二十年的发展历程,无论是传统存储和融合存储阶段,还是之后的全闪存存储与软件定义存储阶段,国际存储大厂一直在市场中唱主角,引领着技术、产品的发...

    大数据在线

扫码关注云+社区

领取腾讯云代金券