CREATE TABLE EmployeeS_WITH_PARENTS
(EmployeeNO INTEGER NOT NULL PRIMARY KEY,
FATHER_EmployeeNO INTEGER,
MOTHER_EmployeeNO INTEGER)
;
CREATE TABLE PENALTIES
(PAYMENTNO INTEGER NOT NULL,
EmployeeNO INTEGER NOT NULL,
PAYMENT_DATE DATE NOT NULL,
AMOUNT DECIMAL(7,2) NOT NULL,
PRIMARY KEY (PAYMENTNO) );
INSERT INTO PENALTIES VALUES (1, 6, '1980-12-08',100);
DELIMITER $$
CREATE PROCEDURE TOTAL_PENALTIES_Employee
(IN P_EmployeeNO INTEGER,
OUT TOTAL_PENALTIES DECIMAL(8,2))
BEGIN
SELECT SUM(AMOUNT)
INTO TOTAL_PENALTIES
FROM PENALTIES
WHERE EmployeeNO = P_EmployeeNO;
END$$
DELIMITER ;
CALL TOTAL_PENALTIES_Employee (27, @TOTAL)
;
drop table Employees_with_parents;
drop table Employees;
|