USE  classicmodels;

DROP PROCEDURE IF EXISTS totalPaid;
CREATE PROCEDURE totalPaid()
  SELECT SUM(amount) FROM payments;

DROP PROCEDURE IF EXISTS amountPaid;
CREATE PROCEDURE amountPaid(IN custNum INT) 
  SELECT SUM(amount) FROM payments WHERE customerNumber = custNum;

DROP PROCEDURE IF EXISTS toShip;
CREATE PROCEDURE toShip(OUT numToShip INT) 
  SELECT COUNT(*) FROM orders WHERE shippedDate IS NULL INTO numToShip;

DROP PROCEDURE IF EXISTS nextCustomer;
CREATE PROCEDURE nextCustomer(INOUT custNum INT)
  SELECT MIN(customerNumber) FROM customers WHERE customerNumber > custNum INTO custNum;

DROP PROCEDURE IF EXISTS cumulativePayments;
DELIMITER ++

CREATE PROCEDURE cumulativePayments()
BEGIN
  DECLARE pr_date DATE;
  DECLARE pr_amt DOUBLE;
  DECLARE pr_cum DOUBLE;
  DECLARE numRows INT;
  DECLARE numDone INT;

  DECLARE paymentCursor CURSOR FOR 
    SELECT paymentDate, amount FROM payments ORDER BY paymentDate;

  DROP TABLE IF EXISTS cumulativePayments;
  CREATE TABLE cumulativePayments(date DATE, amount DOUBLE, cumulativeAmount DOUBLE);
  SET pr_cum = 0.0;
  SET numDone = 0;

  OPEN paymentCursor;
  SELECT FOUND_ROWS() INTO numRows;

  WHILE (numDone < numRows) DO
    FETCH paymentCursor INTO pr_date, pr_amt;
    SET pr_cum = pr_cum + pr_amt;
    INSERT INTO cumulativePayments VALUES(pr_date,pr_amt,pr_cum);
    SET numDone = numDone + 1;
    SELECT numDone;
  END WHILE;
END ++

DELIMITER ;









