Форум пользователей MySQL

create procedure PaymentsForMonthAndPeriod()
begin
declare contractid int;
declare percentage int;
declare sumcontract int;
declare countmonth int;
declare paymentsmonth int;
declare paymentsperiod int;
declare start date;
declare end1 date;
declare done int default 0;

declare DoCursor cursor for select insurance_contract.Sum_insured, object_insurance.Percentage, insurance_contract.Beginning, insurance_contract.Ending
from insurance_contract, object_insurance where insurance_contract.Object = object_insurance.id_object;

declare continue handler for not found set done = 1;
drop temporary table if exists temptable;
create temporary table temptable (id_list int auto_increment, IDcontract int, ForMonth int, ForPeriod int, primary key(id_list));
open DoCursor;

repeat
fetch DoCursor into contractid, sumcontract, percentage, start, end1;
if not done then
set contractid = contractid;
set countmonth = (select period_diff(date_format(start, '%Y%m'), date_format(end1, '%Y%m')));
set paymentsmonth = sumcontract/100*percentage;
set paymentsperiod = paymentsmonth*countmonth;
insert into temptable(IDcontract, ForMonth, ForPeriod) values (contractid, paymentsmonth, paymentsperiod);
end if;
until done end repeat;
close DoCursor;
select IDcontract, ForMonth, ForPeriod from temptable;
end