Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop procedure make_payments;
- delimiter $$
- create procedure make_payments()
- begin
- declare finished int;
- declare current_customer_id int;
- declare current_plan_id int;
- declare current_amount int;
- declare curs CURSOR FOR select c.customerID, pl.planID, a.amount - pl.monthly_fee from accounts as a
- join customers as c on c.customerID = a.customer_id
- join payments as p on p.customer_id = c.customerID
- join plans as pl on pl.planID = p.plan_id;
- declare continue handler FOR NOT FOUND set finished = 1;
- set finished = 0;
- open curs;
- curr_block:
- WHILE (finished = 0)
- do
- fetch curs into current_customer_id, current_plan_id, current_amount;
- select current_amount;
- if (finished = 1)
- then
- leave curr_block;
- end if;
- if (current_amount < 0)
- then
- insert into debtors(customer_id, plan_id, debt_amount)
- values (current_customer_id, current_plan_id, current_amount);
- elseif (current_amount > 0)
- then
- update accounts as a
- set a.amount = current_amount where customer_id = current_customer_id;
- end if;
- end while;
- end $$
- delimiter ;
- call make_payments;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement