정보처리기사 실기 - SQL 응용 : 사용자 정의함수 및 호출쿼리 작성

정보처리기사 실기 - SQL 응용 : 사용자 정의함수 및 호출쿼리 작성

 

사용자 정의함수 작성

 

① 사용자 정의함수의 개념

 

• 프로시저와 동일하게 절차형 SQL을 활용하여 일련의 연산 처리 결과를 단일값으로 반환할 수 있는 절차형 SQL
• DBMS에서 제공되는 공통적 함수 이외에 사용자가 직접 정의하고 작성
• 일반적으로 사용자 정의함수의 호출을 통해 실행되며 반환되는 단일값을 조회/삽입/수정 작업에 이용
• 기본적인 개념 및 사용법, 문법 등은 상기 언급된 프로시저와 동일
• 종료 시 단일값을 반환한다는 것이 프로시저와의 가장 큰 차이점

 

② 사용자 정의함수의 구성

 

• 기본적인 사항은 프로시저와 동일하고 반환에서의 부분만 프로시저와 다름

 

③ 사용자 정의함수의 문법 

CREATE [OR REPLACE] FUNCTION 함수명
(PARAMETER1 [MODE] DATATYPE1, PARAMETER2 [MODE] DATATYPE2, ..)
IS [AS]
...
BEGIN
...
	[SQL]
	[CONTROL]
...
	[EXCEPTION]
...
	RETURN [VALUE];
END;

• CREATE명령어로 DBMS 내에 사용자 정의함수 생성 가능
• [OR REPLACE] 명령을 통해 기존 사용자 정의함수 존재 시에 현재 컴파일하는 내용으로 Overwrite 가능
• 만약 기존에 동명의 프로시저가 존재하고 CREATE 명령문만 사용하여 컴파일 시 에러 발생
• RETURN명령을 통해 사용자 정의함수 종료 시 사용자 정의함수를 호출한 쿼리에 반환하는 단일 값 정의

 

④ 사용자 정의함수의 예제 

CREATE OR REPLACE FUNCTION GET_AGE
(V_BIRTH_DATE IN CHAR(8))
IS
BEGIN
	V_CURRENT_YEAR CHAR(4); --현재 연도
	V_BIRTH_YEAR CHAR(4); --생년
	V_AGE NUMBER; --나이

	SELECT TO_CHAR(SYSDATE,'YYYY'), --현재 일자에서 년도만 추출
	SUBSTR(V_BIRTH_DATE, 1.4), --생년월일에서 앞 4자리 추출
	INTO V_CURRENT_YEAR, V_BIRTH_YEAR --현재 년도(4자리), 생년월일 년도(4자리)
	FROM DUAL;

	SET AGE=TO_NUMBER(V_CURRENT_YEAR)-TO_NUMBER(V_BIRTH_YEAR)+1; --현재 연도와 생년의 차에 1을 더함

	RETURN AGE; --나이를 반환
END;

• 생년월일(V_BIRTH_DATE)을 입력변수로 선언
• 현재 연도(V_CURRENT_YEAR) 변수와 생년(V_BIRTH_DATE) 변수를 문자열 4자리로 선언
• 반환할 대상인 나이(V_AGE) 변수를 숫자 형식으로 선언
• 현재 일자(SYSDATE)를 조회하고 연도 4자리만 파싱하여 현재 연도 변수에 입력
• 생년월일 8자리에서 4자리만 파싱하여 생년 변수에 입력
• 현재 연도와 생년을 숫자 형식으로 변환한 후 두 수의 차에 1을 더하고 나이 변수에 입력
• 구해진 나이값(한국 기준) 반환

 

 

사용자 정의함수 호출쿼리 작성

 

• ⁠⁠작성된 사용자 정의함수는 프로시저와 동일하게 외부에서의 호출을 통해 실행
• 보통 비교적 단순한 결과값을 구하거나 타 시스템에 정보 제공 등 은닉을 통해 캡슐화를 제공하는 데에도 많이 사용

 

① 사용자 정의함수 호출쿼리 작성 

 

• 문법 DQL 또는 DML 문장을 활용하여 사용자 정의함수 호출
• 직접 사용자 정의함수 결과값을 데이터 질의어에 활용
• 사용자 정의함수 결과값을 데이터 조작어에 직접 적용하게 하여 활용
• 그 밖의 부분은 프로시저의 호출과 유사

SELECT 함수명(PARAMETER1, PARAMETER2, ..) FROM DUAL;
UPDATE 테이블명 SET 컬럼명 = 함수명(PARAMETER1, PARAMETER2, ..) WHERE ...;

 

② 사용자 정의함수 예제

SELECT GET_AGE('19951013') FROM DUAL;
UPDATE EMPLOYEE_INFO SET AGE=GET_AGE(BIRTH_DATE) WHERE EMPLOYEE_ID='chimy';