| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/JimMartinson/Classes/CST1602/Labs/ |
Upload File : |
Lab 4 - PROCEDURE BalanceDue
/*
BalanceDue(customerNumber, @HasBalanceDue, @AmountDue)
Returns: @HasBalanceDue as 'Yes' if the customer has a balance remaining, else 'No'.
@AmountDue as any amount unpaid.
Usage: BalanceDue(customerNumber, @HasBalanceDue, @AmountDue);
*/
USE classicmodels;
DROP PROCEDURE IF EXISTS BalanceDue;
DELIMITER $$
CREATE PROCEDURE BalanceDue(
IN p_customerNumber int(11),
OUT p_HasBalanceDue varchar(3),
OUT p_AmountDue DOUBLE
)
BEGIN
DECLARE totalAmountDue DOUBLE;
DECLARE totalPayments DOUBLE;
SELECT CONVERT(SUM(quantityOrdered * priceEach),DECIMAL(8,2)) INTO totalAmountDue
FROM orderdetails
JOIN orders ON orders.orderNumber = orderdetails.orderNumber
WHERE orders.customerNumber = p_customerNumber;
SELECT CONVERT(SUM(amount),DECIMAL(8,2)) INTO totalPayments
FROM payments
WHERE payments.customerNumber = p_customerNumber;
IF ( totalAmountDue > totalPayments ) THEN
SET p_HasBalanceDue = "Yes";
SET p_AmountDue = CONVERT( totalAmountDue - totalPayments, DECIMAL(8,2) );
ELSE
SET p_HasBalanceDue = "No";
SET p_AmountDue = 0;
END IF;
# UNCOMMENT line below to debug values.
#SELECT customerNumber, customerName, p_HasBalanceDue, p_AmountDue, CONVERT(totalAmountDue,DECIMAL(8,2)), CONVERT(totalPayments,DECIMAL(8,2)) FROM customers WHERE customerNumber = p_customerNumber;
END$$
DELIMITER ;
-10 Your BalanceDue PROCEDURE causes an error when called.
You have multiple issues. You query gets multiple customerNumber values.
You do not need to even get customerNumber as it is sent to the procedure as p_customerNumber.
Your FROM clause does not state how the tables are related. You need ON or USING clauses.
The last and clause on line 20 does not declare the table customerNumber should be used from and causes an error.
You misspelled p_customerNumber as pCustomerNumber on line 20.
I really think you need two queries:
One to get TotalOrderAmount (which must be declared)
One to get TotalAmountPaid (which must be declared)
Then you can calculate p_AmountDue and p_HasBalanceDue.
-10 Your BalanceDue PROCEDURE causes an error when called.
Line 13; There are no tables called customerName or customerNumber.
There are many other problems with the procedure.
It does not calculate p_AmountDue or set p_HasBalanceDue.
Think about what you need in order to calculate p_AmountDue...
Then code SELECT statements that get you the values you need.
-10 Your BalanceDue PROCEDURE does not produce the proper results.
Your SELECT query on line 7 does not limit the results to just the customerNumber parameter.
It also does not store the result value for use later.
Your SELECT query on line 11 does not limit the results to just the customerNumber parameter.
It also does not store the result value for use later.
Your SELECT queries on line 15 and 18 are nonsensical.
-1 Line 13; You are storing the result in a global variable.
Instead you should DECLARE a local variable and store the result in that.
This is a bad idea as you may be changing the value of a variable already in use.
Also, users of your procedure cannot see the code and do not know this is happening.
You can also add an INTO clause to your SELECT instead of using SET.
-1 Line 19; You are storing the result in a global variable.
See above.
-1 Line 15; You are storing the result in a global variable.
Instead you should store it in the local variable you have DECLAREd.
This is a bad idea as you may be changing the value of a variable already in use.
Also, users of your procedure cannot see the code and do not know this is happening.
You can also add an INTO clause to your SELECT instead of using SET.
-1 Line 24; You are storing the result in a global variable.
See above.
SELECT IF(@payments<@purchasedAmount,"YES", "NO") INTO p_HasBalanceDue; ;
-0 Line 29. You could also use an IF..THEN..ELSE..END IF statement and SET statements.
Your method is harder to understand at first, but it is elegant and shorter.
-0 Line 32; Same here.