Using aggregate function in procedure : Introduction « Procedure Function « SQL / MySQL

1. SQL / MySQL
2. Procedure Function
3. Introduction

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;

   
    
    
    
    
    
    
  

Related examples in the same category