190411

-- DDL
-- 데이터베이스 정의어
-- 데이터베이스 객체의 생성, 삭제, 수정과 관련된 SQL
-- CREATE문, DROP문, ALTER문
-- COMMIT, ROLLBACK의 적용대상이 아니다.
 
-- 오라클의 데이터베이스 객체
-- 테이블: 가장 기본적인 데이터 저장소, 행과 열로 구분되어 있다.
-- 뷰: 하나 이상의 테이블에 있는 데이터의 부분 집합으로 구성된 가상의 테이블이다.
-- 시퀀스: 일련번호 생성기
-- 인덱스: 쿼리의 실행 속도를 향상시키기 위해서 테이블의 데이터에 대한 색인을 갖고 있는 객체다.
 
SELECT ROWID, EMPLOYEE_ID
FROM EMPLOYEES;
 
-- 시퀀스
-- 일련번호 생성한다.
-- 여러 사용자가 공유할 수 있다.
-- 행의 고유한 기본키 값을 생성하는데 주로 사용된다.
-- 시퀀스는 테이블과는 별도로 저장, 관리되기 때문에 여러 테이블에서 동일한 시퀀스를 사용할 수 있다.
 
-- 시퀀스 생성하기
-- CREATE SEQUENCE 시퀀스명
CREATE SEQUENCE SAMPLE_SEQ;
 
CREATE SEQUENCE MY_SEQ;
 
-- 새로운 일련 번호 발급 받기
-- NEXTVAL을 사용하면 새로운 일련 번호를 발급받을 수 있다.
 
INSERT INTO BOOKS
(BOOK_NO, BOOK_TITLE, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_PRICE, BOOK_PUBDATE, BOOK_CREATE_DATE)
VALUES
(MY_SEQ.NEXTVAL, '이것이 자바다', '신용권', '한빛미디어', 30000, '2015-01-01', SYSDATE);
 
-- CURRVAL을 사용하면 현재 사용자가 방금 생성한 일련번호를 조회할 수 있다.
SELECT MY_SEQ.NEXTVAL FROM DUAL;
SELECT MY_SEQ.CURRVAL FROM DUAL;
 
-- 시퀀스 삭제하기
-- DROP SEQUENCE 시퀀스명
DROP SEQUENCE MY_SEQ;
 
 
-- 시퀀스 생성하기
CREATE SEQUENCE 시퀀스명
    INCREMENT BY 숫자값         번호 사이의 간격을 지정(기본값은 1)
    START WITH 숫자값            첫번째 일련번호 지정(기본값은 1)
    MAXVALUE 숫자값             시퀀스의 최댓값을 지정(기본값은 10^27)
    MINVALUE 숫자값              시퀀스의 최솟값을 지정(기본값은 1)
    CYCLE | NOCYCLE               최댓값 도달 이후에도 시퀀스를 계속 생성할지 여부(기본값은
    CACHE 숫자값 | NOCACHE;   일련번호를 미리 생성해서 메모리에 저장할 개수를 지정(기본값은 CACHE 20);
    
-- 시퀀스의 NEXTVAL, CURRVAL 사용처
-- 1. 서브쿼리에 속하지 않는 SELECT문의 SELECT절에서 사용 가능
-- 2. INSERT문의 VALUES 절
-- 3. UPDATE문의 SET절
 
-- 테이블 정의하기
-- 테이블의 데이터를 저장하는 객체
-- 테이블의 이름, 컬럼의 이름은 30자까지만 가능
-- A-Z, a-z, 0-9, _, $, #를 사용할 수 있다.
 
-- 테이블 생성하기
-- CREATE TABLE 테이블명 (
--     컬럼 이름, 데이터 타입(크기)
--     컬럼 이름, 데이터 타입(크기)
--     컬럼 이름, 데이터 타입(크기), DEFAULT 값
-- );
 
-- 오라클의 데이터 타입
-- VARCHAR2(SIZE)       가변 길이 문자 데이터(최대 4000 바이트)
--                             (이름, 주소, 블로그의 내용, 책의 제목, ...)
-- CHAR(SIZE)              고정 길이 문자 데이터(최대 2000 바이트)
--                             (우편 번호, 군번, 주민등록번호, 사업자등록번호,...)
-- NUMBER(P, S)          가변 길이 숫자 데이터(P는 십진 자릿수, S는 소수점 이하 자릿수)      
-- DATE                     날짜 및 시간
-- LONG                    최대 2GB의 가변 길이 문자 데이터
--                            테이블 당 하나만 정의할 수 있다. 제약 조건을 정의할 수 없다.
--                            GROUP BY, ORDER BY 절에 포함시킬 수 없다.
-- CLOB                     최대 4GB의 가변 길이 문자 데이터 CHARACTER LARGE OBJECT
-- BLOB                     최대 4GB의 이진(바이너리) 데이터 BINARY ~
-- ROWID                  테이블에서 행의 고유 주소를 나타내는 64진수
 
CREATE TABLE SAMPLE_CATEGORY (
    CATEGORY_CODE CHAR(2) PRIMARY KEY,        -- 고정 길이 문자 데이터
    CATEGORY_NAME VARCHAR2(200)                -- 가변 길이 문자 데이터
);
 
INSERT INTO SAMPLE_CATEGORY VALUES('FU', '가구');
INSERT INTO SAMPLE_CATEGORY VALUES('ED', '가전기기');
INSERT INTO SAMPLE_CATEGORY VALUES('KH', '주방기기');
INSERT INTO SAMPLE_CATEGORY VALUES('LG', '생활용품');
 
INSERT INTO SAMPLE_CATEGORY VALUES('E', '기타');
COMMIT;
 
DELETE SAMPLE_CATEGORY;
 
SELECT * FROM SAMPLE_CATEGORY
WHERE CATEGORY_CODE = 'E ';
 
-- 컬럼 레벨 제약 조건 설정
CREATE TABLE SAMPLE_PRODUCTS (
    PRODUCT_NO                    NUMBER(4)                     CONSTRAINT PRODUCTS_NO_PK                 PRIMARY KEY,                                                                                       -- 숫자 데이터, 기본키 제약 조건
    PRODUCT_NAME                VARCHAR2(500)               CONSTRAINT PRODUCTS_NAME_NN             NOT NULL,                                                                                           -- 가변 길이 문자 데이터, not null 제약 조건
    PRODUCT_MAKER               VARCHAR2(200),                                                                                                                                                                                 -- 가변 길이 문자 데이터, 
    PRODUCT_PRICE                 NUMBER(8, 0)                  CONSTRAINT PRODUCTS_PRICE_CK               CHECK (PRODUCT_PRICE > 0),                                                                    -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_STOCK                NUMBER(4, 0)                  CONSTRAINT PRODUCTS_STOCK_CK              CHECK (PRODUCT_STOCK > 0),                                                                  -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_SELL                   CHAR(1)                         CONSTRAINT PRODUCTS_SELL_CK                CHECK(PRODUCT_SELL IN ('Y', 'N')),
    PRODUCT_CATEGORY           CHAR(2)                         CONSTRAINT PRODUCT_CATEGORY_FK           REFERENCES SAMPLE_CATEGORY(CATEGORY_CODE),
    PRODUCT_CREATE_DATE DATE DEFAULT SYSDATE                                                                                                                       
);
 
 
-- 테이블 레벨 제약 조건 설정
CREATE TABLE SAMPLE_PRODUCTS (
    PRODUCT_NO                    NUMBER(4),
    PRODUCT_NAME                VARCHAR2(500)               CONSTRAINT PRODUCTS_NAME_NN             NOT NULL,                                                         -- 가변 길이 문자 데이터, not null 제약 조건은 테이블 레벨로 못 내림
    PRODUCT_MAKER               VARCHAR2(200),                                                                                                                                                                                 -- 가변 길이 문자 데이터, 
    PRODUCT_PRICE                 NUMBER(7)                  CONSTRAINT PRODUCTS_PRICE_NN               NOT NULL,                                                                    -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_STOCK                NUMBER(3)                  CONSTRAINT PRODUCTS_STOCK_NN              NOT NULL,                                                                  -- 숫자 데이터, CHECK 제약 조건
    PRODUCT_SELL                   CHAR(1)                     DEFAULT 'Y' CONSTRAINT PRODUCTS_SELL_NN NOT NULL,
    PRODUCT_CATEGORY           CHAR(2)                         CONSTRAINT PRODUCT_CATEGORY_NN       NOT NULL,
    PRODUCT_CREATE_DATE DATE DEFAULT SYSDATE,    
 
    CONSTRAINT PRODUCTS_NO_PK PRIMARY KEY (PRODUCT_NO),
    CONSTRAINT PRODUCTS_PRICE_CK CHECK (PRODUCT_PRICE > 0),
    CONSTRAINT PRODUCTS_STOCK_CK CHECK (PRODUCT_STOCK > 0),
    CONSTRAINT PRODUCTS_SELL_CK CHECK (PRODUCT_SELL IN ('Y', 'N')),
    CONSTRAINT PRODUCTS_CATEGORY_FK FOREIGN KEY (PRODUCT_CATEGORY) REFERENCES SAMPLE_CATEGORY (CATEGORY_CODE)
);
 
 
 
CREATE SEQUENCE PRODUCTS_SEQ START WITH 1000;
 
INSERT INTO SAMPLE_PRODUCTS
(PRODUCT_NO, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_STOCK, PRODUCT_SELL, PRODUCT_CATEGORY)
VALUES
(PRODUCTS_SEQ.NEXTVAL, '양문형 냉장고', 1000000, 10, 'Y', 'FU');
 
INSERT INTO SAMPLE_PRODUCTS(PRODUCT_NO, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_SELL)
VALUES (PRODUCTS_SEQ.NEXTVAL, '사계절 김치 냉장고', 10000000);
 
INSERT INTO SAMPLE_PRODUCTS(PRODUCT_NO, PRODUCT_NAME, PRODUCT_PRICE, PRODUCT_STOCK, PRODUCT_DISCOUNT_RATE, CATEGORY_CODE)
VALUES
(PRODUCTS_SEQ.NEXTVAL, '가죽 소파', 1000000, 10, 0.00, 'FU');
 
 
SELECT * FROM SAMPLE_PRODUCTS();
DROP TABLE SAMPLE_PRODUCTS;
DROP_TABLE SAMPLE_CATEGORY;