DELIMITER $$
CREATE FUNCTION get_risk_factor_for_id
(
IN id int
)
RETURNS int
READS SQL DATA
BEGIN
DECLARE risk_factor_for_id int default 0;
DECLARE Dr_total decimal (10, 2);
DECLARE Dr_amount decimal (10, 2);
SELECT
SUM(Dr_amount),
COUNT(Dr_amount)
INTO
Dr_total,
Dr_amount
FROM vijay.cash i
WHERE id =i.id;
IF Dr_amount > 0 THEN
SET risk_factor_for_id = Dr_total/Dr_amount * 5;
RETURN risk_factor_for_id;
END;
DELIMITER $$