개념

사용자 정의 함수란? 절차형 SQL을 활용하여 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL 이다. DBMS에서 제공되는 공통적 함수 이외에 사용자가 직접 정의하고 작성할 수 있다.

• 형식

DELIMITER //
CREATE OR REPLACE FUNCTION 함수이름(height INT) RETURNS 리턴타입
BEGIN
DECLARE 선언;
SELECT문; -- 선택적으로
RETURN 리턴값;
END
//
DELIMITER ;

• ex1 : BMI 지수 계산 공식으로 함수 작성 : 키를 입력하면 bmi 표준지수 22에 대한 몸무게 출력

( bmi = 신장(cm) 신장(cm) 22 / 10000 )

DELIMITER //
CREATE OR REPLACE FUNCTION fu1(height INT) RETURNS DOUBLE  
BEGIN
	RETURN height * height * 22 / 10000;
END
//
DELIMITER ;

SELECT ful(175);

• ex2 : 직원 전체의 연봉 평균 반환 함수

DELIMITER //
CREATE OR REPLACE FUNCTION fu2() RETURNS DOUBLE  
BEGIN
	DECLARE result DOUBLE; 
	SELECT AVG(jikwon_pay) INTO result FROM jikwon;
	RETURN result;
END
//
DELIMITER ;

SELECT fu2();

• ex3 : 각 직원의 연봉의 10% 를 반환 함수

DELIMITER //
CREATE OR REPLACE FUNCTION fu3(NO int) RETURNS INT 
BEGIN
	DECLARE pay INT;
	SET pay = 0; 
	SELECT jikwon_pay * 0.1 INTO pay FROM jikwon WHERE jikwon_no = no;
	RETURN pay;
END
//
DELIMITER ;

SELECT fu3(1);
SELECT jikwon_no,jikwon_name,jikwon_pay,fu3(jikwon_no) AS donate FROM jikwon;

• ex4 : 각 직원의 부서명 반환

DELIMITER //
CREATE OR REPLACE FUNCTION fu4(NO INT) RETURNS VARCHAR(10) CHARSET utf8  
BEGIN
	DECLARE bname VARCHAR(10) CHARSET UTF8;
	SELECT buser_name INTO bname FROM buser
	WHERE buser_no =(SELECT buser_num FROM jikwon WHERE jikwon_no=NO);
	RETURN bname;
END
//
DELIMITER ;

SELECT fu4(1);

SELECT jikwon_no,jikwon_name,jikwon_pay,fu4(jikwon_no) AS donate FROM jikwon;

• 문1) jikwon 테이블에 대해 부서번호가 있으면 부서명을 없으면 '임시직'을 반환하는 함수 작성
  방법1

DELIMITER //
CREATE OR REPLACE FUNCTION ftest1(no INT) RETURNS VARCHAR(100) CHARSET utf8  
BEGIN
	DECLARE bu VARCHAR(100) CHARSET UTF8;
	SELECT buser_name INTO bu FROM buser
	WHERE buser_no = (SELECT buser_num FROM jikwon WHERE jikwon_no = NO);
	RETURN nvl(bu,'임시직');
END
//
DELIMITER ;

SELECT ftest1 (6);

방법2

DELIMITER //
CREATE OR REPLACE FUNCTION futest1(no INT) RETURNS VARCHAR(100) CHARSET utf8  
BEGIN
	DECLARE bu VARCHAR(100) CHARSET UTF8;
	IF(NO IS NOT NULL) then
		SELECT buser_name INTO bu FROM buser WHERE buser_no = bu;
		RETURN bu;
	else
		RETURN '임시직';
	END if;
END
//
DELIMITER ;

SELECT futest1(6);

• 문2) 고객번호를 입력하면 나이를 출력하는 함수 작성

DELIMITER //
CREATE OR REPLACE FUNCTION ftest2(NO int) RETURNS DOUBLE 
BEGIN
 DECLARE nai double;
 SELECT TIMESTAMPDIFF(YEAR,DATE_FORMAT(substr(gogek_jumin,1,6), '%y%m%d' ),NOW()) INTO nai FROM gogek
 WHERE gogek_no = NO;
 RETURN nai;
END
//
DELIMITER ;

SELECT ftest2(10);