기술노트 블로그

안녕하세요, 혼자 공부하는 SQL를 보고 학습한 자료를 남깁니다.

스토어드 함수란

스토어드 함수는 

우리가 MySQL에서 주로 사용하는 SUM(), CAST(), CONCAT(), CURRENT_DATE() 등을 사용해 봤습니다.

사용자가 원하는 함수를 직접 만들어서 사용할 수 있습니다.

이렇게 직접 만들어서 사용하는 함수를 스토어드 함수라고 부릅니다.

스토어드 프로시저는 입력 매개변수와 출력 매개변수가 있었는데

스토어드 함수는 입력 매개변수만 있다.

RETURNS 명령어를 통해서 반환형식을 정할 수 있고

RETURN 명령어를 통해서 반환값을 반환한다.

스토어드 함수의 경우 주로 SELECT [스토어드 함수 이름](); 의 형식으로 주로

SELECT문을 통해서 사용을 한다.

스토어드 함수의 사용

SQL로 스토어드 함수 생성 권한 허용

MySQL에서 주로 사용하는 변수이다.

한번만 실행을 해주면 다음부터는 적용이 되어서 동작한다.

먼저 스토어드 프로시저와 다른점을 살펴보자.

매개 변수에 IN 또는 OUT을 입력하여, 입력 매개변수와 출력 매개변수 선언이 없고 

오직 입력 매개변수만 입력할 수 있는 것을 볼 수 있다.

그리고 FUNCTION이라는 예약어를 사용하며

RETURNS 에 반환할 값의 데이터 형식

그리고 RETURN에 반환할 값을 입력을 하는 것을 볼 수 있다.

그외 DELIMETER, CREATE 그리고 BEGIN 및 END는 동일하다.

다음은 데뷔연도를 구하는 스토어드 함수이다.

다음과 같이 2010을 매개변수로 넘기면

활동 년수를 반환하는 것을 볼 수 있다.

SELECT INTO 를 통해서 변수를 입력하고

그 결과를 조회하는 것을 볼 수 있다.

또 다른 예시이다.

스토어드 함수는 이렇게 SELECT문을 통해서 주로 활용이 되는 것을 볼 수 있다.

스토어드 함수 삭제

DROP FUNCTION 명령어를 통해서 스토어드 함수 삭제가 가능하다.

또한 SHOW CREATE FUNTION [함수 이름]; 명령어를 통해서 기존에 작성된 스토어드 함수의 내용을 확인이 가능하다.

또한 추가적으로 result 창에서 Create Function을 우클릭 하고 Open Value in Viewer를 선택하면 Edit Data for Create Function (VARCHAR) 창의 Text탭에서 작성했던 스토어드 함수의 코드를 확인할 수 있다.

커서

커서는 기본적으로 가리킨다고 보면 된다.

먼저 커서를 선언하고

반복한 조건을 선언을 해야 한다.

그리고 데이터를 가져오고 데이터를 처리한다.

그리고 반드시 커서를 닫아야 한다.

사용할 변수 선언

변수 선언에도 DEFAULT 명령어를 통해서 초기값을 설정할 수 있다.

또한 행의 끝을 파악하기 위해서 endOfRow 변수를 준비했다. 

커서 선언하기

다음과 같이 DECLARE memberCuror CURSOR FOR SELECT mem_number FROM member; 명령어로 커서를 선언할 수 있다.

그리고 DECLARE CONTINUE HANDLER FOR NOT FOUND SET endOfRow = TRUE; 명령어를 통해서

더 이상 행이 없으면 endOfRow를 TRUE로 바꾸는 것이다.

커서 열기

행 반복하기

cursor_loop: LOOP

이 부분을 반복

END LOOP cursor_loop

를 통해서 반복이 가능하다.

무한반복을 피하기 위해서 LEAVE 명령어를 사용하여 반복문을 탈출할 수 있다.

그래서 IF 명령어를 통해서

IF endOfRow THEN

    LEAVE cursor_loop;

END IF;

이렇게 cursor_loop 를 탈출할 수 있다.

행의 하나 하나를 처리하기 위한 기능이다.

FETCH

FETCH 명령어를 통해서 하나씩 행을 데이터 처리가 가능하다.

다음은 실습한 코드이다.

커서는 역시 스토어드 프로시저에서 사용을 하는 것이다.

형식이 고정되어 있으므로 이와 같은 형식으로 코드를 사용하는 것이 바람직하다.

결과로 각 회원의 평균 인원수를 조회를 하였고, 그 결괏값은 6.6000이다.

다음과 같이 프로시저 내부에 변수들을 저장한 것을 볼 수 있다.

그리고 다음과 같이 CURSOR를 선언한 것을 볼 수 있다.

그리고 CONTINUE HANDLER 를 만들어서

FOR NOT FOUND SET endOfRow = TRUE; 를 하도록 하는 것을 볼 수 있다.

그리고 CURSOR를 OPEN하는 것을 볼 수 있다.

커서 내부의 반복문은 다음과 같은 형식으로 작성이 되었다.

IF 문을 통해서 행이 마지막인지 체크하는 것을 볼 수 있다.

마지막이  아니라면 읽은 행의 갯수를 count 하고 

회원의 수를 계속 count 하는 것을 볼 수 있다.

END LOOP 명령어를 통해서 cursor loop문을 그 사이에 선언할 수 있다.

모든 행을 읽으면 다음과 같이 SELECT문을 사용하여 '회원의 평균 인원 수'를 조회하는 것을 볼 수 있다.

그리고 CLOSE 명령어를 통해서 커서를 닫고

스토어드 프로시저 선언을 종료하는 것을 볼 수 있다.

그리고 해당 스토어드 프로시저를 CALL로 호출을 하면

다음과 같이 스토어드 프로시저의 목적인 

회원의 평균 인원 수를 조회한 것을 볼 수 있다.

감사합니다.

https://www.youtube.com/watch?v=bMQ_dAoaMzA&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=20