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;
/*支付賬戶個(gè)數(shù)*/
declare arrCount int;
/*支付賬戶keys_id*/
declare payment_keys varchar(200);
/*支付額度最小的賬號(hào)ID*/
declare payment_key varchar(20);
/*當(dāng)天支付賬戶已使用額度統(tǒng)計(jì)*/
declare order_sum decimal(8,2);
/*當(dāng)天支付賬戶剩余額度*/
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存儲(chǔ)過(guò)程的調(diào)用示例
call? pro_query_payment_key(1000,5,0,@result);
select @result;MYSQL入門