SQL TRANSACTION, DDL, 제약조건 개념정리 및 적용예시

#TRANSACTION

 

1작업이 시작. 2작업이 종료

-데이터베이스의 논리적인 단위.

-하나의 트랜잭션에는 하나 이상의 SQL문장이 포함되며, 분할 할 수 없는 최소의 단위이다. 전부 적용 OR 전부 취소.

즉, ALL OR NOTHING

-트랜잭션의 대상이 되는 SQL문은 DML문.

SELECT문장은 직접적인 트랜잭션의 대상이 아니지만, SELECT FOR UPDATE등 배타적인 LOCK을 요구하는 SELECT문장은 트랜잭션의 대상이 될 수 있다.

 

-TRANSACTION 시작 및 종료

시작:

DML 명령을 시작한 경우에는 TRANSACTION은 자동으로 시작됨.

종료(명시적/무시적):

사용자가 COMMIT OR ROLLBACK 명령을 명시적으로 실행한경우

DDL문장을 실행, DCL문장을 실행,

사용자가 SQL*PLUS OR ISQL*PLUS를 종료한 경우

하드웨어 고장 또는 시스템 오류시.

 

#COMMIT

모든 데이터 변경사항을 DB에 영구히 반영시키는 명령어.

변경 전의 데이터의 모두 잃게 된다.

모든 사용자들이 트랜잭션 종류 후의 결과를 확인 할 수 있다.

 

#ROLLBACK

모든 데이터의 변경사항을 취소하는 명령어.

변경전의 데이터가 복원된다.

모든 사용자들이 트랜잭션 종료 후의 결과를 확인 할 수 있다.

 

#LOCK경합

트랜잭션이 시작되고 COMMIT OR ROLLBACK 으로 끝나기 전까지 중간 작업이 끼어서 실행 할 수 없다.

한 드랜잭션이 끝나야 그 후 작업 실행 가능.

 

+스키마

다른 시스템(SYS)에서 SCOTT 내용 쳤을 때 에러 발생

SYS에서 SCOTT 불러오기

 

+EX.

1.

2.

3.

4.COMMIT;

5.

6.SYS에서도 COMMIT OR ROLLBACK 수행

 

+ROLLBACK은 코드를 직접 넣어줘야 하니 더 신경쓰기. COMMIT은 자동으로 된다.

 

#DDL

가장 중점이 테이블!

테이블의 모양을 정의하는 언어

테이블 만들 때 사용

(데이터, 테이블 수정하는 방법도 알아두기)

데이터베이스의 테이블의 구조를 생성, 수정, 삭제에 사용되는 SQL문장.

데이터베이스에 즉각 영향을 미치며 데이터베이스사전(DATA DICTIONARY)에 정보를 기록한다.

 

-테이블 생성(테이블 이름은 명령어 사용 X,한글사용 가능하지만 권장은 하지 않음. 에러가 자주 남.)

 

#스키마(SCHEMA)

다른 권한을 가지고 있는 스키마에 접근하기 위해서는 '스키마.객체명' 형식으로 접근해야 된다.

 

-데이터 타입 종류

-CHAR(고정 길이 문자열 데이터)-남/여 한글자만 입력할때 사용[최대 한글자, 최대 길이 2000바이트(1000자까지, 한글 1자 는 2바이트])

-VARCHAR2(가변 길이, 실제 들어간 글자 수 만큼만, [최대 4000바이트까지])

**프로젝트시 에러 CHAR OR VAR 구분해보기

-NUMBER(가변 길이, 정수는 1~38자리, 소수점 자리수는 -84~127)

-DATE(날짜 및 시간 데이터, 기본으로 RR/MM타입)

-ROWID(테이블 내의 행들을 유일하게 식별할 수 있는 숫자)

-BLOB OR CLOB

 

#DEFAULT옵션

-해당 테이블에 행을 입력할 때, 해당 컬럼에 값을 지정하지 않은 경우 자동으로 디폴트 값이 입력되어 NULL값이 저장되는 것을 방지할 수 있다.

 

DEFAULT 기본값이 SYSDATE로 설정된것.

 

 

+

 

-서브쿼리를 이용한 테이블 생성

:지정된 컬럼의 개수와 서브쿼리에서 리턴된 컬럼의 개수가 일치해야한다.

NOT NULL를 제외한 제약 조건은 생성된 테이블에 만들어지지 않으며 오직 컬럼의 데이터만 동일하게 생성된다.(EX.DEPT 중복불가라는 조건이 있어도 복사할때, 컬럼이름, 몇글자인지, NULL허용인지 아닌지만 복사된다. DEFAULT도 복사 안됨.)

원본 테이블의 인덱스 및 LONG 타입 복사 불가.

 

 

 

#제약조건

제약 조건이 어디 붙냐에 따라

COLUMN 옆에 붙으면 컬럼 레벨

테이블 끝날때 쯤 쓴다면 테이블레벨

결과는 똑같음.

제약 조건 수정할때, 단순한 방법은 테이블 날리고 다시 만들거나 (권장하지 않음), user_constraints의 data dictionary view을 조회하여 수정한다. 변경, 삭제도 가능.

사용 지침:

적절한 이름을 지정하여 제약조건을 사용.

+

*shema:사용자 이름

 

-부적절한 자료가 INSERT되는 것을 방지 하기 위해  CONSTRAINT을 사용한다.

-테이블 LEVEL OR 컬럼 LEVEL

 

-테이블 LEVEL 지정 방법:

테이블의 컬럼 정의와는 별개로 정의한다.

한 개 이상의 컬럼에 한 개의 제약 조건을 정의할 수 있다. 

NOT NULL 제약조건을 제외한 모든 제약조건 정의 가능하다

컬럼 LEVEL 지정 방법:

한 개의 컬럼에 한 개의 제약조건만 정의 가능하다. (못 적은 건 테이블 레벨에 적으면 됨)

모든 제약조건에 대해서 정의 가능하다. ( NOT NULL 제약조건 필수: 반드시 컬럼 레벨에서 지정하기. )

종류(약어기억하자)

#NOT NULL: NULL값이 포함 되지 않는다. (약어: NN)

#UNIQUE: 다 다른값, 유일 값을 가져야 한다. 중복 값 INSERT불가, 똑같은 값 다시 넣을 수 없다.NULL은 허용함.(약어:UK)

#PRIMARY KEY:  UNIQUE(중복불가),NULL허용안됨. 무조건 유일한 값을 넣어라!(약어:PK)

#CHECK:EX.성별 남, 여로 지정하고 남, 여 값 외에는 입력하지 못하게 함. (약어:CK)

#FOREIGN KEY: (약어:FK)

 

#PRIMARY KEY(PK)

테이블에 대한 기본 키를 생성합니다. 

하나의 기본 키만이 각 테이블에 대해 존재할 수 있다. (컬럼 하나만 기본키가 된다는 말이 아니다! 컬럼 여러개를 기본키 하나로 묶을 수 있다.)

PRIMARY KEY 제약 조건은 테이블에서 각행을 유일하게 식별하는 열 또는 열의 집합(복합컬럼)이다.(UNIQUE와 NOT NULL조건을 만족) 

이 제약 조건은 열 또는 열의 집합의 유일성을 요구하고 NULL값을 포함할 수 없음을 보증 한다.  UNIQUE INDEX가 자동 생성된다.

 

+

이름을 붙여줘야 나중에 편하게 찾아서 수정할 수 있다.

 

#UNIQUE(UK)

해당 컬럼에 중복된 값이 저장되지 않도록 제한한다.

한 개 이상의 컬럼( 복합컬럼)으로 구성가능하다.. 

NULL 값 저장 가능하다. 

테이블 LEVEL 및 컬럼 LEVEL 모두 지정 가능하다. 하지만 복합컬럼 지 정시에는 테이블 LEVEL만 가능하다. (UK로 걸어둔 컬럼이 여러개 있으면, 옆에 컬럼테이블 레벨에 적을 수 없고, 한번에 테이블 레벨에 적어줘야 함.)

해당 컬럼에 UNIQUE INDEX 가 자동 생성된다.

+

ERROR WHY?

EMAIL은 UNIQUE로 지정되어 있어 중복 값이 불가능 하다.

 

-복합컬럼 UNIQUE 제약조건

컬럼 레벨이 아닌 테이블 레벨로 적어야 한다.

 

 

-CHECK 제약 조건(CK)

해당 컬럼에 반드시 만족해야 될 조건을 지정하는 제약 조건이다.