(TIL) MySQL 고급 문법 정리: INSERT SELECT부터 트랜잭션, 에러 핸들러까지

- INSERT INTO SELECT

 

SELECT 결과를 INSERT 할 수 있는 SQL문 입니다.

 

 

방법

INSERT INTO 목적_테이블
(COL1, COL2, COL3)
SELECT COL1, COL2, COL3
FROM 출발_테이블
;

 

예시

INSERT INTO MEMBER(
    ,	MEMBER_ID
    ,	MEMBER_NAME
    ,	CITY
    ,	COUNTRY
)
SELECT 	CUSTOMER_ID
    ,	NAME
    ,	CITY
    ,	COUNTRY
FROM	CUSTOMER
WHERE	(COUNTRY='KOR')
;

 

MEMBER 테이블에 값을 삽입할 때

CUSTOMER 테이블의 COUNTRY가 KOR인 값들만 조회에서 넣는 쿼리문 입니다.

 

INSERT와 SELECT를 동시에할 수 있는 장점을 갖고 있습니다.

 

 

- 프로시저의 변수 (DECLARE, SET)

 

SQL 프로시저에서는 로컬 변수 지원을 통해 SQL 값을 지정하고 검색할 수 있습니다.

SQL 프로시저의 변수는 DECLARE문을 사용하여 정의됩니다.

그리고 SET을 통해 변수의 값을 지정할 수 있습니다.

 

방법

-- 변수 정의
DECLARE 변수명 데이터_타입

-- 변수 값 할당
SET 변수명

 

 

예시

CREATE PROCEDURE `EXAMPLE_PROCEDURE`()
BEGIN
	
    -- 기본값 지정한 경우
    DECLARE V_TOTAL INTEGER DEFAULT 0;
    
    -- 기본값은 선택사항
    DECLARE V_MEMBER_ID VARCHAR(30);
    
    SET V_TOTAL = V_TOTAL + 1;
    .
    .
    .    
    
END

 

DECLARE로 V_TOTAL이라는 변수를 만든 후 0으로 초기값을 설정했습니다.

초기값은 필수가 아니기 때문에 V_MEMBER_ID에는 설정하지 않았습니다.

 

(데이터 타입은 필수이기 때문에 꼭 넣어야합니다!)

 

그리고 SET을 통해 선언한 변수에 값을 지정할 수 있습니다.

 

 

- DECLARE ... HANDLER 문

 

DECLARE...HANDLER 에 하나 이상의 조건을 처리하는 핸들러를 지정합니다. 

이러한 조건 중 하나가 발생하면 지정된 statement가 실행됩니다.

 

 

방법

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

 

-- 핸들러문을 실행한 후 핸들러가 수행하는 작업을 나타냄
handler_action: {
    CONTINUE -- 지정된 조건이 맞다면 작성한 statement를 실행하고 이어서 계속 실행 
  | EXIT -- 지정된 조건이 맞다면 작성한 statement를 실행후 종료
}

-- 핸들러를 활성화하는 특정 조건 또는 클래스를 나타냄
condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

 

예시 1

(condition_value에 NOT FOUND를 넣었을 경우)

DECLARE CONTINUE HANDLER FOR NOT FOUND
  BEGIN
    -- body of handler
  END;

 

사용할 수 있는 행이 더 이상 없으면 데이터 없음 조건이 발생합니다.

 

 

예시 2

(condition_value에 SQLWARNING를 넣었을 경우)

 

DECLARE CONTINUE HANDLER FOR SQLWARNING
  BEGIN
    -- body of handler
  END;

핸들러의 조건은 일어나는 상황에 맞게 선택해주시면 됩니다.

 

예시 3

(condition_value에 SQLEXCEPTION를 넣었을 경우)

 

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    -- body of handler
  END;

 

예시 3-1

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
	END;

SQLEXCEPTION이 발생했을 때 ROLLBACK을 실행하는 구문을 넣은 핸들러입니다

 

 

🤔 SQL WARNING 핸들러와 SQLEXCEPTION 중 어느 것이 더 우선순위가 높을까요?

 

-> SQLEXCEPTION이 더 우선합니다!

 

 

일단 SQLWARNING은 에러가 아닌 경고가 발생합니다.

그리고 SQLEXCEPTION은 에러가 발생합니다.

 

SQLEXCEPTION의 경우 EXIT 핸들러가 있는 것처럼 저장된 프로그램이 조건을 제기한 문에서 종료됩니다.

프로그램이 다른 프로그램에서 호출된 경우 호출 프로그램은 자체 핸들러에 적용된 핸들러 선택 규칙을 사용하여 조건을 처리합니다.

 

SQL WARNING의 경우 CONTINUE 핸들러가 있는 것처럼 프로그램이 계속 실행됩니다.

 

 

- START TRANSACTION, COMMIT, ROLLBACK

 

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

 

 

START TRANSACTION: 새로운 트랜잭션을 시작해주는 구문입니다.

COMMIT: 현재의 트랜잭션을 커밋하여 변경사항을 영구하게 만듭니다.

ROLLBACK: 현재의 트랜잭션을 롤백하여 변경사항을 취소합니다.

 

 

기본적으로 MySQL은 자동 커밋 모드를 사용하도록 설정되어 있습니다. 

즉 트랜잭션 내에서 별도로 처리를 해주지 않는다면 START TRANSACTION과 COMMIT으로 둘러싸여 있는 것처럼 처리됩니다.

그래서 START TRANSACTION으로 자동 커밋을 해제하고 내부에서 COMMIT과 ROLLBACK을 적절히 사용하면 오류를 예방할 수 있습니다.

 

 

 

 

출처:

MySql_DECLARE...HANDLER

SQL_프로시저의_변수

SQL_INSERT_INTO_SELECT

sql에러코드

START_TRANSACTION,COMMIT,ROLLBACK