Stored Function

Stored Function

• 사용자가 정의한 함수
• DBMS에 저장되고 사용되는 함수
• SQL의 SELECT, INSERT, UPDATE, DELETE statement에서 사용할 수 있다.

---

⬇️ 임직원의 ID를 열 자리 정수로 랜덤하게 발급하고 싶다면 (맨 앞자리는 1로 고정)

delimiter $$
CREATE FUNCTION id_generator()
RETURNS int
NO SQL
BEGIN
ㅤㅤㅤㅤRETURN (10000000000 + floor(rand() * 10000000000));
END
$$

delimiter ;

- BEGIN 구문의 세미콜론에서 CREATE function이 끝났다고 해석될 수 있기 때문에 delimiter를 잠시 다른 것으로 변경해준다.

 

⬇️ 부서의 ID를 파라미터로 받아서 해당 부서의 평균 연봉을 알고 싶다면

delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA
BEGIN
ㅤㅤㅤㅤDECLARE avg_salary int;
ㅤㅤㅤㅤSELECT avg(salary) into avg_salary
ㅤㅤㅤㅤㅤㅤㅤㅤFROM employee WHERE dept_id = d_id;
ㅤㅤㅤㅤRETURN avg_salary;
END
$$

delimiter ;

또는

delimiter $$
CREATE FUNCTION dept_avg_salary(d_id int)
RETURNS int
READS SQL DATA
BEGIN
ㅤㅤㅤㅤSELECT avg(salary) into @avg_salary
ㅤㅤㅤㅤㅤㅤㅤㅤFROM employee WHERE dept_id = d_id;
ㅤㅤㅤㅤRETURN @avg_salary;
END
$$

delimiter ;

 

⬇️ 토익 800 이상을 충족했는지 알고 싶다면

delimiter $$
CREATE FUNCTION toeic_pass_fail(toeic_score int)
RETURNS char(4)
NO SQL
BEGIN
ㅤㅤㅤㅤDECLARE pass_fail char(4);
ㅤㅤㅤㅤIF toeic_score is null THEN SET pass_fail = 'fail';
ㅤㅤㅤㅤELSEIF toeic_score < 800 THEN SET pass_fail = 'fail';
ㅤㅤㅤㅤELSE SET pass_Fail = 'pass';
ㅤㅤㅤㅤEND IF;
ㅤㅤㅤㅤRETURN pass_fail;
END
$$

delimiter ;

또는

delimiter $$
CREATE FUNCTION toeic_pass_fail(toeic_score int)
RETURNS char(4)
NO SQL
BEGIN
ㅤㅤㅤㅤIF toeic_score is null THEN SET @pass_fail = 'fail';
ㅤㅤㅤㅤELSEIF toeic_score < 800 THEN SET @pass_fail = 'fail';
ㅤㅤㅤㅤELSE SET @pass_fail = 'pass';
ㅤㅤㅤㅤEND IF;
ㅤㅤㅤㅤRETURN @pass_fail;
END
$$

delimiter ;

---

stored function 삭제하기

DROP FUNCTION {stored_function_name};

---

등록된 stored function 파악하기

SHOW FUNCTION STATUS where DB = '{DB_name}';

- 따로 DB 이름을 명시하지 않으면 현재 활성화되어 있는 DB에 해당하는 function이 생성된다.

 

⬇️ DB 이름을 알고 싶다면

SHOW DATABASES;

 

⬇️ 함수가 어떻게 선언되어 있는지 보고 싶다면

SHOW CREATE FUNCTION {function_name};