SQL 存储过程 procedure 讲解+代码实例

简介: SQL 存储过程 procedure 讲解+代码实例

1. 存储过程概述


存储过程是一种在数据库中存储复杂程序,以便外部程序调用的数据库对象。


存储过程经编译创建并保存在数据库中,用户可通过指定存储过程的名字和给定参数来调用执行。存储过程思想上很简单,就是数据库SQL语言层面的代码封装与重用。


作个形象的比喻,存储过程也可以看作是一个"加工厂",它接收"原料"(in参数)然后将这些原料加工处理成"产品"(out/inout参数),再把"产品"交付给"调用者"。


存储过程的优点


减少网络流量的使用

将重复性很高的一系列操作,封装到一个存储过程中,简化了SQL的调用

批量处理: 通过循环减少流量,也就是“跑批”

统一接口,确保数据安全。

存储过程的缺点


存储过程往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。

存储过程的性能调校与撰写受限于具体的数据库系统。


2. 创建存储过程 create procedure


创建存储过程的语法如下:


create [definer = {user|current_user}] procedure [过程名] ( [参数1],[参数2],...,[参数n] )
  [[特征1],[特征2],...,[特征n]] 
  [SQL代码];
-- definer 用于指定存储过程由哪个用户定义,默认是当前用户,注意不是指定存储过程的使用权限
-- [过程名] 该过程名用于指定存储过程,分别用户调用
-- [参数] 参数的形式为:[in|out|inout] [参数名] [参数类型],例如:in pcd_id int、inout pcd_username varchar(3)
-- -- -- in 表示该参数为输入参数,即调用时传入
-- -- -- out 表示该参数为输出参数,即存储过程的返回值
-- -- -- inout 表示该参数即可输入也可输出
-- -- -- 参数类型 可以是 int或者varchar()
-- [特征] 特征是存储过程的属性,它包括了如下几个可选特征
-- -- -- comment '' 注释信息,例如:commet '这是一个存储过程'
-- -- -- language sql 指定存储过程使用的语言为sql
-- -- -- [not] deterministic 是否指定一个输入仅对应一个输出(映射),包含下面2个可选参数
-- -- -- -- -- not deterministic(默认),表示不指定映射关系;
-- -- -- -- -- deterministic,表示指定映射关系
-- -- -- [contains sql | no sql | reads sql data | modifies sql data] 明确子程序对数据的操作,包含下面4个可选参数
-- -- -- -- -- contains sql(默认),表示子程序不包含读或者写数据的语句
-- -- -- -- -- no sql,表示子程序不包含sql
-- -- -- -- -- reads sql data,表示子程序包含读数据的语句,但是不包含写数据的语句
-- -- -- -- -- modifies sql data,表示子程序包含写数据的语句
-- -- -- sql security [definer|invoker] 指定调用权限,包含下面两个可选参数
-- -- -- -- -- sql security definer(默认),使用创建者权限调用存储过程,不受限制
-- -- -- -- -- sql security invoker,使用调用者权限调用存储过程,只有被赋予权限的调用者才能调用


例如:


-- 将tab_sale表中sale_name字段值等于传入参数的记录删除
create procedure delete_sale(in pcd_sale_name varchar(3))
begin
  delete from tab_table
  where tab_table.sale_name= pcd_sale_name ;
end


2.1. 参数 in、out、inout


下面三段代码是对存储过程的参数in、out和inout的代码说明:


-- 创建一个存储过程,参数为in
create procedure test(in i int)
begin
  select i; -- 返回结果i,i=1
  set i=2;
  select i; -- 返回结果i,i=2
end
-- 调用存储过程test,并传入一个参数i=1
set @i = 1; -- 定义全局变量i
call test(@i);
select @i; -- 返回结果i,i=1
-- 由以上代码可知当存储过程的参数为in时,会传入变量的值,并且存储过程内部的赋值不会影响到外部传入的变量


-- 创建一个存储过程,参数为out
create procedure test(out i int)
begin
  select i; -- 返回结果i,i=Null
  set i=2;
  select i; -- 返回结果i,i=2
end
-- 调用存储过程test,并传入一个参数i=1
set @i=1; -- 定义全局变量i
call test(@i);
select @i; -- 返回结果i,i=2
-- 由以上代码可知当存储过程的参数为out时,变量的值不会被传入,并且存储过程内部的赋值运算可以影响到外部传入的变量


-- 创建一个存储过程,参数为inout
create procedure test(inout i int)
begin
  select i; -- 返回结果i,i=1
  set i=2;
  select i; -- 返回结果i,i=2
end
-- 调用存储过程test,并传入一个参数i=1
set @i=1; -- 定义全局变量i
call test6(@i);
select @i; -- 返回结果i,i=2
-- 由以上代码可知当存储过程的参数为out时,会传入变量的值,并且存储过程内部的赋值运算可以影响到外部传入的变量


3. 调用存储过程 call


存储过程的调用使用call关键字

例如:


-- 将tab_sale表中sale_name字段值等于'辣条'的记录删除
create procedure delete_sale(in pcd_sale_name varchar(3))
delete from tab_table
where tab_table.sale_name= pcd_sale_name ;
-- 调用存储过程delete_sale
call delete_sale('辣条');


4. 查看存储过程


4.1. 查看存储过程的状态


语法如下:


show procedure status like '[状态名]';


[状态名]可以查看博客:show status 查看各种状态


例如:


--查看查询时间超过long_query_time秒的查询的个数。
show procedure status like 'slow_queries';


4.2. 查看存储过程的定义


语法如下:


show create procedure '[过程名]'


例如:


-- 查看存储过程delete_sale的定义
show create procedure delete_sale


结果如下:


image.png


4.2. 从information_schema.Routines表查看存储过程的信息 mysql


MySQL数据库的所有存储过程的信息都保存在information_ schema数据库中的routines表中,因此可以使用select语句查询存储过程的相关信息。


语法为:


select * from information_schema.ROUTINES
where routine_name='[过程名]'


例如下面的SQL语句是查看存储过程delete_sale相关信息的语句。


select * from information_schema.ROUTINES
where routine_name='delete_sale'


执行结果如下:


image.png


5. 修改存储过程 alter procedure


修改存储过程的特性可以使用alter procedure关键字,语法如下:


alter procedure [存储过程名] [特性]


例如:


-- 修改存储过程delete_sale,使它可以写数据(modifies sql data)
alter procedure delete_sale modifies sql data;


6. 删除存储过程 drop procedure


语法:


drop procedure [if exists] [存储过程名]


例如:


-- 如果存储过程delete_sale存在,则删除它
drop procedure if exists delete_sale;



相关文章
|
23小时前
|
存储 SQL 数据库
SQL Server 临时存储过程及示例
SQL Server 临时存储过程及示例
14 3
|
1天前
|
SQL 关系型数据库 MySQL
创建SQL数据库的基本步骤与代码指南
在信息时代,数据管理显得尤为重要,其中数据库系统已成为信息技术架构的关键部分。而当我们谈论数据库系统时,SQL(结构化查询语言)无疑是其中最核心的工具之一。本文将详细介绍如何使用SQL创建数据库,包括编写相应的代码和必要的步骤。由于篇幅限制,本文可能无法达到您要求的2000字长度,但会尽量涵盖创建数
12 3
|
1天前
|
SQL 安全 关系型数据库
SQL错误代码1303解析与解决方案:深入理解并应对权限问题
在数据库管理和开发过程中,遇到错误代码是常见的事情,每个错误代码都代表着一种特定的问题
|
3天前
|
存储 SQL 数据库
使用SQL创建视图和存储过程
使用SQL创建视图和存储过程
10 0
|
2月前
|
存储 SQL 安全
【数据库高手的秘密武器:深度解析SQL视图与存储过程的魅力——封装复杂逻辑,实现代码高复用性的终极指南】
【8月更文挑战第31天】本文通过具体代码示例介绍 SQL 视图与存储过程的创建及应用优势。视图作为虚拟表,可简化复杂查询并提升代码可维护性;存储过程则预编译 SQL 语句,支持复杂逻辑与事务处理,增强代码复用性和安全性。通过创建视图 `high_earners` 和存储过程 `get_employee_details` 及 `update_salary` 的实例,展示了二者在实际项目中的强大功能。
30 1
|
1月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能,它能够帮助数据库管理员在数据丢失或损坏的情况下,有效地恢复数据。通过定期备份数据库和事务日志,并在需要时按照正确的步骤恢复,可以最大限度地减少数据丢失的风险。需要注意的是,恢复数据是一个需要谨慎操作的过程,建议在执行恢复操作之前,详细了解相关的操作步骤和注意事项,以确保数据的安全和完整。
76 0
|
2月前
|
JSON 数据格式 Java
化繁为简的魔法:Struts 2 与 JSON 联手打造超流畅数据交换体验,让应用飞起来!
【8月更文挑战第31天】在现代 Web 开发中,JSON 成为数据交换的主流格式,以其轻量、易读和易解析的特点受到青睐。Struts 2 内置对 JSON 的支持,结合 Jackson 库可便捷实现数据传输。本文通过具体示例展示了如何在 Struts 2 中进行 JSON 数据的序列化与反序列化,并结合 AJAX 技术提升 Web 应用的响应速度和用户体验。
84 0
|
2月前
|
存储 SQL 数据库
如何使用 SQL Server 创建存储过程?
【8月更文挑战第31天】
81 0
|
2月前
|
SQL 数据库 索引
SQL 编程最佳实践简直太牛啦!带你编写高效又可维护的 SQL 代码,轻松应对数据库挑战!
【8月更文挑战第31天】在SQL编程中,高效与可维护的代码至关重要,不仅能提升数据库性能,还降低维护成本。本文通过案例分析探讨SQL最佳实践:避免全表扫描,利用索引加速查询;合理使用JOIN,避免性能问题;避免使用`SELECT *`,减少不必要的数据传输;使用`COMMIT`和`ROLLBACK`确保事务一致性;添加注释提高代码可读性。遵循这些实践,不仅提升性能,还便于后期维护和扩展。应根据具体情况选择合适方法并持续优化SQL代码。
33 0
|
SQL 关系型数据库 MySQL