简介(what):
存储过程(Stored Procedure)是一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
一句话:存储过程就是一个或者多个已经编译好的sql语句。
优点和缺点(why)
优势:
1、提高性能
SQL语句在创建过程时进行分析和编译。 存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划,这样,在执行过程时便可节省此开销。
2、降低网络开销
存储过程调用时只需用提供存储过程名和必要的参数信息,从而可降低网络的流量。
3、便于进行代码移植
数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。
4、更强的安全性
1)系统管理员可以对执行的某一个存储过程进行权限限制,避免非授权用户对数据的访问
2)在通过网络调用过程时,只有对执行过程的调用是可见的。 因此,恶意用户无法 看到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。
3)使用过程参数有助于避免 SQL 注入攻击。 因为参数输入被视作文字值而非可执行代码,所以,攻击者将命令插入过程内的 Transact-SQL 语句并损害安全性将更为困难。
4)可以对过程进行加密,这有助于对源代码进行模糊处理。
劣势:
1、存储过程需要专门的数据库开发人员进行维护,但实际情况是,往往由程序开发员人员兼职
2、设计逻辑变更,修改存储过程没有SQL灵活
使用场景(where)
当一个业务同时对多个表进行处理的时候采用存储过程比较合适。
如何操作(how)
创建存储过程
create procedure sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
.........
end
以上格式还可以简写成:
create proc sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
.........
end
/*注:“sp_name”为需要创建的存储过程的名字,该名字不可以以阿拉伯数字开头*/
删除存储过程
- 基本语法:
drop procedure sp_name
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程
调用存储过程
1.基本语法:
exec sp_name [参数名]
具体应用:
1、创建不带参数的存储过程
--查询存储过程(查询学生总数)
CREATE procedure PROC_SELECT_STUDENTS_COUNT
AS
SELECT COUNT(ID) FROM Students
GO
--执行存储过程:
EXEC PROC_SELECT_STUDENTS_COUNT
2、带参数的存储过程
--查询存储过程,根据班级查询总数
CREATE procedure PROC_SELECT_STUDENTS_BY_class_COUNT(@class nvarchar(50))
AS
SELECT COUNT(ID) FROM Students WHERE class=@class
GO
--执行
Exec PROC_SELECT_STUDENTS_BY_CITY_COUNT 1班
3、 修改存储过程
--修改存储过程
alter proc proc_get_student
as
select * from student;
4、带有输出参数的存储过程
create proc proc_getStudentRecord(
@id int, --默认输入参数
@name varchar(20) out, --输出参数
@age varchar(20) output--输入输出参数
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go
--
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;