学习目标
- 理解存储过得的概念、优点和类型
- 创建存储过程
- 执行存储过程
存储过程
存储过程是存储在数据库中的一种编译对象。它是一组为了完成特定功能而编写的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 男生人数