[MySQL] stored function

stored function

• 사용자가 정의한 함수
• DBMS에 저장되고 사용되는 함수
• SQL의 select, insert, update, delete statement에서 사용할 수 있다
• loop를 돌면서 반복적인 작업을 수행하거나
• case키워드를 사용해서 값에 따라 분기 처리 하거나
• 에러를 핸들링하거나 에러를 일으키는 등의 다양한 동작을 정의할 수 있다

 

예제1

임직원의 ID를 열자리 정수로 랜덤하게 발급하고 싶다.

ID의 맨 앞자리는 1로 고정이다

-- stored function 생성
delimiter $$
CREATE FUNCTION id_generator()
RETURNS int
NO SQL
BEGIN
	RETURN (1000000000 + floor(rand() * 1000000000));
END
$$
delimiter;

-- 사용
INSERT INTO employee
VALUES (id_generator(), 'JEHN', '1991-08-04', 'F', 'PO', 100000000, 1005);

rand() : 0~1 사이의 랜덤 수

 

 

예제 2

부서의 ID를 파라미터로 받으면 해당 부서의 평균 연봉을 알려주는 함수

delemiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA
BEGIN
		select avg(salary) into @avg_sal
		from employee
		where dept_id = d_id;
		RETURN @avg_sal;
END
$$

DECLARE 변수이름 변수타입 : 변수 선언

@변수명 : DECLARE사용하지 않고 변수로 사용

 

stored function 생성 때 DB지정하려면?

CREATE FUNCTION db.function

 

stored function 삭제하기

DROP FUNCTION stored_function_name;

 

등록된 stored function 파악하기

-- 해당 데이터베이스에서 function 조회 
SHOW FUNCTION STATUS where DB = 'company';

-- 데이터베이스 목록 보기  
SHOW DATABASES;

-- function 내용 보기
SHOW CREATE FUNCTION id_generator;

 

 

참고: 쉬운코드