但也不是说学不会,今天就给大家好好恶补一下存储过程的一些常规用法,一些非常规用法实在是太多了,这里就不一一列举了。
作者:丶平凡世界
来源: 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
上面就是一个简单的示例。
存储过程在创建阶段可以带参数或不带参数,不带参数的一般是执行一些不需要传递参数的语句就可以完成的功能,带参数那就是需要传递参数的SQL语句,就像上面的示例,传递了两个参数给SQL语句。带参数的一定要定义参数类型,是字符型的还要定义长度,给参数加默认值是可选的。
存储过程的优点和缺点
优点:
1、提高性能
SQL语句在创建过程时进行分析和编译。存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划,这样,在执行过程时便可节省此开销。
2、降低网络开销
存储过程调用时只需用提供存储过程名和必要的参数信息,从而可降低网络的流量。
3、便于进行代码移植
数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。
4、更强的安全性
1)系统管理员可以对执行的某一个存储过程进行权限限制,避免非授权用户对数据的访问
2)在通过网络调用过程时,只有对执行过程的调用是可见的。因此,恶意用户无法看到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。
3)使用过程参数有助于避免 SQL 注入攻击。因为参数输入被视作文字值而非可执行代码,所以,攻击者将命令插入过程内的 Transact-SQL 语句并损害安全性将更为困难。
4)可以对过程进行加密,这有助于对源代码进行模糊处理。
劣势:
1、逻辑处理吃力
SQL本身是一种结构化查询语言,但不是面向对象的的,本质上还是过程化的语言,面对复杂的业务逻辑,过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理,如果如果把业务逻辑全放在存储过程里面,违背了这一原则。
2、修改参数复杂
如果需要对输入存储过程的参数进行更改,或者要更改由其返回的数据,则您仍需要更新程序集中的代码以添加参数、更新调用,等等,这时候估计会比较繁琐了。
3、开发调试复杂
由于IDE的问题,存储过程的开发调试要比一般程序困难。
4、无法应用缓存
虽然有全局临时表之类的方法可以做缓存,但同样加重了数据库的负担。如果缓存并发严重,经常要加锁,那效率实在堪忧。
5、不支持群集
数据库服务器无法水平扩展,或者数据库的切割(水平或垂直切割)。数据库切割之后,存储过程并不清楚数据存储在哪个数据库中。
存储过程的具体应用
基础应用
1、创建不带参数的存储过程
示例:查询订单表中订单总数
--查询存储过程 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;
2、创建带参数的存储过程
示例:根据城市查询订单数量
--查询存储过程,根据城市查询总数 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';
进阶应用
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
执行完的效果如图:
以上就是存储过程的详细用法了。还有不明白或有疑问的地方,欢迎留言讨论。
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的需求持续飙升。世界经济论坛发布的《未来就业报告》, ...
2025-03-28你有没有遇到过这样的情况?流量进来了,转化率却不高,辛辛苦苦拉来的用户,最后大部分都悄无声息地离开了,这时候漏斗分析就非 ...
2025-03-27TensorFlow Datasets(TFDS)是一个用于下载、管理和预处理机器学习数据集的库。它提供了易于使用的API,允许用户从现有集合中 ...
2025-03-26"不谋全局者,不足谋一域。"在数据驱动的商业时代,战略级数据分析能力已成为职场核心竞争力。《CDA二级教材:商业策略数据分析 ...
2025-03-26当你在某宝刷到【猜你喜欢】时,当抖音精准推来你的梦中情猫时,当美团外卖弹窗刚好是你想吃的火锅店…… 恭喜你,你正在被用户 ...
2025-03-26当面试官问起随机森林时,他到底在考察什么? ""请解释随机森林的原理""——这是数据分析岗位面试中的经典问题。但你可能不知道 ...
2025-03-25在数字化浪潮席卷的当下,数据俨然成为企业的命脉,贯穿于业务运作的各个环节。从线上到线下,从平台的交易数据,到门店的运营 ...
2025-03-25在互联网和移动应用领域,DAU(日活跃用户数)是一个耳熟能详的指标。无论是产品经理、运营,还是数据分析师,DAU都是衡量产品 ...
2025-03-24ABtest做的好,产品优化效果差不了!可见ABtest在评估优化策略的效果方面地位还是很高的,那么如何在业务中应用ABtest? 结合企业 ...
2025-03-21在企业数据分析中,指标体系是至关重要的工具。不仅帮助企业统一数据标准、提升数据质量,还能为业务决策提供有力支持。本文将围 ...
2025-03-20解锁数据分析师高薪密码,CDA 脱产就业班助你逆袭! 在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的 ...
2025-03-19在 MySQL 数据库中,查询一张表但是不包含某个字段可以通过以下两种方法实现:使用 SELECT 子句以明确指定想要的字段,或者使 ...
2025-03-17在当今数字化时代,数据成为企业发展的关键驱动力,而用户画像作为数据分析的重要成果,改变了企业理解用户、开展业务的方式。无 ...
2025-03-172025年是智能体(AI Agent)的元年,大模型和智能体的发展比较迅猛。感觉年初的deepseek刚火没多久,这几天Manus又成为媒体头条 ...
2025-03-14以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-13以下的文章内容来源于刘静老师的专栏,如果您想阅读专栏《10大业务分析模型突破业务瓶颈》,点击下方链接 https://edu.cda.cn/go ...
2025-03-12以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-11随着数字化转型的加速,企业积累了海量数据,如何从这些数据中挖掘有价值的信息,成为企业提升竞争力的关键。CDA认证考试体系应 ...
2025-03-10推荐学习书籍 《CDA一级教材》在线电子版正式上线CDA网校,为你提供系统、实用、前沿的学习资源,助你轻松迈入数据分析的大门! ...
2025-03-07在数据驱动决策的时代,掌握多样的数据分析方法,就如同拥有了开启宝藏的多把钥匙,能帮助我们从海量数据中挖掘出关键信息,本 ...
2025-03-06