[ORACLE] DCL - 데이터 제어어

SQL(Structured Query Language)의 DDL(Data Definition Language), DML(Data Manipulation Language), DCL(Data Control Language) 중 지난번 포스팅에서는 DDL에 대하여 알아봤었는데요.

이번 포스팅에서는 데이터 정의 언어인 DCL에 대하여 알아볼 예정입니다.

DCL은 데이터 제어 언어로 데이터, 트랙잭션을 저장 및 취소하는 COMMIT과 ROLLBACK 명령어가 있고 DB 권한을 부여 및 삭제하는 GRANT / REVOKE가 있습니다.

 

COMMIT / ROLLBACK : 데이터, 트랜잭션 저장 / 취소

GRANT / REVOKE : DB 권한 부여 / 삭제

 

1. 권한

1. 시스템 권한

- 객체 생성, 변경, 소멸 등에 관한 권한으로, SYS(SYSTEM)에게 부여함.

- 시스템 권한은 기능이 매우 강력하여, 대부분 관리자나 개발자만 부여함.

EX) 테이블 스페이스 생성, 임의의 테이블행 삭제 등을 할 수 있는 권한

 

2. 개체 권한

- 사용자가 특정 개체에 대해 특정 작업을 수행 가능하도록 일반 사용자에게 부여하는 권한

- 객체 내용 조작과 관련된 권한으로, 객체 소유자에게 부여받는 권한

EX) 특정 테이블의 행 삭제

 

3. 역할(ROLE)

- 시스템 권한만 해도 120가지 이상

- 많은 권한을 사용자마다 일일이 부여하기가 힘들어서 권한들을 미리 정의해 놓은 집합(ROLE)을 만들었다.

- 사용자에게 특정 집합을 권한 대신 부여

 

 

설치와 동시에 기본적으로 생성되어 있는 ROLE

- CONNECT : 접속 권한을 가진 ROLE

- RESOURCE : 객체의 생성, 변경, 삭제 등의 기본 시스템 권한을 가진 ROLE

- DBA : DB 관리에 필요한 권한을 가진 ROLE

- SYSDBA : DB 시작과 종료 및 관리 권한을 가진 ROLE

- SYSOPER : SYSDBA + DB 생성 권한을 가진 ROLE

 

​

​

2. 제약조건

제약조건은 테이블을 만들 때 조건을 정해주는 것을 말합니다.

제약조건도 이름으로 관리를 하게 됩니다.

제약조건의 이름은 문자로 시작해야 하며 길이는 30자까지 가능합니다.

이름을 따로 지정하지 않게 되면 자동 생성됩니다.

테이블을 생성한 후 테이블의 생성과 동시에 생성됩니다.

 

데이터 무결성 - 데이터가 손상되거나 원래의 의미를 잃지 않고 유지되는 상태.

 

*무결성 제약조건 : 입력되는 자료들의 규칙을 정해줌

제약조건	설명	설정 레벨
NOT NULL	해당 걸럼에 NULL을 입력할 수 없도록	컬럼
UNIQUE	해당 컬럼 또는 컬럼 조합 값이 유일하도록	컬럼, 테이블
PRIMARY KEY	각 행을 유일하게 식별할 수 있도록	컬럼, 테이블
CHECK	해당 컬럼에 특정 조건을 향상 만족시키도록	컬럼, 테이블

제약조건 NOT NUL이 입력된 컬럼은 NULL 값이 들어갈 수 없습니다.

UNIQUE는 해당 칼럼의 값을 입력할 때 중복된 값을 입력할 수 없습니다.

PRIMARY KEY는 기본 키로 지정합니다. (UNIQUE + NOT NULL)

CHECK는 조건을 넣어주면 컬럼의 값이 조건에 항상 만족해야 합니다..

 

 

 

3. 연습

--Q1) 크기가 3인 CHAR형의 ID컬럼, 크기가 20인 VARCHAR2형의 NAME컬럼이 있는 TABLE_NOTNULL01테이블을 만들자( 단, ID컬럼은 컬럼레벨에서 NOT NULL 제약조건을 가진다).

CREATE TABLE TABLE_NOTNULL01(
ID CHAR(3) NOT NULL,
NAME VARCHAR2(20)
);

한번 이 테이블에 값을 넣어 볼까요??

INSERT INTO TAVLE_NITNULL01
VALUES('100','ORACLE');

값이 잘 들어가네요.

하지만 NULL 값을 넣으면 오류가 납니다!

 

 

--Q2) 크기가 3인 CHAR형의 ID컬럼, 크기가 20인 VARCHAR2형의 NAME컬럼이 있는 TABLE_NOTNULL02테이블을 만들자( 단, 테이블 레벨에서 ID 컬럼에 NOT NULL 제약조건을 넣는다). (에러가 발생하면 그 이유를 설명하자.)

CREATE TABLE TABLE_NOTNULL02(
ID CHAR(3),
NAME VARCHAR2(20),
CONSTRAINT TN02_ID_NN NOT NULL(ID)
);

NOT NULL의 경우 컬럼레벨에서만 제약조건을 넣어줄 수 있습니다. 테이블 레벨에 넣으려고 하면 에러가 발생합니다.

 

 

--Q3) 크기가 3인 CHAR형의 ID컬럼, 크기가 20인 VARCHAR2형의 NAME컬럼이 있는 TABLE_UNIQUE01테이블을 만들자( 단, 컬럼레벨에서 ID 컬럼에 UNIQUE 제약조건을 넣는다).

CREATE TABLE TABLE_UNIQUE01(
ID CHAR(3) UNIQUE,
NAME VARCHAR2(20)
);

ID컬럼은 UNIQUE 제약조건이 걸려있습니다. 그럼 ID컬럼에 같은 값을 한번 넣어볼까요??

INSERT INTO TABLE_UNIQUE01 VALUES('100','ORACLE');
INSERT INTO TABLE_UNIQUE01 VALUES('100','JAVA');

처음 들어간 값은 잘 들어갔지만 ID컬럼이 UNIQUE의 제약조건이 걸려있기 때문에 이후 ID에는 100이라는 값은 들어갈 수 없습니다.

 

 

--Q4) 크기가 3인 CHAR형의 ID컬럼, 크기가 20인 VARCHAR2형의 NAME컬럼, NUMBER형의 NUM컬럼이 있는 TABLE_UNIQUE02테이블을 만들자( 단, 테이블 레벨에서 ID 컬럼과 NAME컬럼에 UNIQUE제약조건을 넣는다 – 제약조건의 이름은 TU02_ID_UN).

CREATE TABLE TABLE_UNIQUE02(
ID CHAR(3),
NAME VARCHAR2(20),
NUM NUMBER,
CONSTRAINT TU02_ID_UN UNIQUE(ID,NAME)
);

이번에는 ID컬럼과 NAME컬럼이 묶여있습니다.

둘 다 같은 값이 들어올 수 없습니다.

ID와 NAME 중 둘 중 하나만 다른 값이 들어와도 값을 넣어줄 수 있습니다.

하지만 두 개의 값 모두 같으면 테이블에 값을 넣을 수 없습니다.

 

 

--Q5) 크기가 3인 CHAR형의 ID컬럼, 크기가 20인 VARCHAR2형의 NAME컬럼이 있는 TABLE_PK01테이블을 만들자. (단, ID컬럼의 제약조건은 컬럼레벨에서 PRIMARY KEY로 설정한다.)

CREATE TABLE TABLE_PK01(
ID CHAR(3) PRIMARY KEY,
NAME VARCHAR2(20)
);

PRIMARY KEY는 NOT NULL과 UNIQUE가 합쳐진 제약조건이라고 할 수 있습니다. NULL 값이 들어갈 수 없고 중복 값이 들어갈 수 없게 됩니다.

 

INSERT INTO TABLE_PK01 VALUES('100', 'ORACLE');
INSERT INTO TALBE_PK01 VALUES('200', 'ORACLE');
INSERT INTO TABLE_PK01 VALUES('200', 'ORACLE');
INSERT INTO TALBE_PK01 VALUES(NULL, 'ORACLE');

ID에 이미 있는 값을 넣거나 NULL 값을 넣으면 위와 같은 에러가 발생합니다.

​

 

 

--Q6) 크기가 3인 CHAR형의 ID컬럼, 크기가 20인 VARCHAR2형의 NAME컬럼, NUMBER형의 NUM컬럼이 있는 TABLE_PK02테이블을 만들자. (단, ID와 NAME컬럼의 제약조건은 PRIMARY KEY로 설정한다. 제약조건은 테이블 레벨에서 설정하며 이름은 TP02_PK로 한다.)

CREATE TABLE TABLE_PK02(
ID CHAR(3),
NAME VARCHAR2(20),
NUM NUMBER,
CONSTRAINT TP02_PK PRIMARY KEY (ID,NAME)
);

ID와 NAME컬럼에 제약조건이 걸려있습니다. 어떤 값이 입력이 안될까요??

INSERT INTO TABLE_PK02 VALUES('100','ORACLE',1);
INSERT INTO TABLE_PK02 VALUES('200','JAVA',2);
INSERT INTO TABLE_PK02 VALUES(NULL,'ORACLE',3);
INSERT INTO TABLE_PK02 VALUES('100','JAVA',4);

NULL 값은 당연히 들어갈 수 없습니다. 또한 ID, NAME 두컬럼의 값이 같은 값도 들어갈 수 없습니다. 대신 한 컬럼만 같고 다른 컬럼이 다른 값이라면 들어갈 수 있습니다.

 

 

--Q7) 크기가 3인 CHAR형의 ID컬럼, 크기가 20인 VARCHAR2형의 NAME컬럼, 크기가 3인 CHAR의 PKID컬럼이 있는 TABLE_FK01테이블을 만들자.(단, ID컬럼은 컬럼 레벨에서 PRIMARY KEY이며 PKID컬럼은 TABLE_PK01테이블의 ID컬럼을 참조한다.)

 

CREATE TABLE TABLE_FK01(
ID CHAR(3) PRIMARY KEY,
NAME VARCHAR2(20),
PKID CHAR(3) REFERENCES TABLE_PK01(ID)
);

PKID가 TABLE_PK01테이블의 ID컬럼을 참조한다고 했습니다. 그럼 TABLE_PK01테이블의 ID컬럼안에 있는 값만 가져다 쓸 수 있습니다.

INSERT INTO TABLE_FK01 VALUES('123','ORACLE','100');
INSERT INTO TABLE_FK01 VALUES('124','ORACLE','200');
INSERT INTO TABLE_FK01 VALUES('125','JAVA','300');

아까 TABLE_PK01테이블의 ID컬럼에 100이랑 200은 넣었었는데 300은 안 넣었었죠??

그래서 에러가 발생합니다.

 

 

--Q7) 크기가 3인 CHAR형의 ID컬럼, 크기가 3인 CHAR형의 PKID컬럼, 크기가 20인 VARCHAR2형의 PKNAME컬럼이 있는 TABLE_FK02 테이블을 만들자.(단, ID컬럼은 컬럼레벨의 PRIMARY KEY의 제약조건을 가지며 PKID,PKNAME컬럼은 TABLE_PK02테이블의 ID,NAME컬럼을 참조한다.)

CREATE TABLE TABLE_FK02(
ID CHAR(3) PRIMARY KEY,
PKID CHAR(3),
PKNAME VARCHAR2(20),
FOREIGN KEY(PKID,PKNAME) REFERENCES TABLE_PK02(ID,NAME)
);

좀 전에 TABLE_PK02테이블에 들어있는 ID, NAME의 값을 참조할 수 있습니다.

 

 

INSERT INTO TABLE_FK02 VALUES(1,'100','ORACLE');
INSERT INTO TABLE_FK02 VALUES(2,'200','JAVA');
INSERT INTO TABLE_FK02 VALUES(3,'100','JAVA');

위 3개의 명령은 모두 잘 수행이 됩니다.

INSERT INTO TABLE_FK02 VALUES(4,'400','JAVA')

위의 명령어는 TABLE_PK02 테이블에 400의 값을 가진 ID가 없습니다. 따라서 ORACLE이라는 값을 가진 NAME이 있다고 하더라도 에러가 발생하게 됩니다.

 

 

--Q8) 크기가 3이고 CHAR형의 EMP_ID컬럼, 크기가 20이고 VARCHAR2형의 NAME컬럼, 크기가 1이고 CHAR형의 MARRIAGE컬럼을 가진 TABLE_CHECK01테이블을 만들자.(단, EMP_ID컬럼은 PRIMARY KEY, MARRIAGE에는 ‘Y’와 ‘N’의 값만 들어갈 수 있다.)

CREATE TABLE TABLE_CHECK01(
EMP_ID CHAR(3) PRIMARY KEY,
NAME VARCHAR2(20),
MARRIAGE CHAR(1) CHECK(MARRIAGE IN('Y','N'))
);

테이블을 만들었습니다. MARRIAGE컬럼에 정말 Y랑 N만 들어갈까요??

INSERT INTO TABLE_CHECK01 VALUES('123','HONG','Y');
INSERT INTO TABLE_CHECK01 VALUES('124','LEE','N');
INSERT INTO TABLE_CHECK01 VALUES('125','KIM','A');

위의 값을 넣어보겠습니다.

MARRIAGE컬럼에 Y와 N 이외의 값을 넣으면 에러가 발생합니다.

 

 

--Q9) 크기가 3이고 CHAR형의 EMP_ID컬럼, 크기가 20이고 VARCHAR2형의 NAME컬럼, 크기가 1이고 CHAR형의 MARRIAGE컬럼을 가진 TABLE_CHECK02테이블을 만들자.(단, EMP_ID컬럼은 PRIMARY KEY, MARRIAGE에는 ‘Y’와 ‘N’의 값만 들어갈 수 있으며 테이블 레벨의 제약조건 이름은 TC_CK로 한다.)

CREATE TABLE TABLE_CHECK02(
EMP_ID CHAR(3) PRIMARY KEY,
NAME VARCHAR2(20),
MARRIAGE CHAR(1),
CONSTRAINT TC_CK CHECK(MARRIAGE IN('Y','N'))
);

이번에도 Y와 N 값만 들어갈 수 있을까?

INSERT INTO TABLE_CHECK02 VALUES('123','HONG','Y');
INSERT INTO TABLE_CHECK02 VALUES('124','LEE','N');
INSERT INTO TABLE_CHECK02 VALUES('125','KIM','A');

마찬가지로 Y와 N은 들어가지만 A를 넣으려 하면 에러가 발생합니다.