How to create Mysql funcation ?

 

DELIMITER $$

CREATE  FUNCTION `getMaxHumidityForCertificate`(dataDate TIMESTAMP,st_id INTEGER, formulaID INTEGER,cid INTEGER) RETURNS double

BEGIN

DECLARE RES DOUBLE;

IF (formulaID = 2) THEN

SELECT ROUND(MAX(Humidity),2) INTO RES FROM  TEMP_TABLE st,COMPANY_TABLE csm WHERE st.STATION_ID = st_id  AND DATA_DATE>=DATE_FORMAT(dataDate, '%Y-%m-%d 01:00:00')  AND DATA_DATE<=DATE_FORMAT(DATE_ADD(dataDate, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00')  AND DATE(DATA_DATE)<=DATE(DATA_TO_DATE) AND COMPANY_ID=cid AND csm.STATION_ID=st.STATION_ID;

ELSE

SELECT ROUND(MAX(Humidity),2) INTO RES FROM TEMP_TABLE WHERE STATION_ID = st_id  AND DATA_DATE>=DATE_FORMAT(dataDate, '%Y-%m-%d 00:00:00') AND DATA_DATE<=DATE_FORMAT(dataDate, '%Y-%m-%d 23:59:59');

END IF;

RETURN RES;

END$$

DELIMITER ;

How to use funcation :

select id,getMinTempForCertificate(date(DATA_DATE),st.STATION_ID,2,10) mintemp from TEMP_TABLE group by id

b.)

CREATE DEFINER=`db_name`@`%` FUNCTION `calRh830ForDS`(_data_date DATE,_stationId INTEGER) RETURNS double
BEGIN
 DECLARE RES DOUBLE;
    SELECT ROUND(Humidity,1) INTO RES  FROM TEMP_DATA 
    WHERE DATE_FORMAT(DATA_DATE,'%Y-%m-%d %H:%i') = CONCAT(_data_date,'08:30')
    AND STATION_ID=_stationId;
    
    RETURN RES;
    
 END 

Use : 

select id,calRh830ForDS (date(DATA_DATE),st.STATION_ID,2,10) mintemp from TEMP_TABLE group by id