스토어드 프로시저 특징.
1. MySQL 성능 향상가능
- 긴 코드로 구현된 쿼리를 실행하면, 클라이언트에서 서버로 쿼리의 모든 텍스트가 전송되어야 한다.
하지만 이러한 긴 코드의 쿼리를 서버에 스토어드 프로시저로 생성해 놓는다면,
단지 스토어드 프로시저 이름 및 매개 변수 등 몇 글자의 텍스트만 전송하면 되므로 네트워크의 부하를 어느 정도는 줄일 수 있으며, 결과적으로 MySql 성능을 향상시킨다.
- 유지관리가 간편함.
- C# 이나 JAVA 등의 클라이언트 응용프로그램에서 직접 SQL 문을 작성하지 않고
스토어드 프로시저 이름만 호출하도록 설정함으로써, 데이터베이스에서 관련된 스토어드 프로시저 내용을 일관되게 수정/유지보수 등의 작업을 할 수 있다.
- 보안 강화
- 사용자 별로 테이블에 접근 권한을 주지않고, 스토어드 프로시저에만 접근 권한을 줌으로써
조금 더 보안을 강화할 수 있다.
- 모듈식 개발이 가능.
- 스토어드 프로시저 vs 스토어드 함수
- 스토어드 프로시저의 파라미터와 달리 IN, OUT 을 사용할 수 없다.
스토어드 함수의 파라미터는 모두 입력 파라미터로 사용한다. - 스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로
하나의 값을 반환해야 한다. 스토어드 프로시저는 별도의 반환하는 구문이 없으며,
꼭 필요하다면, 여러 개의 OUT 파라미터를 사용해서 값을 반환할 수 있다. - 스토어드 프로시저는 CALL 로 호출하지만, 스토어드 함수는 SELECT 문장 안에서 호출한다.
- 스토어드 프로시저 안에는 SELECT 문을 사용할 수 있지만, 스토어드 함수 안에서는 집합 결과를 반환하는 SELECT를 사용할 수 없다.
- 커서 (행의 집합 다루기)
- 테이블에서 여러 개의 행을 쿼리한 후, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식.
3.1 커서의 처리 순서
- 커서 선언하기 (DECLARE CURSOR)
- 반복 조건 선언하기 (DECLARE CONTINUE HANDLER)
- 더 이상 읽을 행이 없을 경우에, 실행할 내용 설정
- 커서 열기 (CURSOR OPEN)
- 커서에서 데이터 가져오기 (FETCH)
- LOOP ~ END LOOP 문으로 반복 구간 설정
- 데이터 처리
- 커서 닫기 (CLOSE)
- 트리거
- 테이블에 무슨 일이 일어나면 '자동'으로 실행 되는 것.
- 테이블에 삽입, 수정, 삭제 등의 작업(이벤트) 발생 시에 자동으로 작동되는 개체로, 스토어드 프로시저와 비슷한 모양을 가짐
- 트리거는 제약 조건과 더불어 데이터 무결성을 위해서 MySQL 에서 사용할 수 있는 또 다른 기능임.
- 트리거는 테이블에 관련되고 DML문 (Insert, Update, Delete)의 이벤트가 발생될 때 작동하는 데이터베이스 개체 중 하나
테이블에 부착되는 프로그램 코드. 스토어드 프로시저와 거의 비슷한 문법으로 내용 작성이 가능하고,
트리거가 부착된 테이블에 이벤트 (입력, 수정, 삭제)가 발생하면 자동으로 부착된 트리거가 실행된다. - 트리거는 스토어드 프로시저와 작동이 비슷하지만 직접 실행시킬 수 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행되고
트리거에는 스토어드 프로시저와 달리 IN, OUT 매개 변수를 사용할 수도 없다.- SQL 문장이 실행되는 조건 == 이벤트
- DML 문장이 실행 될 때마다 트리거 안에 조건문이 자동실행됨.
- AFTER 트리거
- 테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동하는 트리거를 말함.
INSERT, UPDATE, DELETE 작업 후에 작동한다.
- BEFORE 트리거
- 이벤트 발생 전에 작동하는 트리거.
- 트랜잭션
- 모든 SQL 문이 성공하거나 성공하지 않도록 SQL 문 집합을 그룹화 할때 사용하는 매커니즘
- 여러 사용자가 동일한 데이터에 액세스 할 수 있도록 하는 요소.
- 트랜잭션은 여러 SQL 문을 함께 그룹화 하여 모든 구문이 성공하거나 성공하지 않도록 하는 장치
예를 들어서 이체 요청을 처리하는 프로그램은 먼저 트랜잭션을 시작한 다음 저축한 돈을 예금 계좌로 옮기는 데
필요한 SQL 문을 실행하며 모든 과정이 성공하면 commit 명령어를 실행하여 거래를 종료하고
예상치 못한 일이 발생하면 프로그램은 rollback 명령어를 실행해서 트랜잭션이 시작된 이후의 모든 변경 사항을 취소.
- 트랜잭션 프로세스
트랜잭션이 시작되면 start transaction 명령어를 통해 명시적으로, 또는 데이터베이스 서버에 의해
암시적으로 트랜잭션을 종료해야 변경 사항이 영구 적용된다. commit 명령어를 사용해서 서버가 변경 사항을
영구적으로 적용하고 트랜잭션 중에 사용된 모든 리소스 (페이지 또는 행 잠금 등) 를 해제하도록 지시함.
트랜잭션 시작 이후의 모든 변경 사항을 실행 취소하려면 rollback 명령어를 실행해 데이터를 트랜잭션 전 상태로 되돌리도록 서버에 지시해야 함. 롤백이 완료되면 세션에서 사용된 모든 리소스가 해제됨.
commit 또는 rollback 명령어를 실행했을 때 뿐만 아니라 다른 작업의 간접적인 결과로, 또는 제어할 수 없는 어떤 이유로 트랜잭션이 종료될 수 있느 시나리오
1. 서버가 종료되고 서버가 재시작되면서 트랜잭션이 자동으로 롤백됨.
2. alter table 과 같은 SQL 스키마 문을 실행하면 현재 트랜잭션이 커밋되고 새로운 트랜잭션이 시작됨
3. 다른 교착 상태를 감지했을 때 해당 트랜잭션이 원인이라고 판단되면 서버는 트랜잭션을 조기 종료한다 .
이 경우 트랜잭션이 롤백되고 오류 메시지가 표시된다.
