Day28

1.뷰(VIew),시퀀스(Sequence),인덱스(Index)등을 구현하고 활용할 수 있다.

2.뷰와 테이블의 장단점을 기술할 수 있다.

3.뷰 생성 및 구현 수정과 삭제 구문을 활용할 수 있다.

4.인덱스의 개념을 이해할 수 있다.

---

문제

Q1.테이블 수정 - 이름 변경

CREATE TABLE TB_EXAM(
COL1 CHAR(3) PRIMARY KEY,
ENAME VARCHAR2(20),
FOREIGN KEY (COL1) REFERENCES EMPLOYEE); -> EMPLOYEE의 PK를 참조하게 된다.*

---

Q2.컬럼이름을 조회하자.

SELECT COLUMN_NAME
FROM USER_TAB_COLS  -> 생성된 테이블의 정보를 가진 테이블
WHERE TABLE_NAME = 'TB_EXAM';

DESC USER_TAB_COLS;

---

Q3.제약조건의 현황을 조회해 보자.

SELECT CONSTRAINT_NAME AS 이름,
CONSTRAINT_TYPE AS 유형,
COLUMN_NAME AS 컬럼,
R_CONSTRAINT_NAME AS 참조,
DELETE_RULE AS 삭제규칙
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS
USING (CONSTRAINT_NAME,TABLE_NAME)
WHERE TABLE_NAME = 'TB_EXAM';
 

---

Q4.Q1에서 생성한 테이블의 컬럼 이름을 변경
COL1 -> EMPID

ALTER TABLE TB_EXAM
RENAME COLUMN COL1 TO EMPID;

---

Q5.제약조건 이름 변경 SYS_C007351 -> PK_EID

ALTER TABLE TB_EXAM
RENAME CONSTRAINTS SYS_C007351 TO PK_EID;

---

Q6.참조 제약조건의 이름을 FK_EID로 변경해보자.

ALTER TABLE TB_EXAM
RENAME CONSTRAINTS SYS_C007352 TO FK_EID;

---

Q7.테이블 이름 변경

ALTER TABLE TB_EXAM RENAME TO TB_SAMPLE;

RENAME TB_EXAM TO TB_SAMPLE;

---

Q8.테이블삭제

DROP TABLE table_name [CASCADE CONSTRAINTS];

• 포함된 데이터 및 테이블과 관련된 데이터 딕셔너리 정보까지 모두 삭제

• 삭제 작업은 복구할 수 없다.

• CASCADE CONSTRAINTS
  삭제 대상 테이블의 PK 또는 U 제약 조건을 참조하는
  다른 제약조건을 삭제하는 옵션,참조중인 제약조건이 있는경우 옵션이 미 사용시 삭제할 수 없다.

CREATE TABLE MY_DEPT(
DID CHAR(2) PRIMARY KEY,
DNAME VARCHAR2(10) );


CREATE TABLE MY_EMP02(
COL1 CHAR(3) PRIMARY KEY,
ENAME VARCHAR2(20),
DID CHAR(2) REFERENCES MY_DEPT);

만약 MY_DEPT테이블을 삭제한다면
참조하고있는 MY_EMP02테이블의 있는 DID는 삭제되지않고 대기상태 OR NULL로된다.
강한 주종관계 : 모든테이블삭제
약한 주종관계: 대기상태나 NULL로 변경

DROP TABLE MY DEPT;

ORA-02449: unique/primary keys in table referenced by foreign keys
해당테이블에 참조하는 FOREIGN KEY 가있다.

DROP TABLE MY_DEPT CASCADE CONSTRAINTS;

제약조건을 삭제

INSERT INTO MY_DEPT VALUES(10,'10');
INSERT INTO MY_DEPT VALUES(20,'10');
INSERT INTO MY_DEPT VALUES(30,'10');
INSERT INTO MY_DEPT VALUES(40,'10');

INSERT INTO MY_EMP02 VALUES(1,'1',10);
INSERT INTO MY_EMP02 VALUES(2,'2',40);

SELECT CONSTRAINT_NAME AS 이름,
CONSTRAINT_TYPE AS 유형,
COLUMN_NAME AS 컬럼,
R_CONSTRAINT_NAME AS 참조,
DELETE_RULE AS 삭제규칙
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS
USING (CONSTRAINT_NAME,TABLE_NAME)
WHERE TABLE_NAME IN ('MY_DEPT','MY_EMP02');

DROP TABLE MY_DEPT CASCADE CONSTRAINTS;

CASCADE는 주 테이블을 삭제할때 참조하는 종 테이블의 제약조건을 삭제한다.

---

Q9.컬럼 삭제
삭제되는 컬럼을 참조하고 있는 다른 컬럼에 설정된 제약조건 까지 함께 삭제된다.

CREATE TABLE TB1(
PK NUMBER PRIMARY KEY,
FK NUMBER REFERENCES TB1,
COL1 NUMBER,
CHECK (PK > 0 AND COL1 > 0) );


SELECT CONSTRAINT_NAME AS 이름,
CONSTRAINT_TYPE AS 유형,
COLUMN_NAME AS 컬럼,
R_CONSTRAINT_NAME AS 참조,
DELETE_RULE AS 삭제규칙
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS
USING (CONSTRAINT_NAME,TABLE_NAME)
WHERE TABLE_NAME ='TB1';

컬럼 삭제

ALTER TABLE TB1
DROP (PK) ;

ORA-12992: cannot drop parent key column

ALTER TABLE TB1
DROP (COL1) ;

ORA-12991: column is referenced in a multi-column constraint

CASCADE CONSTRAINTS (제약조건)를 사용하여 제거해준다

ALTER TABLE TB1
DROP (PK) CASCADE CONSTRAINTS;
ALTER TABLE TB1
DROP (COL1)CASCADE CONSTRAINTS;

후 명령확인
no rows selected (삭제됬음을 확인)

---

VIEW

Q10.VIEW : 다른 테이블이나 뷰에 포함된 데이터의 맞춤표현

• STORED QUERY, VIRTUAL TABLE로 간주되는 데이터 베이스 객체

• 하나 이상의( 1 MORE) 테이블/뷰에 포함된 데이터 부분 집합을 나타내는 논리적인 객체

• 자체적으로 데이터를 포함하지 않는다.

• 베이스 테이블에 있는 데이터를 조건 또는 조인 등을 이용해서 참조하는 형식

사용목적 및 장점

• Restricted data access
  뷰에 접근하는 사용자는 미리 정의된 결과만 볼 수 있다. (데이터 보호)

• Hide data complexity
  여러테이블을 조인하게 되면 복잡한 sql을 숨길 수 있다.

• Simplify statement for the user
  sql구문을 몰라도 간단한 select 구문만으로도 원하는 결과를 조회 할 수 있다.

• Present the data in a different perspective
  뷰에 표현되는 컬럼은 참조 대상 테이블에 영향을 주지 않고 다른 이름으로 참조가 가능하다.

• Isolate applications from changes in definitions of base tables
  베이스 테이블에 포함된 여러 개 컬럼 중 일부만 사용하도록 뷰를 생성할 경우 뷰가 참조 되지 않는
  나머지 컬럼이 변경되어도 뷰를 사용하는 다른 프로그램 영향을 받지 않는다.

• Save complex queries
  자주 사용하는 복잡한 sql문을 뷰형태로 저장하면 반복적으로 사용가능하다.

---

생성구문
CREATE [OR REPLACE][FORCE | NOFORCE] VIEW view_name [(alias [, alias,,,])]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]]

[구문 설명]
CREATE [OR REPLACE]
지정한 뷰가 없으면 새로 생성, 동일이 존재 하면 수정(over_write)
[FORCE | NOFORCE]
원본 테이블이 존재하지 않아도 뷰 생성가능 또는 존재하는 경우에만 뷰생성가능
alias
뷰에서 사용할 이름
subquery
뷰에서 표현하는 데이터를 생성하는 select 구문

제약조건
WITH CHECK OPTION
뷰를 통해 접근 가능한 데이터 베이스에 대해서만 DML 작업 허용
WITH READ ONLY
뷰를 통해 DML작업 허용 하지 않겠다.

---

Q11.사원테이블에서 부서번호가 90번 데이터를 가진 V_EMP인 VIEW를 생성해보자.

CREATE OR REPLACE VIEW V_EMP
AS SELECT EMP_NAME,DEPT_ID
FROM EMPLOYEE
WHERE DEPT_ID = '90';


SELECT * FROM V_EMP;

SELECT COLUMN_NAME,DATA_TYPE,NULLABLE
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'V_EMP';

DESC V_EMP;
N = NOT NULL

---

Q12.사원의 이름, 부서명, 직급을 출력하는 V_EMP_DEPT_JOB 이름의 VIEW를 생성해보자

CREATE OR REPLACE VIEW V_EMP_DEPT_JOB
AS SELECT EMP_NAME,DEPT_NAME,JOB_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT USING(DEPT_ID)
LEFT JOIN JOB USING(JOB_ID)
WHERE JOB_TITLE = '사원';


SELECT COLUMN_NAME,DATA_TYPE,NULLABLE
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'V_EMP_DEPT_JOB';

---

Q13.별칭을 사용한 VIEW 생성

별칭으로 생성

CREATE OR REPLACE VIEW V_EMP_DEPT_JOB(ENM,DNM,TITLE)
AS SELECT EMP_NAME,DEPT_NAME,JOB_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT USING(DEPT_ID)s
LEFT JOIN JOB USING(JOB_ID)
WHERE JOB_TITLE = '사원';

AS로 생성

CREATE OR REPLACE VIEW V_EMP_DEPT_JOB
AS SELECT EMP_NAME AS A1, DEPT_NAME AS D1, JOB_TITLE AS J1
FROM EMPLOYEE
LEFT JOIN DEPARTMENT USING(DEPT_ID)
LEFT JOIN JOB USING(JOB_ID)
WHERE JOB_TITLE = '사원';

---

Q14.조건 함수를 사용한 VIEW 생성할 경우 컬럼의 별칭이 반드시 존재해야 한다.

CREATE OR REPLACE VIEW  V_EMP("ENM","Gender","Years")
AS
SELECT EMP_NAME,

DECODE(SUBSTR(EMP_NO,8,1),'1','남자','3','남자' ,'여자'),
ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12,0)
FROM EMPLOYEE;

CASE 1 별칭이 없는경우 오류 발생 
CREATE OR REPLACE VIEW  V_EMP
AS
SELECT EMP_NAME,
DECODE(SUBSTR(EMP_NO,8,1),'1','남자','3','남자' ,'여자'),
ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12,0)
FROM EMPLOYEE;

ORA-00998: must name this expression with a column alias
반드시 별칭을 줘야한다.

CASE 2 별칭이 없는경우 오류 발생

CREATE OR REPLACE VIEW  V_EMP("Gender" , "Years")
AS
SELECT EMP_NAME,
DECODE(SUBSTR(EMP_NO,8,1),'1','남자','3','남자' ,'여자'),
ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12,0)
FROM EMPLOYEE;

ORA-01730: invalid number of column names specified
컬럼명의 수와 별칭의수가 맞지않음

CASE 3 별칭을 선택적으로 선언할 수 있다.

CREATE OR REPLACE VIEW  V_EMP
AS
SELECT EMP_NAME,
DECODE(SUBSTR(EMP_NO,8,1),'1','남자','3','남자' ,'여자') AS Gender,
ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12,0) AS Years
FROM EMPLOYEE;

---

Q15. 뷰 생성 제약조건 : WITH READ ONLY

CREATE OR REPLACE VIEW V_EMP
AS
SELECT * FROM EMPLOYEE
WITH READ ONLY;

DML을 허용하지 않는다.

UPDATE V_EMP
SET PHONE = NULL;

ORA-42399: cannot perform a DML operation on a read-only view

INSERT INTO V_EMP(EMP_ID,EMP_NAME,EMP_NO)
VALUES ('777','홍길동','666666-6666666');

ORA-01756: quoted string not properly terminated

DELETE FROM V_EMP;

cannot perform a DML operation on a read-only view

---

Q16. 뷰 생성 제약조건 : WITH CHECK OPTION

• 조건에 따라 INSERT/UPDATE 작업제한 (DELETE는 제한없음)

CREATE OR REPLACE VIEW V_EMP
AS
SELECT EMP_ID,EMP_NAME,EMP_NO,MARRIAGE
FROM EMPLOYEE
WHERE MARRIAGE = 'N'
WITH CHECK OPTION;

INSERT INTO V_EMP(EMP_ID,EMP_NAME,EMP_NO,MARRIAGE)

VALUES ('777','홍길동','666666-6666666','Y');

ORA-01402: view WITH CHECK OPTION where-clause violation
CHECK OPTION의 위배된다.

SELECT VIEW_NAME,TEXT
FROM USER_VIEWS
WHERE VIEW_NAME = 'V_EMP';

---

Q17.뷰 - 데이터 조회 절차
뷰를 사용한 SQL 구문 해석 - > 데이터 딕셔너리 "USER_VIEWS"에서 뷰 정의 검색
-> SQL구문을 실행한 계정이 관련된 베이스 테이블(원본) 에 접근하여 SELECT 권한 확인
-> 뷰 대신 원본 테이블을 기반으로 하는 동등한 작업으로 변환
->베이스 테이블(원본)을 대상으로 데이터를 조회

---

Q18.뷰 삭제 -> DROP VIEW VIEW_NAME;

---

시퀀스개념

Q19.시퀀스 개념 : 순차적으로 정수 값을 자동으로 생성하는 객체
.nextval = 다음 수를 리턴 .currval =현재 수를 리턴

CREATE SEQUENCE user_name
INCREMENT BY n
시퀀스 번호 증가/감소 (default 1)
START WITH n
시퀀스 시작 값
{ MAXVALUE n | NOMAXVALUE }
10의 27승을 기점으로 사용된다.
{ MINVALUE n | NOMINVALUE }
-10의 26승을 기점으로 사용된다.
{ CYCLE | NOCYCLE }
최대/최소 값 도달하게 되면 반복 여부를 결정
{CACHE n | NOCACHE }
지정한 수량 만큼 미리 메모리에 생성여부를 결정 (최소값 2,기본 값 20)

---

Q20. 300부터 310번까지 5개씩 증가되는 SEQ_EMPID 라는 객체를 만들자

CREATE SEQUENCE SEQ_EMPID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;

SELECT SEQ_EMPID.NEXTVAL FROM DUAL; 

다음 값을 리턴

ORA-08004: sequence SEQ_EMPID.NEXTVAL exceeds MAXVALUE and cannot be instantiated
맥스밸류를 넘엇거나...

SELECT SEQ_EMPID.CURRVAL FROM DUAL;

현재의 값을 리턴

---

Q21. 5부터 15번까지 5개씩 증가되는 SEQ_EMPID02 라는 객체를 만들자

CREATE SEQUENCE SEQ_EMPID02
START WITH 5
INCREMENT BY 5
MAXVALUE 15
CYCLE
NOCACHE;

사이클을 주게되면 15까지 생성한 뒤 DEFAUL값인 1을 출력한다. -> 1 ->6 ->11

---

Q22.시퀀스를 수정해 보자 단 시퀀스는 START WITH는 수정 할 수 없다.
SEQ_EMPID02의 3씩 증가하면서 10까지 반복하지 않는 시퀀스로 변경 해보자.

ALTER SEQUENCE SEQ_EMPID02
INCREMENT BY 3
MAXVALUE 10
NOCYCLE
NOCACHE;

CREATE SEQUENCE SEQ_01
START WITH 1
INCREMENT BY 3
MAXVALUE 10
NOCYCLE
NOCACHE;


SELECT * FROM USER_SEQUENCES;

---

Q23. INDEX : 책의 목차와 같은 색인을 의미 한다.

• 키워드와 해당 내용의 위치가 정렬된 상태로 구성된다.
• 키워드를 이용해서 내용을 빠르게 찾는 목적을 가진다.
• 데이터 베이스에서 인덱스는 컬럼값을 이용해서 원하는 행을 빠르게 찾기 위한 목적

EX)
INDEX 128P
키워드 위치

DEPT 테이블에 DEPTNO가 있다라고 생각하자.
INDEX를 지정하지 않을경우
20 10 50 60 80 90 30 로 나열된 데이터의(ROWID로 정렬을 한 다음) 데이터를 찾게된다.

INDEX를 DEPTNO로 지정할경우
20 10 50 60 80 90 30 을 10 20 30 50 60 80 90 정렬을 명시적으로 한 다음
해당 ROWID를 위치로 삼아 찾게 된다.

[ 생성 ]
CREATE [UNIQUE] INDEX index_name ON table_name (column_list | function, expr);
ex)
CREATE UNIQUE INDEX IDX_DNM ON DEPARTMENT (DEPT_NAME);
CREATE INDEX IDX_JID ON EMPLOYEE(JOB_ID);

UNIQUE : 중복 값이 포함 될 수 없다.
오라클은 PK 제약조건을 생성하면 자동으로 해당 컬럼에 Unique Index를 생성

PK를 사용하게 되면 access를 하는데 성능 효과를 가진다.

Non_UNIQUE : 빈번하게 사용되는 일반 컬럼을 대상으로 생성함

USER_INDEXES -> 인덱스 정보를 가진 테이블
USER_IND_COLUMNS -> 인덱스가 지정된 컬럼의 정보

---

Q24.EMPLOYEE 테이블의 EMP_NAME 컬럼에 IDX_ENM 이름의 UNIQUE를 생성하시오

CREATE UNIQUE INDEX IDX_ENM ON EMPLOYEE(EMP_NAME); -> UNIQUE 제약조건 수행

INSERT INTO EMPLOYEE (EMP_ID,EMP_NO,EMP_NAME)

VALUES ('400','800000-0000000','감우섭');
ORA-00001: unique constraint (TEST.PK_EMPID) violated
중복값을 설정 할 수 없다.

CREATE UNIQUE INDEX IDX_DID ON EMPLOYEE(DEPT_ID);

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
기존 데이터가 있기때문에 중복

---

Q24.EMPLOYEE에 생성된 인덱스 조회

SELECT INDEX_NAME,COLUMN_NAME,INDEX_TYPE,UNIQUENESS
FROM USER_INDEXES
JOIN USER_IND_COLUMNS USING(INDEX_NAME,TABLE_NAME)
WHERE TABLE_NAME= 'EMPLOYEE';

---

Q25. DML(Data Mainpulation Language) : update, insert, delete, transaction,lock
UPDATE table_name
SET column_name = value [, column_name=value ,,,,] or subquery , default 옵션
[WHERE condition]; -> where가 생력되면 전체 행이 갱신된다.

90번 부서의 부서명을 '전략기획팀'으로 변경해 보자

SELECT * FROM DEPARTMENT;

UPDATE DEPARTMENT
SET DEPT_NAME = '전략기회팀'
WHERE DEPT_ID ='90';

ROLLBACK = DML초기화

---

Q26.'심하균'의 직업과 월급을 성해교랑 같은 직업과 월급으로 변경해라

SELECT EMP_NAME,JOB_ID,SALARY
FROM EMPLOYEE
WHERE EMP_NAME IN('성해교','심하균');

UPDATE EMPLOYEE
SET (JOB_ID,SALARY) = (SELECT JOB_ID,SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '성해교'); 

---

Q27.DELETE -테이블 전체 데이터 삭제

• DELETE FROM TEST;

• TRUNCATE TALBE table_name;
  -> ROLLBACK 불가능, 제약조건이 있는 테이블은 삭제 불가능

ex) TRUNCATE TABLE 명령을 사용해서 전체 데이터를 삭제할 때는

1) 참조되는 테이블의 제약조건을 DISABLE로 지정한다.

ALTER TABLE EMPLOYEE
DISABLE CONSTRAINTS FK_DEPTID;

2)전체 내용을 삭제 한다.

TRUNCATE TABLE DEPARTMENT;

---

Q28.다양한 삭제 룰을 실행 해보자.

DELETE
FROM JOB
WHERE JOB_ID = 'J2';
ORA-02292: integrity constraint (TEST.FK_JOBID) violated - child record found

DELETE
FROM EMPLOYEE
WHERE EMP_ID = '141';

---

Q29.주종관계 있을 떄 삭제 할 수 없는 상태가 된다. 만일 삭제를 하고 싶다면
삭제 룰에서 SET NULL을 사용한다.

SELECT CONSTRAINT_NAME AS 이름,
CONSTRAINT_TYPE AS 유형,
COLUMN_NAME AS 컬럼,
R_CONSTRAINT_NAME AS 참조,
DELETE_RULE AS 삭제규칙
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS
USING (CONSTRAINT_NAME, TABLE_NAME)
WHERE TABLE_NAME = 'EMPLOYEE';

1)기존 제약 조건을 삭제한다

ALTER TABLE EMPLOYEE DROP CONSTRAINTS FK_MGRID;

2)제약조건을 추가하되 옵션을 지정한다.

ALTER TABLE EMPLOYEE
ADD CONSTRAINTS FK_MGRID FOREIGN KEY(MGR_ID)
REFERENCES EMPLOYEE ON DELETE SET NULL;

3)데이터를 삭제 한다.

DELETE
FROM EMPLOYEE
WHERE EMP_ID = '141';

---

Q30.다양한 삭제 룰을 실행 해보자. 주종 테이블에서 주테이블의 내용이 삭제 될 때
종 테이블의 데이터도 삭제하자.

1)기존 제약 조건을 삭제한다

ALTER TABLE EMPLOYEE DROP CONSTRAINTS FK_JOBID;

2)제약조건을 추가하되 옵션을 지정한다.

ALTER TABLE EMPLOYEE
ADD CONSTRAINTS FK_JOBID FOREIGN KEY(JOB_ID)
REFERENCES JOB ON DELETE CASCADE;

3)데이터를 삭제 한다.

DELETE
FROM JOB
WHERE JOB_ID='J2';

J2가 사라진것을 볼수있다.