nrybSd54

1DROP PROCEDURE IF EXISTS functionSix;
2DELIMITER |
3CREATE PROCEDURE functionSix(IN firstNameGiven VARCHAR(25), IN lastNameGiven VARCHAR (25),
4	IN middleNameGiven VARCHAR (25))
5    
6BEGIN
7DECLARE finished INT;
8DECLARE tempFullName VARCHAR(25);
9DECLARE tempPaymentId INT;
10DECLARE tempContractId INT;
11DECLARE tempAmount DOUBLE;
12
13DECLARE procedureCursor CURSOR FOR 
14	SELECT CONCAT (firstName, ' ', middleName, ' ', lastName),
15	paymentID, contract_id, paymentAmount
16    FROM customers JOIN payments
17    ON customerID IN ( SELECT customer_id
18						FROM contracts JOIN payments
19                        WHERE contractID = contract_id)
20	WHERE firstName = firstNameGiven
21	AND middleName = middleNameGiven
22	AND lastName = lastNameGiven;
23    
24    
25DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'SQL Exception';
26DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
27
28
29CREATE TEMPORARY TABLE tempTbl (
30fullName VARCHAR(255),
31paymentId INT,
32contractID INT,
33amount DOUBLE)
34ENGINE = MEMORY;
35
36OPEN procedureCursor;
37SET finished = 0;
38while_loop_label: WHILE(finished = 0)
39		DO 
40        FETCH procedureCursor INTO
41        tempFullName,
42        tempPaymentId,
43		tempContractId, 
44        tempAmount;
45        IF (finished = 1)
46    THEN LEAVE while_loop_label;
47    ELSE 
48    INSERT INTO tempTbl 
49    VALUES(tempFullName, tempPaymentId, tempContractId, tempAmount);
50	END IF;
51    END WHILE;
52    
53	CLOSE procedureCursor;
54    SELECT * FROM tempTbl;
55    DROP TABLE tempTbl;
56
57
58END 
59| 
60DELIMITER ;