MySQL Forums
Forum List  »  General

How to write a function in MYSQL to handle errors
Posted by: Rhonda Churchill
Date: January 03, 2021 07:31AM

I want help with functions in MYSQL for error handling

Write a stored function totalRaceTime that, given a racing number, the name of a race, and the date of a race, returns the total race time for the car specified by the racing number in the given race. If the given race does not exist, the routine should throw the error procedure Race does not exist. If the specified racing number did not take part in the existing race, the routine should throw an error procedure RaceEntry does not exist. In the case that not all required lap times for the (existing) car in the (existing) race are available either until race finish or retirement, the routine should throw the error procedure TimeForAllLaps does not exist. If the (existing) race was not completed by the (participating) car in the race due to retirement but all lap times were available until retirement, the routine must not throw an error but return null. Note that in those error cases the function must not return a string but produce an SQL error.

What I wrote so far:
DELIMITER $$
CREATE FUNCTION totalRaceTime(
raceNumber INT,
raceName VARCHAR(30),
raceDate DATE
)
RETURNS INTEGER
BEGIN
DECLARE errorMessage VARCHAR(50);
DECLARE TimeForAllLaps INTEGER;
SELECT lapInfoRaceName , lapInfoRaceNumber, lapInfoRaceDate, lapInfoTime
FROM MoSpo_LapInfo

IF (raceName != lapInfoRaceName ) THEN SET errorMessage = 'procedure Race does not exist'
ELSEIF (raceNumber != lapInfoRaceNumber THEN SET errorMessage = 'procedure RaceEntry does not exist'
ELSEIF (lapInfoTime IS NULL THEN SET errorMessage = 'TimeForAllLaps does not exist'
END
DELIMITER $$

Options: ReplyQuote


Subject
Written By
Posted
How to write a function in MYSQL to handle errors
January 03, 2021 07:31AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.