Advertisement
Guest User

Untitled

a guest
May 16th, 2018
115
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.21 KB | None | 0 0
  1. drop procedure make_payments;
  2. delimiter $$
  3. create procedure make_payments()
  4. begin
  5.  
  6.     declare finished int;
  7.     declare current_customer_id int;
  8.     declare current_plan_id int;
  9.     declare current_amount int;
  10.    
  11.     declare curs CURSOR FOR select c.customerID, pl.planID, a.amount - pl.monthly_fee from accounts as a
  12.     join customers as c on c.customerID = a.customer_id
  13.     join payments as p on p.customer_id = c.customerID
  14.     join plans as pl on pl.planID = p.plan_id;
  15.    
  16.     declare continue handler FOR NOT FOUND set finished = 1;
  17.    
  18.     set finished = 0;
  19.     open curs;
  20.    
  21.     curr_block:
  22.     WHILE (finished = 0)
  23.     do
  24.         fetch curs into current_customer_id, current_plan_id, current_amount;
  25.         select current_amount;
  26.         if (finished = 1)
  27.             then
  28.             leave curr_block;
  29.         end if;
  30.        
  31.         if (current_amount < 0)
  32.         then
  33.             insert into debtors(customer_id, plan_id, debt_amount)
  34.             values (current_customer_id, current_plan_id, current_amount);
  35.         elseif (current_amount > 0)
  36.             then
  37.             update accounts as a
  38.             set a.amount = current_amount where customer_id = current_customer_id;
  39.         end if;
  40.     end while;
  41.    
  42. end $$
  43. delimiter ;
  44.  
  45. call make_payments;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement