오래 못 할 짓 하지 않기

[ 데이터베이스 ] 9. stored function 본문

3학년 1학기/데이터베이스(DB)

[ 데이터베이스 ] 9. stored function

쫑알bot 2024. 1. 4. 12:22
728x90

stored function

: 사용자가 정의한 함수

 

 

예제) 직원의 ID를 10자리 정수로 랜덤하게 발급하려고 한다.

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

 

→ 1xxxxxxxxx

 

▶ delimiter $$

▶ Create function id_generator()   //parameter 없음 

▶ Returns int                                  

▶ No SQL                                       // 중요한 거 아님

▶ Begin                                          // 이제부터 함수 Body 시작 , 아래부터 동작할 부분임

▶         RETURN ( 1000000000 + floor(rand()*1000000000));

▶ END                                           // 함수 Body 끝

▶ $$                                               // delimiter로 설정한 세미콜론

 

▶ delimiter ;                                   // 다시 delimiter로 세미콜론 설정을 바꾼다.

 

 

이렇게 만들어진 함수를 사용해보자

 

Insert into employee

▶ Values ( id_generator() , 'JEHN', '1991-08-04' , 'F' , 'PO', 10000000,1005);

 

 

 

 

 

[ 필수는 아니지만 어느 정도 알고 있어야 하는 거 ]

더보기

delimiter : 구문 문자 , 그냥 ; 세미콜론이라고 생각하면 되고, 문법의 끝을 나타내는 역할을 의미한다.

delimiter 명령어를 통해 어느 걸 세미콜론 역할을 시킬지 정한다.

ex) delimiter $$

이러면 세미콜론 대신 $$를 해야 명령어가 완성된다.

 

쓰는 이유 : function에서 명령어들마다 ; 를 붙여야하는데, 그럴 때마다 명령어 완성이 아닌 함수 완성으로 인식할 수도 있기 때문

 

 


예제 2)

부서의 ID를 Parameter로 받으면, 해당 부서의 평균 연봉을 알려주는 함수 작성 

 

delimiter $$

 

Create function avg_salary(d_id int)   // parameter 설정할 때 type이 뒤로 옴

▶ Return int

▶ READS SQL DATA

▶ BEGIN

▶     DECLARE avg_sal int;                     // 변수 하나 선언하는 거, Declare [ 변수 이름 ] [ 변수 타입 ]

▶     Select avg(salary) into avg_sal        //  밑에 from 과 where에 통과해서 나온 avg(salary)를 avg_sal 변수에 담는다.

▶     from employee 

▶     where dept_id = d_id;

▶ Return avg_sal;

   

▶ END

▶ $$

 

▶ delimiter ;

 

굳이 변수 선언 하기 귀찮다 하면

함수 내부가 이렇게 바뀐다.

 

▶     Select avg(salary) into @avg_sal        //  밑에 from 과 where에 통과해서 나온 avg(salary)를 avg_sal 변수에 담는다.

▶     from employee 

▶     where dept_id = d_id;

▶ Return @avg_sal;

 

 

써보면

Select *, avg_salary(id)   // department에 있는 모든 항목과 avg_salary를 조회한다.

▶ From department;

 

 


예제 3) 졸업요건 : 토익 800 이상 을 충족했는지 알려주는 함수를 만들어라

 

delimiter $$

 

Create function pass_or_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;  // if문 끝났음 

        RETURN pass_fail;

END$$

 

delimiter;

 

써보자

 

Select *, toeic_pass_fail(toeic)From student;

 

 


 

Function 삭제는

DROP FUNCTION [함수 이름] ;


 

등록된 Stored function 파악하기

SHOW FUNCTION STATUS where DB = 'DB이름';

 

적은 DB에 있는 모든 function을 볼 수 있다.

 

해당 함수를 자세히 보고싶다면

Show Create function id_generator

 

근데 이쁘게 나오진 않음.

+ 함수 만든 사람도 표시해줌

 


Stored function 은 언제 써야할까? (가볍게 들어도 됨)
(강의하시는 분의 의견이 다분히 들어갔다고 하고 시작하심)

 

● Three-tier Architecture

 

 

 

 

비즈니스 로직은 Logic tier에 두어야 하는데

Stored function은 data tier에 두는 것이므로

규모가 커질수록 신경써야하는 것이 Logic tier + Data tier가 되기 때문에 좋지 않다.

 

 

 

 

(출처)

유튜브 쉬운코드