点击上方SQL数据库开发,关注获取SQL视频教程
SQL专栏
create proc | procedure procedure_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
go
示例
create proc sp_test
@param1 int,
@param2 varchar(16)
as
select * from test
where id=@param1
and t_no=@param2;
go
优点:
存储过程调用时只需用提供存储过程名和必要的参数信息,从而可降低网络的流量。
数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。
1)系统管理员可以对执行的某一个存储过程进行权限限制,避免非授权用户对数据的访问
2)在通过网络调用过程时,只有对执行过程的调用是可见的。 因此,恶意用户无法看到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。
3)使用过程参数有助于避免 SQL 注入攻击。 因为参数输入被视作文字值而非可执行代码,所以,攻击者将命令插入过程内的 Transact-SQL 语句并损害安全性将更为困难。
4)可以对过程进行加密,这有助于对源代码进行模糊处理。
劣势:
由于IDE的问题,存储过程的开发调试要比一般程序困难。
基础应用
示例:查询订单表中订单总数
--查询存储过程
IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
DROP procedure PROC_ORDER_COUNT;
GO
CREATE procedure PROC_ORDER_COUNT
AS
SELECT COUNT(OrderID) FROM Orders;
GO
--执行上述存储过程:
EXEC PROC_ORDER_COUNT;
示例:根据城市查询订单数量
--查询存储过程,根据城市查询总数
IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
DROP procedure PROC_ORDER_COUNT;
GO
CREATE procedure PROC_ORDER_COUNT(@city nvarchar(50))
AS
SELECT COUNT(OrderID) FROM Orders WHERE City=@city
GO
--执行上述存储过程:
EXEC PROC_ORDER_COUNT N'GuangZhou';
进阶应用
--查询订单编号头两位是LJ的订单信息,含通配符
IF OBJECT_ID (N'PROC_ORDER_INFO', N'P') IS NOT NULL
DROP procedure PROC_ORDER_INFO;
GO
CREATE procedure PROC_ORDER_INFO
@OrderID nvarchar(50)='LJ%' --默认值
AS
SELECT OrderID,City,OrderDate,Price FROM Orders
WHERE OrderID like @OrderID;
GO
--执行上述存储过程:
EXEC PROC_ORDER_INFO;
EXEC PROC_ORDER_INFO N'LJ%';
EXEC PROC_ORDER_INFO N'%LJ%';
--根据订单查询的信息,返回订单的城市及单价
IF OBJECT_ID (N'PROC_ORDER_INFO ', N'P') IS NOT NULL
DROP procedure PROC_ORDER_INFO ;
GO
CREATE procedure PROC_ORDER_INFO
@orderid nvarchar(50), --输入参数
@city nvarchar(20) out, --输出参数
@price float output --输入输出参数
AS
SELECT @city=City,@price=Price FROM Orders
WHERE OrderID=@orderid AND Price=@price;
GO
--执行上述存储过程:
declare @orderid nvarchar(50),
@city nvarchar(20),
@price int;
set @orderid= N'LJ0001';
set @price = 35.21;
exec PROC_ORDER_INFO @orderid,@city out, @price output;
select @city, @price;
--新增订单信息
IF OBJECT_ID (N'PROC_INSERT_ORDER', N'P') IS NOT NULL
DROP procedure PROC_INSERT_ORDER;
GO
CREATE procedure PROC_INSERT_ORDER
@orderid nvarchar(50),
@city nvarchar(20),
@price float
AS
INSERT INTO Orders(OrderID,City,Price)
VALUES(@orderid,@city,@price)
GO
--执行
EXEC PROC_INSERT_ORDER N'LJ0001',N'GuangZhou',35.21;
--修改订单信息
IF OBJECT_ID (N'PROC_UPDATE_ORDER', N'P') IS NOT NULL
DROP procedure PROC_UPDATE_ORDER;
GO
CREATE procedure PROC_UPDATE_ORDER
@orderid nvarchar(50),
@city nvarchar(20),
@price float
AS
UPDATE Orders SET OrderID=@orderid,City=@city,Price=@price;
GO
--执行
EXEC PROC_UPDATE_ORDER N'LJ0001',N'ShangHai',37.21;
--修改订单信息
IF OBJECT_ID (N'PROC_DELETE_ORDER', N'P') IS NOT NULL
DROP procedure PROC_DELETE_ORDER;
GO
CREATE procedure PROC_DELETE_ORDER
@orderid nvarchar(50),
AS
DELETE FROM Orders WHERE OrderID=@orderid;
GO
--执行
EXEC PROC_DELETE_ORDER N'LJ0001';
这部分是选修内容,有兴趣的可以了解一下
--重复编译
IF OBJECT_ID (N'PROC_ORDER_WITH_RECOMPILE', N'P') IS NOT NULL
DROP procedure PROC_ORDER_WITH_RECOMPILE;
GO
CREATE procedure PROC_ORDER_WITH_RECOMPILE
with recompile --重复编译
AS
SELECT * FROM Orders;
GO
--查询存储过程,进行加密,加密后不能查看和修改源脚本
IF OBJECT_ID (N'PROC_ORDER_WITH_ENCRYPTION', N'P') IS NOT NULL
DROP procedure PROC_ORDER_WITH_ENCRYPTION;
GO
CREATE procedure PROC_ORDER_WITH_ENCRYPTION
with encryption --加密
AS
SELECT * FROM Orders;
GO
--执行上述存储过程:
EXEC PROC_ORDER_WITH_ENCRYPTION
执行完的效果如图:
这就是存储过程的详细用法了。
关注SQL数据库开发公众号,在后台回复关键字:资料领取,可以获取一份精心整理的技术干货。
推荐阅读
点击「阅读原文」了解SQL训练营