0822. 트랜잭션, 데이터정의어(DDL)

DDL, DCL 파트

 

왜 테이블은 한 군데에 같이 두지 않고 분리해 둘까?

코드 중복 방지 때문이다.

동일한 코드를 여러 곳에서 중복 사용하면 나중에 수정할 때도 많은 부분을 봐야 한다.

사람인 개발자가 하는 일이라 완벽하게 다 수정하지 못 하는 실수를 할 수도 있다.

삭제도 마찬가지이다. 여기저기서 파일을 덜어내려는 것은 찾기도 힘들기에 관리가 어렵다.

데이터 중복 발생도 이와 같은 이유이며 그러한 경우 RDBMS가 어떤 작업을 하는지 알아보자.

---

 

📍트랜잭션

 

 

 

계좌이체 예시

 

만약 이체 과정(특히 타행이체)에서 네트워크 문제로 돈이 공중분해된다면 좋지 않다.

이를 방지하기 위해 나온 게 트랜잭션(transaction)이다.

 

더이상 분할되지 않는 최소 수행단위(논리적 실행단위)

*SQL문이 여러 줄이지만 실제로 실행은 하나의 작동이므로 논리적 실행단위 

=>ALL OR NOTHING

하나 이상의 데이터 조작 명령어(DML)로 구성된다.

 

*트랜잭션 명령어 : TCL(Transaction Control Language)

 

로그인 ~ 로그아웃 이 모든 과정의 시점 => "세션"

로그인 시 생성되는 작업공간(workspace)에 세션과 관련된 메모리가 담긴다.

작업공간은 세션마다 하나씩 발생한다.

이러한 작업공간에서 로그인과 함께 트랜잭션이 실행

=> 세션의 실행 = 트랜잭션의 실행

 

트랜잭션의 SQL문을 반영하는 행위

새로운 트랜잭션이 시작될 때 기존의 트랜잭션은 종료된다.

 

하드디스크(HDD)에는 "작업공간에서 실제 수행했던 내용"을 반영한다. => 커밋

한 번 커밋될 경우 그 이전으로 돌아가지 못 한다.

 

트랜잭션이 시작되는 시점 이전으로 돌리는 것 : ROLLBACK ( 가장 최근에 시작한 트랜잭션 시점으로 호출)

롤백도 가장 최근에 커밋이 된 시점까지밖에 못 간다.

 

DDL을 실행(테이블 생성,수정,삭제 등) 시 자동으로 커밋된다.

원래 실행되자마자 바로 커밋되므로 되돌릴 수 없다.

 

---

디비버에서 트랜잭션 모드를 Auto에서 Manual로 바꾸자.

이 커밋모드는 SQL 파일마다 설정해야 한다.

새 SQL 파일을 열면 다시 Auto로 디폴트가 된다.

 

 

 

*SELECT 는 읽기 작업이므로 트랜잭션에 큰 영향을 미치지 않는다.

INSERT, UPDATE, DELETE 이 3가지만 트랜잭션 관리에 영향을 미친다.

 

 

--트랜잭션 제어 명령어 TCL
INSERT INTO DEPT_TCL VALUES(50, 'DATABASE', 'SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';

SELECT * FROM DEPT_TCL;

 

AUTO가 아니므로 커밋할 내용 숫자가 쌓이고 있다.

 

--트랜잭션 제어 명령어 TCL
INSERT INTO DEPT_TCL VALUES(50, 'DATABASE', 'SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';

ROLLBACK;

SELECT * FROM DEPT_TCL;

ROLLBACK 성공 

좌(수정된 상태) 우(원본)

 

트랜잭션을 영원히 반영하려면 COMMIT

INSERT INTO DEPT_TCL VALUES(50, 'DATABASE', 'SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';

COMMIT;

SELECT * FROM DEPT_TCL;

ROLLBACK 해도 커밋된 상태라 안 돌아감

리턴값(VALUE) : 이 명령에 대해 영향받은 행의 개수

 

4

다시 롤백하니 나온다.

 

 

❗ 쓰면 바로 커밋되는 명령어 3가지

CREATE, DROP, ALTER => DDL

DML 만 롤백 가능(INSERT, UPDATE, DELETE)

---

 🖤 세션이 여러 개 열릴 경우

EX)

DB는 하나고 각 WORKSPACE가 열림.

토드라는 세션에서 작업한 INSERT, UPDATE는 HDD에 커밋을 해야

디비버라는 다른 세션에서 SELECT(읽기) 실행 시 나타난다.

 

 

---

📍데이터 정의어 DDL

DATA DEFINITION LANGUAGE

객체(user, table, view, 권한 등)의 생성(create),변경(update),삭제(delete) 관련 기능 

 

*실행 즉시 커밋되므로 rollback 으로 되돌릴 수 없다.

 

---

CREATE 객체유형 소유계정.객체이름 ( ,콤마로 구분 );

- *Schema의 이름이 계정 이름

- "소유계정." 부분은 본인 계정의 본인 schema 생성 시 생략가능하다.

- 다른 계정에서 schema생성은 권한이 있어야 가능하다.

- 테이블명은 대소문자 구분하지 않는다.

열N이름 (한칸 띄우고) 열N 자료형 : 필수요소 

 

동일스키마에서는 테이블명 중복이 안 된다.

 

 

 

 

number(4)는 () 앞에 "number" 자체 생략 가능

CHAR(길이) 는 데이터와 상관없이 무조건 길이가 고정된다. 모자라도 넘쳐도 고정된 byte로 잡힌다.

 - 주민등록번호, 우편번호, 전화번호

 

VARCHAR(길이)는 모자란 경우 예로, 10byte가 최대일 때 데이터가 3byte라면 3byte로 반영. (공간효율)

- 그렇지 않는 경우 가변적일 때

VARCHAR 뒤에 숫자는 버전수이다. (그냥 VARCHAR는 오라클 내부사용이라, 2를 붙여서 쓰도록)

 

 

 

데이터타입 명칭(NUMBER, VARCHAR, DATE 등)은 DBMS 제품마다 다르다.

 

---

⭐서브쿼리에 WHERE 절을 사용하면 일부 데이터만 불러올 수 있다.

 

CREATE TABLE EMP_DDL_30
AS SELECT * FROM EMP
	WHERE DEPTNO = 30;
	
SELECT * FROM EMP_DDL_30;

+) AS SELECT 뒤에 * 도 모양을 여러 개 정할 수 있다. (일부에서만 읽어서 불러오기)

---

+) HR 테이블 권한 부여사용

--HR 테이블 사용
-- 그냥 이렇게 SELECT * FROM EMPLOYEES; 안 나옴
SELECT * FROM HR.EMPLOYEES; -- 권한을 주자 "HR."

 

 

 

 

 

1. 서브쿼리 확인

--테이블명 MEMBER (USERID, PASSWORD, EMAIL)
-- HR.EMAIL -> USERID  SKING (as)
-- PASSWORD 컬럼은 USERID와 동일한 값  SKING (as)
-- EMAIL 컬럼 기존 EMAIL  @galapagos.org   sking@galapagos.org (소문자, 문자열결합)
-- 문자열 결합 : ||

SELECT EMAIL userid, EMAIL password, LOWER(EMAIL) || '@galapagos.org' email
FROM HR.EMPLOYEES;

 

2. 테이블명 적용하고 출력

--테이블명 MEMBER (USERID, PASSWORD, EMAIL)
-- HR.EMAIL -> USERID  SKING (as)
-- PASSWORD 컬럼은 USERID와 동일한 값  SKING (as)
-- EMAIL 컬럼 기존 EMAIL  @galapagos.org   sking@galapagos.org (소문자, 문자열결합)
-- 문자열 결합 : ||
CREATE TABLE MEMBER
AS
 SELECT EMAIL userid, EMAIL password, LOWER(EMAIL) || '@galapagos.org' email
 FROM HR.EMPLOYEES;

SELECT * FROM MEMBER;

 

3. UPDATE문으로 소문자 변경 

--UPDATE 문 사용하여 USERID와 PASSWORD를 소문자로 변경
UPDATE "MEMBER"
SET PASSWORD = LOWER(PASSWORD),
 USERID = LOWER(USERID);

---

• 테이블을 변경(수정)하는 ALTER

 

--ALTER 테이블--
CREATE TABLE EMP_ALTER
AS SELECT * FROM EMP;

컬럼을 추가하겠다 => ADD 그 뒤로 컬럼명, 타입이 온다.

처음은 모두 NULL로 초기화된다.

• 열 이름을 변경하기 RENAME

--ALTER 테이블--
CREATE TABLE EMP_ALTER
AS SELECT * FROM EMP;

ALTER TABLE EMP_ALTER
ADD HP VARCHAR2(20);

-- ALTER로 열 이름 변경
ALTER TABLE EMP_ALTER
RENAME COLUMN HP TO TEL;

SELECT * FROM EMP_ALTER;

 

• 열의 자료형을 변경하는 MODIFY

기존 데이터가 없으면 항상 가능하다.

 

데이터가 존재하면, 타입캐스팅이기 때문에 타입이 같고 늘어나는(정보손실X) 방향으로만 가능하다.

VARCHAR를 조정할 때 주로 많이 하는 작업이다.

-- MODIFY 열 길이 변경(자료형 변경)--
ALTER TABLE EMP_ALTER
MODIFY EMPNO NUMBER(5);

SELECT * FROM EMP_ALTER;

 

 

• 특정 열 삭제 DROP ⭐

-- DROP 으로 특정 열 삭제
ALTER TABLE EMP_ALTER
DROP COLUMN TEL;

SELECT * FROM EMP_ALTER;

 

---

• 테이블 이름 변경 RENAME

---테이블 이름 변경하기---
--RENAME
RENAME EMP_ALTER TO EMP_RENAME;
SELECT * FROM EMP_RENAME;

 

• 테이블 안 데이터 삭제 TRUNCATE

+) DML의 DELETE FROM도 되지만 바로 COMMIT이라 조심

속도는 TRUNCATE가 더 빠르다.

--TRUNCATE 테이블 안에 데이터 삭제
TRUNCATE TABLE EMP_RENAME;

 

• 테이블 삭제 DROP

DDL이므로 한 번 삭제되면 복원 불가

--DROP 테이블 자체를 삭제
DROP TABLE EMP_RENAME;

---

✨CREATE 할 때 괄호 생략말고 써야 하는 이유 

제약조건을 지키자..

SELECT * FROM ~ ;

INSERT INTO EMP()
VALUES( , , , )