MySQL存储过程和函数

四种存储过程

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