MySQL + SQL · Lesson 96
Stored Functions in MySQL
Stored Function vs Procedure
A stored function is like a procedure but it RETURNS a single value and can be used inside a SELECT. A procedure performs actions; a function computes and returns.
Create and Use
DELIMITER //
CREATE FUNCTION grade(marks INT) RETURNS VARCHAR(5)
DETERMINISTIC
BEGIN
IF marks >= 90 THEN RETURN 'A+';
ELSEIF marks >= 33 THEN RETURN 'Pass';
ELSE RETURN 'Fail';
END IF;
END //
DELIMITER ;
SELECT name, grade(marks) AS result FROM students;The function is called inside SELECT to label each student.
Summary
- A function RETURNS a value and can be used in SELECT.
- A procedure performs actions and is run with CALL.