四种存储过程
-- 存储过程 out
delimiter //
create procedure stampItemOut(out taxableDocPriceMin DECIMAL)
begin
select MIN(taxabledocprice) into taxableDocPriceMin from taxstamptaxregister;
end //
delimiter ;
call stampItemOut(@taxableDocPriceMin);
select @taxableDocPriceMin;
-- 存储过程 in
delimiter //
create procedure stampItemIn(in itemCode varchar(7))
begin
select min(taxabledocprice) from taxstamptaxregister1 where taxableitem = itemCode;
end
delimiter ;
call stampItemIn('2207_07');
set @itemCode2 = '2207_02';
call stampItemIn(@itemCode2);
-- 存储过程 in and out
delimiter //
create procedure stampItemInAndOut(in itemCode varchar(7), out minItemCodeDocPrice decimal(10,2))
begin
select min(taxabledocprice) into minItemCodeDocPrice from taxstamptaxregister where taxableitem = itemCode;
end //
delimiter ;
call stampItemInAndOut('2207_02',@minItemCodeDocPrice);
select @minItemCodeDocPrice;
-- 存储过程 inout
delimiter //
create procedure stampItemInOut(inout itemName varchar(20))
begin
select name into itemName from bftaxitem where code = itemName;
end //
delimiter ;
set @item := '2207_02';
call stampItemInOut(@item)
select @item
存储函数
必须有返回类型,使用returns声明返回类型
函数体中必须有返回值,使用return
delimiter $
create function reverseStampItemName(itemCode varchar(7))
returns varchar(20)
deterministic
contains sql
reads sql DATA
begin
return (select name from bftaxitem where code = itemCode);
end $
delimiter ;
select reverseStampItemName('2207_09') from dual;
set global log_bin_trust_function_creators = 1;
-- 使用变量实现的函数
delimiter $$
create function queryCount(itemCode varchar(7))
returns int
begin
return (select count(1) from taxstamptaxregister1 where taxableitem = itemCode);
end
delimiter ;
set @countItem := '2207_01';
select queryCount(@countItem) from dual;
select count(*) from taxstamptaxregister1 where taxableitemcode = '2207_01';
characteristic配置
deterministic
contains sql
reads sql DATA
#如果不想每次都设置以上的characteristic,可以使用
set global log_bin_trust_function_creators = 1;
查看创建命令
show create procedure stampItemInAndOut;
show create function reverseStampItemName;
查看状态
show procedure status;
show procedure status like 'stampItemIn%';
show function status;
show function status like 'reverseStampItemName%';
存储过程存储在information_schema.ROUTINES表中,通过ROUTINE_NAME 查询,存储过程和函数区分大小写。
select * from information_schema.ROUTINES where ROUTINE_NAME = 'reverseStampItemName';
修改存储过程和函数
只能修改特性characteristic,比如权限调用者,
#修改权限和备注。
alter procedure stampItemInAndOut sql security invoker COMMENT '转换名字';
删除
drop function if exists reverseStampItemName;
drop procedure if exists stampItemInAndOut;