SQL基础——存储过程

什么是存储过程

存储过程(Stored Procedure)是一种存储在数据库中的批处理，它由一组完成特定功能的SQL语句集组成，具有输入参数，输出参数以及返回值。

存储过程实例

数据库表

无参存储过程

查询学生表并按姓名排序

GO
CREATE PROCEDURE GetOrderedStudents
AS
SELECT * FROM STUDENT ORDER BY NAME

EXEC GetOrderedStudents

这是最简单的存储过程，通过CREATE PROCEDURE语句创建存储过程，GetOrderedStudents为存储过程名，AS之后为存储过程的主体。EXEC用来执行存储过程。

包含输入参数的存储过程（无默认值）

按照姓名查询学生信息

GO
CREATE PROCEDURE GetStudentByName
	@NAME VARCHAR(20)
AS
SELECT * FROM STUDENT WHERE NAME LIKE @NAME

EXEC GetStudentByName 'ROY'

在声明变量的时候，必须以@符号开始，按照变量名称，数据类型，变量默认值，以及参数的方向来声明一个变量。此例中，NAME为变量名，VARCHAR(20)为变量的数据类型，并未指明参数的默认值，未指明参数为OUTPUT类型则为输入参数。由于未指明变量默认值，则默认在调用该存储过程时，输入参数是必须的，若去掉参数 'ROY', 则数据库会报错。

包含输入参数的存储过程（包含默认值）

按照姓名查询学生信息

GO
CREATE PROCEDURE GetStudent
	@NAME VARCHAR(20) = NULL
AS
SELECT * FROM STUDENT WHERE NAME = @NAME

EXEC GetStudent 'ROSE'

此时即使在执行时不添加参数 ‘ROSE’, 数据库也不会报错。

包含输出参数的存储过程

向学生表插入一条信息并返回自动生成的ID值

GO
CREATE PROCEDURE InsertStudent
	@NAME VARCHAR(20),
	@ID INT OUTPUT
AS
INSERT INTO STUDENT(NAME) VALUES(@NAME)
SET @ID = SCOPE_IDENTITY()

DECLARE @newID INT
EXEC InsertStudent @NAME = 'LORA',@ID=@newID OUTPUT
SELECT @newID

第一个参数未指定OUTPUT, 则为输入参数，第二个参数ID数据类型未INT, 使用OUTPUT关键字指定该参数为输出参数，也可简写未OUT.AS后为存储过程主体，在插入信息后，为输出参数ID赋以自动生成的ID值。

在调用该存储过程时，首先声明一个变量，在使用EXEC执行存储过程时分别为两个参数赋值，其中为输出参数赋以生命的变量，并且要明确指明为OUTPUT. 

包含返回值的存储过程

返回值可以用来返回数据，也可以通过返回值来确认存储过程执行的成功与失败。返回值必须为整数。从存储过程向调用代码传递返回值，使用RETURN语句。

修改学生姓名

GO
CREATE PROCEDURE UPDATESTUDENT
	@ID INT,
	@NAME VARCHAR(20)
AS
UPDATE STUDENT SET NAME = @NAME WHERE ID=@ID
RETURN 1

EXEC UPDATESTUDENT 3,'Vince'

存储过程的优势

1. 安全性

通过创建存储过程可避免用户访问数据库底层。例如可以使用户只能通过存储过程来对数据进行修改而不能直接修改数据库表。

2. 高效性

在第一次执行存储过程时，存储过程会被优化，查询计划被编译并储存在缓存中，之后再执行时，如果没有使用WITH RECOMPILE选项，将会使用缓存中的查询计划，跳过优化和编译的动作。