进阶应用
3、参数带通配符
--查询订单编号头两位是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%';
4、带输出参数
--根据订单查询的信息,返回订单的城市及单价 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;
上面两个在平时工作中遇到的较少,需要的时候知道怎么用即可,1,2个是必须掌握的操作。
存储过程进行增删改
1、新增
--新增订单信息 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;
2、修改
--修改订单信息
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;
3、删除
--修改订单信息 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';
存储过程其他功能
这部分是选修内容,有兴趣的可以了解一下
1、重复编译存储过程
--重复编译 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
2、加密存储过程
--查询存储过程,进行加密,加密后不能查看和修改源脚本 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
执行完的效果如图: