Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE mytable(
- id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
- name VARCHAR(20),
- salary INT UNSIGNED NOT NULL
- );
- INSERT INTO mytable (name, salary) VALUES ('John Smith',5000), ('Tom Wright',900);
- SELECT * FROM mytable;
- DELIMITER $$
- CREATE FUNCTION SalaryLevel(
- salary INT
- )
- RETURNS VARCHAR(5)
- DETERMINISTIC
- BEGIN
- DECLARE salaryLevel VARCHAR(5);
- CASE
- WHEN salary > 4000 THEN
- SET salaryLevel='HIGH';
- WHEN salary>1000 AND salary<= 4000 THEN
- SET salaryLevel='MIDLE';
- ELSE
- SET salaryLevel='LOW';
- END CASE;
- RETURN (salaryLevel);
- END$$
- DELIMITER ;
- SELECT name, salary, SalaryLevel(salary) FROM mytable;
- +------------+--------+---------------------+
- | name | salary | SalaryLevel(salary) |
- +------------+--------+---------------------+
- | John Smith | 5000 | HIGH |
- | Tom Wright | 900 | LOW |
- +------------+--------+---------------------+
- 2 rows in set (0.00 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement