20211122 데이터베이스 트랜잭션, 프로시저

41기 개발자과정

20211122 데이터베이스 트랜잭션, 프로시저

segment 2021. 11. 22. 10:18

실습1.

CREATE TABLE `tb_test` (
	`t_name` VARCHAR(5) NOT NULL COMMENT '이름',
	`t_season` VARCHAR(5) NOT NULL COMMENT '계절',
	`t_amount` INT(11) NOT NULL COMMENT '수량'
)

실습2.

SELECT
t_season AS '계절'
,sum(if(t_name='김성주', t_amount, 0)) AS '김성주'
,sum(if(t_name='정동영', t_amount, 0)) AS '정동영'
,sum(t_amount) AS '합계'
FROM
tb_test
GROUP BY t_season
ORDER BY FIELD(t_season, '봄','여름','가을','겨울');

3. 실습

SELECT
	result.orderName AS '구매자'
	,GROUP_CONCAT(result.goodsName) AS '상품목록'
FROM
	(SELECT
		m.m_name AS 'orderName'
		,g.g_name AS 'goodsName'
		,SUM(o.o_amount) AS 'total'
	FROM
		tb_member AS m
		INNER JOIN
		tb_order AS o
		on m.m_id = o.o_id
		INNER join
		tb_goods AS g
		ON o.o_g_code = g.g_code
	GROUP BY o.o_id, g.g_code
	HAVING SUM(o.o_amount) >= 20 ) AS result
GROUP BY result.orderName;

group_concat : 그룹이 지어지어진 것의 특정 컬럼을 문자열로 합친다.

delete : 레코드 당 삭제 한다, 레코드가 1000개 있으면 1000번 삭제한다.
truncate : drop 하고 그 구조를 다시 생성한다. delete 보다 빠르다

트랜잭션

정의 :

- 하나의 작업을 수행하기 위해 필요한 데이터베이스 연산들을 모아 놓은 것

- 작업 수행에 필요한 SQL 문들의 모임

- 논리적인 작업의 단위

- 장애 발생 시 복구 작업이나 병행 제어 작업을 위한 중요한 단위로 사용됨

- 데이터베이스의 무결성과 일관성을 보장하기 위해 작업 수행에 필요한 연산들을 하나의 트랜잭션으로 제대로 정의하고 관리해야 함

트랜잭션의 특징 :

트랜잭션의 상태 :

수행시작 -> 활동 -> 부분완료 -> commit 연산 -> 완료

수행시작 -> 장애발생 -> 실패 -> rollback 연산 -> 철회

트랜잭션의 상태

- 활동상태 : 트랜잭션이 수행을 시작하여 현재 수행 중인 상태

- 부분완료상태 : 트랜잭션의 마지막 연산이 실행을 끝낸 직후의 상태

- 완료상태

- 트랜잭션이 성공적으로 완료되어 commit연산을 실행한 상태

- 트랜잭션이 수행한 최종 결과를 데이터베이스에 반영하고
데이터베이스가 새로운 일관된 상태가 되면서 트랜잭션이 종료됨.

- 실패 상태: 장애가 발생하여 트랜잭션의 수행이 중단된 상태

- 철회상태

- 트랜잭션의 수행 실패로 rollback 연산을 실행한 상태

- 지금까지 실행한 트랜잭션의 연산을 모두 취소하고 트랜잭션이
수행되기 전의 데이터베이스 상태로 되돌리면서 트랜잭션이 종료됨.

- 철회 상태로 종료된 트랜잭션은 상황에 따라 다시 수행되거나 폐기됨.

1. 묶음의 단위는 DML이다.

2. commit은 dml 기준이다.

3. MySQL의 InnoDB 엔진은 기본 설정이 트랜잭션이 미지원 상태이다.
왜냐하면 autocommit모드 (줄 단위 커밋)이 켜져있는 상태이기 때문에 트랜잭션이 미지원 상태이다.
트랜잭션은 여러줄이 실행되다가 문제가 생기면 다 취소가 되는 것이기 때문에 autocommit 모드에서는 트랜잭션이 수행 불가능하다! 트랜잭션이 지원되게 하려면 기본 설정을 autocommit 모드를 'False'로 변경해야한다!

트랜잭션의 시작 :	DML 구문 실행시
트랜잭션의 종료 :	1) COMMIT 또는 ROOLBACK 실행시 2) DDL 또는 DCL 구문 실행시

프로시저 : ddl, dml을 포함한다.

sp_ (stored~ )

declare 변수명 , 변수의 타입

프로시저에서는 if 사용시 then이 필수이다. else는 then 사용하지 않는다.

end if : 끝

세미콜론이 빠진경우 에러 발생한다.

프로시저 반복 :

반복문 자체도 이름을 정의 할 수 있다.

while_sum : 이름정의

iterate : continue 를 의미한다. 건너뛴다.

leave : break를 의미한다.

2일 때 2를 건너뛰고 총합을 구한다.

반복문 loop :

loop을 선언하면 무한정 돈다.

SELECT @@autocommit;
START TRANSACTION;
DELETE FROM user_backup WHERE u_id = 'id010';
DELETE FROM user_backup WHERE u_id = 'id009';
CREATE TABLE user_test(u_id VARCHAR(5));
INSERT INTO user_backup VALUES('id011','pw011','홍11');
ROLLBACK;
SELECT * FROM USER_backup;

프로시저 생성 :