8day

-- 8일차
-- 제약조건을 테이블 생성 뒤 따로 추가하기
CREATE TABLE EMP4 AS SELECT * FROM EMP WHERE 1=2;
CREATE TABLE DEPT4 AS SELECT * FROM DEPT WHERE 1=2;
-- 제약조건은 복사가 되지 않는다.
ALTER TABLE EMP4
ADD CONSTRAINT PK_EMP4_EMPNO PRIMARY KEY(EMPNO);
DESC EMP4;
-- dept4에 deptno를 PK로 만드세요 (PK_DEPT4_DEPTNO)
ALTER TABLE DEPT4
ADD CONSTRAINT PK_DEPT4_DEPTNO PRIMARY KEY(DEPTNO);
-- composite key(복합키) : 컬럼 2개 이상이 primary key로 잡히는 경우
ALTER TABLE EMP4
DROP CONSTRAINT PK_EMP4_EMPNO;
ALTER TABLE EMP4
ADD CONSTRAINT PK_EMP4_EMPNOENAME PRIMARY KEY(EMPNO, ENAME);
DESC EMP4;
-- emp4 의 deptno가 dept4의 deptno만 들어가야 한다. (Foreign Key 설정)
ALTER TABLE EMP4
ADD CONSTRAINT FK_EMP4_DEPTNO FOREIGN KEY (DEPTNO)
                              REFERENCES DEPT4(DEPTNO);
INSERT INTO EMP4 (EMPNO, ENAME, DEPTNO) VALUES(1111, '빈', NULL);
-- null 이 들어가는 관계는 비식별관계(non-identifying relationship)
-- null 허용하지 않으면 식별관계(identifying relationship)
-- 먼저 dept4에 부서를 입력!
INSERT INTO DEPT4(DEPTNO, DNAME, LOC) VALUES(10, '게임부', '오락실');
INSERT INTO EMP4(EMPNO, ENAME, DEPTNO) VALUES(2222, '완', 10);
SELECT * FROM DEPT4;
DELETE FROM DEPT4 WHERE DEPTNO = 10;
ALTER TABLE EMP4
DROP CONSTRAINT FK_EMP4_DEPTNO;
-- dept4 에서 deptno를 지우면 emp4도 같이 지워지는 방식 설정
ALTER TABLE EMP4
ADD CONSTRAINT FK_EMP4_DEPTNO FOREIGN KEY (DEPTNO)
                    REFERENCES DEPT4(DEPTNO) ON DELETE CASCADE;
DELETE DEPT4 WHERE DEPTNO = 10;
SELECT * FROM EMP4;
ALTER TABLE EMP4
DROP CONSTRAINT FK_EMP4_DEPTNO;
-- dept4에서 삭제시 emp4의 deptno를 null로 바꾸는 설정
ALTER TABLE EMP4
ADD CONSTRAINT FK_EMP4_DEPTNO FOREIGN KEY (DEPTNO)
                    REFERENCES DEPT4(DEPTNO) ON DELETE SET NULL;
INSERT INTO DEPT4(DEPTNO, DNAME, LOC) VALUES(10, '게임부', '오락실');
INSERT INTO EMP4(EMPNO, ENAME, DEPTNO) VALUES(2222, '완', 10);
DELETE DEPT4 WHERE DEPTNO = 10;
SELECT * FROM EMP4;
-- 제약조건 비활성화
ALTER TABLE EMP4
DISABLE CONSTRAINT FK_EMP4_DEPTNO;
-- user_constraints 에서 제약조건명을 찾을 수 있다.
SELECT * FROM USER_CONSTRAINTS;
-- 제약조건 활성화
ALTER TABLE EMP4
ENABLE CONSTRAINT FK_EMP4_DEPTNO;
-- view
CREATE OR REPLACE VIEW V_TEST
AS
SELECT EMPNO 사원번호, ENAME 사원이름, DNAME 부서명
FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
-- sys 계정으로 scott에게 view 만들기 권한을 준다.
GRANT CREATE VIEW TO SCOTT;
SELECT * FROM V_TEST;
-- force 옵션 : 테이블이 존재하지 않아도 강제로 뷰를 미리 만들기
CREATE FORCE VIEW V_TEST2
AS SELECT * FROM DDD;
create table ddd (no number, ttt varchar2(4000));
SELECT * FROM V_TEST2;
DROP VIEW V_TEST2;
SELECT * FROM RECYCLEBIN; -- 뷰는 쓰레기통에 남지 않는다.
-- with read only 옵션을 주면 뷰를 통한 DML이 불가능해진다.
delete v_test where 사원번호 = 9999;
CREATE OR REPLACE VIEW V_TEST
AS
SELECT EMPNO 사원번호, ENAME 사원이름, DNAME 부서명
FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WITH READ ONLY;
DELETE V_TEST WHERE 사원번호 = 7934;
-- sequence : 숫자 제조기
/*
create sequence 시퀀스이름
start with n -- n부터 시작
increment by n -- n값으로 증가
maxvalue n | nomaxvalue -- 최대값 n 지정
minvalue n | nominvalue -- 최소값 n 지정
cycle | nocycle -- 순환여부
cache | nocache -- 캐쉬 사용여부(미리 숫자를 만들어놓는다)
*/
CREATE SEQUENCE SEQ_TEST
NOCYCLE
NOCACHE;
-- 사용 : nextval, currval
SELECT SEQ_TEST.NEXTVAL FROM DUAL;
-- 예전 방식(시쿼스가 없던 시절)
INSERT INTO DDD VALUES ((SELECT nvl(MAX(NO),0)+1 FROM DDD), 121);
SELECT * FROM DDD;
DELETE DDD WHERE NO = 4;
INSERT INTO DDD VALUES(SEQ_TEST.NEXTVAL, SEQ_TEST.CURRVAL || '번 입력!');
-- index (색인, 전화번호부 ㄱ~ㅎ)
-- 인덱스를 걸어야 하는 경우
-- 1. 선택도(selectivity)가 높아야 한다.
-- 2. where절에 자주 등장하는 컬럼(join 조건에 걸리는 컬럼)
-- 3. 최소 만건 이상의 데이터
--    (주문 테이블 같이 늘어날 것이 예상되는 경우는 예외)
-- 4. null 값이 남발하는 컬럼
-- 단점 : DML 속도가 느려진다.
-- 풀스캔(full scan) : 인덱스 없이 데이터를 테이블 전체를 뒤져서 찾는 것
-- PK, UK : 인덱스가 자동으로 생성된다. FK도 자동으로 인덱스 효과를 누린다.
SELECT * FROM USER_INDEXES;
CREATE INDEX IDX_TEST
ON EMP(ENAME);
SELECT * FROM EMP WHERE ENAME LIKE '%T%';
-- c.f.) /*+ index(emp idx_test) */ : 오라클 hint 인덱스를 강제로 타게 만듬
-- 복합인덱스(composite index) : 컬럼 2개 이상에 인덱스 사용
-- 선행컬럼이 중요 : (job, sal) sal만 검색할때(인덱스 X) job만 검색(인덱스 O)
-- SYNONYM : 오라클클럽참고(www.oracleclub.com)

-- 오라클 데이터 저장단위, tablespace
/*
    물리적인 단위
    1. block : 데이터를 읽고 쓰는 메모리 상의 단위
       (2 / 4 / 8 / 16 / 32KB - default : 8KB)
    2. extent : block이 8개
    3. segment : table, index 저장되는 단위
    4. tablespace : 세그먼트 집합체
    5. Database : 테이블스페이스의 집합체
    6. Data Ware House : DB의 집합체
   
    7. quota : 테이블스페이스에서 사용자에게 할당된 용량 
*/
-- sys 접속
CREATE TABLESPACE JAVACTS
DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\javacts.dbf'--파일지정
SIZE 100M    -- 용량 지정 (M, K)
AUTOEXTEND ON; -- 공간 자동 확장 여부
-- blocksize 8K 블럭사이즈 지정(8K 기본값)
-- segment space management auto -- 오라클이 세그먼트 자동으로 관리
-- flashback on -- 쓰레기통 사용유무
CREATE TABLE TWINKLE (NO NUMBER, TEXT CLOB) TABLESPACE JAVACTS;
select * from user_tables;

-- sys접속
-- 계정 추가 : 계정을 만들면 스키마도 만들어진다.
CREATE USER WAN IDENTIFIED BY WAN; -- 아이디,패스워드 지정
ALTER USER WAN DEFAULT TABLESPACE JAVACTS;
-- ROLE : priviege(권한)의 집합
GRANT CONNECT, RESOURCE TO WAN;

CREATE TABLE WAN.TTT (NO NUMBER);
SELECT * FROM WAN.TTT;
SELECT * FROM SCOTT.EMP;
GRANT SELECT ON SCOTT.EMP TO WAN;
REVOKE SELECT ON SCOTT.EMP FROM WAN;