[MySQL] 함수(function) 생성하기

출처 : unsplash.com

 

MySQL에서 함수를 생성해서 업무적으로 활용할 수 있다.

여기서는 간단한 테이블을 만들고 해당 테이블을 조작하는 함수를 만들어 본다.

툴은 MySQL workbench를 사용함

 

 

< 예제1 >

show databases;
create database testdb;
show databases;
use testdb;
show tables;
 
-- 1. 테이블 생성
create table professor (
professor_id int primary key auto_increment,
name varchar(32) not null,
belong varchar(12) default 'foo',
phone varchar(12)
) engine=innodb;
 
desc professor;
 
-- 2. 테스트 데이터 생성
insert into professor (name, belong, phone) values ('유재석', 'ide', '01012345678');
commit;
 
-- 3. 데이터 확인
select * from professor;
 
 
/* 
   함수를 생성하고 실행하면 "ERROR 1418" 이 발생하는데, 이는 '권한'이 없어 발생하는 문제이다.
   아래 set 명령어를 통해 권한 부여 가능하다. (show 명령어로 value가 off인 경우 '권한없음')
*/
 
show global variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
 
 
-- 4. 생성한 함수 실행하기
select get_name('철수', 50) as col;
 

 

< 예제2 >

/* MySQL function 을 이용해서 '직원 출석등록' 함수 생성 */
 
-- 1. 테이블 생성
create table `emp_attend` (
    `attend_ymd` varchar(8) not null collate 'utf8_bin', 
    `emp_no` varchar(9) not null collate 'utf8_bin'
)
collate='utf8mb4_unicode_ci'
engine=InnoDB;
 
-- 2. 함수 생성
/* 
-- 'create a new function' 버튼 클릭 시 기본 생성되는 함수 포맷
CREATE FUNCTION `new_function` ()
RETURNS INTEGER
BEGIN
 
RETURN 1;
END
*/
 
delimiter $$
drop function if exists fnc_attend;
create function `fnc_attend` (
    attendType varchar(10),
    empNo varchar(10)
)
returns bool
begin
    declare exist_flag int;
    declare returnVal bool;
    set returnVal = false;
    
    -- 출석 등록
    if 'attend'=attendType then
        select count(1)
          into exist_flag
          from emp_attend
         where emp_no = empNo
           and attend_ymd = date_format(now(), '%Y%m%d') ;
           
        if exist_flag = 0 then
            insert into emp_attend (attend_ymd, emp_no) values (date_format(now(), '%Y%m%d'), empNo) ;
            set returnVal = true;
        end if;
    end if;
    
    return returnVal;
end $$
 
 
-- 3. 실행하기
select fnc_attend('attend', '1000');
 
-- 4. 테이블 조회하기
select * from emp_attend;

 

참고)

1. https://wakestand.tistory.com/503

2. https://shlee0882.tistory.com/242

3. https://colorscripter.com/