大数据&云计算|SQL存储过程的详细用法，不信你看不懂 |存储过程|用法|

本文插图
作者：丶平凡世界
来源： SQL数据库开发
时不时有小伙伴私聊我这个存储过程怎么这么难啊？
说实话，我刚开始学SQL也觉得写存储过程可能是整个SQL开发中最难的了。因为存储过程简单起来可以只写一句SELECT就行，复杂起来写几天几夜也未必能写完（夸张了）。
但也不是说学不会，今天就给大家好好恶补一下存储过程的一些常规用法，一些非常规用法实在是太多了，这里就不一一列举了。
创建存储过程
create proc | procedure procedure_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
go
示例
createproc sp_test@param1 int,@param2 varchar(16)asselect * from test where id=@param1and t_no=@param2go
上面就是一个简单的示例。
存储过程在创建阶段可以带参数或不带参数，不带参数的一般是执行一些不需要传递参数的语句就可以完成的功能，带参数那就是需要传递参数的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 NULLDROP procedure PROC_ORDER_COUNTGOCREATE procedure PROC_ORDER_COUNTAS SELECT COUNT(OrderID) FROM OrdersGO--执行上述存储过程：EXEC PROC_ORDER_COUNT
2、创建带参数的存储过程
示例：根据城市查询订单数量
--查询存储过程，根据城市查询总数IF OBJECT_ID (N"PROC_ORDER_COUNT", N"P") IS NOT NULLDROP procedure PROC_ORDER_COUNTGOCREATE procedure PROC_ORDER_COUNT(@city nvarchar(50))ASSELECT COUNT(OrderID)FROM Orders WHERE City=@cityGO--执行上述存储过程：EXEC PROC_ORDER_COUNTN"GuangZhou"
进阶应用
3、参数带通配符
--查询订单编号头两位是LJ的订单信息，含通配符IF OBJECT_ID (N"PROC_ORDER_INFO", N"P") IS NOT NULLDROP procedure PROC_ORDER_INFOGOCREATE procedure PROC_ORDER_INFO@OrderIDnvarchar(50)="LJ%"--默认值AS SELECT OrderID,City,OrderDate,Price FROM OrdersWHERE OrderID like @OrderIDGO--执行上述存储过程：EXEC PROC_ORDER_INFOEXEC PROC_ORDER_INFO N"LJ%"EXEC PROC_ORDER_INFO N"%LJ%"
4、带输出参数
--根据订单查询的信息，返回订单的城市及单价IF OBJECT_ID (N"PROC_ORDER_INFO ", N"P") IS NOT NULLDROP procedure PROC_ORDER_INFO GOCREATE procedure PROC_ORDER_INFO@orderidnvarchar(50),--输入参数@citynvarchar(20) out,--输出参数@price float output--输入输出参数AS SELECT @city=City,@price=Price FROM OrdersWHERE OrderID=@orderidAND Price=@priceGO--执行上述存储过程：declare @orderidnvarchar(50),@citynvarchar(20),@price intset @orderid= N"LJ0001"set @price = 35.21exec PROC_ORDER_INFO @orderid,@city out, @price outputselect @city, @price
上面两个在平时工作中遇到的较少，需要的时候知道怎么用即可， 1 ， 2个是必须掌握的操作。
存储过程进行增删改
1、新增
--新增订单信息IF OBJECT_ID (N"PROC_INSERT_ORDER", N"P") IS NOT NULLDROP procedure PROC_INSERT_ORDERGOCREATE procedure PROC_INSERT_ORDER@orderidnvarchar(50),@city nvarchar(20),@price floatAS 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_ORDERGOCREATE procedure PROC_UPDATE_ORDER @orderid nvarchar(50), @city nvarchar(20), @price floatASUPDATE Orders SET OrderID=@orderid,City=@city,Price=@priceGO--执行EXEC PROC_UPDATE_ORDER N"LJ0001",N"ShangHai",37.21
3、删除
--修改订单信息IF OBJECT_ID (N"PROC_DELETE_ORDER", N"P") IS NOT NULLDROP procedure PROC_DELETE_ORDERGOCREATE procedure PROC_DELETE_ORDER@orderid nvarchar(50),AS DELETEFROM Orders WHERE OrderID=@orderidGO--执行EXEC PROC_DELETE_ORDER N"LJ0001"
存储过程其他功能
这部分是选修内容，有兴趣的可以了解一下
1、重复编译存储过程
--重复编译IF OBJECT_ID (N"PROC_ORDER_WITH_RECOMPILE", N"P") IS NOT NULLDROP procedure PROC_ORDER_WITH_RECOMPILEGOCREATE procedure PROC_ORDER_WITH_RECOMPILEwith recompile --重复编译AS SELECT * FROM OrdersGO 分页标题
2、加密存储过程
--查询存储过程，进行加密，加密后不能查看和修改源脚本IF OBJECT_ID (N"PROC_ORDER_WITH_ENCRYPTION", N"P") IS NOT NULLDROP procedure PROC_ORDER_WITH_ENCRYPTIONGOCREATE procedure PROC_ORDER_WITH_ENCRYPTIONwith encryption --加密AS SELECT * FROM OrdersGO--执行上述存储过程：EXEC PROC_ORDER_WITH_ENCRYPTION
执行完的效果如图：

本文插图
【来源：CDA数据分析师】
声明：转载此文是出于传递更多信息之目的。若有来源标注错误或侵犯了您的合法权益，请作者持权属证明与本网联系，我们将及时更正、删除，谢谢。邮箱地址：newmedia@xxcb.cn
【大数据&云计算|SQL存储过程的详细用法，不信你看不懂】

大数据&amp;云计算|SQL存储过程的详细用法，不信你看不懂

大数据&云计算|SQL存储过程的详细用法，不信你看不懂