[20110714] Oracle DDL & 제약조건 & VIEW & ROWNUM & SEQUENCE & SYNONYM & INDEX

DESC user_objects;
SELECT object_name, object_type, created, last_ddl_time
FROM user_objects
ORDER BY object_type DESC;

REM Table DDLs
--1. CREATE TABLE
CREATE TABLE table_name
(
    column_name     column_datatype,
)
--2. DROP TABLE
DROP TABLE table_name
--3. ALTER TABLE
ALTER TABLE table_name
ADD ()
DROP COLUMN column_name
ADD CONSTRAINT constraint_name constraint_type
DROP CONSTRAINT constraint_name
ENABLE CONSTRAINT constraint_name
DISABLE CONSTRAINT constraint_name
MODIFY (column_name    column_datatype)
--4. RENAME A TO B
RENAME old_name TO new_name
--5. TRUNCATE TABLE
TRUNCATE TABLE table_name  (<---DELETE FROM
--6. COMMENT ON TABLE
COMMENT ON TABLE table_name IS '~~~~'

REM column options
--1. DEFAULT option
column_name    column_datatype  DEFAULT default_value
ex)hiredate    DATE  DEFAULT  sysdate
--2. constraint(제약조건)
--1)PRIMARY KEY
--ⓐ
column_name column_datatype PRIMARY KEY
ex)ID   NUMBER(2)   PRIMARY KEY
--ⓑ
column_name column_datatype
CONSTRAINT constraint_name constraint_type
ex)ID  number(2) CONSTRAINT member_id_pk PRIMARY KEY
--ⓒ
column_name column_datatype,
CONSTRAINT constraint_name constraint_type(column_name);
ID  NUMBER(2),
CONSTRAINT member_id_pk  PRIMARY KEY(id)

--2)FOREIGN KEY
--ⓐ부모테이블의 값과 일치하거나 NULL 인 데이터 참조
--ⓑ부모테이블의 primary key 이거나 unique 를 참조가능
column_name column_datatype,
CONSTRAINT constraint_name constraint_type(column_name)
REFERENCES paranet_table_name(column_name)
deptno  VARCHAR2(10)
CONSTRAINT emp_deptno_fk FOREIGN KEY
REFERENCES DEPT(deptno);
--3)NOT NULL
--ⓐcolumn-level 제약조건만 가능
--ⓑ
column_name   column_datatype NOT NULL
ex)dname  VARCHAR2(10)  NOT NULL,
--ⓒ
column_name   column_datatype
CONSTRAINT constraint_name NOT NULL
dname VARCHAR2(10)
CONSTRAINT dept_dname_nn NOT NULL
--4)UNIQUE
--ⓐ
ex)dname VARCHAR2(10) UNIQUE
--ⓑ
ex)dname VARCHAR2(10)
   CONSTRAINT dept_dname_uk UNIQUE
--ⓒ
ex)dname VARCHAR2(10),
   CONSTRAINT dept_dname_uk UNIQUE(dname)

REM CONSTRAINT ADD
--1)제약조건은 추가, 삭제가 가능하지만 변경은 불가능
--2)제약조건의 활성화, 비활성화가능
--3)NOT NULL은 MODIFY 를 통해서만 가능

CREATE TABLE emp1
(
	empno	NUMBER(2),
    ename   VARCHAR2(10),
    hiredate  DATE DEFAULT sysdate
);

ALTER TABLE emp1
ADD CONSTRAINT emp1_empno_pk
PRIMARY KEY(empno);

ALTER TABLE emp1
DROP CONSTRAINT emp1_empno_pk;

ALTER TABLE emp1
MODIFY ename NOT NULL;

DESC EMP1;

ALTER TABLE emp1
ADD (deptno  NUMBER(2));

ALTER TABLE emp1
ADD CONSTRAINT emp1_deptno_fk FOREIGN KEY(deptno)
REFERENCES DEPT(deptno);

DESC user_constraints;

SELECT constraint_name, constraint_type, table_name,
status
FROM user_constraints
WHERE table_name = UPPER('emp1');

Rem 제약조건의 활성화/비활성화
ALTER TABLE emp1
DISABLE constraint emp1_deptno_fk;

SELECT constraint_name, constraint_type, table_name,
status
FROM user_constraints
WHERE table_name = UPPER('dept');

CREATE TABLE DEPT1
AS
SELECT * FROM DEPT;

DESC DEPT;

ALTER TABLE DEPT
DISABLE CONSTRAINT PK_DEPT CASCADE;

ALTER TABLE DEPT
ENABLE CONSTRAINT PK_DEPT;

--부서번호 10번 부서의 이름을 'Oracle'로 변경
UPDATE dept
SET dname = 'Oracle'
WHERE deptno = 10;
--2. 90번 부서, 이름은 'Database', 위치는 'Seoul' 삽입
INSERT INTO dept
VALUES (90, 'Database', 'Seoul');
--3. SAVEPOINT를 지정하라.
SAVEPOINT aaa;
--4. 부서번호 90번 부서 삭제하라.
DELETE FROM DEPT
WHERE deptno = 90;
--5. 위 3번에서 지정한 위치로 rollback 하시오.
ROLLBACK TO aaa;

SELECT * FROM DEPT;


REM VIEW
--1. 테이블의 논리적 부분집합.
--2. 논리적 테이블
--3. 원본데이터를 갖지 않는다.
--4. View 가 참조하는 테이블을 기본테이블이라고 한다.
--5. Data Dictionary 의 멤버이다. ALL_VIEWS, USER_VIEWS
--6. Select 를 하기 위한 논리 테이블이다.
--7. 데이터의 독립성을 보장한다.
--8. 동일한 데이타의 다른 VIEW가 가능하다.
--9. 한개의 VIEW 로 여러 테이블의 조회가 가능하다.

--View를 생성할 권한 부여
--GRANT CREATE VIEW TO scott;   <--SYS 계정으로 수행해야

CREATE VIEW VIEW_emp
AS
SELECT empno, ename, hiredate, sal
FROM EMP
WHERE deptno = 20;

SELECT * FROM view_emp
ORDER BY sal DESC;

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [alias..]
AS subquery

REM VIEW Guide Line
--1. 뷰를 정의하는 서브쿼리는 조인, 그루핑, 하위 질의(subquery)
--등의 복잡한 SELECT 구문을 포함가능하다.
--2. 단, 뷰에서 정의하는 서브쿼리에는 ORDER BY는 포함할 수 없다.

CREATE VIEW view_emp_10
AS
SELECT empno, ename, job
FROM EMP
WHERE deptno = 10;

DESC view_emp_10;

SELECT * FROM view_emp_10;

DESC user_views;

SELECT view_name, text, view_type
FROM user_views
WHERE view_name = UPPER('view_emp_10');

CREATE OR REPLACE VIEW view_emp_10
AS
SELECT empno, ename, sal
FROM EMP
WHERE deptno = 20;

SELECT * FROM view_emp_10;

--부서별로 부서명, 최소급여, 최대급여, 부서의 평균급여
--를 포함하는 view_dept_sum VIEW 를 생성하시오.
CREATE OR REPLACE VIEW view_dept_view
(dname, sal_min, sal_max, sal_avg)
AS
SELECT d.dname, MIN(e.sal), MAX(e.SAL), AVG(e.sal)
FROM emp e, dept d
WHERE e.DEPTNO = d.DEPTNO
GROUP BY d.dname;

DESC view_dept_view;

SELECT * FROM view_dept_view;

--이름, 업무, 급여, 부서명, 위치를 포함하는
--view_emp_dept VIEW 를 생성하시오.
CREATE OR REPLACE VIEW view_emp_dept
("사원번호", "업무", "급여", "부서명", "위치")
as
SELECT e.ename, e.job, e.sal, d.dname, d.loc
FROM emp e, dept d
WHERE e.DEPTNO = d.DEPTNO AND e.sal >= 2000;
SELECT * FROM view_emp_dept;

DROP VIEW view_emp_dept;

REM Top-n query
SELECT ename AS "이름", sal AS "봉급"
FROM (select ename, sal
      FROM EMP
      ORDER BY sal DESC) aaa
WHERE ROWNUM <= 3;

SELECT ROWNUM AS "랭킹", ename AS "이름", hiredate AS "입사날짜"
FROM (SELECT ename, hiredate
      FROM EMP
      ORDER BY hiredate DESC) bbb
WHERE ROWNUM <= 3;

DESC TITLES;


SELECT ROWNUM AS "순위", title AS "책이름", price "가격"
FROM (SELECT title, price
      FROM TITLES
      ORDER BY NVL(price,0) DESC) ccc
WHERE ROWNUM <= 5;

REM SEQUENCE
--1)INSERT 할 때 자동증가/감소하기 위한 객체
--2)작업코드의 양을 줄일 수 있다.
--3)테이블이나 뷰와 별도로 생성하기 때문에,
--한번 만들어서 여러번 재사용 가능

CURRVAL  --> 현재값
NEXTVAL  --> 다음값
--90부터 100까지 4씩 증가하는 시퀀스
CREATE SEQUENCE seq_demo
            INCREMENT BY 4
            START WITH 90
            MAXVALUE 100
            NOCYCLE;

DESC user_sequences;
SELECT sequence_name, max_value
FROM user_sequences
WHERE  sequence_name = UPPER('seq_demo');

CREATE TABLE member
(
	seq    NUMBER(2) PRIMARY KEY,
    irum   VARCHAR2(10) NOT NULL
);

INSERT INTO member
VALUES(SEQ_DEMO.NEXTVAL, 'Sujan');

INSERT INTO member
VALUES(SEQ_DEMO.NEXTVAL, 'Smith');

INSERT INTO member
VALUES(SEQ_DEMO.NEXTVAL, 'Michael');

INSERT INTO member
VALUES(SEQ_DEMO.NEXTVAL, 'Jane');

SELECT SEQ_DEMO.CURRVAL
FROM dual;

SELECT * FROM member;

ALTER TABLE member
MODIFY seq number(3);

ALTER SEQUENCE seq_demo
            MAXVALUE 999

INSERT INTO member
VALUES(SEQ_DEMO.NEXTVAL, 'Junho');

SELECT * FROM member;

--이름은 dept_deptno_seq
CREATE SEQUENCE dept_deptno_seq
             INCREMENT BY 10
             START WITH 60
             MAXVALUE 200
            NOCYCLE;

INSERT INTO DEPT
VALUES (DEPT_DEPTNO_SEQ.NEXTVAL, 'Education', 'Yatap');

SELECT * FROM DEPT;

DROP SEQUENCE dept_deptno_seq;

REM SYNONYM
--계정명.객체 의 이름으로 호출할 때
--너무 이름이 길 때 단축하기 위해 사용한다.

CREATE SYNONYM tit
FOR SCOTT.TITLES;
SELECT * FROM SCOTT.TITLES;
SELECT * FROM TIT;

DESC user_synonyms;

SELECT * FROM user_synonyms
WHERE synonym_name = UPPER('tit');


DROP SYNONYM tit;

INDEX 의 생성조건 --> PDF 참조할것
INDEX 를 생성하지 않아야 할 조건
--1)테이블의 행이 적을 경우
--2)해당컬럼이 WHERE 에서 자주 이용되지 않을 때
--3)WHERE 절의 결과가 전체레코드의 10 ~ 15%의 결과보다
--높게 나올 때
--4)테이블이 자주 입력, 수정, 삭제될 때는 오히려 속도가
--떨어진다.

--INDEX 생성
DESC ZIPCODE;

CREATE INDEX I_zipcode_dong
ON ZIPCODE(dong);

DESC user_indexes;
SELECT index_name, index_type, table_name
FROM user_indexes
WHERE table_name = UPPER('zipcode');

DROP INDEX I_zipcode_dong;