DDL 문 사용

DDL 문을 사용하여 테이블 생성 및 관리

스키마는 스키마 개체로 알려진 여러 데이터베이스 개체의 모음입니다. 이러한 개체는 소유자 스키마에 의해 직접 액세스 할 수 있습니다. 이러한 개체는 스키마 개체를 나열합니다 .Below table lists the schema objects.

테이블-데이터 저장
보기-하나 이상의 테이블에서 원하는 형식으로 데이터를 투영합니다.
시퀀스-숫자 값 생성
인덱스-테이블에 대한 쿼리 성능 향상
동의어-개체의 대체 이름

데이터베이스를 만드는 첫 번째 단계 중 하나는 조직의 데이터를 저장할 테이블을 만드는 것입니다. 데이터베이스 설계에는 주문 입력, 재고 관리 및 미수금과 같은 다양한 조직 시스템에 대한 시스템 사용자 요구 사항 식별이 포함됩니다. 데이터베이스 크기 및 복잡성에 관계없이 각 데이터베이스는 테이블로 구성됩니다.

테이블 만들기

데이터베이스에 테이블을 만들려면 DBA가 테이블 이름, 열 이름, 열 데이터 유형 및 열 크기와 같은 특정 정보를 가지고 있어야합니다. 이 모든 정보는 나중에 DDL 명령을 사용하여 수정할 수 있습니다.

테이블 명명 규칙-

테이블에 대해 선택한 이름은 다음 표준 규칙을 따라야합니다.
이름은 문자 AZ 또는 az로 시작해야합니다.
숫자와 밑줄을 포함 할 수 있습니다.
소문자의 대문자 일 수 있습니다.
최대 30 자까지 가능합니다.
스키마에서 다른 기존 개체의 동일한 이름을 사용할 수 없습니다.
SQL 예약어가 아니어야합니다.

위의 지침에 따라 'EMP85'는 유효한 테이블 이름이 될 수 있지만 85EMP는 그렇지 않으며 마찬가지로 UPDATE는 SQL 예약 키워드이므로 테이블 이름으로 선택할 수 없습니다.

CREATE TABLE 문

CREATE TABLE은 데이터베이스에 테이블을 생성하는 데 사용되는 DDL 문으로, CREATE TABLE 스크립트가 실행 되 자마자 테이블이 생성되고 데이터를 보관할 준비가되어 있습니다. 사용자는 생성을 위해 CREATE TABLE 시스템 권한이 있어야합니다. 사용자의 스키마에 테이블을 생성하려면 사용자에게 CREATE ANY TABLE 스키마가 있어야합니다.

기본 CREATE TABLE 문의 구문은 다음과 같습니다. 스토리지 사양 또는 세그먼트 값을 명시 적으로 제공하기위한 추가 절이 많이있을 수 있습니다.

CREATE TABLE [schema.]table 
          ( { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} 
         [, { column datatype [DEFAULT expr] [column_constraint] ... 
            | table_constraint} ]...) 
         [AS subquery]

위 구문에서 DEFAULT는 컬럼이 무시 될 경우 INSERT 문에서 사용할 수있는 기본값을 지정합니다. SYSDATE 및 USER 또는 완전히 지정되지 않은 날짜 상수를 제외하고 다른 테이블 열 또는 의사 열 (CURRVAL, NEXTVAL, LEVEL 및 ROWNUM)에 대한 참조를 포함 할 수 없습니다.

제약 조건은 열 수준 또는 테이블 수준에서 선택적으로 정의 된 규칙입니다 (이 장의 뒷부분에서 설명). 이러한 규칙은 테이블에 대한 데이터 작업 (삽입, 업데이트) 중에 확인되고 위반시 작업을 중단하기 위해 오류를 발생시킵니다.

예를 들어 아래의 CREATE TABLE 문은 EMP_TEST 테이블을 만듭니다. 컬럼 스펙, 데이터 유형 및 정밀도에 유의하십시오.

CREATE TABLE SCOTT.EMP_TEST
(EMPID NUMBER,
ENAME VARCHAR2(100),
DEPARTMENT_ID NUMBER,
SALARY NUMBER,
JOB_ID VARCHAR2(3),
HIREDATE DATE,
COMM NUMBER);

사용자는 사용자 이름이나 스키마 앞에 테이블 이름을 붙여 다른 사용자의 스키마에서 테이블을 참조 할 수 있습니다. 예를 들어 사용자 GUEST는 SCOTT가 소유 한 EMP_TEST 테이블에서 직원 이름과 급여를 쿼리하려고합니다. 그는 아래 쿼리를 실행할 수 있습니다.

SELECT  ENAME, SALARY,
FROM 	GUEST.EMP_TEST;

열은 테이블 생성시 기본값을 보유 할 수 있으며, NULL 값이 열로 들어가는 것을 제한하는 데 도움이됩니다. 기본값은 열에 호환 가능한 데이터 유형을 반환해야하는 리터럴, 표현식 또는 SQL 함수에서 추론 할 수 있습니다. 아래 CREATE TABLE 문에서 LOCATION_ID 열의 기본값은 100입니다.

CREATE TABLE SCOTT.DEPARTMENT
(DEPARTMENT_ID NUMBER,
   DNAME VARCHAR2 (100),
   LOCATION_ID NUMBER DEFAULT 100);

CTAS-하위 쿼리를 사용하여 테이블 만들기

하위 쿼리 옵션을 사용하여 데이터베이스의 기존 테이블에서 테이블을 생성 할 수 있으며 테이블 구조와 테이블의 데이터를 복사합니다. 조건에 따라 데이터를 복사 할 수도 있습니다. 명시 적으로 부과 된 NOT NULL 제약 조건을 포함하는 열 데이터 형식 정의가 새 테이블에 복사됩니다.

아래 CTAS 스크립트는 새 테이블 EMP_BACKUP을 생성합니다. 부서 20의 직원 데이터가 새 테이블에 복사됩니다.

CREATE TABLE EMP_BACKUP
AS
SELECT * FROM EMP_TEST
WHERE department_id=20;

데이터 유형

데이터 유형은 테이블에있는 열의 기본 동작을 지정하는 데 사용됩니다. 광범위한 기준으로 열 동작은 숫자, 문자 또는 날짜 계열에 속할 수 있습니다. 이러한 계열에 속하는 다른 여러 하위 유형이 있습니다.

숫자 데이터 유형

NUMBER 데이터 유형은 정수, 고정 소수점 및 부동 소수점 숫자 값을 모두 포함합니다. Oracle의 초기 버전은 이러한 서로 다른 유형의 숫자 각각에 대해 서로 다른 데이터 유형을 정의했지만 이제는 NUMBER 데이터 유형이 이러한 모든 용도에 사용됩니다. 열은 수학적 계산에 사용할 수있는 숫자 데이터를 저장해야하며, 때때로 DBMS에서 일련 번호로 생성 된 식별 번호를 저장하기 위해 NUMBER 데이터 유형이 사용됩니다.

NUMBER (p, s), 여기서 p는 최대 38 자리의 정밀도이고 s는 스케일 (소수점 오른쪽의 자릿수)입니다. 스케일 범위는 -84에서 127까지입니다.

NUMBER (p)는 스케일이 0이고 정밀도가 p 인 고정 소수점 숫자입니다.

FLOAT [(p)], 여기서 p는 1에서 126까지의 범위가 될 수있는 2 진 정밀도입니다. p가 지정되지 않은 경우 기본값은 2 진 126입니다.

날짜 데이터 유형

각 DATE 데이터 유형에 대해 세기, 연도, 월, 일,시, 분, 초가 데이터베이스에 저장됩니다. 모든 데이터베이스 시스템에는 초기화 매개 변수 NLS_DATE_FORMAT에 정의 된 기본 날짜 형식이 있습니다. 이 매개 변수는 일반적으로 DD-MON-YY로 설정되며 시간을 지정하지 않을 경우 기본 시간은 오전 12:00:00입니다.

문자 데이터 유형

Oracle은 CHAR, VARCHAR, VARCHAR2 및 LONG을 포함한 세 가지 사전 정의 된 문자 데이터 유형을 지원합니다. VARCHAR 및 VARCHAR2는 실제로 동의어이며 Oracle은 VARCHAR 대신 VARCHAR2를 사용할 것을 권장합니다. 열이 고정 길이 인 문자 값을 저장할 때 CHAR 데이터 유형을 사용합니다. 예를 들어 미국의 사회 보장 번호 (SSN)는 모든 시민에게 할당되며 크기는 항상 9 자이며 (SSN은 숫자로 엄격하게 구성되지만 숫자는 문자로 처리됨) CHAR로 지정됩니다. (9). VARCHAR2 데이터 유형을 사용하여 가변 길이의 영숫자 데이터를 저장합니다. 예를 들어 고객 이름 또는 주소는 저장할 문자 수에 따라 상당히 달라질 수 있습니다. VARCHAR2 열의 최대 크기는 4,000 자입니다.

LOB 데이터 유형

Oracle은 CLOB (character large object) 및 BLOB (binary large object)를 포함한 여러 가지 LOB 데이터 유형을 제공합니다. 이러한 데이터 유형의 열은 텍스트, 이미지, 비디오 및 공간 데이터를 포함한 비정형 데이터를 저장할 수 있습니다. CLOB 데이터 유형은 최대 8TB를 저장할 수 있습니다. BLOB 데이터 유형은 데이터가 단순히 "비트"값의 스트림 인 이미지 및 비디오 데이터와 연관된 것과 같은 비정형 이진 대형 객체를 저장하는 데 사용됩니다. BLOB 데이터 유형은 최대 8TB의 이진 데이터. NCLOB 데이터 유형은 최대 8TB ~ 128TB의 멀티 바이트 국가 문자 집합에 문자 대형 개체를 저장할 수 있습니다. BFILE 데이터 유형 값은 서버의 파일 시스템에서 파일 로케이터 또는 파일에 대한 포인터로 작동합니다. 지원되는 최대 파일 크기는 8TB ~ 128TB입니다.

제약

Constraints는 데이터 무결성을 보장하기 위해 Oracle 테이블에 정의 된 규칙 집합입니다. 이러한 규칙은 각 열 또는 열 집합에 대해 적용됩니다. 이러한 규칙은 각 열 또는 열 집합에 적용됩니다. 테이블이 데이터 작업에 참여할 때마다 이러한 규칙이 검증되고 위반시 예외가 발생합니다. 사용 가능한 제약 유형은 NOT NULL, 기본 키, 고유, 검사 및 외래 키입니다.

아래 구문을 사용하여 열 수준에서 제약 조건을 적용 할 수 있습니다.

통사론:

column [data type] [CONSTRAINT constraint_name] constraint_type

NOT NULL을 제외한 모든 제약 조건은 테이블 수준에서도 정의 할 수 있습니다. 복합 제약 조건은 테이블 수준에서만 지정할 수 있습니다.

NOT NULL 제약

NOT NULL 제약 조건은 데이터 행에 NOT NULL로 지정된 열에 대한 값이 있어야 함을 의미하며, 열이 NOT NULL로 지정되면 Oracle RDBMS는이 제약 조건을 위반하는 직원 테이블에 행을 저장할 수 없습니다. 테이블 수준이 아닌 열 수준에서만 정의 할 수 있습니다.

통사론:

COLUMN [data type] [NOT NULL]

UNIQUE 제약

때때로 기본 키 열이 아닌 열 값에 대해 고유성을 적용해야합니다. UNIQUE 제약 조건을 사용하여이 규칙을 적용 할 수 있으며 Oracle은 고유 제약 조건을 위반하는 모든 행을 거부합니다 .Unique 제약 조건은 열 값이 구별되도록합니다. , 중복없이.

통사론:

Column Level:

COLUMN [data type] [CONSTRAINT <name>] [UNIQUE]

Table Level: CONSTRAINT [제약 조건 이름] UNIQUE (열 이름)

참고 : Oracle은 내부적으로 고유 인덱스를 생성하여 열 값의 중복을 방지합니다. 인덱스는 나중에 PL / SQL에서 설명합니다.

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20) 
          CONSTRAINT TEST_NAME_UK UNIQUE,
  ... );

복합 고유 키의 경우 아래와 같이 테이블 수준에서 정의해야합니다.

CREATE TABLE TEST
( ... ,
  NAME VARCHAR2(20),
  STD VARCHAR2(20) ,
      CONSTRAINT TEST_NAME_UK UNIQUE (NAME, STD)
 );

기본 키

각 테이블은 일반적으로 테이블에 저장된 데이터 행을 고유하게 식별하는 열 또는 열 집합을 포함해야합니다. 각 테이블은 일반적으로 테이블에 저장된 데이터 행을 고유하게 식별하는 열 또는 열 집합을 포함해야합니다. 각 테이블은 일반적으로 테이블에 저장된 데이터 행을 고유하게 식별하는 열 또는 열 집합을 포함해야합니다. 각 테이블은 기본 키로 참조됩니다. 키 열은 NULL 및 중복 값에 대해 제한됩니다.

주목할 점-

테이블에는 하나의 기본 키만있을 수 있습니다.
복합 기본 키 아래에서 여러 열을 클럽화할 수 있습니다.
Oracle은 내부적으로 고유 인덱스를 생성하여 열 값의 중복을 방지합니다. 인덱스는 나중에 PL / SQL에서 설명합니다.

통사론:

Column level:

COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]

Table level:

CONSTRAINT [constraint name] PRIMARY KEY [column (s)]

다음 예에서는 열 수준에서 PRIMARY KEY 제약 조건을 사용하는 방법을 보여줍니다.

CREATE TABLE TEST
( ID  NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
  ...  );

다음 예제는 테이블 수준에서 PRIMARY KEY 제약 조건을 사용하여 복합 기본 키를 정의하는 방법을 보여줍니다.

CREATE TABLE TEST
 ( ...,
   CONSTRAINT TEST_PK PRIMARY KEY (ID) 
 );

외래 키

두 테이블이 특정 열을 기준으로 부모 자식 관계를 공유하는 경우 자식 테이블의 조인 열을 외래 키라고합니다 .When two tables share the parent child relationship based on specific column, the joining column in the child table is known as Foreign Key. 부모 테이블의 해당 열에 대한이 속성은 참조 무결성이라고합니다 .Foreign Key column values in the child table can can null이거나 부모 테이블의 기존 값이어야합니다. 참조 된 테이블의 기본 키 열만 참조 무결성을 적용 할 수 있습니다.

하위 테이블의 컬럼에 외래 키가 정의되어있는 경우 Oracle은 상위 행에 하위 행이 포함되어있는 경우 삭제를 허용하지 않지만, 외래 키 정의시 ON DELETE CASCADE 옵션이 제공되면 Oracle은 삭제합니다. all child rows while parent row is being deleted. 마찬가지로 ON DELETE SET NULL은 부모 테이블의 행이 삭제 될 때 외래 키 값이 null로 설정됨을 나타냅니다.

통사론:

Column Level:

COLUMN [data type] [CONSTRAINT] [constraint name] [REFERENCES] [table name (column name)]

Table level:

CONSTRAINT [constraint name] [FOREIGN KEY (foreign key column name) REFERENCES] [referenced table name (referenced column name)]

다음 예제는 컬럼 레벨에서 FOREIGN KEY 제약 조건을 사용하는 방법을 보여줍니다.

CREATE TABLE TEST
(ccode varchar2(5) 
     CONSTRAINT TEST_FK REFERENCES PARENT_TEST(ccode),
   ...
);

Usage of ON DELETE CASCADE clause

CREATE TABLE TEST
(ccode varchar2(5) 
   CONSTRAINT TEST_FK REFERENCES PARENT_TEST (ccode)
   ON DELETE CASCADE,
   ...
);

제약 조건 확인

때로는 특정 열에 저장된 데이터 값이 허용 가능한 값 범위 내에 있어야합니다. CHECK 제약 조건은 지정된 검사 조건이 테이블에 저장된 각 행에 대해 true 또는 unknown이어야합니다 .Check constraint allows to impose a conditional rule on a 데이터를 열에 삽입하기 전에 유효성을 검사해야합니다. 조건에는 하위 쿼리 또는 의사 열 CURRVAL NEXTVAL, LEVEL, ROWNUM 또는 SYSDATE가 포함되지 않아야합니다.

Oracle에서는 단일 열에 둘 이상의 CHECK 제약 조건이있을 수 있습니다. 실제로 열에 대해 정의 할 수있는 CHECK 제약 조건의 수에는 실질적인 제한이 없습니다.

통사론:

Column level:

COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]

Table level:

CONSTRAINT [name] CHECK (condition)

다음 예에서는 열 수준에서 CHECK 제약 조건을 사용하는 방법을 보여줍니다.

CREATE TABLE TEST
( ...,
   GRADE char (1) CONSTRAINT TEST_CHK
   CHECK (upper (GRADE) in ('A','B','C')),
   ...
);

다음 예제는 테이블 수준에서 CHECK 제약 조건을 사용하는 방법을 보여줍니다.

CREATE TABLE TEST
( ...,
   CONSTRAINT TEST_CHK
   CHECK (stdate < = enddate),
);

ALTER TABLE 문

DBA는 데이터베이스에 테이블이 생성 된 후 테이블 구조 또는 컬럼 정의를 변경할 수 있으며, DDL 명령 인 ALTER TABLE은 이러한 작업을 수행하는 데 사용됩니다 .Alter 명령은 스키마 객체 전용 여러 유틸리티를 제공합니다 .ALTER TABLE 문이 사용됩니다. 테이블의 열을 추가, 삭제, 이름 바꾸기 및 수정합니다.

아래의 ALTER TABLE 문은 EMP 테이블의 이름을 EMP_NEW로 바꿉니다.

ALTER TABLE EMP RENAME TO EMP_NEW;

아래 ALTER TABLE 문은 EMP_NEW 테이블에 새 열 TESTCOL을 추가합니다.

ALTER TABLE EMP_NEW ADD (TESTCOL VARCHAR2 (100))

아래 ALTER TABLE 문은 TESTCOL 열의 이름을 TESTNEW로 바꿉니다.

ALTER TABLE EMP_NEW RENAME COLUMN TESTCOL TO TESTNEW

아래 ALTER TABLE 문은 EMP_NEW 테이블에서 TESTNEW 열을 삭제합니다.

ALTER TABLE EMP_NEW DROP COLUMN TESTNEW;

아래 ALTER TABLE 문은 EMPLOYEE_ID 열에 기본 키를 추가합니다.

ALTER TABLE EMP_NEW ADD PRIMARY KEY (EMPLOYEE_ID)

아래 ALTER TABLE 문은 기본 키를 삭제합니다.

ALTER TABLE EMP_NEW DROP PRIMARY KEY;

아래 ALTER TABLE 문은 테이블 모드를 읽기 전용으로 전환합니다.

ALTER TABLE EMP_NEW READ ONLY;

읽기 전용 테이블

읽기 전용 테이블은 Oracle 11g의 향상된 기능으로 테이블을 읽기 전용으로 사용할 수 있습니다. 이전 오라클 버전에서는 다른 사용자에게 SELECT 권한을 부여하여 테이블을 읽기 전용으로 만들었지 만 소유자는 여전히 읽기 쓰기 권한을 가지고 있었지만 이제는 테이블이 읽기 전용으로 설정되어 있으면 소유자도 데이터 조작에 대한 액세스 권한이 없습니다. .

통사론:

ALTER TALE [TABLE NAME] READ ONLY

ALTER TALE [TABLE NAME] READ WRITE

삽화

SQL>CREATE TABLE ORATEST (id NUMBER)

SQL>INSERT INTO ORATEST VALUES (1);

SQL>ALTER TABLE ORATEST READ ONLY;

SQL> INSERT INTO ORATEST VALUES (2);
INSERT INTO ORATEST VALUES (2)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> UPDATE ORATEST SET id = 2;
UPDATE ORATEST SET id = 2
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> DELETE FROM ORATEST;
DELETE FROM ORATEST
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> TRUNCATE TABLE ORATEST;
TRUNCATE TABLE ORATEST
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST ADD (description VARCHAR2 (50));
ALTER TABLE ORATEST ADD (description VARCHAR2 (50))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."ORATEST"

SQL> ALTER TABLE ORATEST READ WRITE;

Table altered.

SQL> DELETE FROM ORATEST;

1 row deleted.

DROP TABLE 문

DROP TABLE 문은 데이터베이스에서 테이블을 제거하는 데 사용됩니다. 삭제 된 테이블과 해당 데이터는 더 이상 선택에 사용할 수 없습니다. 삭제 된 테이블은 recyclebin에서 사용할 수있는 경우 FLASHBACK 유틸리티를 사용하여 복구 할 수 있습니다. 테이블을 삭제하면 이와 관련된 인덱스 및 트리거가 삭제됩니다.