Mega Code Archive

 

Categories / MySQL / Procedure Function

 

Using aggregate function in procedure

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;