[MySQL] 사용자 정의 함수 (User-Defined Function)

기본구조

CREATE FUNCTION 'function name' ('parameter name', 'datatype')
RETURNS 'datatype' (DETERMINSTIC)
BEGIN
DECLARE 'varialbe name' 'datatype';
SET ;
RETURN (Query) / 'variable name';
END

사용방법

• SELECT 'function name' (parameter)

예제 문제

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null.

The query result format is in the following example.

Example 1:

Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
n = 2
Output:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
Example 2:

Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
n = 2
Output:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null |
+------------------------+

코드

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
        SELECT CASE WHEN COUNT(sub.Salary) < N THEN NULL
               ELSE MIN(sub.Salary)
               END
        FROM (
            SELECT DISTINCT Salary
            FROM Employee
            ORDER BY Salary DESC
            LIMIT N
             )sub
  );
END