Sql server数据库设计 4
第四章(存储过程)
存储过程的概念
一、什么是存储过程
1、存储过程(procedure)类似于C语言中的函数
2、用来执行管理任务或应用复杂的业务规则
3、存储过程可以带参数,也可以返回结果
4、存储过程可以包含数据操纵语句、变量、逻辑 控制语句等
二、存储过程的分类
1、系统存储过程
由系统定义,存放在master数据库中
类似C语言中的系统函数
系统存储过程的名称都以“sp_”开头或”xp_”开头
2、用户自定义存储过程
由用户在自己的数据库中创建的存储过程
类似C语言中的用户自定义函数
三、存储过程的优点
1、执行速度更快
2、允许模块化程序设计
3、提高系统安全性
4、减少网络流通量
系统存储过程
一、常用的扩展存储过程:xp_cmdshell
可以执行DOS命令下的一些的操作
以文本行方式返回任何输出
二、调用语法:
EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
三、系统常用存储过程
系统存储过程 说明
sp_databases 列出服务器上的所有数据库。
sp_helpdb 报告有关指定数据库或所有数据库的信息
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象的列表
sp_columns 回某个表列的信息
sp_help 查看某个表的所有信息
sp_helpconstraint 查看某个表的约束
sp_helpindex 查看某个表的索引
sp_stored_procedures 列出当前环境中的所有存储过程。
sp_password 添加或修改登录帐户的密码。
sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
用户自定义存储过程
一、创建不带参数的存储过程
1、定义存储过程的语法
和C语言的函数一样,参数可选
参数分为输入参数、输出参数
输入参数允许有默认值
create procedure 存储过程名
as
begin
SQL语句
end
go
eg:--创建不带参数的存储过程
create procedure proc_getpc
--没有参数
as
begin
select pcid as '电脑编号','使用状态'=case
when pcuse=0 then '空闲'
when pcuse=1 then '使用'
end,
pcnote as '备注'
from pcinfo where pcuse=0
end
go
EXECUTE(执行)语句用来调用存储过程。
· 调用的语法如下:
EXEC 过程名 [参数]
EXEC proc_getpc
二、创建带输入参数的存储过程
1、语法:
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值,
…… ,
@参数n 数据类型 = 默认值
AS
begin
SQL语句
end
GO
2、为输入参数指定默认值
eg:@参数1 int=0,@参数2 varchar(10)=" "
3、调用存储过程
A:execute 存储过程名 default
B:execute 存储过程名 @参数=default
注意:是否指定默认值,指定什么样的默认值需要根据存储过程实现的功能来决定。
三、创建带输出参数的存储过程
1、语法:
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值,
…… ,
@参数n 数据类型 output
AS
begin
SQL语句
end
GO
2、调用存储过程
declare @参数 int --定义变量,用于存放调用存储过程是返回的结果
exec proc_getbalance 4,@balance output
四、return的使用
1、return关键字可以终止存储过程或者返回数值(返回int类型)
eg:
--创建存储过程,用户新增电脑,并使用return返回新增电脑的编号
create procedure proc_addpc
@pcnode varchar(20)
as
begin
--插入数据,默认使用状态为空闲
insert into pcinfo values(0,@pcnode)
--返回当前自动增长列的值
return @@identity 用于返回数值
end
go
--调用存储过程
declare @pcid int--声明变量,用于保存存储过程的返回值
-执行存储过程,为变量赋值,并指定输入参数
exec @pcid=proc_addpc '新增的电脑'
select @pcid as '新增电脑编号' --输出变量
处理存储过程中的错误
一、语法
raiserror(自定义错误信息,错误的严重级别,错误的状态)
自定义错误信息:表示输出的错误提示文本
错误的严重级别:表示用户定义错误的严重性级别。(0—18)级。 值越大,错误性就越小
错误的状态:表示自定义错误的状态,值的范围在1—127
eg:
if OBJECT_ID('proc_myprocraiseerror','procedure') is not null
drop procedure proc_myprocraiseerror
go
create procedure proc_myprocraiseerror
@cname varchar(20)='NET22'
as
begin
if not exists (select * from classInfo where cname = @cname)
begin
raiserror('NET22班已经存在不允许添加',16,1);
end
insert into classInfo values(@cname)
return @@identity
end;
declare @cno int
execute @cno = proc_myprocraiseerror default
begin
print '新增的班级编号是:' + @cno)
end