SQLServer学习笔记十三：存储过程

学习目标

• 理解存储过得的概念、优点和类型
• 创建存储过程
• 执行存储过程

存储过程

存储过程是存储在数据库中的一种编译对象。它是一组为了完成特定功能而编写的T-SQL语句集。存储过程与程序设计语言中的函数类似，可以执行下列操作：

• 接受输入参数，并以输出参数的形式将多个值返回调用过程或批处理
• 可以包含数据操作、数据定义语句
• 可以调用其它过程

与函数不同的是，存储过程不能直接在表达式中使用。

存储过程的类型

• 系统存储过程，SQL Server系统内置的存储过程，存储在master数据库中，并以sp_为命名前缀。系统存储过程主要从系统表中获取信息，从而获取数据库信息，管理数据库对象。例如：sp_rename可以修改用户对象（表、视图、列…）的当前名称
• 扩展存储过程，可以动态加载和运行动态链接库（DDL），一般以xp_结尾。
• 用户定义的存储过程，由用户自行定义，存储在用户数据库中的存储过程。

创建存储过程

使用T-SQL语言创建存储过程的语法格式如下：

CREATE PROC [EDURE] procedure_name [;number ]
[ { @parameter data_type }[ VARYING ] [ = default ] [ OUTPUT ]] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] | [ FOR REPLICATION ]
AS sql_statement [ ...n ]

创建不带参数的存储过程

练习

从students表中获取少数民族的学生信息。

CREATE PROC student_min
AS
SELECT *
FROM students
WHERE nation != '汉族'

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

练习

从students表中查询指定姓名的学生信息，支持模糊查询。

CREATE PROC student_name
@sname nvarchar(20)
AS
SELECT * 
FROM students
WHERE sname LIKE '%' + @sname + '%'  

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

练习

从students表中查询指定性别的学生人数。

CREATE PROC student_sex
@sex nchar(10), @cnt int OUTPUT
AS
SELECT *
FROM students
WHERE gender = @sex  
SELECT @cnt = @@rowcount

执行存储过程

使用T-SQL执行存储过程的语法格式如下：

EXEC	[@return_value = ]{procedure_name}[;number]
		[[@parameter = ]{value[ OUTPUT]},
		@cnt = @cnt OUTPUT

执行不带参数的存储过程

EXEC student_name

执行带输入参数的存储过程

EXEC student_name 
@sname = '张'

执行带输出参数的存储过程

DECLARE @cnt int
EXEC student_sex N'男', @cnt OUTPUT
select @cnt AS 男生人数