什么是存储过程
存储过程(Stored Procedure)是一种存储在数据库中的批处理,它由一组完成特定功能的SQL语句集组成,具有输入参数,输出参数以及返回值。
存储过程实例
数据库表
无参存储过程
查询学生表并按姓名排序
- CREATE PROCEDURE GetOrderedStudents
- AS
- SELECT * FROM STUDENT ORDER BY NAME
- EXEC GetOrderedStudents
包含输入参数的存储过程(无默认值)
按照姓名查询学生信息
- CREATE PROCEDURE GetStudentByName
- @NAME VARCHAR(20)
- AS
- SELECT * FROM STUDENT WHERE NAME LIKE @NAME
- EXEC GetStudentByName 'ROY'
包含输入参数的存储过程(包含默认值)
按照姓名查询学生信息
- CREATE PROCEDURE GetStudent
- @NAME VARCHAR(20) = NULL
- AS
- SELECT * FROM STUDENT WHERE NAME = @NAME
- EXEC GetStudent 'ROSE'
包含输出参数的存储过程
向学生表插入一条信息并返回自动生成的ID值
- 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
在调用该存储过程时,首先声明一个变量,在使用EXEC执行存储过程时分别为两个参数赋值,其中为输出参数赋以生命的变量,并且要明确指明为OUTPUT.
包含返回值的存储过程
返回值可以用来返回数据,也可以通过返回值来确认存储过程执行的成功与失败。返回值必须为整数。从存储过程向调用代码传递返回值,使用RETURN语句。
修改学生姓名
- 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选项,将会使用缓存中的查询计划,跳过优化和编译的动作。