학원 day34. 시퀀스, 테이블, 뷰, 무결성 제약조건

시퀀스

일련번호를 자동으로 생성하는 데이터베이스 객체. 

테이블의 기본 키 값으로 사용된다. 

일련번호 발행이 필요한 테이블마다 시퀀스를 따로 만든다. (주로 새로운 데이터가 빈번하게 추가, 삭제되는 테이블의 경우 시퀀스가 필요하다.)

 

시퀀스 생성하기

 CREATE SEQUENCE 시퀀스명;
 INCREMENT BY value : 한번에 value값 만큼 증가시킨다. 기본값은 1이다. 
 START WITH value      : 시작값을 value로 지정한다. 기본값은 1이다.
 MAXVALUE value        : 최대값을 value로 지정한다. 기본값은 NOMAXVALUE다.
 MINVALUE value         : 최소값을 value로 지정한다. 기본값은 NOMINVALUE다.
 CACHE value               : 지정된 value개 만큼 일련번호를 미리 생성해서 메모리에 캐시한다. 
                                        기본값은 CACHE 20이다. NOCACHE로 설정하면 일련번호를 미리 생성해두지 않는다.

-- 시퀀스 생성하기
CREATE SEQUENCE SAMPLE_PRODUCTS_SEQ;
-- 위의 명령어로 시퀀스를 생성하면 자동으로 아래의 기본값이 설정된다.
-- INCREMENT BY 1
-- START WITH 1
-- NOMAXVALUE
-- NOMINVALUE
-- CACHE 20

시퀀스 사용하기

- 새 일련번호 발행하기 

   시퀀스명.NEXTVAL : 새로운 일련번호를 발행한다. 시퀀스의 내장함수다.

- 현재 일련번호 조회하기

   시퀀스명.CURRVAL : 현재 연결된 세션에서 NEXTVAL로 발행했던 일련번호를 CURRVAL로 다시 사용할 수 있다.

    반드시 NEXTVAL이 실행된 후에만 사용 가능하다.

 

시퀀스 수정하기

 ALTER SEQUENCE 시퀀스명;

 INCREMENT BY value : 한번에 value값 만큼 증가시킨다. 기본값은 1이다. 
 START WITH value      : 시작값을 value로 지정한다. 기본값은 1이다.
 MAXVALUE value        : 최대값을 value로 지정한다. 기본값은 NOMAXVALUE다.
 MINVALUE value         : 최소값을 value로 지정한다. 기본값은 NOMINVALUE다.
 CACHE value              : 지정된 value개 만큼 일련번호를 미리 생성해서 메모리에 캐시한다. 
                                     기본값은 CACHE 20이다. NOCACHE로 설정하면 일련번호를 미리 생성해두지 않는다.

 

* START WITH 값은 절대로 수정할 수 없다. (중복을 피하기 위해)

-- 한번에 10씩 증가되고, 일련번호를 캐싱하지 않도록 시퀀스를 수정하기
ALTER SEQUENCE SAMPLE_PRODUCTS_SEQ
INCREMENT BY 10
NOCACHE;

 

시퀀스 삭제하기

DROP SEQUENCE 시퀀스명;

 

---

테이블

데이터의 실질적인 저장소 역할을 수행하는 데이터베이스 객체.

 

테이블 생성하기

CREATE TABLE 테이블명 (

             컬럼명        데이터타입(크기)    제약조건,

             컬럼명        데이터타입(크기)     DEFAULT 기본값,

             컬럼명      데이터타입,

              ...

 );

 

오라클의 데이터타입
- 문자 데이터타입
     VARCHAR2    : 가변길이 문자데이터타입, 최대길이 4000바이트, 크기를 지정한다.
     CHAR             : 고정길이 문자데이터타입, 최대길이 2000바이트, 크기를 지정한다.
     LONG             : 대용량 문자데이터타입, 최대길이 2기가 바이트, 테이블당 하나만 정의할 수 있다.
     CLOB              : 대용량 문자데이터타입, 최대길이 4기가 바이트
 * 한글은 1글자가 3바이트를 차지한다.

- 숫자 데이터타입
     NUMBER        : 가변길이 숫자데이터타입, NUMBER(p, s)에서 p는 총 숫자갯수, s는 소숫점이하 자릿수다. 
- 날짜 데이터타입
     DATE              : 날짜 및 시간 데이터타입, 년월일 시분초 정보를 포함한다.      
     TIMESTAMP   : 날짜 및 시간 데이터타입, 년월일 시분초 및 소숫점 이하 초까지 포함한다. 

 

테이블 삭제하기

DROP TABLE 테이블명;

 

테이블 이름 변경하기

RENAME 테이블명 TO 새테이블명;

 

테이블 잘라내기

테이블이 모든 데이터를 삭제하고, 테이블을 최초 생성싯점과 동일한 상태로 만들어준다.

TRUNCATE 테이블명;

 

---

뷰

테이블 혹은 다른 뷰를 기반으로 하는 가상의 테이블(논리적인 테이블)이다.
물리적인 저장소를 가지지 않는다.
뷰를 대상으로 INSERT, UPDATE, DELETE 작업의 수행이 사실상 불가능하다.
사용목적 : 복잡한 SQL문을 간단하게 작성할 수 있다.
                 데이터에 대한 액세스를 제한할 수 있다. (데이터에 대한 보안성 강화)
                 동일한 데이터로부터 다양한 결과를 얻을 수 있다. (동일한 테이블로 여러 뷰를 만들 수 있다.)

뷰를 이용한 데이터 보안

고객정보 테이블의 일부 데이터, 보유계좌 정보 테이블의 일부 데이터만 포함된 가상의 테이블을 생성한다.

개발자는 고객정보, 보유 계좌정보 테이블에 대한 액세스 권한이 없다. 개발자는 가상의 테이블(뷰)에 대한 액세스 권한만 가진다.

고객정보 및 보유 계좌정보에 대한 데이터 보안이 가능해진다.

 

뷰 생성하기

CREATE OR REPLACE VIEW 뷰이름

AS SELECT문;

* SELECT문의 실행결과로 획득된 데이터로 구성된 가상의 테이블을 정의하는 것이다. (영구적)

-- 부서아이디, 부서명, 관리자아이디, 관리자명, 소속된 사원수, 소재지 아이디, 소재지 주소, 
-- 소재지 우편번호, 소재지 도시명, 소재지 국가코드, 소재지 국가명으로 구성된 뷰(가상의 테이블) 생성하기
CREATE OR REPLACE VIEW DEPT_DETAILS_VIEW
AS 
SELECT D.DEPARTMENT_ID      AS DEPT_ID, 
       D.DEPARTMENT_NAME    AS DEPT_NAME,
       D.MANAGER_ID         AS MANAGER_ID,
       M.FIRST_NAME         AS MANAGER_NAME,
       NVL(X.EMP_COUNTS, 0) AS EMP_COUNTS, 
       L.LOCATION_ID        AS LOC_ID,
       L.STREET_ADDRESS     AS ADDRESS,
       L.POSTAL_CODE,
       L.CITY,
       L.COUNTRY_ID,
       C.COUNTRY_NAME
FROM (SELECT DEPARTMENT_ID, COUNT(*) EMP_COUNTS
      FROM EMPLOYEES
      WHERE DEPARTMENT_ID IS NOT NULL
      GROUP BY DEPARTMENT_ID) X, DEPARTMENTS D, EMPLOYEES M, LOCATIONS L, COUNTRIES C
WHERE X.DEPARTMENT_ID(+) = D.DEPARTMENT_ID
AND D.MANAGER_ID = M.EMPLOYEE_ID(+)
AND D.LOCATION_ID = L.LOCATION_ID
AND L.COUNTRY_ID = C.COUNTRY_ID;

-- 뷰를 활용해서 데이터 조회하기
-- 부서아이디, 부서명, 직원수를 조회하기
SELECT  DEPT_ID, DEPT_NAME, EMP_COUNTS
FROM DEPT_DETAILS_VIEW;

-- 뷰를 사용하지 않고, 부서아이디, 부서명, 직원수 조회하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, X.EMP_COUNTS
FROM (SELECT DEPARTMENT_ID, COUNT(*) EMP_COUNTS
      FROM EMPLOYEES
      WHERE DEPARTMENT_ID IS NOT NULL
      GROUP BY DEPARTMENT_ID) X, DEPARTMENTS D
WHERE X.DEPARTMENT_ID(+) = D.DEPARTMENT_ID;

뷰 삭제하기

DROP VIEW 뷰이름;

---

무결성 제약조건

테이블에 유효하지 않은 데이터가 저장되지 않게 한다.

테이블에 데이터 추가/삭제/변경 될 때마다 무결성 제약조건에 위배되는지 검사한다.

 

- NOT NULL 제약조건
    해당 컬럼이 NULL 값을 가질 수 없게 한다.
    예) 고객아이디, 고객명, 비밀번호, 상품명 등의 컬럼은 NULL 값을 가질 수 없다.
    작성예
     CREATE TABLE SAMPLE_USERS ( 
          USER_NAME VARCHAR2(100) NOT NULL,
           ...
     );

- UNIQUE 제약조건(고유키 제약조건)
     해당 컬럼의 값은 테이블의 모든 행에 대해서 고유한 값을 가져야 한다. (중복값을 허용하지 않는다.)
     하나 이상의 컬럼을 사용해서 정의할 수도 있다.
     NOT NULL 제약조건이 정의되어 있지 않으면 NULL값은 중복이 가능하다. 
      예) 이메일 컬럼은 중복값을 가질 수 없다.
     작성예
     CREATE TABLE SAMPLE_USERS (
         USER_EMAIL VARCHAR2(255) UNIQUE,
         ...
      )

- PRIMARY KEY 제약조건 (기본키 제약조건)
      테이블의 각 행을 대표하는 컬럼에 정의되는 제약조건이다.
      테이블 당 하나만 정의할 수 있다.
      하나 이상의 컬럼을 사용해서 정의할 수도 있다. 
      해당 컬럼의 값은 테이블 전체에서 고유한 값이어야 하고, NULL값을 허용하지 않는다.
      일반적으로 해당 컬럼은 숫자타입인 경우가 많다.  (숫자는 가장 단순한 타입이기 때문에)
      예) 상품번호, 주문번호, 학생번호, 고객아이디 등의 컬럼은 행을 대표하는 컬럼이다.
      작성예)
      CREATE TABLE SAMPLE_PRODUCTS (
      PRODUCT_NO NUMBER(8, 0) PRIMARY KEY

- CHECK 제약조건
       제시된 조건을 만족하는 값만 가질 수 있게 한다.
       예) 점수(1~100), 고객등급(골드, 실버, 브론즈, 비회원), 성별(남, 여) 등의 컬럼은 제시된 값만 가질 수 있어야 한다.
       작성예
       CREATE TABLE SAMPLE_SCORES (
            KOR_SCORE  NUMBER(3,0)  CHECK (KOR_SCORE >=0 AND KOR_SCORE <= 100),
       )
       CREATE TABLE SAMPLE_USERS (
            USER_GENDER   CHAR(1)  CHECK (USER_GENDER IN ('M', 'F')),
       )

- FOREIGN KEY 제약조건(외래키 제약조건, 참조키 제약조건)
       해당 컬럼의 값이 다른 테이블(혹은 같은 테이블)의 특정 컬럼이 가지고 있는 값과 관련있는 값만 가져야 한다.
       다른 컬럼이 참조하는 컬럼은 반드시 기본키 제약조건 혹은 고유키 제약조건이 정의된 컬럼만 가능하다.

       (중복된 값이 있을 경우, 어떤걸 참조해야하는지 불분명해지기 때문)
       예시) 직원테이블의 부서아이디는 부서테이블의 부서아이디컬럼의 값과 일치하는 값만 허용된다.
                부서테이블의 관리자아이디는 직원테이블의 직원아이디컬럼의 값과 일치하는 값만 허용된다.
                주문테이블의 주문자아이디는 사용자테이블의 사용자아이디컬럼의 값과 일치하는 값만 허용된다.
                예매테이블의 공연번호는 공연테이블의 공연번호컬럼의 값과 일치하는 값만 허용된다.
       작성예)
       CREATE TABLE SAMPLE_USERS ( 
            USER_ID   VARCHAR2(20)  PRIMARY KEY,
            ...
       )
       CREATE TABLE SAMPLE_ORDERS(
               ORDER_USER VARCHAR2(20) REFERENCES SAMPLE_USERS (USER_ID),   -- 외래키 제약조건 정의
               * ORDER_USER 컬럼의 값은 SAMPLE_USERS 테이블의 USER_ID 컬럼의 값과 일치하는 값만 허용된다.
       )

 

제약조건 정의하기

제약조건 정의방법
-  컬럼레벨 제약조건 정의하기
    CREATE TABLE 테이블명 (
            컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
            컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
             ...
      );

-  테이블레벨 제약조건 정의하기
    CREATE TABLE 테이블명 (
        컬럼명, 데이터타입,
        컬럼명, 데이터타입,
          ...
        [CONSTRAINT 제약조건별칭] 제약조건 (컬럼명),
        [CONSTRAINT 제약조건별칭] 제약조건 (컬럼명, 컬럼명),

   );

          * NOT NULL 제약조건을 제외한 나머지 제약조건은 테이블레벨 제약조건으로 정의할 수 있다.
          * 하나의 컬럼에 제약조건을 여러 개 정의해야 할 경우 테이블레벨 제약조건으로 정의하면 된다.
          * 두 개 이상의 컬럼을 이용해서 제약조건을 정의해야 할 때 테이블레벨 제약조건으로 정의하면 된다.
          * 일반적으로 하나의 컬럼에 NOT NULL 제약조건과 다른 제약조건을 같이 정의해야 할 때, 
            NOT NULL 제약조건은 컬럼레벨 제약조건으로 정의하고, 다른 제약조건은 테이블레벨 제약조건으로 정의한다.
          * 테이블 정의할 때 컬럼레벨제약조건과 테이블레벨 제약조건을 혼용해서 사용하는 경우가 대부분이다.

-- 제약조건이 정의된 사용자 테이블 생성하기
-- 제약조건
--      사용자 아이디는 테이블의 기본키 컬럼이다.
--      사용자 이름, 비밀번호, 이메일은 NULL 값을 허용하지 않는다.
--      이메일은 중복을 허용하지 않는다.
--      성별은 '남', '여'만 가능하다.
CREATE TABLE SAMPLE_USERS (
    USER_ID             VARCHAR2(20)                CONSTRAINT USERS_ID_PK PRIMARY KEY,    -- USERS_ID_PK는 제약조건 별칭이다.(테이블명_컬럼명_제약조건약어)
    USER_PASSWORD       VARCHAR2(20)                NOT NULL,
    USER_NAME           VARCHAR2(100)               NOT NULL,
    USER_EMAIL          VARCHAR2(255)               NOT NULL,                              -- UNIQUE 제약조건은 테이블 레벨 제약조건으로 정의
    USER_TEL            VARCHAR2(20),
    USER_GENDER         CHAR(3)                     CONSTRAINT USERS_GENDER_CK CHECK (USER_GENDER IN ('남', '여')),
    USER_POINT          NUMBER(8)   DEFAULT 0,
    USER_ENABLED        CHAR(1)     DEFAULT 'Y'     CONSTRAINT USERS_ENABLED_CK CHECK (USER_ENABLED IN ('Y', 'N')),
    USER_CREATED_DATE   DATE        DEFAULT SYSDATE,
    USER_UPDATED_DATE   DATE        DEFAULT SYSDATE,
    -- 테이블레벨 제약조건 정의
    CONSTRAINT USERS_EMAIL_UK UNIQUE (USER_EMAIL)
);


-- 제약조건이 정의된 상품 테이블 정의하기
-- 제약조건
--      상품번호는 테이블의 기본키 컬럼이다.
--      상품이름은 NULL값을 허용하지 않는다.
--      상품가격은 0보다 큰 값이다.
--      가격할인율은 0보다 크거나 같고, 1보다 작다.
CREATE TABLE SAMPLE_PRODUCTS (
    PRODUCT_NO              NUMBER(8)                   CONSTRAINT PRODUCTS_NO_PK PRIMARY KEY,
    PRODUCT_NAME            VARCHAR2(255)               NOT NULL,
    PRODUCT_MAKER           VARCHAR2(100),
    PRODUCT_PRICE           NUMBER(8)                   CONSTRAINT PRODUCTS_PRICE_CK CHECK (PRODUCT_PRICE >= 0),
    PRODUCT_DISCOUNT_RATE   NUMBER(2, 2)                CONSTRAINT PRODUCTS_DISCOUNT_RATE_CK 
                                                        CHECK (PRODUCT_DISCOUNT_RATE >= 0 AND PRODUCT_DISCOUNT_RATE < 1),
    PRODUCT_STOCK           NUMBER(6),
    PRODUCT_ON_SELL         CHAR(1)     DEFAULT 'Y'     CONSTRAINT PRODUCT_ON_SELL_CK
                                                        CHECK (PRODUCT_ON_SELL IN ('Y', 'N')),
    PRODUCT_CREATED_DATE    DATE        DEFAULT SYSDATE,
    PRODUCT_UPDATED_DATE    DATE        DEFAULT SYSDATE
);


-- 제약조건이 포함된 장바구니 테이블 생성하기
-- 제약조건
--      사용자 아이디는 SAMPLE_USERS 테이블의 USER_ID컬럼의 값만 허용된다.
--      상품번호는 SAMPLE_PRODUCTS 테이블의 PRODUCT_NO컬럼의 값만 허용된다.
--      사용자아이디와 상품번호를 조합한 값이 이 테이블의 행을 대표하는 값이다.
CREATE TABLE SAMPLE_CART_ITEMS (
    USER_ID VARCHAR2(20) CONSTRAINT CART_USER_ID_FK REFERENCES SAMPLE_USERS (USER_ID),
    PRODUCT_NO NUMBER(8),
    ITEM_AMOUNT NUMBER(3),
    ITEM_CREATED_DATE DATE DEFAULT SYSDATE,
    ITEM_UPDATED_DATE DATE DEFAULT SYSDATE,
    
    CONSTRAINT CART_PRODUCT_NO_FK FOREIGN KEY (PRODUCT_NO) REFERENCES SAMPLE_PRODUCTS (PRODUCT_NO),
    CONSTRAINT CART_ITEM_PK PRIMARY KEY (USER_ID, PRODUCT_NO)
);