[Oracle] Query문을 작성해 보자! (5) - 기초 쿼리문 ( INSERT, UPDATE, DELETE, COMMIT, ROLLBACK, 트랜잭션 )

 

 

 

이번 글은 데이터를 삽입하고, 수정하고, 지우는 쿼리문인 INSERT, UPDATE, DELETE에 대해서 써보자.

 

 

 

1. INSERT(데이터 삽입)

 

INSERT는 테이블에 데이터를 삽입(추가)하는 쿼리문이다. 단일행, 다중행, 서브쿼리를 이용하는 방법이 있다. 예시를 통하여 하나씩 알아보자

 

 

 

1. 단일행 삽입

 

 

" INSERT INTO [테이블 명] ( 컬럼1, 컬럼2, 컬럼3 ....) VALUES (값1, 값2, 값3...) "

 

 

14번째 행에 데이터가 추가된 것을 확인할 수 있다. 하지만 이상한점... NO와 REGDATE의 값이 "NULL"로 들어가 있다. 보면 알겠지만 INSERT할 때 들어가는 컬럼하고 값의 개수랑, 테이블에 있는 컬럼하고 값의 개수랑 다르다. INSERT할 때 도 몇가지 규칙이 존재한다.

 

 

INSERT할 때 적는 컬럼의 개수와 테이블에 들어있는 컬럼의 개수랑 일치해야한다.   

 

 

1. 컬럼의 개수가 맞지 않으면 NULL값으로 들어가게 된다. (테이블 컬럼은 8개인데, INSERT 할 때 컬럼이 6개면 나머지 컬럼은 NULL값으로 들어간다)

 

- 전체 컬럼을 다 안적어도 개수만 맞으면 에러 없이 INSERT가 되고, 빠트린 컬럼들은 NULL값으로 들어가는 것

 

 

 

2. 컬럼의 개수와 VALUES의 개수가 다르면 에러가 난다.(INTO 뒤에 컬럼의 개수를 6개 적었는데, VALUES뒤에 값이 5개면 서로 일치하지 않아 NULL값조차 들어가지 않고 에러가 나버린다. )

 

 

3. 컬럼의 타입과 VALUES의 타입이 일치히지 않아도 에러가 난다. 

 

-근데 이게 숫자 타입인데 문자를 넣으면, 자동으로 숫자가 되는 이상한 규칙이 있다. 

 

4. 해당하는 칼럼보다 VALUES에 들어가는 값이 크면 에러가 난다.

 

- VARCHAR2(10)인데 30자가 넘어가는 글자를 넣으면 당연 에러가 난다.

 

---

 

"디폴트값으로" 시퀀스 or 날짜값 을 미리 지정해 놓았다면, 컬럼과 VALUES에 굳이 값을 적지 않아도 자동으로 들어간다. (비워놓고 INSERT해도 에러가 나지않는다. 기본값으로 들어가진다)

 

기본값 입력

 

기본값으로 시퀀스와 날짜타입을 넣어 놓은 뒤  NO와 REGDATE를 쓰지 않고 INSERT를 하면, 디폴트값인 시퀀스 값과 현재날짜 값이 자동으로 들어가진다.

 

---

 

2. 단일행 삽입(컬럼 지정 X )

 

처음 방법은 컬럼을 지정해서 컬럼의 개수에 맞춰 값을 넣는 방법이다. 하지만 컬럼을 지정안하고 테이블 명만 적고도 INSERT를 할 수있다. 

 

 

" INSERT INTO [테이블 명] VALUES (값1, 값2, 값3...) "

 

 

이렇게도 할 수 있는데, 내가 실험을 몇번 해본결과 디폴트값으로 시퀀스나 날짜값을 미리 세팅 해놨더라도 빈 칼럼 없이 다 채워 넣어야한다. 그래야 값이 제대로 들어갔다. (간단히 말하면 디폴트값이 안먹힌다?)

 

---

 

3. 다중행 삽입

 

 

 

 

같은 시간에 여러개의 데이터가 동시에 들어간 것을 확인할 수 있다. 

 

---

 

4. 서브쿼리 삽입

 

※ 서브쿼리란? 

 

서브쿼리(Subquery)는 SQL 문장 안에 포함된 또 다른 SQL 문장으로, 주로 다른 쿼리의 결과를 가지고 와서 부가적인 작업을 수행하는데 사용한다. 즉, 서브쿼리는 주 쿼리에 대한 데이터를 생성하거나 필터링하는 데 사용되는 중첩된 쿼리를 말한다.

 

[ 서브쿼리 설명글 ]

 

[Oracle] Query문을 작성해 보자! (6) - 서브쿼리란? ( 스칼라 서브쿼리, 인라인 뷰, 중첩 서브쿼리)

 

 

 

 

먼저 서브쿼리 사용을 위해  ICECREAM이라는 새로운 테이블을 하나 생성 했다.

 

 

 

" INSERT INTO [테이블 명] ( 컬럼1, 컬럼2, 컬럼3 ....)

SELECT 컬럼1, 컬럼2, 컬럼3 .... FROM [테이블 명]  WHERE [컬럼명] = '조건' "

 

 

 

아이스크림 테이블에서 1200원 이상인 아이스크림만 INSERT된 것을 확인할 수 있다.

 

값을 일일이 입력해서 넣는 INSERT 와, 다른 테이블에서 필요한 값을 꺼내와서 넣는 방법. 두 방법 모두 자주 쓰이는 방법이며, 상황에 맞게 활용한다면 보다 유연한 SQL문 작성이 가능할 것이다.

 

이 외에도 질의결과 테이블을 만들거나, 테이블 구조를 복사하는 쿼리문도 있지만 대표적으로 많이 쓰는 INSERT쿼리문을 모아 정리해 보았다.

 

---

 

2. UPDATE (데이터 수정)

 

UPDATE는 테이블 안에 데이터를 수정하는 쿼리문이다. 조건을 주어 내가 원하는 데이터를 변경할 수도 있고, 테이블 안에 모든 데이터를 한번에 변경 할 수 도있다.

 

 

[기본 테이블]

 

 

 

 

" UPDATE  [테이블 명] SET [컬럼명] = '바꿀 값'  WHERE [컬럼명] = '조건' "

 

 

 

※ 주의할 점은 UPDATE를 쓸 때 WHERE 조건을 넣어주지 않으면, 해당하는 컬럼의 모든 값을 변경 해버리기 때문에 UPDATE를 쓸 때는 조건을 반드시 입력하자 (다 바꾸고 싶다면 상관없지만...)

 

 

결과를 보면 8개의 행이 바뀐것을 확인 할 수있다. 그럼 실제로 바꼈는지 확인을 해보자.

 

 

잘 바뀐거 같다. 그리고 꼭 하나만 값을 바꾸는게 아니라 여러개도 바꿀 수 있다.  이렇게 SET을 추가해서 여러개의 컬럼도 한번에 변경이 가능하다.

 

UPDATE  [테이블 명] SET [컬럼명] = '바꿀 값1', SET [컬럼명] = '바꿀 값2'..  WHERE [컬럼명] = '조건' "

 

 

---

3. DELETE (데이터 삭제)

 

테이블이나 컬럼을 지워버리는 DROP이 아니라 , 테이블 안의 튜플(데이터)을 지우는 DELETE쿼리문이다. UPDATE와 마찬가지로 WHERE 조건을 걸어주지 않으면 모든 데이터를 지워버리는 불상사가 일어날 수 있으니 주의하시고, 그외에는 그냥 테이블 안의 데이터를 지워버리는 명령어이다. 

 

DELETE FROM MEMBER3;

 

DELETE FROM MEMBER3 WHERE NO BETWEEN 26 AND 29;

 

1. 처음처럼 아무 조건이 없이 DELETE를 하면 모든 데이터를 지워버리게 된다.

2. 조건을 줘서 내가 지금 필요없는 데이터만 지우도록 하자.

 

DELETE  FROM [테이블 명] WHERE = '조건' ;

 

 

4개의 ROW가 지워졌다고 말해주니 테이블을 확인해보자.

 

 

26 ~ 29 번까지의 행이 지워진 것을 확인할 수 있다. 

 

사실 실제로 웹사이트에서는 DELETE보다 UPDATE로 데이터의 사용 유/무를 판단한다. 데이터를 완전히 지우는게 아니라 활성화 -> 비활성화로 바꾸어 기존의 데이터는 보관하는 형태로 이용된다.

 

 

 

4. COMMIT / ROLLBACK 

 

데이터를 넣었는데.. 잘못 넣어서 다시 원상태로 돌리고 싶은 경우가 있을것이다. 마치 Ctrl + Z 를 눌러서 결과를 되돌리는 것과 같이, SQL에서는 COMMIT과 ROLLBACK를 이용하여 작업의 수행 여부를 결정할 수있다.

 

트랜잭션 명령어	기능
COMMIT	- 보류중인 모든 데이터 변경사항을 영구적으로 적용. 현재 트랜잭션 종료
ROLLBACK	- 보류중인 모든 데이터 변경사항을 폐기. 현재 트랜잭션 종료 - 직전 커밋 직후의 단계로 회귀(되돌아가기) - 전체 트랜잭션을 롤백

 

[DML 조작어]

 

데이터 조작어인 DML은 대표적으로 테이블 안에서 데이터의 조회, 삽입, 수정, 삭제를 담당하는 SELECT, INSERT, UPDATE, DELDET가 있다. COMMIT과 ROLLBACK은 이 DML 조작어의 수행 여부를 결정하는 명령어이다. "트랜잭션 제어 명령어"라고도 한다.

 

 

[트랜잭션]

 

COMMIT와 ROLLBACK를 설명하기 위해서는 트랜잭션의 개념을 꼭 알고 가야한다. 트랙잭션에 대해서 이것저것 알아보자

 

1. 트랜잰션이란 무엇인가?

 

" 데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 모두 수행되어야 할 일련의 연산들을 의미한다" 라고 인터넷을 검색해보면 나온다.

 

내가 이해한것은  그냥 " 여러가지 작업(연산)의 집합(모음)" 이라고 생각하면 될거같다. 모든 일은 작업의 순서가 있다. 여러 작업이 모여서 하나의 결과물이 나오는것이다. 나 같은 경우는 공장 아르바이트를 했을 때, 내가 나사 스크류 작업을 하면 옆에 계시는 분은 조립을 하고, 마지막에 포장 그리고 검수 이러한 일련의 작업 과정들이 모여 하나의 제품을 완성시켰다. 트랜잭션도 똑같다.

 

 < 스크류 -> 조립 -> 포장 -> 검수 -> 운반 > 

 

이 작업이 SQL에서는 하나의 작업 수행 단위인 트랜잭션이라고 불리우는 것이고, 조립 포장 같은 일?이라고 해야하나 이러한 일들은 INSERT, UPDATE 와 같은 DML 조작어가 되는것이다. SQL문의 덩어리하고 표현해도 될것이다.

 

     < INSERT -> UPDATE -> INSERT -> DELETE  > 

 

2. 트랜잭션의 특징

 

트랜잭션은 두가지의 특징이 있다. 완벽한 수행이냐 아니냐로 접근해야하는... 까다로운 특징이랄까? 근데 데이터의 정합성과 안정성을 생각하면 특징이 이해가 간다.

 

 두가지 특징 

 

① 어떠한 상황이든 모든 SQL문을 완전히 실행시키는 것

 

② 만약 모든 SQL문이 실행될수 없는 상황이라면, 첫 SQL문이 실행되기전 즉, 아무것도 실행되지 않은 처음상태로 유지되어야 하는 것

 

"모아니면 도!  할거면 다하고, 아니면 하지말어" 

 

이렇게 말하고 있는것 같다. 우리가 처음 데이터베이스를 열면 그때부터 트랜잭션이라는 놈이 스위치가 켜진다. 그리고 SQL문을 실행할 때 마다 트랜잭션 안에 쌓이게 되고, 작업이 아무 에러없이 잘 되는것을 확인하면 COMMIT을 통해 트랜잭션을 종료시킨다. 만일 에러가 났거나 먼가 이상하다? 그러면 ROLLBACK시켜 다시 원점으로 돌아가는 것이다. 좀 번거로운 작업이라고 생각할 수 도 있지만 데이터의 안전성 , 실수를 줄이기 위함이라고 생각하자.

 

---

 

2. 트랜잭션의 성질

 

 

① 원자성(Atomicity) : 트랜잭션이 모든 작업을 성공적으로 완료하거나 전혀 수행하지 않는 두 가지 결과만을 가진다.

 

② 일관성(Consistency) : 트랜잭션이 데이터베이스의 일관된 상태를 유지해야 함을 의미한다. 즉, 업데이트가 도중에 중단되거나 실패하더라도 업데이트 된 데이터베이스로 트랜잭션이 실행되는 것이 아니라, 업데이트 전 데이터베이스로 진행이 되어 일관성을 유지한다. (수행되나 안되나 같은 형태) 

 

③ 격리성(Isolation) : 여러 트랜잭션이 동시에 실행될 때 각 트랜잭션이 다른 트랜잭션의 작업에 영향을 미치지 않고 독립적으로 수행되어야 함을 의미한다.

 

④ 지속성(Durability) : 트랜잭션이 성공적으로 완료된 후에도 해당 작업의 결과가 영구적으로 보존되어야 함을 의미한다.

 

SQL 트랜잭션은 데이터베이스 시스템에서 데이터의 정합성과 안전성을 보장하기 위해 중요한 개념이다.  그럼 이 성질들을 인터넷에서 가져온 정의로만 끝내지 말고 예시를 들어 설명해 보자.

 

 

<사이트에 회원가입을 해보자. 데이터베이스 안에는 사용자의 정보를 저장하는 Member 테이블이 존재한다.>

 

 

첫째, 회원가입이 정상적으로 되어 가입처리가 되느냐,  아니면 어떤 에러가 생겨 가입처리가 안되거나 이 둘 중에 하나는 반드시 수행되어야 하는 것. 이게 원자성이다.

 

둘째,  회원가입이 되거나 혹은 되지 않더라도 Member 테이블 자체에는 아무련 변화가 없는 상태여야한다 이게 일관성이다.(데이터를 유지한다는 말, 데이터 베이스의 고유속성, 형태는 변하지 않음)

 

셋째, 회원가입을 하려는 사람이 여러명이 있을것이다. 사용자 각각 개별적인 트랜잭션의 사용하기 때문에, 다른 트랜잭션에 영향을 주지 않는다. 격리성을 말함.(다른 트랜잭션의 연산에 끼어들지 못함)

 

넷째, 회원가입이 정상적으로 수행이 되면 Member 테이블에 저장이 되어 영구히 보존하는 것이다. 지속성

 

---

 

[DBeaver 셋팅]

 

지금 내가 쓰고 있는 DBeaver 툴은 COMMIT를 자동으로 수행한다. 이게 처음에 설정에서 Auto - commit이라는 옵션이 체크되어있기 때문인데.. 안전성을 위해서라도 이 기능은 끄는게 맞는거 같다. (사실 커밋과 롤백의 기능을 보여주기 위해 찾아보다가 알게 된 내용)

 

 

 

① 윈도우 -> 설정에 들어간다.

 

 

② 연결탭에서 -> 연결유형 클릭 후 Auto-commit by default 옵셥의 체크를 해제한다.

 

 

---

 

그러면 ICECREAM 테이블에 아이스크림 몇개를 더 추가해보자.

 

 

이제 COMMIT과 ROLLBACK을 해볼건데 그림과 글로 설명하기가 좀 애매하다. 결과를 보여주고 싶은데 먼가 심심하다.. 양해를 구하고, 커밋과 롤백을 실행시켜보자. 

 

 

롤백을 시켜주면 다시 INSERT를 하기 전 상태로 돌아온것을 확인할 수있다.

 

 

그림으로 확인할 수 있는 방법은  시퀀스값이다. COMMIT이나 ROLLBACK을 하더라도 INSERT할 때 썼던 시퀀스값은 올라간채 유지된다. 시퀀스 값은 커밋이나 롤백으로 복구가 안되니, 이점 유의하고 사용하면된다.

 

분명 커밋과 롤백은 안정장치이다. 데이터를 넣고나서 한번 더 확인해보고 올바른 데이터가 들어갔는지 체크 후 작업을 수행하자.

---

 

 

저번 SELECT부터 이번에 INSERT, UPDATE, DELETE까지 SQL 쿼리문에서 가장 기본이 되는 명령문들을 정리해 보았다. 서브쿼리, PL/SQL 로 넘어가게 되면 많이 복잡해 지는데 천천히 빠르게 나아가자.. ㅋㅋ 

 

이제 기본글은 "오라클 내장함수", "서브쿼리", "JOIN" 정도가 남았다. 더불어 기본 쿼리문과 트랜잭션, 인덱스의 간략한 개념을 아는것으로 SQL의 첫 발걸음을 내딛은거라고 생각한다.  앞으로도 으쌰!!

 

 

 

[도움받은 사이트]

[오라클/SQL] COMMIT, ROLLBACK : 커밋(작업 확정), 롤백(작업 취소) - 트랜잭션 제어 명령어