사용자 정의 함수의 개요
- 사용자 정의 함수는 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하며, 종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL이다.
- 사용자 정의 함수는 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML문의 호출에 의해 실행된다.
- 사용자 정의 함수는 예약어 RETURN을 통해 값을 반환하기 때문에 출력 파라미터가 없다.
- 사용자 정의 함수는 INSERT, DELETE, UPDATE를 통한 테이블 조작은 할 수 없고 SELECT를 통한 조회만 할 수 있다.
- 사용자 정의 함수는 프로시저를 호출하여 사용할 수 없다.
- 사용자 정의 함수는 SUM(), AVG() 등의 내장 함수처럼 DML문에서 반환값을 활용하기 위한 용도로 사용된다.
프로시저 vs 사용자 정의 함수
| 구분 | 프로시저 | 사용자 정의 함수 |
|---|
| 반환값 | 없거나 1개 이상 가능 | 1개 |
| 파라미터 | 입·출력 가능 | 입력만 가능 |
| 사용 가능 명령문 | DML, DCL | SELECT |
| 호출 | 프로시저, 사용자 정의 함수 | 사용자 정의 함수 |
| 사용 방법 | 실행문 | DML에 포함 |
사용자 정의 함수의 구성
- 사용자 정의 함수의 구성은 프로시저와 유사하다. 프로시저의 구성에서 RETURN만 추가하면 된다.
- 사용자 정의 함수의 구성도
- DECLARE: 사용자 정의 함수의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부이다.
- BEGIN/END: 사용자 정의 함수의 시작과 종료를 의미한다.
- CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리된다.
- SQL: SELECT문이 삽입되어 데이터 조회 작업을 수행한다.
- EXCEPTION: BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의한다.
- RETURN: 호출 프로그램에 반환할 값이나 변수를 정의한다.
사용자 정의 함수 생성
- 사용자 정의 함수를 생성하기 위해서는 CREATE FUNCTION 명령어를 사용한다.
- 표기 형식
CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
사용자 정의 함수 BODY;
RETURN 반환값;
END;
- OR REPLACE: 선택석인(Optional) 예약어이다. 이 예약어를 사용하면 동일한 사용자 정의 함수의 이름이 이미 존재하는 경우, 기존의 사용자 정의 함수를 대체할 수 있다.
- 파라미터: 사용자 정의 함수의 파라미터로는 다음과 같은 것들이 올 수 있다.
- IN: 호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정한다.
- 매개변수명: 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정한다.
- 자료형: 변수의 자료형을 지정한다.
- 사용자 정의 함수 BODY
- 사용자 정의 함수의 코드를 기록하는 부분이다.
- BEGIN에서 시작하여 END로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL문이 있어야 한다.
- RETURN 반환값: 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려준다.
- 예제: 'i성별코드'를 입력받아 1이면 "남자"를, 2면 "여자"를 반환하는 사용자 정의 함수를 'Get_S성별'이라는 이름으로 정의하시오.
1. CREATE FUNCTION Get_S_성별(i_성벌코드 IN INT)
2. RETURN VARCHAR2
3. IS
BEGIN
4. IF i_성벌코드 = 1 THEN
RETURN '남자';
5. ELSE
RETURN '여자';
6. END IF;
END;
- 해설
1. 파라미터로 'i_성별코드'를 전달받는 사용자 정의 함수 'Get_S_성별'을 생성한다.
2. 블록에서 리턴할 데이터의 자료형을 정의한다. 자료형의 크기는 입력할 필요 없다.
- 형식: RETURN [자료형]
3. 변수 선언을 위해 사용하는 예약어로 변수를 사용하지 않으므로 예약어만 입력한다.
4. 'i_성별코드'가 1이면 "남자"를 반환하고
5. 'i_성별코드'가 1이 아니면 "여자"를 반환한다.
6. IF문의 끝
사용자 정의 함수 실행
- 사용자 정의 함수는 DML에서 속성명이나 값이 놓일 자리를 대체하여 사용된다.
- 표기 형식
SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;
- 예제: 다음의 <사원> 테이블을 출력하되, '성별코드'는 앞에서 사용자 정의 함수 'GetS성별'에 값을 전달하여 반환받은 값으로 대체하여 출력하시오.
<사원>
SELECT 이름, Get_S_성별(성별코드) FROM 사원;
<결과>
| 이름 | GetS성별(성별코드) |
|---|
| 김대진 | 남자 |
| 이고을 | 여자 |
| 최승규 | 남자 |
| 송하나 | 여자 |
- 해설: <사원> 테이블에서 '이름' 속성과 앞에서 정의한 사용자 정의 함수 'GetS성별'에 '성별코드' 속성을 인수로 전달하고 반환받은 값을 결과로 출력한다. '성별코드'의 값이 1이면 "남자"가 출력되고, 1이 아니면 "여자"가 출력된다.
사용자 정의 함수 제거
- 사용자 정의 함수를 제거하기 위해서는 DROP FUNCTION 명령어를 사용한다.
- 표기 형식
DROP FUNCTION 사용자 정의 함수명;
- 예제: 앞에서 생성된 사용자 정의 함수 'GetS성별'을 제거하시오.
DROP FUNCTION Get_S_성별;
