[정보처리기사 필기] SQL 활용 - 088. 사용자 정의 함수

1. 사용자 정의 함수의 개요

• 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리
• 종료 시 처리 결과를 단일 값으로 반환하는 절차형 SQL
• 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML문의 호출에 의해 실행
• 예약어 RETURN을 통해 값을 반환하기 때문에 출력 파라미터가 없음
• INSERT, DELETE, UPDATE를 통한 테이블 조작은 할 수 없고 SELECT를 통한 조회만 할 수 있음
• 프로시저를 호출하여 사용할 수 없음
• 사용자 정의 함수는 SUM(), AVG() 등의 내장 함수처럼 DNML문에서 반환값을 활용하기 위한 용도로 사용
  • 내장 함수 : DBMS에 기본적으로 포함되어 있는 함수들, 그룹함수에 해당됨

구분	프로시저	사용자 정의 함수
반환값	없거나 1개 이상 가능	1개
파라미터	입출력 가능	입력만 가능
사용 가능 명령문	DML, DCL	SELECT
호출	프로시저, 사용자 정의 함수	사용자 정의 함수
사용 방법	실행문	DML에 포함

2. 사용자 정의 함수의 구성

• 구성이 프로시저와 유사, 프로시저의 구성에서 RETURN만 추가된 것
• 사용자 정의 함수의 구성도

  • 

  • DECLARE : 사용자 정의 함수의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
  • BEGIN / END : 사용자 정의 함수의 시작과 종료를 의미
  • CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
  • SQL : SELECT문이 삽입되어 데이터 조회 작업을 수행
  • EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의
  • RETURN : 호출 프로그램에 반환할 값이나 변수를 정의

3. 사용자 정의 함수 생성

• 명령어 : CREATE FUNCTION
• 표기 형식

  • CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터) [지역변수 선언] BEGIN    사용자 정의 함수 BODY;    RETURN 반환값; END;

  • OR REPLACE : 선택적인 예약어, 이 예약어를 사용하면 동일한 사용자 정의 함수의 이름이 이미 존재하는 경우, 기존의 사용자 정의 함수를 대체할 수 있음
  • 파라미터
    • IN : 호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정
    • 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정
    • 자료형 : 변수의 자료형을 지정
  • 사용자 정의 함수 BODY
    • 사용자 정의 함수의 코드를 기록하는 부분
    • BEGIN에서 시작하여 END로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL문이 있어야 함
  • RETURN 반환값 : 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려줌

4. 사용자 정의 함수 실행

• 사용자 정의 함수는 DML에서 속성명이나 값이 놓일 자리를 대체하여 사용
• 표기형식

  • SELECT 사용자 정의 함수명 FROM 테이블명; INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명); DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명; UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

5. 사용자 정의 함수 제거

• 명령어 : DROP FUNCION
• 표기 형식

  • DROP FUNCTION 사용자 정의 함수명;