Advertisement
attila66

MySQL Stored Function with RETURN value

May 4th, 2020
2,163
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.01 KB | None | 0 0
  1. CREATE TABLE mytable(
  2.   id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.   name    VARCHAR(20),
  4.   salary  INT UNSIGNED NOT NULL
  5. );
  6.  
  7. INSERT INTO mytable (name, salary) VALUES ('John Smith',5000), ('Tom Wright',900);
  8. SELECT * FROM mytable;
  9.  
  10. DELIMITER $$
  11.  
  12. CREATE FUNCTION SalaryLevel(
  13.     salary INT
  14. )
  15. RETURNS VARCHAR(5)
  16. DETERMINISTIC
  17. BEGIN
  18.     DECLARE salaryLevel VARCHAR(5);
  19.  
  20.     CASE  
  21.       WHEN salary > 4000 THEN
  22.         SET salaryLevel='HIGH';
  23.       WHEN salary>1000 AND salary<= 4000 THEN
  24.         SET salaryLevel='MIDLE';
  25.       ELSE
  26.         SET salaryLevel='LOW';
  27.     END CASE;
  28.  
  29.     RETURN (salaryLevel);
  30. END$$
  31.  
  32. DELIMITER ;  
  33.  
  34. SELECT name, salary, SalaryLevel(salary) FROM mytable;
  35. +------------+--------+---------------------+
  36. | name       | salary | SalaryLevel(salary) |
  37. +------------+--------+---------------------+
  38. | John Smith |   5000 | HIGH                |
  39. | Tom Wright |    900 | LOW                 |
  40. +------------+--------+---------------------+
  41. 2 rows in set (0.00 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement