[SQL]CREATE TABLE

작성일자 : 2023-08-07
수정일자 : 2023-08-08
Ver 0.1.2

1. 데이터 타입

오라클 데이터베이스는 다양한 데이터 타입을 제공하며, 아래는 자주 사용하는 데이터 타입이다.

데이터 타입	설명
CHAR[(size)]	- 고정 길이 문자 데이터 타입 - size는 1~2000의 범위를 가지며(기본 값은 1), BYTE 또는 CHAR(글자수)로 지정할 수 있다.
VARCHAR2(size)	- 가변 길이 문자 데이터 타입 - size는 1~4000의 범위를 가지며, BYTE 또는 CHAR(글자수)로 지정할 수 있다. - CHAR에 비해 활용도가 높음
NUMBER[(p[,s])]	- 가변 길이 숫자 데이터 타입 - p는 전체 자리수(138)이고, s는 소수부 자릿수(-84127)이다.
DATE	- 고정 길이 날짜 데이터 타입 - 일자(년월일)와 시간(시분초)을 저장할수 있으며, 1초 단위로 관리된다.
TIMESTAMP[(precision)]	- 가변 길이 날짜 데이터 타입 - prcision는 소수점 이하 초의 정밀도(0~9)이며, 기본값은 6이다.
CLOB	- 가변 길이 문자 데이터 타입(Character Large Object) - 최대(4GB-1바이트)*(데이터베이스 블록 크기)의 데이터를 저장할 수 있다.

---

2. 테이블 생성(열 정의 방식)

기본적인 테이블 생성 방식으로, 각 칼럼마다 데이터 타입을 정의하여 테이블을 생성한다.

• Syntax

CREATE TABLE [schema.]table
(
   column datatype [DEFAULT expr][NOT NULL]
 [,column datatype [DEFAULT expr][NOT NULL] ]
 ...
);

• 구문 설명
  • schema : 테이블이 생성될 스키마(계정)의 이름
  • table : 테이블의 이름
  • column : 테이블에 속한 칼럼의 이름
  • datatype : 해당 칼럼에 저장될 데이터의 타입(ex. varchar2, number, date, ..)
  • DEFAULT : 해당 칼럼에 값이 입력되지 않을 경우, 저장될 기본 값(expr)
  • NOT NULL : 해당 칼럼에 NOT NULL 제약 조건을 지정

EX)

DROP TABLE t1 PURGE;

CREATE TABLE t1
(
  EMPNO  NUMBER(4)
 ,ENAME  VARCHAR2(20) NOT NULL
 ,SAL    NUMBER
 ,DEPTNO NUMBER(2) DEFAULT NOT NULL
);

DESC t1

[조회 결과]

EMPNO	ENAME	SAL	DEPTNO

no rows selected

---

3. 테이블 생성(서브쿼리 방식)

서브쿼리를 이용하여 테이블을 생성하며, 서브 쿼리의 결과가 테이블에 입력된다.

• Syntax

CREATE TABLE [schema.]table
(
   column datatype [DEFAULT expr][NOT NULL]
 [,column datatype [DEFAULT expr][NOT NULL] ]
 ...
)
AS
 subquery;

{: .highlight }
서브쿼리 방식을 CREATE TABLE AS SELEC의 앞 글자를 따서 CTAS 방식이라고 부르기도 한다.

• 구문 설명
  • schema : 테이블이 생성될 스키마(계정)의 이름
  • table : 테이블의 이름
  • column : 테이블에 속한 칼럼의 이름
  • DEFAULT : 해당 칼럼에 값이 입력되지 않을 경우, 저장될 기본 값(expr)
  • NOT NULL : 해당 칼럼에 NOT NULL 제약 조건을 지정

{: .note }
서브쿼리 방식(CTAS 방식)은 데이터 유형을 지정하지 않는다.

EX)

DROP TABLE t2 PURGE;

CREATE TABLE t2
(
  EMPNO  
 ,ENAME  NOT NULL
 ,SAL    
 ,DEPTNO DEFAULT NOT NULL
)
AS
SELECT A.EMPNO, A.ENAME, A.SAL, A.DEPTNO
  FROM EMP A
 WHERE A.DEPTNO = 20;

DESC t2

COLUMN	Nullable	Type
EMPNO		NUMBER(4)
ENAME	NOT NULL	VARCHAR2(10)
SAL		NUMBER(7,2)
DEPTNO	NOT NULL	NUMBER(2)

• 기존에 존재하는 테이블의 데이터 유형을 그대로 받아온다.

[조회 결과]

EMPNO	ENAME	SAL	DEPTNO
7369	SMITH	800	20
7566	JONES	2975	20
7788	SCOTT	3000	20
7876	ADAMS	1100	20
7902	FORD	3000	20

• 서브쿼리에 항상 FALSE인 조건(ex. 1=2)을 사용하여, 데이터 없이 테이블 구조만 복제한 새로운 테이블을 생성할 수 있다.

EX)

DROP TABLE t2 PURGE;

CREATE TABLE t2
AS
SELECT A.*
  FROM EMP A
 WHERE 1=2;  -- 무조건 거짓, 데이터는 조회되지 않고, 테이블 구조만 가져옴

DESC t2

COLUMN	Nullable	Type
DEPTNO		NUMBER(2)
DNAME		VARCHAR2(14)
LOC		VARCHAR2(13)

[조회 결과]

EMPNO	ENAME	SAL	DEPTNO

no rows selected

---

4. 데이터 딕셔너리

user_table 딕셔너리를 조회하면, 해당 계정이 소유한 테이블 목록을 조회할 수 있다.

SELECT A.TABLE_NAME
  FROM USER_TABLES A;

TABLE_NAME
DEPT
EMP
BONUS
SALGRADE
T1
T2

user_tab_columns 딕셔너리를 조회하면, 테이블에 정의된 칼럼 정보를 조회할 수 있다.

SELECT A.TABLE_NAME
      ,A.COLUMN_NAME
      ,A.DATA_TYPE
      ,A.DATA_LENGTH
      ,A.DATA_PRECISION
      ,A.DATA_SCALE
      ,A.NULLABLE
  FROM USER_TABLES A
 WHERE A.TABLE_NAME = 'T1'
 ORDER BY A.TABLE_NAME, A.COLUMN_ID;

TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	DATA_PRECISION	DATA_SCALE	NULLABLE
T1	EMPNO	NUMBER	22	4	0	Y
T1	ENAME	VARCHAR2	20			N
T1	SAL	NUMBER	22			Y
T1	DEPTNO	NUMBER	22	2	0	N