SQL 存储过程 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

-- 创建一个存储过程，参数为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时，会传入变量的值，并且存储过程内部的赋值运算可以影响到外部传入的变量

-- 将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('辣条');

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

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

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

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

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

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