14.2.6.3 반복 루프 까지
294p ~ 326p

스토어드 프로그램 장단점

장점

보안 향상
기능 추상화
네트워크 소요 시간 절감
절차적 기능 구현
개발 업무의 구분
단점
낮은 처리 성능
애플리케이션 코드의 조각화
스토어드 프로시저
파라미터는 IN / OUT / INOUT 타입을 가진다.
OUT / INOUT type은 세션 변수를 사용해야 한다.
스토어드 프로시저 내부에서 실행하는 SELECT 결과 셋은 커서를 열지 않으면 클라이언트로 전송된다. (디버깅 용도 사용 가능)
스토어드 함수
하나의 SQL 문장으로 작성이 불가능한 기능을 하나의 SQL 문장으로 구현해야할 때 사용
ex. 그룹 별 몇건씩 레코드 조회 하기
파러미터는 모두 읽기 전용
반환값의 타입과 반환 (RETURN)을 명시해야 한다.
스토어드 함수는 스토어드 프로시저와 다르게 쿼리 내에서 실행 가능
반대로 CALL로 실행 불가능
트리거
테이블 단위로 실행 가능
각 ROW 단위로만 TRIGGER 가능
BEFORE / AFTER , INSERT / UPDATE / DELETE 가능
SQL 별로 발생시키는 트리거를 명확히 알고 있어야 한다.
이벤트
특정 시간에 스토어드 프로그램을 실행할 수 있다.
일회성 / 반복성
스토어드 프로그램 본문 작성

트랜잭션
트랜잭션은 START TRANSACTION 으로 실행한다.
스토어드 프로시저와 이벤트에서는 트랜잭션을 사용할 수 있지만, 스토어드 함수와 트리거에서는 트랜잭션을 사용할 수 없다.
다만 내부에서 트랜잭션 시작 종료는 외부와 독립적이 되므로, 외부 실행에 종속적으로 실행하려면 TRANSACTION 을 내부에서 실행하지 않도록 한다.
변수
BEGIN ... END 내의 로컬 변수와 사용자 변수가 있다.
SET / SELECT INTO 로 값을 할당할 수 있다.
SELECT INTO 의 경우 1건의 레코드만을 반환해야 한다.
IF - ELSEIF - ELSE - END IF

CASE WHEN ... THEN ... ELSE ... END ELSE

반복 루프
LOOP
- LEAVE 로 루프에서 나오는 것을 명시한다.
REPEAT ... UNTIL
WHILE

MySQL에서는 절차적인 처리를 위해 스토어드 프로그램을 이용할 수 있다.

스토어드 프로그램
- 스토어드 루틴, 스토어드 프로시저, 스토어드 함수, 트리거, 이벤트 등을 아우르는 명칭
- 절차적인 처리를 제공하긴 하지만 애플리케이션을 대체할 수 있을지 충분히 고려해봐야 함.
스토어드 프로그램 장점
- 보안 향상, 기능 추상화, 네트워크 소요 시간 절감, 절차적 기능 구현, 개발 업무의 구분
스토어드 프로그램 단점
- 낮은 처리 성능, 애플리케이션 코드의 조각화
스토어드 프로시저
- 용도
- 여러 쿼리를 하나의 그룹으로 묶어서 독립적으로 실행하기 위해 사용
- 특징
- SELECT 쿼리에 사용될 수 없음.
- 반드시 CALL로 실행
- 프로시저는 기본 반환값이 없다.
스토어드 함수
- 용도
  - 하나의 SQL 문장으로 작성이 불가능한 기능을 하나의 SQL 문장으로 구현해야 할 때 사용
- 특징
  - SQL 문장의 일부로 사용 가능
  - 반환되는 값과 타입을 명시해야함.
  - 프로시저에 비해 상대적으로 제약 사항이 많아서 독립적으로 실행해도 된다면 프로시저를 쓰는게 좋다.
- 사용 예시
  - 부서별로 최근에 배속된 사원을 2명씩 가져오는 기능
트리거
- 용도
- 테이블의 레코드가 저장되거나 변경될 때, 미리 정의해둔 작업을 자동으로 실행
- 특징
- 스토어드 함수나 프로시저보다 필요성 떨어짐
  - 테이블에 칼럼을 추가하거나 삭제하는 작업은 임시 테이블에 복사하는 작업이 필요한데,
  - 이때 레코드마다 트리거를 한 번씩 실행해서 오래걸린다.
- 사용 예시
- 칼럼의 유효성 체크
- 다른 테이블로의 복사나 백업 등..
이벤트
- 용도
- 주어진 특정한 시간에 스토어드 프로그램을 실행할 수 있는 스케줄러 기능
스토어드 프로그램 본문
- BEGIN...END 블록 내에서는 트랜잭션의 시작이 BEGIN 키워드가 아닌 START TRANSACTION 명령을 사용해야한다.

스토어드 프로그램

스토어드 프로그램 vs 애플리케이션
- 키 발급기 정도는 사용해도 될듯
  - 잘 사용하는 auto increment 정도는 괜찮을텐데 샤딩키나 특정 로직으로 조합하는 키는 소스코드 단에서 관리하는게 낫지 않나..
- dbms 코드를 개발하는 조직에서는 트랜잭션 단위로 데이터베이스 관련 처리를 하는 스토어드 프로그램을 만들어 API 처럼 제공 → 어떻게 생각하시는지...?
  - 개인적으로 msa한다고 플랫폼 조직으로 로직 분산되어있는 것도 힘들어 죽겠는데 비즈니스 로직이 dba 조직까지 분산되어 관리된다면 개발하기 많이 힘들듯(더 읽어보니까 단점으로 나오네요..ㅎㅎ)
세션 변수 @
- https://dev.mysql.com/doc/refman/5.6/en/set-variable.html
스토어드 프로시저 → 로그 파일에 기록도 못하고 디버깅도 못하긴 함..매번 println처럼 select로 뽑아보며 개발해야함
스토어드 함수 → return 해서 함수처럼 쓸 수 있지만 sql 문장과 관계없이 실행된다면 프로시저가 낫다함
- 자주나오는 cs 데이터 추출 함수로 만들어두고 공유해도 좋을듯(by 우디)
이벤트 = 특정한 시간에 스토어드 프로그램을 실행하는 스케줄러 기능
- 강제로 실행 못하고 스케줄링 시점을 임의로 설정해서 실행해야 함
aop가 자동으로 해주는 트랜잭션 시작과 커밋도 수동으로 해야함
기타 조건문, 반복문은 알고있는 것과 비슷

누가쓰지?

2014년의 카카오 db팀에서 사용했다함
네이버에서는 2006~10년 정도에 쓴듯

스토어드 프로그램

어휘 정리
- 스토어드 프로그램
- 스토어드 루틴
- 스토어드 함수
- 트리거
- 이벤트
- 스토어드 쿼리(뷰)

장단점

장점: SQL 문법 취약점 방어
단점: 설치, 배포, 유지보수가 복잡해짐

스토어드 프로그램의 문법

p297, 자세한 설명은 생략한다.
유지보수하기에 좋지 않다고 생각하기에 차라리 JPA 를 공부하는게 낫지 않을까 라는 생각이 들었다.

스토어드 프로시저

CREATE PROCEDURE

여러 쿼리를 하나의 그룹으로 묶어서 독립적으로 실행

스토어드 함수

CREATE FUNCTION

하나의 SQL 문장으로 불가능한 기능을 하나의 SQL 문장으로 구현하려 함

트리거

CREATE TRIGGER

미리 정의해둔 작업을 자동으로 실행

이벤트

CREATE EVENT

특정 시간에 스토어드 프로그램을 실행할 수 있는 스케줄러 기능

스토어드 프르그램 Body 작성

공통으로 사용하는 제어문들

BEGIN START TRANSACTION; ... END
DECLARE 변수
IF ... ELSEIF .... ELSE ... END IF
CASE WHEN ... THEN ... ELSE ... END CASE
loop_name: LOOP ... END LOOP;, WHILE ... END WHILE

스토어드 프로그램 장단점

장점

DB 보안 향상
- 동적 SQL 생성
- 세밀한 권한 제어
기능의 추상화
네트워크 소요 시간 절감
- 295p '일반적으로 어플리케이션과 데이터베이스 서버는 같은 네트워크 구간에 존재한다' ??
- 유기적으로 연결된 두 쿼리가 있다면, 스토어드 프로그램 내에서 두 쿼리 차례로 호출 가능 -> 네트워크 통신 비용 감소
절차적 기능 구현
개발 업무의 구분

단점

낮은 처리 성능
- 스토어드 프로그램의 처리 성능이 다른 프로그램언어에 비해 상대적으로 떨어진다
- 실행 시마다, 스토어드 프로그램 파싱되어야 함
어플리케이션 코드의 조각화
- 개인적으로 가장 문제가 되는 부분일 수 있다고 생각함
- 하나의 기능에 대한 프로그램 코드가 어플리케이션 영역과 스토어드 프로그램으로 분산된다면 설치/배포/유지보수 측면에서 어려움이 생길 수도

스토어드 프로그램 문법

구성

헤더 (정의부)
- 스토어드 프로그램의 이름, 입출력 값 명시
본문 (바디)
- 스토어드 프로그램이 호출될 때 실행하는 내용 작성

주의

공백 주의
- 혹은 IGNORE_SPACE
32비트 운영체제
- 스택 크기로 인한 문제 발생할 수도
- thread_stack 설정에서 각 스레드가 사용하는 스택의 크기 늘려라
- https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_stack
별도의 로그파일 기록 기능 없다(302p)

스토어드 프로시저

생성 및 삭제

-- 생성
CREATE PROCEDURE sp_sum (IN param1 INTEGER, INOUT param2 INTEGER, OUT param3 INTEGER)
    BEGIN
      SET param3 = param1 + param2
      SET param2 = param2 + 1
    END ;;
-- 삭제
DROP PROCEDURE sp_sum
-- 수정
ALTER PROCEDURE sp_sum SQL SECURITY DEFINER;

파라미터 타입
- IN : 입력 전용 파라미터
- OUT : 출력 전용 파라미터
- INOUT : 입력 및 출력용 파라미터
명령의 끝을 알려주는 종료문자
- DELIMITER로 종료문자 설정 가능 (기본 종료 문자는 ;)
- ;;, // 연속된 문자열로 보통 설정
- 당근 예약어 놉

프로시저 실행

SELECT 쿼리에 사용 놉
CALL 명령으로 실행

인자 전달 시,

IN은 상수값 그대로 전달 OK

OUT, INOUT은 세션 변수 사용해야 함 (@result)

SET @param2:=0; -- INOUT 세션변수
SET @result:=0; -- OUT 세션변수
CALL sp_sum(1,@param2,@result); -- 프로시저 호출
SELECT @result; -- 결과 조회

프로시저 딕셔너리

MySQL 8 이전 : mysql 데이터베이스의 proc 테이블 저장
MySQL 8 이후 : 시스템 테이블에 저장 (information_schema.ROUTINES 뷰에서 조회 가능)

스토어드 함수

SQL 문에서 반복적으로 사용되는 구문을 함수로 모듈화 가능

사용/생성/변경/삭제

사용

SELECT dept_no, sf_getRecentEmp(dept_no)
FROM dept_emp
GROUP BY dept_no;

생성
```
CREATE FUNCTION sf_sum(param1 INTEGER, param2 INTEGER)
RETURNS INTEGER
BEGIN
DECLARE param3 INTEGER DEFAULT 0;
SET param3 = param1 + param2;
RETURN param3;
END;;
```
- 모든 입력 파라미터는 읽기 전용 -> IN, OUT, INOUT 개념 없음
- 정의부에 REUTNRS로 반환되는 값의 타입을 명시하고, 정의부 마지막에 RETURN하는 값이 동일한 타입으로 지정되어있어야 함
- 정의부 BEGIN ~ END 사이에서의 제한사항은 306p 참고
변경
```
ALTER FUNCTION sf_sum SQL SECRUITY DEFINER;
```
- 스토어드 함수 특성만 변경 가능
- 파라미터나 내용 변경은 불가능 (삭제후 재생성)
삭제
```
DROP FUNCTION sf_sum;
```
스토어드 함수 실행
- CALL 대신 SELECT 명령으로 실행
```
SELECT sf_sum(1,2);
```

트리거

테이블의 레코드가 저장되거나 변경될 때 미리 정의해둔 작업을 자동으로 실행해주는 스토어드 프로그램
주로
- 유효성 체크 // 어플리케이션 레벨에서 하자
- 다른 테이블로의 복사나 백업 // 언제 필요하려나
- 계산된 결과를 다른 테이블에 함께 업데이트 // 언제 필요하려나 이런 상황을 만들지 말고 이것도 어플리케이션 레벨에서 하면 어떨까
테이블에만 생성 가능

이벤트

주어진 특정한 시간에 스토어드 프로그램을 실행할 수 있는 스케줄러 기능
MySQL에서 이벤트 스케줄링 전담 스레드 있음
event_scheduler ON
전체 실행 이력 기록하지 않으므로 별도로 직접 이벤트 처리로직에서 기록하자

BEGIN .. END 블록과 트랜잭션

START_TRANSACTION ~ COMMIT
프로시저 내부에서 트랜잭션 완료하면, 어플리케이션 호출하는 쪽(ex 어플리케이션)에서는 트랜잭션 조절 못함 (함수의 경우 어플리케이션 단에서 트랜잭션 처리하자)

스토어드

14.1 스토어드 프로그램의 장단점

14.1.1 스토어드 프로그램의 장점

스토어드 프로그램 단위의 권한
테이블/킬럼 마다의 권한 접근제어 가능
유효성 체크를 기본으로 수행 > SQL 자체의 취약점 면역
기능의 추상화
네트워크 오버헤드가 준다 (짧은 쿼리를 여러번 보내는 상황에서 더 유리)
절차적인 기능 구현시
개발업무 구분(DBA - 프로그래머 간) R&R 분리가능

14.1.2 스토어드 프로그램의 단점

낮은 성능
로직이 소스코드 , DB 두군데에서 흩어져서 관리됨(응집성이 떨어진다)

14.2.2 스토어드 프로시저

데이터를 주고받아야 하는 쿼리문 사이에서 동릭접으로 실행됨
네트워크 효율적임 (오버헤드를 줄임)

14.2.3 스토어드 함수

한줄쿼리로 불가능한 여러줄 쿼리를 묶어줄때

스토어드 함수 생성과 삭제

생성시 모든 입력파라미터는 읽기전용

스토어드 함수 VS 스토어드 프로시져 차이점 두가지

함수 정의부에 리턴값을 정의해야 한다 (함수가)
본문 마지막에 정의부에 지정된 타입과 동일한 타입으로 리턴(함수가)

14.2.4 트리거

트리거 정의 : 테이블의 레코드가 저장되거나 변경될때 미리 정의해둔 작업을 자동으로 실행해주는 프로그램의 일종

14.2.5 이벤트

특정한 시간에 스토어드 프로그그램 실행하기 위한 스케쥴러가 : 이벤트

변수

사용자변수와 다름
스토어드 프로그램 내부에서만 동작하는 일종의 로컬변수가 있음

제어문

조건문, 반복문 등의 처리절차

14.1 스토어드 프로그램의 장단점

장점

데이터베이스 보안 향상: 자체적인 보안 설정, 스토어드 프로그램 단위로 실행 권한 부여 가능
기능의 추상화: 예를 들어, 일련번호(타임스탬프 + 정수)를 MySQL에서 발급하면 개발 언어나 도구와 관계없이 활용 가능 -> 반대로 말하면 일련번호 정책이 바뀌면 스토어드 프로그램을 변경해야함
네트워크 소요 시간 절감: 애플리케이션 <-> 데이터베이스 간의 네트워크 경유시간 >> 쿼리 실행시간인 경우에 유리
절차적 기능 구현: SQL에서 제공하지 않는 절차적 기능 제공(IF, WHILE 등)
개발 업무의 구분: 애플리케이션 개발 조직과 DBMS관련 코드 개발 조직을 분리 가능 -> 이미 많은 회사에서 DBA와 애플리케이션 개발자의 역할과 경계가 통용되므로 오히려 단점이 될 수 있음

단점

낮은 처리 성능: MySQL 서버는 절차적 코드 처리 성능이 떨어짐
애플리케이션 코드의 조각화: 애플리케이션의 설치 및 배포가 복잡, 유지보수도 어려움
디버깅이 어렵다 (by Jane)

14.2 스토어드 프로그램의 문법

스토어드 프로시저

여러 쿼리를 하나의 그룹으로 묶어서 독립적으로 실행하기 위해 사용

스토어드 함수

하나의 SQL 문장으로 구현해야 할 때 사용
SQL 문장의 일부로 사용할 수 있음(스토어드 프로시저는 x)
5.7까지는 단일 SQL 문장으로 작성할 수 있는 방법은 없음
활용: GROUP BY의 SELECT절에 여러개를 합치는 경우

2명의 사원 번호만 SELECT하고 문자열로 결합하려면 스토어드 함수없이 어떻게?

트리거

데이터 변화가 발생할 때 실행되는 스토어드 프로그램
활용: 유효성 체크, 복사 및 백업, 계산 결과를 업데이트 등

이벤트

특정 시간에 스토어드 프로그램을 실행하는 스케줄러 기능

Q) @Scheduled vs cron? Q) 쉘 스크립트 vs 파이썬 및 Golang 스크립트, 쉘 스크립트 활용 전망? Q) RDB가 아니라면, MongoDB 에서는?

SQL의 문법을 확장해서 스토어드 프로그램의 기능을 일부 포함할 정도가 된다면 사용할만할 것 같다.

BackendSquid / RealMySQL

Ch14 스토어드 프로그램 #30

스토어드 프로그램 장단점

장점

단점

스토어드 프로시저

스토어드 함수

트리거

이벤트

스토어드 프로그램 본문 작성

트랜잭션

변수

IF - ELSEIF - ELSE - END IF

CASE WHEN ... THEN ... ELSE ... END ELSE

반복 루프

스토어드 프로그램

누가쓰지?

스토어드 프로그램

장단점

스토어드 프로그램의 문법

스토어드 프로시저

스토어드 함수

트리거

이벤트

스토어드 프르그램 Body 작성

스토어드 프로그램 장단점

장점

단점

스토어드 프로그램 문법

구성

주의

스토어드 프로시저

생성 및 삭제

프로시저 실행

프로시저 딕셔너리

스토어드 함수

사용/생성/변경/삭제

스토어드 함수 실행

트리거

이벤트

BEGIN .. END 블록과 트랜잭션

스토어드

14.1 스토어드 프로그램의 장단점

14.1.1 스토어드 프로그램의 장점

14.1.2 스토어드 프로그램의 단점

14.2.2 스토어드 프로시저

14.2.3 스토어드 함수

스토어드 함수 생성과 삭제

스토어드 함수 VS 스토어드 프로시져 차이점 두가지

14.2.4 트리거

14.2.5 이벤트

변수

제어문

14.1 스토어드 프로그램의 장단점

장점

단점

14.2 스토어드 프로그램의 문법

스토어드 프로시저

스토어드 함수

트리거

이벤트