[6/18] TCL과 TRANSATION

5일차.sql

0.01MB

--제약조건을 확인할 수 있는 명령
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME ='USER2';

[TCL]

• *트랜잭션 제어어
• SQL명령이 저장된 트렌젝션을 실제 데이터베이스에 적용하거나 적용하지 않고 제거하는 명령
• 쉽게말하면, 잘못된 명령이나 데이터가 테이블에 들어가거나 삭제되는것을 방지하기 위해 있는 것이 트랜잭션이다.

*트랜잭션 : 현재 세션에서 작성된 SQL명령을 처리하기 위한 작업단위 - SQL명령의 집합

*세션 : DBMS에 접속하여 SQL명령을 전달하여 실행할 수 있는 사용자의 작업환경(즉, 사용자마다 작업환경을 바꿀 수 있단 말임)

TCL의 종류
COMMIT	ROLLBACK	SAVEPOINT
올바르게 반영된 데이터를 DB에 반영시키는것	트랜잭션 시작 이전의 상태로 되돌리는것	저장점을 정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라, 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

 

• 트랜잭션은 현재 세션에서 SQL명령(DML, DDL, DCL)을 작성하여 전달할 경우 자동으로 생성
• 현재 세션에서 작성되어 전달된 SQL명령은 DBMS에 의해 실행되는 것이 아니라, 세션의 트랜젝션에 저장된다.
• 트랜잭션에 저장된 SQL명령을 DBMS에 전달하여 실행하는 방법 - SQL명령 전달 후, 트랜잭션 자동소멸된다

 

1. 현재 세션이 정상적으로 종료된 경우, 트랜젝션의 SQL명령이 자동으로 DBMS에 전달되어 실행된다.
2. DDL명령 또는 DCL명령은 트랜젝션에 저장되면, 자동으로 DBMS에 전달되어 실행된다.(DML만 계속 쌓인다)
3. DML은 COMMIT을 이용하여 트랜젝션에 저장된 SQL명령을 DBMS에 전달하여 실행한다. (세션이 정상 종료된 경우와 DDL과 DCL AUTO COMMIT이다.)

• 트랜젝션을 제거하는 방법 : 트랜젝션에 저장된 SQL명령이 전달되지 않아 실행되지 않는다.

1. 현재 세션이 비정상적으로 접속 종료된 경우 트랜젝션 자동제거
2. ROLLBACK명령을 이용하여 트랜젝션 제거 -> SQL명령이 제거가 된다.

SELECT * FROM DEPT; -- 얘는 트랜젝션이 저장되지 않음
--DBMS에 SELECCT명령은 바로 전달되어 검색결과를 제공받을 수 있다.
SELECT * FROM DEPT;
 
--DEPT 테이블에서 부서번호가 50인 부서정보를 삭제
DELETE FROM DEPT WHERE DEPTNO = 50; --아직 삭제된게 아님, SELECT명령의 검색결과를 제공받아 트랜젝션의 SQL명령을 실행하여 결과를 제공 
--현재 세션에서만 삭제된 것처럼 검색되며, 실제 테이블에서는 삭제되지 않고 존재한다.
--DML명령은 DBMS에 전달되어 실행되지 않고 트랜젝션에 저장된다.
SELECT * FROM DEPT;
--트랜젝션을 제거 : 트랜젝션에 저장된 SQL명령 삭제
ROLLBACK;
SELECT * FROM DEPT; -- 다시 50번 부서 살아남
 
COMMIT; -- 트랜젝션에 저장된 삭제명령을 DBMS에 전달하여 사용 -> 트랜잭션 자동 소멸됨
SELECT * FROM DEPT; -- 진짜 테이블에도 50번부서가 삭제됨

 

• 데이터의 일관성을 제공하기 위해 트렌젝션을 이용
• 데이터 일관성 : 다중사용자에게 동일한 검색결과를 제공하는 기능
• 현재 세션에서 작업중인 결과값을 다른 사용자에게 제공되지 않도록 트렌젝션 이용

DELETE FROM BONUS WHERE ENAME = 'KIM';
SELECT * FROM BONUS;
--다른 세션에서는 BONUS테이블에서 사원이름이 KIM인 사원을 검색된다.
--현재 세션의 작업완료 전까지 다른 세션에게는 작업 전의 결과값을 제공한다.

• 현재 세션에서 COMMIT 명령을 실행하면 작업이 완료되어 다른 세션에도 적용된 결과값이 나온다.

다른세션 ) DELETE 명령 실행후 CMD창에서 보여지는 결과값

SQL DEVELOPER에서 보여지는 결과값

[LOCK기능]

• 데이터 LOCK기능을 제공하기 위해 트렌젝션을 이용
• ==> DBMS는 다중사용자 환경으로 같은 테이블의 행을 세션마다 조작이 가능하다
• 현재 세션에서 작업중인 테이블의 행을 다른 세션에서 작업하지 못하도록 트렌젝션이 데이터 잠금기능을 제공한다.
• 다중사용자가 동일한 테이블의 행을 조작할 수도 있으므로 잠금기능을 제공한다.
• 따라서 SYCRONIZED기능을 제공하는 벡터를 쓰기보다는 ARRAYLIST를 많이 사용한다. (굳이 사용X)

SELECT * FROM BONUS;
UPDATE BONUS SET SAL = 2000 WHERE ENAME = 'ALLEN';

 

[SAVEPOINT]

• SAVE POINT : 트렌젝션에 라벨을 붙이는 명령
• ROLLBACK처리 : 잘못된 SQL명령에 대한 취소 작업
• 트렌젝션에 저장된 모든 SQL명령 제거 작업초기화
• 라벨을 이용한 롤백 처리
• ROLLBACK TO 라벨명

--BONUS 테이블에서 사원이름이 ALLEN인 사원정보 삭제 - 라벨 설정
DELETE FROM BONUS WHERE ENAME='ALLEN';
SAVEPOINT DEL_ALLEN;
SELECT * FROM BONUS;

--BONUS 테이블에서 사원이름이 MARTIN인 사원정보 삭제 - 라벨 설정
DELETE FROM BONUS WHERE ENAME='MARTIN';
SAVEPOINT DEL_MARTIN;
SELECT * FROM BONUS;

--BONUS 테이블에 저장된 모든 사원정보 삭제
DELETE FROM BONUS;
SELECT * FROM BONUS;

--라벨을 이용한 롤백 처리
--형식)ROLLBACK TO 라벨명;
ROLLBACK TO DEL_MARTIN;
SELECT * FROM BONUS;

--사원이름이 MARTIN인 사원정보 삭제 명령 취소
ROLLBACK TO DEL_ALLEN;
SELECT * FROM BONUS;

--사원이름이 ALLEN인 사원정보 삭제 명령 취소
ROLLBACK;
SELECT * FROM BONUS;

 

---

[DDL(DATA DEFINITION)]

• 데이터 정의어.
• 데이터베이스 객체(테이블, 뷰, 시퀀스, 인덱스, 동의어, 사용자 등)를 관리하기 위한 명령
  

1. 테이블(TABLE) : 데이터베이스에서 데이터를 저장하기 위한 가장 기본적인 객체

• 테이블 생성 : 속성의 집합
• 형식

CREATE TABLE 테이블명(컬럼명 자료형 (크기)) [DEFAULT 기본값] (컬럼명[컬럼제약조건], 컬럼명 자료형(크기 DEFAULT값 컬럼제약조건 테이블 제약조건)

 

[식별자] 작성 규칙 : 테이블명, 컬럼명, 라벨명 등 

1. 영문자로 시작되며 1~30 범위의 문자들로 작성

2. A~Z, 0~9, _, $, # 문자를 조합하여 작성 - 대소문자 미구분 (단, 단어와 단어는 _를 사용하여 구분)

3. 영문자와 다른 문자 사용 가능 - 비권장

4. 키워드를 식별자로 선언할 경우 에러발생

5. 키워드와 이미 사용중인 식별자를 사용할 경우 에러가 발생

 

[자료형] : 컬럼에 저장 가능한 값을 표현하기 위한 키워드

1. 숫자형 : NUMBER(전체자릿수, 소숫점자릿수)

2. 문자형 : CHAR(크기) - 크기 : 1-2000(BYTE) >> 고정형 길이
               VARCHAR2(크기) - 크기 : 1- 4000 (BYTE) >> 가변형 길이
               LONG - 크기를 따로 주지 않아도 됨. 최대 2GBYTE를 줄 수 있음 >> 가변형(테이블에서 하나에 컬럼에만                   부여가능하며, 정렬이 불가능하다.)
                CLOB - 최대 4GBYTE저장가능 >> 가변형(문자데이터) >> 텍스트 파일을 저장할때 주로 사용
                BLOB - 최대 4GBYTE저장가능 >> 가변형(원시데이터) >> 일반파일(이진 파일 : 이미지, 동영상파일)을 저                  장 할 때 주로 사용

3. 날짜형 :  DATE - 날짜와 시간정보를 저장할 수 있음
                TIMESTAMP - 초단위 시간(원하는 패턴으로 변경가능) -> SIMPLEDATE를 사용하면 됨

 

[딕셔너리] : 시스템 정보를 확인할 수 있는 가상의 테이블(뷰)

-- USER_DICTIONARY(관리자가 아닐 경우) , DBA_DICTIONARY(관리자용) , ALL_DICTIONARY

1) USER_DICTIONARY : 현재 접속 사용자의 객체정보를 제공하는 딕셔너리

2) 현재 접속 사용자의 테이블 정보를 확인

SELECT OBJECT_NAME FROM USER_OBJECT WHERE OBJECT_TYPE = 'TABLE';

*USER_TABLES : 현재 접속 사용자의 테이블 정보를 제공하는 딕셔너리

--딕셔너리(DICTIONARY) : 시스템 정보를 확인할 수 있는 가상의 테이블(뷰)
--USER_DICTIONARY(일반사용자),DBA_DICTIONARY(관리자),ALL_DICTIONARY(모든 사용자)

--USER_OBJECTS : 현재 접속 사용자의 객체 정보를 제공하는 딕셔너리
--현재 접속 사용자의 테이블 정보 확인
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE';

--USER_TABLES : 현재 접속 사용자의 테이블 정보를 제공하는 딕셔너리
SELECT TABLE_NAME FROM USER_TABLES;
--USER_TABLES 딕셔너리에 대한 동의어(SYNONYM)로 TABS 제공
SELECT TABLE_NAME FROM TABS;

SELECT * FROM TAB

--컬럼에 제약조건이 없는 경우 어떤한 값이든 저장가능
--데이터 무결성 : 테이블에 잘못된 행(값)이 저장되지 않도록 설정하기 위한 기능 - 제약조건
INSERT INTO SALESMAN VALUES(2000,'임꺽정','10/10/10');

• 컬럼에 제약조건이 없는 경우 어떤한 값이든 저장가능
• 데이터 무결성 : 테이블에 잘못된 행(값)이 저장되지 않도록 설정하기 위한 기능 - 제약조건
• 행삽입시 컬럼 생략가능 : 생략된 컬럼에는 컬럼 기본값 자동저장됨
• 컬럼기본값을 설정하지 않으면 자동으로 NULL을 컬럼기본값으로 설정한다.

CREATE TABLE SALESMAN(NO NUMBER(4), NAME VARCHAR2(20), STARTDATE DATE);
SELECT TABLE_NAME FROM USER_TABLES;
--테이블 목록 및 속성 확인
SELECT TABLE_NAME FROM TABS;
SELECT * FROM TAB;

--컬럼기본값은 선언시 DEFAULT키워드를 사용하여 설정한다.
CREATE TABLE MANAGER(NO NUMBER(4), NAME VARCHAR2(20), STARTDATE DATE DEFAULT SYSDATE, PAY NUMBER DEFAULT 1000);

--현재 만들어져 있는 테이블 목록 및 속성 확인
SELECT TABLE_NAME FROM TABS;

• USER_TAB COLUMS : 테이블의 컬럼정보를 제공하는 딕셔너리
• 테이블의 컬럼 기본값 확인

SELECT COLUMS_NAME, DATA_DEFAULT FROM USER_TAB_COLUMS WHERE TABLE_NAME = 'MANAGER';

--값을 넣지 않은 컬럼은 DEFAULT값으로 채워지게 된다.
INSERT INTO MANAGER(NO, NAME) VALUES(1000,'홍길동');

STARTDATE와 PAY는 기본값으로 설정된다.

[제약조건] : 잘못된 값이 컬럼에 저장되는 것을 방지하기 위한 기능 : 데이터 무결성

1) 컬럼 수준 제약조건 : 속성 선언시 컬럼에 제약조건을 설정

2) 테이블 수준 제약조건 : 테이블 선언시 테이블의 특정 컬럼에 제약조건을 설정

 

[NOT NULL 제약조건] : 컬럼에 NULL저장을 허용하지 않는 제약조건

1) 컬럼 수준의 제약조건으로만 설정가능

2) NOT NULL설정되지 않은 컬럼에는 기본적으로 NULL저장 허용

3) NOT NULL제약조건이 설정된 컬럼에 NULL을 전달할 경우 에러발생

--이게 컬럼수준의 제약조건
CREATE TABLE DEPT2 (DEPTNO NUMBER(2) NOT NULL, DNAME VARCHAR2(12) NOT NULL, LOC VARCHAR2(11) NOT NULL);

--DEPT1 테이블 생성 : 부서번호 (숫자형), 부서이름(문자형), 부서위치(문자형)
CREATE TABLE DEPT1 (DEPTNO NUMBER(2), DNAME VARCHAR2(12), LOC VARCHAR2(11));
DESC DEPT1;

--DEP1T 테이블에 행 삽입
INSERT INTO DEPT1 VALUES(10, NULL, NULL);
INSERT INTO DEPT1 (DEPTNO) VALUES(20);
SELECT * FROM DEPT1;
COMMIT;

--DEPT2 테이블 생성 : 부서번호 (숫자형), 부서이름(문자형), 부서위치(문자형)
CREATE TABLE DEPT2 (DEPTNO NUMBER(2) NOT NULL, DNAME VARCHAR2(12) NOT NULL, LOC VARCHAR2(11) NOT NULL);
DESC DEPT2;

--DEPT2 테이블에 행 삽입
INSERT INTO DEPT2 VALUES(10, NULL, NULL); --NOT NULL제약조건때문에 이렇게 삽입 X
INSERT INTO DEPT2 (DEPTNO) VALUES(20); -- 얘도 마찬가지(생략된 컬럼의 기본값이 NULL인 경우 에러발생)
SELECT * FROM DEPT2;
COMMIT;

 

[USER_CONSTRAINTS] : 테이블의 칼럼에 설정된 제약조건을 제공하는 딕셔너리

--CONSTRAINTS_NAME : 제약조건명 - 제약조건명을 설정하지 않으면 SYS_XXXX형식으로 자동설정

--CONSTRAINTS_TYPE : 제약조건 타입 - C(CHECK), P(PRIMARY), U(UNIQUE), R(REFERRENCE)

--칼럼에 제약조건을 설정할 경우 제약조건명을 저장하는 것을 권장(명확하게 알기 위해)

--형식) 컬럼명 자료형(크기) CONSTRAINT 제약조건명 제약조건 - 컬럼수준의 제약조건

--DEPT3 테이블 생성 - 부서번호(숫자형-NOT NULL),부서이름(문자형-NOT NULL),부서위치(문자형-NOT NULL)
CREATE TABLE DEPT3(DEPTNO NUMBER(2) CONSTRAINT DEPT3_DEPTNO_NN NOT NULL
    ,DNAME VARCHAR2(12) CONSTRAINT DEPT3_DNAME_NN NOT NULL
    ,LOC VARCHAR2(11) CONSTRAINT DEPT3_LOC_NN NOT NULL);

 

[CHECK] :  저장조건을 제공하여 조건에 맞는 값만 저장되도록 설정하는 제약조건

1) 컬럼수준의 제약조건으로는 해당 컬럼에 대한 조건식만 설정이 가능하다.

--컬럼수준으로 CHECK 제약조건 설정
CREATE TABLE SAWON2(NO NUMBER(4), NAME VARCHAR2(20), PAY NUMBER CONSTRAINT SAWON2_PAY_CHECK CHECK(PAY >= 3000000));
--제약조건확인
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE, SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE TABLE_NAME='SAWON2';

--CHECK조건을 만족하지 못하므로 INSERT되지 못한다.

INSERT INTO SAWON2 VALUES(2000,'임꺽정', 50000)

 

2) 테이블 수준의 제약조건으로 설정

--모든 컬럼에 대한 조건식이 설정이 가능하다

--모든 컬럼 뒤에 , 찍고 CONSTRAINT __제약조건명__CHECK(제약조건 AND 제약조건...) 해주면 된다.

--테이블수준의 제약조건으로 설정
CREATE TABLE SAWON3 (NO NUMBER(4), NAME VARCHAR2(20), PAY NUMBER, CONSTRAINT SAWON3_PAY_CHECK CHECK(NO >= 1000 AND PAY >= 3000000));

 

 

[UNIQUE] : 중복된 컬럼값 저장을 방지하기 위한 제약조건

1) 컬럼수준의 제약조건

--테이블 다수의 컬럼에 UNIQUE 제약조건 설정이 가능하며 NULL 저장을 허용한다.

CREATE TABLE USER2 (ID VARCHAR2(20), 
NAME VARCHAR2(20) CONSTRAINT USER2_NAME_UK UNIQUE, 
PHONE VARCHAR2(15) CONSTRAINT USER2_PHONE_UK UNIQUE);

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME ='USER2';

INSERT INTO USER2 VALUES('ABC', '홍길동', '010-1234-5678');

--NAME컬럼이 기존행의 컬럼값과 중복값을 전달할 경우, UNIQUE제약조건에 의해 에러가 발생한다.
INSERT INTO USER2 VALUES('ABC', '홍길동', '010-1234-5688');

 

2)테이블 수준의 제약조건

CREATE TABLE USER3 (ID VARCHAR2(20), 
NAME VARCHAR2(20), 
PHONE VARCHAR2(15) , CONSTRAINT USER3_PHONE_UK UNIQUE(PHONE));

 

3) 테이블 수준의 제약조건 - 제약조건을 두개의 컬럼을 동시에 설정

-- 하나만 중복되도 상관없다.

CREATE TABLE USER3 (ID VARCHAR2(20), 
NAME VARCHAR2(20), 
PHONE VARCHAR2(15) , CONSTRAINT USER3_PHONE_UK UNIQUE(NAME, PHONE));

--NAME컬럼과 PHONE컬럼에 동일한 값이 전달될 경우, 유니크 제약조건에 의해 에러가 발생

 

[정리]

• unique와 pk제약조건은 중요한 제약조건이다.
• fk제약조건은 조인할때 필요한 제약조건이니 아는것이 좋다.