存储过程
一、
存储过程概念
存储过程(
procedure
)类似于
C
语言中的函数,它是
SQL
语句和控制流程语句的预编译集合。
存储过程(
procedure
)的优点:
1
允许模块化设计院
2
允许更书报地执行
3
减少网络流量
4
可作为安全机制使用
二、
存储过程的分类
存储过程分为三类
:
1
系统存储过程:以
sp_
开头,类似于
C
语言中的系统函数。系统存储过程位于
master
数据库中。
2
系统扩展存储过程:以
xp_
开头,类似于
C
语言中的系统函数。系统扩展存储过程位于
master
数据库中。
3
自定义存储过程:类似于
C
语言中的自定义函数。
三、
常用系统存储过程
1 sp_helpdb:
报告数据库的信息
execute sp_helpdb --
查看所有数据库信息
execute sp_helpdb '
数据库名
'
--
查看当前数据库信息
2 sp_help:
查看某个数据库中表的信息
execute sp_help --
查看某个数据库中表的所有信息
execute sp_help '
表名
'
--
查看某个数据库中单个表的信息
3 sp_helpfile:
查看当前数据库文件的
.
mdf
和
.
ldf
的位置
execute sp_helpfile
4 sp_helpindex:
查看某个表的索引
execute sp_helpindex '
表名
'
5 sp_helpconstraint:
查看某个表的约束
execute sp_helpconstraint '
表名
'
6 sp_helptext:
显示未加密的存储过程触发器或视图的实际文本
.
execute sp_helptext '
存储过程
|
触发器
|
视图
'
7 sp_stored_procedures:
返回当前数据库中的存储过程的列表
.
execute sp_stored_procedures
8 sp_tables:
查看当前环境下可查询的对象的列表
execute sp_tables
execute sp_tables '
表名
'
9 sp_columns:
查看表中列的信息
execute sp_columns '
表名
'
10 sp_databases:
列出服务器上所有的数据库
execute sp_databases
11 sp_renamedb:
更改数据库的名字
execute sp_renamedb '
原数据库名
'
,
'
新数据库名
'
12 sp_password:
设置登录帐户的密码
execute sp_password '
旧密码
'
,
'
新密码
'
,
'
登录名
'
alter login '
登录名
'
enable --
启用帐户
execute sp_password 'sb','sbsb','sa'
alter login sa enable
四、
常用系统扩展存储过程
execute
xp_cmdshell 'dos
命令
'
[,no_output]
execute
xp_cmdshell 'mkdir d:/stu',no_output
execute
xp_cmdshell 'dir d:/stu'
五、
自定义存储过程
语法
:
create
procedure
存储过程名
[{@
参数数据类型
}[=
默认值
][output]
......,
{@
参数
n
数据类型
}
[=
默认值
][output]]
as
SQL
语句
省略
output
则视为输入参数
.
1
不带参数的存储过程
use
stuDB
go
if exists ( select * from sysobjects where name = ' proc_stu ' )
drop procedure proc_stu
go
/**/ /*--创建存储过程--*/
create procedure proc_stu
as
declare @writtenAvg float , @labAvg float
select @writtenAvg = avg (WrittenExam), @labAvg = avg (LabExam) from stuMarks
print ' 笔试平均分: ' + convert ( varchar ( 5 ), @writtenAvg )
print ' 机试平均分: ' + cast ( @labAvg as varchar ( 5 ))
if ( @writtenAvg > 70 and @labAvg > 70 )
print ' 本班考试成绩: 优秀 '
else
print ' 本班考试成绩: 差 '
print ' ------------------------------------- '
print ' 参加本次考试没有通过的学生 '
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
where writtenExam < 60 or labExam < 60
-- 调用存储过程--
execute proc_stu
go
if exists ( select * from sysobjects where name = ' proc_stu ' )
drop procedure proc_stu
go
/**/ /*--创建存储过程--*/
create procedure proc_stu
as
declare @writtenAvg float , @labAvg float
select @writtenAvg = avg (WrittenExam), @labAvg = avg (LabExam) from stuMarks
print ' 笔试平均分: ' + convert ( varchar ( 5 ), @writtenAvg )
print ' 机试平均分: ' + cast ( @labAvg as varchar ( 5 ))
if ( @writtenAvg > 70 and @labAvg > 70 )
print ' 本班考试成绩: 优秀 '
else
print ' 本班考试成绩: 差 '
print ' ------------------------------------- '
print ' 参加本次考试没有通过的学生 '
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
where writtenExam < 60 or labExam < 60
-- 调用存储过程--
execute proc_stu
2
带输入参数的存储过程
use stuDB
go
if exists ( select * from sysobjects where name = ' proc_stu ' )
drop procedure proc_stu
go
/**/ /*--创建存储过程--*/
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 < @labPass
go
-- 调用存储过程--
execute proc_stu -- 笔试和机试都采用默认值
execute proc_stu 50 -- 笔试及格线,机试采用默认值.
execute proc_stu @labPass = 55 -- 机试及格线,笔试采用默认值.
execute proc_stu 65 , 65 -- 都不采用默认值
3
带输出参数的存储过程
use
stuDB
go
if exists ( select * from sysobjects where name = ' proc_stu ' )
drop procedure proc_stu
go
/**/ /*--创建存储过程--*/
create procedure proc_stu
@notpassSum int output, -- 指出是输出参数
@writtenPass int = 60 , -- 输入参数,笔试及格线,默认参数放后.
@labPass int = 60 -- 输入参数,机试及格线,默认参数放后.
as
print ' 笔试平均分: ' + convert ( varchar ( 5 ), @writtenPass )
print ' 机试平均分: ' + cast ( @labPass as varchar ( 5 ))
print ' ------------------------------------- '
print ' 参加本次考试没有通过的学生 '
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
where writtenExam < @writtenPass or labExam < @labPass
/**/ /*------统计不有通过考试的学员人数------*/
select @notpassSum = count (stuNo) from stuMarks
where writtenExam < @writtenPass or labExam < @labPass
go
/**/ /**--调用存储过程--**/
-- 定义变量,用于存放调用存储过程时返回的结果--
declare @sum int
-- 调用时也带output,笔试及格线为,机试及格线默认为
execute proc_stu @sum output, 64
select ' 未通过人数 ' + cast ( @sum as varchar ( 5 )) + ' 人 '
go
if exists ( select * from sysobjects where name = ' proc_stu ' )
drop procedure proc_stu
go
/**/ /*--创建存储过程--*/
create procedure proc_stu
@notpassSum int output, -- 指出是输出参数
@writtenPass int = 60 , -- 输入参数,笔试及格线,默认参数放后.
@labPass int = 60 -- 输入参数,机试及格线,默认参数放后.
as
print ' 笔试平均分: ' + convert ( varchar ( 5 ), @writtenPass )
print ' 机试平均分: ' + cast ( @labPass as varchar ( 5 ))
print ' ------------------------------------- '
print ' 参加本次考试没有通过的学生 '
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
where writtenExam < @writtenPass or labExam < @labPass
/**/ /*------统计不有通过考试的学员人数------*/
select @notpassSum = count (stuNo) from stuMarks
where writtenExam < @writtenPass or labExam < @labPass
go
/**/ /**--调用存储过程--**/
-- 定义变量,用于存放调用存储过程时返回的结果--
declare @sum int
-- 调用时也带output,笔试及格线为,机试及格线默认为
execute proc_stu @sum output, 64
select ' 未通过人数 ' + cast ( @sum as varchar ( 5 )) + ' 人 '
4
raiserror
处理错误信息
use
stuDB
go
if exists ( select * from sysobjects where name = ' proc_stu ' )
drop procedure proc_stu
go
/**/ /*--创建存储过程--*/
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 ( ' 及格线错误,请指定-100之间的数,统计中断退出! ' , 16 , 1 )
return -- 立即返回,退出存储过程
end
print ' 笔试平均分: ' + convert ( varchar ( 5 ), @writtenPass )
print ' 机试平均分: ' + cast ( @labPass as varchar ( 5 ))
print ' ------------------------------------- '
print ' 参加本次考试没有通过的学生 '
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
where writtenExam < @writtenPass or labExam < @labPass
/**/ /*------统计不有通过考试的学员人数------*/
select @notpassSum = count (stuNo) from stuMarks
where writtenExam < @writtenPass or labExam < @labPass
go
/**/ /**--调用存储过程--**/
-- 定义变量,用于存放调用存储过程时返回的结果--
declare @sum int , @t int
-- 调用时也带output,笔试及格线为,机试及格线默认为
execute proc_stu @sum output, 800 -- 大于报错
set @t = @@ERROR -- raiserror报错误后@@ERROR将不等于,表示有错
if @t <> 0
print ' @@ERROR的值是: ' + convert ( varchar ( 5 ), @t )
return -- 退出批处理,后续语句不执行.
select ' 未通过人数 ' + cast ( @sum as varchar ( 5 )) + ' 人 '
go
go
if exists ( select * from sysobjects where name = ' proc_stu ' )
drop procedure proc_stu
go
/**/ /*--创建存储过程--*/
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 ( ' 及格线错误,请指定-100之间的数,统计中断退出! ' , 16 , 1 )
return -- 立即返回,退出存储过程
end
print ' 笔试平均分: ' + convert ( varchar ( 5 ), @writtenPass )
print ' 机试平均分: ' + cast ( @labPass as varchar ( 5 ))
print ' ------------------------------------- '
print ' 参加本次考试没有通过的学生 '
select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
where writtenExam < @writtenPass or labExam < @labPass
/**/ /*------统计不有通过考试的学员人数------*/
select @notpassSum = count (stuNo) from stuMarks
where writtenExam < @writtenPass or labExam < @labPass
go
/**/ /**--调用存储过程--**/
-- 定义变量,用于存放调用存储过程时返回的结果--
declare @sum int , @t int
-- 调用时也带output,笔试及格线为,机试及格线默认为
execute proc_stu @sum output, 800 -- 大于报错
set @t = @@ERROR -- raiserror报错误后@@ERROR将不等于,表示有错
if @t <> 0
print ' @@ERROR的值是: ' + convert ( varchar ( 5 ), @t )
return -- 退出批处理,后续语句不执行.
select ' 未通过人数 ' + cast ( @sum as varchar ( 5 )) + ' 人 '
go