DDL
- DDL은 데이터 베이스 객체를 생성, 삭제, 변경하는 언어로 대략 4가지 정도로 구분할 수 있습니다.
- CREATE : 테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성
- DROP : 생성된 데이터베이스 객체를 영구히 삭제
- ALTER : 이미 생성된 데이터베이스 객체의 특성을 변경합니다.
- TRUNCATE : 테이블이나 클러스터의 데이터를 통째로 삭제
DML
-DML은 실제 데이터를 조작하는 언어도 6가지 정도로 나눌 수 있습니다.
- SELECT : 테이블이나 뷰에 있는 데이터를 조회
- INSERT : 데이터를 신규로 생성
- UPDATE : 이미 생성된 데이터를 수정
- DELETE : 데이터 삭제
- COMMIT : 트랜잭션 처리, 변경된 데이터를 최종 적용
- ROLLBACK : 트랜잭션 처리, 변경된 데이트를 적용하지 않고 이전으로 되돌림
DDL < DML 외에도 데이터 제어 언어라고 하는 DCL이 있는데 사용자에게 특정 권한을 주는 GRANT와 회수하는 REVOKE 문이 있습니다. 특정 사용자에게 DDL 권한을 주거나 회수하는 것도 DCL로 처리합니다.
.
데이터 베이스 객체의 종류
이외이도 오라클에는 여러 가지 다른 종류의 객체가 존재합니다.
테이블 생성
테이블은 CREATE 문으로 생성할 수 있는데, 기본 구문은 다음과 같습니다.
CREATE TABLE [스키마.]테이블명 {
컬럼1 컬럼1데이터타입 [NULL, NOT NULL],
컬럼2 컬럼2데이터타입 [NULL, NOT NULL],
....
) [테이블 페이스명];스크립트 입력하기
CREATE TABLE EX2_1 (
COLUMN1 CHAR(10),
COLUMN2 VARCHAR2(10),
COLUMN3 NVARCHAR2(10),
COLUMN4 NUMBER
);
테이블이 만들어졌습니다.
생성된 테이블 확인하기
sql developer 상에서 왼쪽 ora_user 밑의 테이블을 선택하고 안에 테이블을 눌러보면
테이블이 만들어진 것이 보입니다.
오른쪽에 보이는 sql 탭을 눌러보면
위와 같은 테이블 생성 구문(DDL)을 볼 수 있습니다.
테이블 생성은 칼럼 명칭과 칼럼의 데이터 타입을 명시함으로써 정의하는데 즉 해당 칼럼이 어떤 테이터 유형에 속하는지를 명시해주어야 합니다. 예를 들어서
EMPLOYEES테이블에서 사원명(EMP_NAME)은 문자형, 급여(SALARY)는 숫자형, 입사일자(HIRE_DATE) 칼럼은 날짜형 데이터입니다.
CREATE TABLE employees (
EMPLOYEE_ID NUMBER(6, 0) NOT NULL,
EMP_NAME VARCHAR2(80) NOT NULL,
SALARY NUMBER(8, 2) NOT NULL
);이처럼 칼럼의 특성에 맞게 데이터 타입을 명시해 줘야 합니다. 또한 테이블명과 칼럼명은 몇 가지 규칙에만 벗어나지 않는다면 자유롭게 만들 수 있는데, 그 규칙은 다음과 같습니다.
1. 테이블명, 칼럼 명의 최대 크기는 30바이트다.
2. 테이블명, 칼럼명으로 예약어를 사용할 수 없다,
(예약어란 오라클에서 미리 선점한 단어로 SELECT, UPDATE과 같은 구문, COUNT, ASC와 같은 SQL함수들이 있으며 예약어 목록은 V$RESERVED_WORDS 시스템 뷰에서 조회 가능합니다.)
3. 테이블명 , 칼럼명으로 문자, 숫자."_"."$"."#"을 사용할 수 있지만, 첫 글자는 문자만 올 수 있습니다.
4. 한 테이블에 사용 가능한 칼럼은 최대 255개입니다.
데이터 타입
데이터 타입인 칼럼이 저장되는 데이터 유형을 말하며, 오라클에서는 기본적으로 제공되는 기본 데이터 타입(원시 데이터 타입)과 사용자 정의 데이터 타입으로 구분할 수 있습니다.
| 데이터 타입 | 설명 |
| CHAR(크기[BYRE | CHAR]) | 고정길이 문자, 최대 2000byte 디폴트 값은 1byte |
| VARCHAR2(크기[BYRE | CHAR]) | 가변길이 문자, 최대 4000byte 디폴트 값은 1byte |
| NCHAR(크기) | 고정길이 유니코드 문자(다국어 입력 가능) 최대 2000byte 디폴드 값은 1 |
| NVARCHAR2(크기) | 가변길이 유니코드 문자(다국어 입력 가능) 최대 4000byte 디폴드 값은 1 |
| LONG | 최대 2GB 크기의 가변길이 문자형, 잘 사용하지 않음 |
실제 입력된 데이터 길이에 따라 크기가 변해 정해지는 것을 만합니다.
예를 들어, 특정 칼럼을 VARCHAR2(10)으로 선언하면, 10byte까지 데이터를 입력할 수 있습니다.
그런데 이 칼럼에 bac라고 세 글자만 입력하면 실제 칼럼 길이는 3byte가 됩니다. 반면 고정길이 char(10)으로 만든 후 'bar'라고 입력하면 세 문자만 입력됐더라도 이 칼럼 길이는 10byte가 됩니다.
실제로 확인해 보겠습니다.
데이터 삽입하는 문법
INSERT INTO 테이블명 (1, 2) VALUES(1의 값, 2의 값);아까 만들어진 테이블에 정보를 넣습니다.
SQL> INSERT INTO EX2_1 (column1, column2) VALUES ('abc', 'abc');
SQL> SELECT column1, LENGTH(column1) as len1,
column2, LENGTH(column2) as len2
FROM EX2_1;
LENGTH는 칼럼 길이를 반환하는 SQL함수입니다. 결과를 보면 같은 값을 입력했지만
CHAR 타입은 길이가 3이 아닌 10 임을 알 수 있습니다. SQL을 작성하고 사용하는 측면에서 CHAR와 VARCHAR2 타입은 실질적 차이가 없으므로 문자형 칼럼은 저장 공간 효율화를 위해 CHAR보다는 VARCHAR2를 사용하면 좋습니다.
숫자 데이터 타입
오라클에서 제공하는 숫자형 데이터입니다.
| 데이터 타입 | 설명 |
| NUMBER[(p, [s])] | 가변숫자,p(1~38, 디폴트 값은38)와 s(-84~127, 디폴트 값은 0) 는 십진수 기준 최대 22byte |
| FLOAT[(p)] | NUMBER의 하위 타입, p는 1~128, 디폴트 값은 128, 이진수 기준 최대 22byte |
| BINARY_FLOAT | 32비트 부동소수점 수, 최대 4byte |
| BINARY_DOUBLE | 64비트 부동소수점 수, 최대 8byte |
숫자 타입은 총 4가지가 있지만 NUMBER형만 사용할 때가 많습니다. 다른 DBMS는 INTEGER와 같은 정수형, DECIMAL과 같은 실수형을 제공합니다. 오라클도 이런 타입으로 칼럼 생성이 가능하지만 내부적으로는 모두 NUMBER형으로 변환되어 생성됩니다.
CREATE TABLE EX2_3 (
COL_INT INTEGER,
COL_DEC DECIMAL,
COL_NUM NUMBER
);테이블을 생성 하고 직접 해보겠습니다. 칼럼의 타입과 길이는 user_tab_cols라는 시스텝 뷰를 조회하면 알 수 있습니다.
보면 아이디는 COL_INT 타입은 NUMBER 길이는 22라고 나오는 걸 확인할 수 있습니다.
NUMBER형의 크기를 명시하지 않거나 * 를 명시하면, 즉 "컬러명 NUMBER(*)"를 명시하면 디폴트 값인 38이 적용되어 최대 크기인 22byte를 차지하는 것입니다.
그리고 NUMBER(p, s) 형식으로 크기를 지정할 수도 있는데, p(precision, 정밀도)는 최대 유효숫자 자릿수를 s(scale)는 소수점 기준 자릿수를 의미합니다.
FLOAT형으로 인한 오류
다른 기종의 DBMS에서 사용하던 테이블을 오라클에 생성한 뒤 데이터를 옮기는 작업을 하는데 숫자형 데이터를 입력하니 데이터가 잘려 들어갔습니다. 원인은 FLOAT형이었는데 예를 보자면
SQL> CREATE TABLE EX2_4 (
2 COL_FLOAT1 FLOAT(32),
3 COL_FLOAT2 FLOAT
4 );
SQL> INSERT INTO EX2_4 (col_float1, col_float2) VALUES (1234567891234, 1234567891234);
SQL> SELECT * FROM EX2_4;
FLOAT형에 값을 넣었더니 값이 잘려서 나옵니다. 이유는 FLOAT(p)에서는 p에 들어가는 자릿수는 이진수가 기준이라서 그렇습니다. FLOAT(32)라고 해서 십진수 기준으로 32자리 숫자가 들어가는 것이 아닌 이진수 기준 32자리(10110100010.. 이런 식으로 32자리)가 들어갑니다. 이진수 기준 32자리 를 십진수로 변환하려면 0.30103을 곱하면 되는데
따라서 32*0.30103 = 9.63296이고 1.234.567.891.234에서 열 번째 자리까지만 제대로 들어가고 나머지는 0으로 들어간 것입니다.
데이터 타입
| 데이터 타입 | 설명 |
| DATE | BC 4712년 1월 1일 부터 9999년 12월 31일 연,월,일,시,분,초까지 입력 가능합니다. |
| TIMESTAMP[(fractional_seconds_precision)] | 연도, 월, 일, 시, 분, 초는 물론 밀리초까지 입력 가능합니다. fractional_seconds_precision은 0~9까지 입력할 수 있고 디폴트 값은 6입니다. |
오라클에서 제공하는 날짜형 데이터입니다.
SQL> CREATE TABLE EX2_5 (
2 COL_DATE DATE,
3 COL_TIMESTAMP TIMESTAMP);테이블 먼저 만들고
SQL> INSERT INTO EX2_5 (col_date, col_timestamp) VALUES (SYSDATE, SYSTIMESTAMP);값 입력
SQL> SELECT *FROM EX2_5;테이블을 살펴보면
SYSDATE와 SYSTIMESTAMP는 현재 일자와 시간을 반환하는 오라클 내부 함수로, 위 결과를 보면 DATE보다 TIMESTAMP의 날짜 정확도가 더 높다는 것을 확인할 수 있습니다
SQL> SELECT SYSDATE FROM dual;
SYSDATE
--------
22/06/13
SQL> SELECT TO_CHAR
2 (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
3 FROM dual;
NOW
--------------------------------------
06-13-2022 13:48:39
LOB데이터 타입 (LOB는 Large Object의 약자로 대용량 데이터를 저장할 수 있는 데이터 타입입니다.)
| 데이터 타입 | 설명 |
| CLOB | 문자형 대용량 객체, 고정길이와 가변길이 문자 집합 지원, 최대 크기는 (4GB-1)*(데이터베이스 블록 사이즈) |
| NCLOB | 유니코드(다국어 지원)을 포함한 문자형 대용량 객체, 최대 크기는 (4GB-1)*(데이터베이스 블록 사이즈) |
| BLOB | 이진형 대용량 객체, 최대 크기는(4GB-1)*(데이터베이스 블록 사이즈) |
| BFILE | 대용량 이진 파일에 대한 로케이터(위치, 이름) 저장, 최대 크기는 4GB |
일반적인 정형 데이터(문자, 숫자, 날짜 등 구조화된 데이터)에 비해 텍스트, 그래픽, 이미지, 사운드 등 비정형 데이터는 그 크기가 매우 큰데, 이런 데이터를 저장하는데 LOB타입을 사용합니다.
문자형 대용량 데이터는 CLOB나 NCLOB, 나머지 그래픽, 이미지, 동영상 등의 데이터는 BLOB를 사용하고 BFILE은 실제 이진 파일이 아닌 데이터베이스 외부에 있는 파일에 대한 로케이터를 저장하며, 실제 파일을 수정할 수는 없고 읽기만 가능합니다.
NULL
null은 '값이 없음'을 의미하며 테이블을 생성할 때 칼럼 속성에 기술합니다.
디폴트 값이 NULL이므로 별도로 지정하지 않으면 해당 칼럼은 NULL을 허용하게 되고
필수 id값 같은 값은 NOT NULL로 지정해서 명시해 줘야 합니다.
SQL> CREATE TABLE EX2_6 (
2 COL_NULL VARCHAR2(10),
3 COL_NOT_NULL VARCHAR2(10) NOT NULL
4 );테이블 생성
SQL> INSERT INTO EX2_6 VALUES('AA', '');
NOT NULL에 값을 넣지 않아서 오류가 발생하였습니다.
SQL> INSERT INTO EX2_6 VALUES('AA', 'BB');값을 넣으니 정상적으로 테이블이 생성되었습니다.
제약조건은 칼럼 속성처럼 보이지만 하나의 데이터베이스 객체이므로 고유의 이름이 있는데 별도로 이름을 명시하지 않으면 오라클에서 자동으로 생성해줍니다. 사용자가 생성한 제약 조건은 USER_CONSTRAINTS 시스템 뷰에서 확인 가능합니다.
SQL> SELECT constraint_name, table_name, search_condition
2 FROM user_constraints
3 WHERE table_name = 'EX2_6';
EX2_6 테이블의 col_not_null 칼럼이 부여한 제약조건이 SYS_C007022란 이름으로 생성됐음을 볼 수 있습니다.
UNIQUE
UNIQUE 제약조건은 말 그대로 해당 칼럼에 들어가는 값이 유일해야 한다는 의미이고 중복 값을 허용하지 않습니다.
컬렴명 데이터타입 unique
혹은
constraints 제약조건명 unique(컬럼명, ...)
SQL> CREATE TABLE EX2_7 (
2 COL_UNIQUE_NULL VARCHAR2(10) UNIQUE,
3 COL_UNIQUE_NNULL VARCHAR2(10) UNIQUE NOT NULL,
4 COL_UNIQUE VARCHAR2(10),
5 CONSTRAINTS unique_nm1 UNIQUE (COL_UNIQUE)
6 );테이블 생성
SQL> SELECT constraint_name, constraint_type, table_name, search_condition
2 FROM user_constraints
3 WHERE table_name = 'EX2_7';입력
결과(cmd에서 보면 좀 개판으로 나오네요)
결과 출력.
UNIQUE 제약 조건이 3개가 만들어졌습니다. UNIQUE NM1 제약조건은 CONSTRAINTS 키워드를 사용해 제약조건명을 명시해서 만들었으므로 UNIQUE_NM1이란 이름으로 생성됐음을 확인할 수 있습니다.
데이터를 넣어보겠습니다.
SQL> INSERT INTO EX2_7 VALUES ('AA', 'AA', 'AA');
SQL> INSERT INTO EX2_7 VALUES ('AA', 'AA', 'AA');값을 동일하게 다시 넣어보면
- 무결성 제약 조건에 위반된다고 출력
SQL> INSERT INTO EX2_7 VALUES ('', 'BB', 'BB');
SQL> INSERT INTO EX2_7 VALUES ('', 'CC', 'CC');
이상하게 COL_UNIQUE_NULL은 NULL 허용 칼럼이기는 하지만 UNIQUE 제약조건이 붙었으므로 중복 입력되지 않아야 하는데 입력이 되고 있습니다. 왜 입력되는지는 NULL은 값이 없음을 의미하므로 UNIQUE 비교 대상에서 NULL은 제외 단다는 점입니다.
기본키(primary key) ==(NOT NULL + UNIQUE)
보통 테이블에 키를 생성했다고 할 때. 이는 기본키를 의미합니다. 기본키는 UNIQUE와 NOT NULL 속성을 동시에 가진 제약조건으로 테이블당 1개의 기본키만 생성 가능합니다.
컬럼명 데이터타입 PRIMARY KEY
혹은
CONSTRAINTS 제약조건명 PRIMARY KEY(컬럼명...)예를 들면 사원과 관련된 테이블을 만들었을 때 사번과 사원명이 있다면 특정한 사람을 식별하려면 사번은 반드시 한 개 여야만 가능합니다. 만약 한 명의 사원의 사번이 두 개이거나 사번 자체가 들어가지 않고 데이터 생성됐다면. 그 사원을 정확히 식별할 방법이 없습니다.
외래키(Foreign Key)
외래키는 테이블 간의 참조 데이터 무결성을 위한 제약 조건입니다.
CONSTRAINT 외래키명 FOREIGN KEY(컬럼명, ...)
REFERENCES 참조 테이블(참조 테이블 컬럼명, ...)외래키에 대한 제약사항
- 반드시 참조하는 테이블이 먼저 생성 되어야 하며, 참조키가 참조 테이블의 기본키로 만들어져 있어야 한다.
- 외래키는 사용할 수 있는 컬럼 개수는 최대 32개다.
- 여러 칼럼을 외래키로 만들려면, 참조하는 컬럼과 외래키 컬럼의 순서와 개수는 같아야 합니다.
CHECK
제약조건 CHECK - 컬럼에 입력되는 데이터를 체크해 특정 조건에 맞는 데이터만 입력 받고 그렇지 않으면 오류를 뱉어 냅니다.
'Oracle' 카테고리의 다른 글
| Oracle (UP.MERGE.DEL) (0) | 2022.06.15 |
|---|---|
| Oracle (SELECT/INSERT) (0) | 2022.06.14 |
| Oracle ER Diagram 생성하기 (0) | 2022.06.09 |
| Oracle 학습준비 (6) | 2022.06.09 |
| 실습용 Oracle 11xe 설치 (0) | 2022.06.09 |
