사용자 정의 함수

사용자 정의 함수(user defined function)는 값 하나를 돌려주도록 만들어 SQL 문 안에서 부르는 절차형 SQL입니다. 돌려주는 통로가 RETURN 하나인 것이 프로시저와 가장 크게 다릅니다.

앞 레슨의 프로시저는 EXECUTE나 CALL로 따로 불러야 해서 SELECT 안에 넣을 수 없습니다. 그래서 '점수를 등급으로 바꾸기' 같은 계산은 쿼리를 쓰는 사람마다 CASE 식으로 다시 적게 됩니다. CASE는 위에서부터 조건을 견주어 맞는 첫 WHEN만 쓰고, 아무 조건도 맞지 않으면 ELSE를 돌려줍니다(SQLD의 NULL 함수와 CASE에서 자세히 봅니다). 기준이 바뀌면 그 식이 들어간 쿼리를 모두 찾아 고쳐야 합니다. 같은 계산에 이름을 붙여 함수로 저장해 두면 쿼리는 이름만 부르고, 기준은 함수 한 곳에서 고칩니다. 아래 그림은 점수를 등급으로 바꾸는 함수가 SELECT 안에서 행마다 한 번씩 불리는 장면입니다.

CREATE FUNCTION 등급(  p_점수 NUMBER) RETURN VARCHAR2 ISBEGIN  IF p_점수 >= 80 THEN    RETURN 'A';  END IF;  RETURN 'B';END;SELECT 학번,  등급(점수) AS 등급FROM 수강WHERE 과목코드 = 'DS'ORDER BY 학번;

학번  등급101  A102  B103  B105  A

CREATE FUNCTION 등급(  p_점수 NUMBER) RETURN VARCHAR2 ISBEGIN  IF p_점수 >= 80 THEN    RETURN 'A';  END IF;  RETURN 'B';END;SELECT 학번,  등급(점수) AS 등급FROM 수강WHERE 과목코드 = 'DS'ORDER BY 학번;

학번  등급101  A

CREATE FUNCTION 등급(  p_점수 NUMBER) RETURN VARCHAR2 ISBEGIN  IF p_점수 >= 80 THEN    RETURN 'A';  END IF;  RETURN 'B';END;SELECT 학번,  등급(점수) AS 등급FROM 수강WHERE 과목코드 = 'DS'ORDER BY 학번;

학번  등급101  A102  B?

CREATE FUNCTION 등급(  p_점수 NUMBER) RETURN VARCHAR2 ISBEGIN저장됨  IF p_점수 >= 80 THEN    RETURN 'A';  END IF;  RETURN 'B';END;SELECT 학번,  등급(점수) AS 등급FROM 수강WHERE 과목코드 = 'DS'ORDER BY 학번;

CREATE FUNCTION 등급(  p_점수 NUMBER) RETURN VARCHAR2 ISBEGIN  IF p_점수 >= 80 THEN    RETURN 'A';  END IF;  RETURN 'B';END;SELECT 학번,  등급(점수) AS 등급FROM 수강WHERE 과목코드 = 'DS'ORDER BY 학번;

학번  등급101  A102  B

CREATE FUNCTION 등급(  p_점수 NUMBER) RETURN VARCHAR2 ISBEGIN  IF p_점수 >= 80 THEN    RETURN 'A';  END IF;  RETURN 'B';END;SELECT 학번,  등급(점수) AS 등급FROM 수강WHERE 과목코드 = 'DS'ORDER BY 학번;

학번  등급101  A102  B103  B

CREATE FUNCTION 등급(  p_점수 NUMBER) RETURN VARCHAR2 ISBEGIN  IF p_점수 >= 80 THEN    RETURN 'A';  END IF;  RETURN 'B';END;SELECT 학번,  등급(점수) AS 등급FROM 수강WHERE 과목코드 = 'DS'ORDER BY 학번;

1함수를 만드는 꼴 — RETURN이 두 곳에 나온다

CREATE FUNCTION 다음에 이름과 매개변수를 쓰는 것까지는 프로시저와 같습니다. 다른 곳은 RETURN입니다. 머리에서 한 번, 몸에서 또 한 번 나옵니다.

CREATE FUNCTION 이름(
  매개변수 자료형
) RETURN 돌려줄자료형 IS
BEGIN
  RETURN 값;
END;

• 머리의 RETURN에는 자료형만 적습니다. 길이는 적지 않습니다.
• 몸의 RETURN은 값을 들고 그 자리에서 함수를 끝냅니다. 아래에 문장이 더 있어도 실행되지 않습니다. 그림에서 조건이 참인 행은 IF 안의 RETURN에서 끝나 아래 RETURN까지 가지 않습니다.
• 갈림길이 여럿이면 길마다 RETURN이 있어야 합니다. RETURN을 지나지 않고 END에 닿으면 부를 때 오류가 납니다.
• 함수의 매개변수는 IN만 씁니다. 돌려주는 통로가 RETURN 하나이기 때문이고, 표준 SQL과 시험도 IN 하나로 봅니다. Oracle은 OUT을 적은 함수도 오류 없이 만들어 주지만, 그런 함수는 SQL 문에서 부를 수 없습니다(부를 때 ORA-06553 오류).
• 지울 때는 DROP FUNCTION 이름;입니다.

시험이 내는 표준 SQL 꼴은 머리에 S가 붙은 RETURNS를 쓰고 IS가 없습니다. 몸의 RETURN 값;은 두 쪽이 같습니다.

CREATE FUNCTION 이름(
  매개변수 자료형)
RETURNS 돌려줄자료형
BEGIN
  RETURN 값;
END;

2SQL 문 안에서 부른다

함수는 값 하나로 계산되므로, 값이 올 수 있는 자리면 어디서든 부릅니다. SELECT 목록·WHERE 조건·GROUP BY·ORDER BY가 모두 그런 자리입니다.

SELECT 등급(점수) FROM 수강;
SELECT * FROM 수강
WHERE 등급(점수) = 'A';

• 함수는 행마다 한 번씩 불립니다. 그림의 쿼리는 DS를 듣는 행이 넷이라 함수도 네 번 불렸습니다.
• 그래서 함수 안에서 데이터를 고칠 수 없습니다. 함수 안에 INSERT·UPDATE·DELETE를 두고 SELECT에서 부르면 ORA-14551 오류가 납니다. 읽는 도중에 같은 데이터를 고치게 되기 때문입니다.
• 프로시저는 이 자리에 올 수 없습니다. SELECT 과목인원('OS') FROM DUAL⁠처럼 쓰면 ORA-00904 오류가 납니다. 데이터베이스가 그 이름을 값으로 알아보지 못합니다.

3프로시저와 무엇이 다른가

둘 다 블록에 이름을 붙여 저장한 것이고 만드는 꼴도 닮았습니다. 시험은 둘을 가르는 기준을 묻습니다.

• 값을 여럿 돌려줘야 하거나 데이터를 고쳐야 하면 프로시저입니다.
• 쿼리 안에서 쓸 계산이면 함수입니다.

예제

함수를 만들고 부르는 데까지가 실습 하나입니다. 점수를 받아 등급을 돌려주는 함수 하나를 세 가지 자리에서 불러 봅니다. 블록마다 처음 데이터에서 시작합니다 — 앞 블록이 만들거나 바꾼 것은 남아 있지 않습니다.

SELECT 목록에서 부른다

그림의 쿼리입니다. 자료구조(DS)를 듣는 네 행에 등급 칸이 붙습니다.

CREATE FUNCTION 등급(
  p_점수 NUMBER
) RETURN VARCHAR2 IS
BEGIN
  IF p_점수 >= 80 THEN
    RETURN 'A';
  END IF;
  RETURN 'B';
END;
/
SELECT 학번,
  등급(점수) AS 등급
FROM 수강
WHERE 과목코드 = 'DS'
ORDER BY 학번;

WHERE 조건에서 부른다

같은 함수를 조건 자리에 둡니다. 과목을 가리지 않고 등급이 A인 수강 행만 남습니다.

CREATE FUNCTION 등급(
  p_점수 NUMBER
) RETURN VARCHAR2 IS
BEGIN
  IF p_점수 >= 80 THEN
    RETURN 'A';
  END IF;
  RETURN 'B';
END;
/
SELECT 학번,
  과목코드
FROM 수강
WHERE 등급(점수) = 'A'
ORDER BY 학번, 과목코드;

조건 자리의 함수도 행마다 한 번씩 불립니다. 수강 표 12행을 모두 거쳐 다섯 행이 남았습니다.

GROUP BY로 묶는다

함수가 돌려준 값으로 묶습니다. 등급이라는 칸이 표에 없어도 묶는 기준이 됩니다.

CREATE FUNCTION 등급(
  p_점수 NUMBER
) RETURN VARCHAR2 IS
BEGIN
  IF p_점수 >= 80 THEN
    RETURN 'A';
  END IF;
  RETURN 'B';
END;
/
SELECT 등급(점수) AS 등급,
  COUNT(*) AS 인원
FROM 수강
GROUP BY 등급(점수)
ORDER BY 등급(점수);

기준을 90점으로 바꾸고 싶으면 함수의 >= 80 한 곳만 고칩니다. 세 쿼리는 그대로 둡니다.

시험 포인트

• 필기: 사용자 정의 함수의 표시는 RETURN입니다. 머리에 돌려줄 자료형을 적고 몸에서 RETURN 값;으로 값을 돌려줍니다. 돌려주는 값은 반드시 하나입니다. 프로시저와 가르는 기준으로는 네 가지가 나옵니다. 함수는 SQL 문 안에서 부를 수 있고, 프로시저는 EXECUTE·CALL로 따로 부릅니다. 함수의 매개변수는 모두 IN이고, 프로시저는 IN·OUT·IN OUT을 씁니다. 함수는 쿼리 안에서 데이터를 고치지 못합니다. 표준 꼴은 머리에 RETURNS를 씁니다.
• 실기: 이름을 쓰게 하면 사용자 정의 함수(User Defined Function)이고, 값을 돌려주는 문장은 RETURN입니다. 문장을 쓰게 하면 CREATE FUNCTION으로 시작해 머리의 RETURN 자료형 IS와 몸의 RETURN 값;을 모두 쓰고 END;로 끝냅니다. 부르는 꼴은 호출문이 아니라 쿼리 안의 함수이름(값)입니다.

확인 문제

1. 학과 표를 쓰는 다음 네 가지 가운데, 프로시저가 아니라 사용자 정의 함수로 만들어야 하는 것을 고르세요.

• ① 학과코드를 받아 그 학과의 건물 이름을 쿼리의 한 칸으로 보이게 한다
• ② 학과코드를 받아 그 학과 학생들의 학년을 모두 1씩 올린다
• ③ 학과코드와 새 건물 이름을 받아 학과 표의 건물을 바꾼다
• ④ 건물 이름을 받아 그 건물의 학과 수와 학생 수를 함께 돌려준다

2. 다음 (가)·(나)에 맞는 말을 쓰세요. (가)는 한글 이름과 영문으로 함께, (나)는 SQL 키워드로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 값 하나를 돌려주도록 만들어 두고, 쿼리의 SELECT 목록이나 WHERE 조건처럼 값이 올 자리에서 이름으로 부르는 절차형 SQL입니다. 데이터베이스가 미리 만들어 둔 것이 아니라 쓰는 사람이 만든 것입니다.
• (나) (가)를 만들 때 머리에는 돌려줄 자료형과 함께, 몸에는 돌려줄 값과 함께 쓰는 키워드입니다. 몸에서 이 문장을 만나면 아래에 문장이 더 있어도 거기서 끝납니다.

3. 점수를 받아 90점 이상이면 '지급', 아니면 '미지급'을 돌려주는 함수 장학을 쓰고, 네트워크(NW)를 듣는 학생의 학번과 함께 보이는 쿼리를 학번 차례로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

CREATE FUNCTION 장학(
  p_점수 NUMBER
) RETURN VARCHAR2 IS
BEGIN
  IF p_점수 >= 90 THEN
    RETURN '지급';
  END IF;
  RETURN '미지급';
END;
/
SELECT 학번,
  장학(점수) AS 장학
FROM 수강
WHERE 과목코드 = 'NW'
ORDER BY 학번;