Mysql学习mysql带输出参数存储过程(代码与示例)

CREATE DEFINER=`root`@`%` PROCEDURE `pro_query_payment_key`(IN `f_pid` varchar(20), IN `paychannel` int, IN `f_money` DECIMAL(10,2), OUT `result` int)
BEGIN
/*while 变量*/
declare i_arrCount int;
/*支付账户个数*/
declare arrCount int;
/*支付账户keys_id*/
declare payment_keys varchar(200);
/*支付额度最小的账号ID*/
declare payment_key varchar(20);
/*当天支付账户已使用额度统计*/
declare order_sum decimal(8,2);
/*当天支付账户剩余额度*/
declare current_day_remanant_limit decimal(8,2);
/*变量初始化*/
set i_arrCount=0;
set arrCount=0;
set payment_keys = '';
set payment_key = '';
set order_sum = 0;
set result = 0;
set current_day_remanant_limit =0;
select keys_id into payment_keys from iweb_payment_key where pid=f_pid and payment_id=paychannel limit 1;MYSQL入门

select (select sum(daylimit) from iwb_mer_account_limit where pid=f_pid)-(select ifnull(sum(transMoney),0) from iweb_orders where pid=f_pid and paychannel=paychannel and state=1 and  date(payTime)=date(now()))
as remnant_limit into current_day_remanant_limit;MYSQL入门

if f_money<=current_day_remanant_limit thenMYSQL入门

if length(payment_keys)>0 then
select fun_split_total(payment_keys,',') into arrCount;
drop table if exists $tempTable;
create table $tempTable(
pay_key int DEFAULT 0,
orders_sum decimal(8,2)  DEFAULT 0
);
while i_arrCount<arrCount do
begin
set i_arrCount=i_arrCount+1;
select fun_split_string(payment_keys,',',i_arrCount) into payment_key;
select  ifnull(sum(transMoney),0) as transMoney into order_sum  from iweb_orders where pId=f_pid  and paychannel=paychannel and  date(payTime)=date(now()) and payKey=payment_key and state=1;
insert into $tempTable(pay_key,orders_sum) values(payment_key,order_sum);
end;
end while;
select ifnull(pay_key,0) into result from $tempTable where orders_sum=(select min(orders_sum) from $tempTable limit 1) limit 1;
drop table if exists $tempTable;
end if;
else
select -1 into result;
end if;
end;
 
--//mysql存储过程的调用示例
call  pro_query_payment_key(1000,5,0,@result);
select @result;MYSQL入门