[Oracle] FOREIGN KEY,테이블 명령,뷰(VIEW),시퀀스(SEQUENCE)

FOREIGN KEY (외래키,참조키)

• 부모 테이블에 저장된 행의 컬럼값을 참조하여 자식 테이블의 컬럼에 비정상적인 값이 저장되는 것을 방지하는 제약조건이다.
• 컬럼 수준의 제약조건 또는 테이블 수준의 제약조건 설정 모두 가능하다. 
• 부모 테이블의 PRIMARY KEY 제약조건이 설정된 컬럼을 참조하여 자식 테이블의 컬럼에 FOREIGN KEY 제약조건을 설정한다. 
• 테이블의 관계를 구현하기 위한 제약조건이다. 

 

SUBJECT1 테이블 생성 - 과목코드(숫자형-PRIMARY KEY), 과목명(문자형) : 부모 테이블 

CREATE TABLE SUBJECT1(SNO NUMBER(2) CONSTRAINT SUBJECT1_SNO_PK PRIMARY KEY,SNAME VARCHAR(20));

SUBJECT1 테이블에 행 삽입

INSERT INTO SUBJECT1 VALUES(10,'JAVA');
INSERT INTO SUBJECT1 VALUES(20,'JSP');
INSERT INTO SUBJECT1 VALUES(30,'SPRING');

SELECT * FROM SUBJECT1;

TRAINEE1 테이블 생성 - 수강생번호(숫자형-PRIMARY KEY),수강생이름(문자형),수강과목코드 

CREATE TABLE TRAINEE1(TNO NUMBER(4) CONSTRAINT TRAINEE1_TNO_PK PRIMARY KEY
    ,TNAME VARCHAR2(20),SCODE NUMBER(2));

TRAINEE1 테이블에 행 삽입 

INSERT INTO TRAINEE1 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE1 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE1 VALUES(3000,'전우치',30);
INSERT INTO TRAINEE1 VALUES(4000,'일지매',40);

SELECT * FROM TRAINEE1;

TRAINEE1 테이블과 SUBJECT1 테이블에서 모든 수강생의 수강생번호,수강생이름,수강과목명 검색 
결합조건 : TRAINEE1 테이블의 수강과목코드(SCODE)와 SUBJECT1 테이블의 과목번호(SNO)가 같은 행을 결합 

SELECT TNO,TNAME,SNAME FROM TRAINEE1 JOIN SUBJECT1 ON SCODE=SNO; --[일지매] 미검색

INNER JOIN은 결합조건이 참(TRUE)인 행만 결합하여 검색하므로 결합조건이 맞지 않은 행은 검색되지 않는다.

[일지매]도 검색하고 싶으면 OUTER JOIN을 사용하면 된다.

OUTER JOIN을 사용하면 결합조건이 맞지 않는 행은 NULL과 결합하여 검색된다. 

SELECT TNO,TNAME,SNAME FROM TRAINEE1 LEFT JOIN SUBJECT1 ON SCODE=SNO;

 
TRAINEE2 테이블 생성 - 수강생번호(숫자형-PRIMARY KEY),수강생이름(문자형),수강과목코드(숫자형-FOREIGN KEY) : 자식 테이블 
자식 테이블의 컬럼에 설정된 FOREIGN KEY 제약조건은 부모 테이블의 PRIMARY KEY 제약조건이 설정된 컬럼을 참조한다.
TRAINEE2 테이블의 수강과목코드(SCODE)에 FOREIGN KEY 제약조건을 설정하여 SUBJECT1 테이블의 과목코드(SNO)를 참조

CREATE TABLE TRAINEE2(TNO NUMBER(4) CONSTRAINT TRAINEE2_TNO_PK PRIMARY KEY,TNAME VARCHAR2(20)
    ,SCODE NUMBER(2),CONSTRAINT TRAINEE2_SCODE_FK FOREIGN KEY(SCODE) REFERENCES SUBJECT1(SNO));

제약조건 확인

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='TRAINEE2';

• R_CONSTRAINT_NAME : 부모 테이블에 참조되는 컬럼의 PRIMARY KEY 제약조건 이름 

TRAINEE2 테이블에 행 삽입 >> 자식 테이블에 행 삽입시 FOREIGN KEY 제약조건이 설정된 컬럼값은 부모 테이블의 컬럼값 참조

INSERT INTO TRAINEE2 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE2 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE2 VALUES(3000,'전우치',30);
INSERT INTO TRAINEE2 VALUES(4000,'일지매',40); --부모 테이블에 저장된 컬럼값을 참조할 수 없으므로 FOREIGN KEY 제약조건을 위반하여 에러 발생

TRAINEE2 테이블과 SUBJECT1 테이블에서 모든 수강생의 수강생번호,수강생이름,수강과목명 검색 
결합조건 : TRAINEE2 테이블의 수강과목코드(SCODE)와 SUBJECT1 테이블의 과목번호(SNO)가 같은 행을 결합 - INNER JOIN 

SELECT TNO,TNAME,SNAME FROM TRAINEE2 JOIN SUBJECT1 ON SCODE=SNO;

 
TRAINEE2 테이블에서 수강생번호가 1000인 수강생의 수강과목코드를 40으로 변경

UPDATE TRAINEE2 SET SCODE=40 WHERE TNO=1000;--FOREIGN KEY 제약조건을 위반하여 에러 발생

SUBJECT1 테이블에서 과목코드가 10인 과목정보 삭제

DELETE FROM SUBJECT1 WHERE SNO=10;--FOREIGN KEY 제약조건을 위반하여 에러 발생

>> 자식 테이블에서 참조되는 부모 테이블의 행은 삭제가 불가능하다.

---

ON DELETE CASCADE / ON DELETE SET NULL

SUBJECT2 테이블 생성 - 과목코드(숫자형-PRIMARY KEY),과목명(문자형) : 부모 테이블

CREATE TABLE SUBJECT2(SNO NUMBER(2) CONSTRAINT SUBJECT2_SNO_PK PRIMARY KEY,SNAME VARCHAR(20));

SUBJECT2 테이블에 행 삽입 

INSERT INTO SUBJECT2 VALUES(10,'JAVA');
INSERT INTO SUBJECT2 VALUES(20,'JSP');
INSERT INTO SUBJECT2 VALUES(30,'SPRING');

SELECT * FROM SUBJECT2;

 
TRAINEE3 테이블 생성 - 수강생번호(숫자형-PRIMARY KEY),수강생이름(문자형),수강과목코드(숫자형-FOREIGN KEY) : 자식 테이블 
TRAINEE3 테이블의 수강과목코드(SCODE)에 FOREIGN KEY 제약조건을 설정하여 SUBJECT2 테이블의 과목코드(SNO)를 참조
FOREIGN KEY 제약조건 설정시 ON DELETE CASCADE 또는 ON DELETE SET NULL 기능 추가 

• ON DELETE CASCADE : 부모 테이블의 행을 삭제할 경우 자식 테이블의 컬럼값이 저장된 행을 삭제 처리하는 기능을 제공한다.
• ON DELETE SET NULL : 부모 테이블의 행을 삭제할 경우 자식 테이블의 참조 컬럼값을 NULL로 변경하는 기능을 제공한다.

CREATE TABLE TRAINEE3(TNO NUMBER(4) CONSTRAINT TRAINEE2_TNO_PK PRIMARY KEY,TNAME VARCHAR2(20),SCODE NUMBER(2)
    ,CONSTRAINT TRAINEE3_SCODE_FK FOREIGN KEY(SCODE) REFERENCES SUBJECT2(SNO) ON DELETE CASCADE);

TRAINEE3 테이블에 행 삽입 

INSERT INTO TRAINEE3 VALUES(1000,'홍길동',10);
INSERT INTO TRAINEE3 VALUES(2000,'임꺽정',20);
INSERT INTO TRAINEE3 VALUES(3000,'전우치',30);

SUBJECT2 테이블에서 과목코드가 10인 과목정보 삭제

DELETE FROM SUBJECT2 WHERE SNO=10;

>> SUBJECT2 테이블(부모 테이블)의 과목코드를 참조하는 TRAINEE3 테이블의 수강생정보도 함께 삭제된다.

SELECT * FROM SUBJECT2;

SELECT * FROM TRAINEE3;

---

 서브쿼리를 사용하여 테이블 생성 가능 

• 기존 테이블을 이용하여 새로운 테이블 생성 : 행 복사 
• 형식) CREATE TABLE 타겟테이블명[(컬럼명,컬럼명,...)] AS SELECT 검색대상,검색대상,... FROM 원본테이블명 [WHERE 조건식]
• 서브쿼리의 검색결과를 사용하여 타겟테이블을 생성하고 검색된 행은 타겟테이블의 행으로 삽입 처리 
• 타겟테이블의 컬럼명은 변경 가능하지만 자료형 및 크기는 변경 불가능
• 서브쿼리에서 사용된 원본테이블의 제약조건은 타겟테이블에 미적용 

 
EMP 테이블에 저장된 모든 사원의 사원정보를 검색하여 EMP_COPY 테이블을 생성하고 검색행을 삽입 처리 

CREATE TABLE EMP_COPY AS SELECT * FROM EMP;

>> 원본 테이블과 타겟 테이블의 속성은 동일하기 때문에 EMP 테이블과 EMP_COPY 테이블의 구조는 같다.

EMP 테이블과 EMP_COPY 테이블의 제약조건 비교

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP';
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP_COPY';

 
원본 테이블에는 제약조건이 설정되어 있지만 타겟 테이블에는 제약조건이 미설정 되어있다.

EMP 테이블

EMP_COPY 테이블

 
원본 테이블과 타겟테이블의 저장행은 동일하기 때문에 EMP 테이블과 EMP_COPY 테이블의 행은 같다.

SELECT * FROM EMP;
SELECT * FROM EMP_COPY;

---

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여를 검색하여 EMP_COPY2 테이블을 생성하고 검색행을 삽입 처리

CREATE TABLE EMP_COPY2 AS SELECT EMPNO,ENAME,SAL FROM EMP;

EMP_COPY2 테이블의 구조 및 행 검색

DESC EMP_COPY2;
SELECT * FROM EMP_COPY2;

구조

행

---

EMP 테이블에서 급여가 2000 이상인 사원의 사원번호,사원이름,급여를 검색하여 EMP_COPY3 테이블을 생성하고 검색행을 삽입 처리

CREATE TABLE EMP_COPY3 AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>=2000;

SELECT * FROM EMP_COPY3;

---

EMP 테이블에서 급여가 2000 이상인 사원의 사원번호,사원이름,급여를 검색하여 EMP_COPY4 테이블을 생성하고 검색행을 삽입 처리
+ EMP_COPY4 테이블의 컬럼명을 NO(사원번호),NAME(사원이름),PAY(급여)로 설정해 생성 

CREATE TABLE EMP_COPY4(NO,NAME,PAY) AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>=2000;

EMP_COPY4 테이블의 구조 및 행 검색

DESC EMP_COPY4;
SELECT * FROM EMP_COPY4;

컬럼명이 NO,NAME,PAY로 변경된 것을 확인할 수 있다.

---

원본 테이블의 행을 타겟 테이블에 삽입 처리되지 않도록 설정하기

EMP 테이블과 동일한 속성의 EMP_COPY5 테이블 생성 

CREATE TABLE EMP_COPY5 AS SELECT * FROM EMP WHERE 0=1;--조건식이 무조건 거짓이므로 행 미검색

EMP_COPY5 테이블의 구조 및 행 검색

EMP_COPY5 테이블의 구조

EMP_COPY5 테이블의 행

---

DROP

• 테이블 삭제 : 테이블에 저장된 모든 행 삭제 
• 형식)DROP TALBE 테이블명 

 
예시)

USER_TABLES 딕셔너리를 이용해 테이블 목록 확인 

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'USER%';

USER1 테이블 삭제 

DROP TABLE USER1;

 
오라클은 테이블을 삭제할 경우 테이블을 휴지통(RECYCLEBIN)으로 이동하여 삭제 처리한다. 따라서, 삭제 테이블 복구가 가능하다.

USER_TABLES 딕셔너리 대신 TAB 뷰(VIEW)를 이용하여 테이블 목록 검색 가능 

SELECT * FROM TAB; //테이블 목록 검색

TNAME 컬럼에 BIN으로 시작되는 테이블은 오라클 휴지통에 존재하는 삭제 테이블이다. 

오라클 휴지통에 존재하는 객체 목록 확인

SHOW RECYCLEBIN;

오라클 휴지통에 존재하는 삭제 테이블 복구 

• 형식) FLASHBACK TABLE 테이블명 TO BEFORE DROP 

FLASHBACK TABLE USER1 TO BEFORE DROP;

삭제 테이블 복구 확인 및 저장행 확인 

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'USER%';
SELECT * FROM USER1;

---

USER2 테이블 삭제 

DROP TABLE USER2;

오라클 휴지통 확인 

SHOW RECYCLEBIN;

>> 오라클 휴지통에는 테이블 뿐만 아니라 테이블과 종속관계에 있는 인덱스(INDEX) 객체도 같이 존재한다.

USER2 테이블을 복구하면 종속관계의 인덱스 객체도 함께 복구된다.

FLASHBACK TABLE USER2 TO BEFORE DROP;

 
USER1,USER2,USER3,USER4 테이블 삭제 

DROP TABLE USER1;
DROP TABLE USER2;
DROP TABLE USER3;
DROP TABLE USER4;

 

오라클 휴지통의 테이블 삭제 - 테이블에 종속된 인덱스 객체도 함께 삭제 처리된다. 

• 형식) PURGE TABLE 테이블명 

PURGE TABLE USER4;

 
오라클 휴지통 모든 테이블 삭제하기 - 오라클 휴지통 비우기

PURGE RECYCLEBIN;

---

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'MGR%';

MGR1 테이블 삭제

DROP TABLE MGR1;--테이블 삭제 >> 오라클 휴지통으로 이동 
SHOW RECYCLEBIN;
PURGE RECYCLEBIN;--테이블의 물리적 삭제

오라클 휴지통을 사용하지 않고 물리적으로 삭제하는 것도 가능하다.

• 형식) DROP TABLE 테이블명 PURGE

MGR2 테이블 삭제

DROP TABLE MGR2 PURGE;

>> 권장하진 않는다.
 

---

TRUNCATE

• 테이블을 생성 직후의 상태로 초기화 처리하는 명령 - 테이블에 저장된 모든 행 삭제 처리
• 형식) TRUNCATE TABLE 테이블명 

 
DELETE 이용해 BONUS 테이블에 저장된 모든 행 삭제 >> COMMIT을 해야 해당 명령이 실제 테이블에 적용된다.

DELETE FROM BONUS; --실제 테이블에 저장된 행을 삭제 처리한 것이 아닌 트렌젝션에 명령 저장 
ROLLBACK;--롤백 처리 : 트렌젝션 초기화  트렌젝션에 저장된 모든 SQL 명령 제거

 
TRUNCATE로 BONUS 테이블 초기화하면 자동 커밋 처리되어, 롤백이 불가능하다.

TRUNCATE TABLE BONUS;--자동 커밋 처리 
SELECT * FROM BONUS;
ROLLBACK;--롤백 불가능

---

RENAME

• 테이블의 이름 변경 
• 형식) RENAME 기존테이블명 TO 변경테이블명

BONUS 테이블의 이름을 COMM으로 변경하기

RENAME BONUS TO COMM;

테이블명 검색 

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME IN('BONUS','COMM');

---

ALTER

• 테이블의 속성 및 제약조건 변경
• 형식) ALTER TABLE 테이블명 변경옵션 
• 변경옵션에 의해 테이블 속성에 대한 추가,삭제,변경 및 제약조건에 대한 추가,삭제가 가능하다.

 
USER1 테이블 생성 - 회원번호(숫자형),회원이름(문자형),전화번호(문자형)

CREATE TABLE USER1(NO NUMBER(4),NAME VARCHAR2(20),PHONE VARCHAR2(15));

DESC USER1;

USER1 테이블 행 삽입

INSERT INTO USER1 VALUES(1000,'홍길동','010-1234-5678');

SELECT * FROM USER1;

 
테이블 속성 추가

• 컬럼 기본값 및 컬럼 수준의 제약조건 설정 가능 
• 형식) ALTER TABLE 테이블명 ADD(컬럼명 자료형[(크기)] [DEFAULT 기본값] [제약조건]) 

 
USER1 테이블에 주소(문자형) 속성 추가 

ALTER TABLE USER1 ADD(ADDRESS VARCHAR2(100));

DESC USER1;

SELECT * FROM USER1;

UPDATE 이용해 추가된 속성의 컬럼값을 변경한다.

UPDATE USER1 SET ADDRESS='서울시 강남구' WHERE NO=1000;

SELECT * FROM USER1;

---

USER1 테이블 초기화

TRUNCATE TABLE USER1;

 
테이블 속성의 컬럼 자료형 또는 크기 변경 

• 컬럼 기본값 및 컬럼 수준의 제약조건 설정
• 형식) ALTER TABLE 테이블명 MODIFY(컬럼명 자료형[(크기)] [DEFAULT 기본값] [제약조건]) 

 
USER1 테이블의 NO 컬럼의 자료형을 숫자형에서 문자형으로 변경 

ALTER TABLE USER1 MODIFY(NO VARCHAR2(4));

DESC USER1;

USER1 테이블 행 삽입 

INSERT INTO USER1 VALUES('1000','홍길동','010-1234-5678','서울시 강남구'); --NO 컬럼에 문자값 전달

 
USER1 테이블의 NO 컬럼의 자료형을 문자형에서 숫자형으로 변경 

ALTER TABLE USER1 MODIFY(NO NUMBER(4));--에러 발생

 >> 컬럼 자료형을 변경할 속성에 컬럼값이 저장되어 있는 경우 컬럼의 자료형 변경이 불가능하다. 
 
USER1 테이블의 NAME 컬럼의 크기를 20BYTE에서 10BYTE로 변경 

ALTER TABLE USER1 MODIFY(NAME VARCHAR(10)); --변경 성공

DESC USER1;

USER1 테이블의 NAME 컬럼의 크기를 10BYTE에서 5BYTE로 변경 
>> 컬럼의 크기를 변경할 속성에 컬럼값이 저장되어 있는 경우 컬럼값의 크기보다 작은 컬럼의 크기로 변경이 불가능하다. 

ALTER TABLE USER1 MODIFY(NAME VARCHAR2(5));--에러 발생

---

테이블 속성의 컬럼명 변경 

• 형식) ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 변경컬럼명 

 
USER1 테이블의 ADDRESS 컬럼의 이름을 ADDR로 변경

ALTER TABLE USER1 RENAME COLUMN ADDRESS TO ADDR;

DESC USER1;

---

테이블 속성 삭제

• 테이블 속성에 저장된 컬럼값도 함께 삭제된다.
• 형식) ALTER TABLE 테이블명 DROP COLUMN 컬럼명

 
USER1 테이블에서 PHONE 컬럼 삭제

ALTER TABLE USER1 DROP COLUMN PHONE;

DESC USER1;

SELECT * FROM USER1;

---

제약조건 추가

• 테이블의 속성 추가 및 테이블 속성 변경시 컬럼 수준의 제약조건 추가가 가능하다.

 
USER1 테이블의 NAME 컬럼에 NOT NULL 제약조건 추가 

ALTER TABLE USER1 MODIFY(NAME VARCHAR2(10) CONSTRAINT USER1_NAME_NN NOT NULL);;

제약조건 확인

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER1';

 
테이블 수준의 제약조건은 ADD 옵션을 사용하여 추가 가능 

• 형식) ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건 

 
USER1 테이블의 NO 컬럼에 PRIMARY KEY 제약조건 추가 

ALTER TABLE USER1 ADD CONSTRAINT USER1_NO_PK PRIMARY KEY(NO);

제약조건 확인

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER1';

 

제약조건 삭제

• DROP 옵션을 사용하여 제약조건 삭제 
• 형식) ALTER TABLE 테이블명 DROP {PRIMARY KEY|CONSTRAINT 제약조건명}

 
USER1 테이블의 NAME 컬럼에 설정된 NOT NULL 제약조건 삭제 

ALTER TABLE USER1 DROP CONSTRAINT USER1_NAME_NN;

USER1 테이블의 NO 컬럼에 설정된 PRIMARY KEY 제약조건 삭제 

ALTER TABLE USER1 DROP PRIMARY KEY;

---

뷰(VIEW)

• 테이블을 기반으로 만들어지는 가상의 테이블 (논리적인 테이블)
• 단순뷰와 복합뷰로 구분된다. 
• 뷰는 테이블의 행검색 또는 테이블의 권한 설정을 간편하게 제공하기 위해 생성한다.
• 단순뷰 : 하나의 테이블을 기반으로 생성되는 뷰 - 뷰를 이용한 테이블의 검색뿐만 아니라 뷰를 이용한 테이블의 행 삽입,삭제,변경 가능 
• -> 단순뷰 생성시 그룹함수 또는 DISTINCT 키워드를 사용한 경우 삽입,삭제,변경은 안되고 검색만 가능하다.
• 복합뷰 : 여러개의 테이블을 기반으로 생선된 뷰 - 테이블의 행을 결합하여 생성된 뷰 - 검색만 가능 

 

뷰 생성

• 형식)

CREATE [OR REPLACE] VIEW [{FORCE|NOFORCE}] VIEW 뷰이름[(컬럼명,컬럼명,...)]
AS SELECT 검색대상,검색대상,... FROM 테이블명 [WHERE 조건식] [WITH CHECK OPTION] [WITH READ ONLY]

• 서브쿼리의 검색결과를 이용하여 뷰를 생성한다.
• CREATE OR REPLACE : 동일한 이름의 뷰가 있는 경우 기존 뷰를 삭제하고 새로운 뷰 생성 
• FORCE : 서브쿼리의 검색결과가 없어도 강제로 뷰를 생성하기 위한 기능 제공 
• WITH CHECK OPTION : 뷰를 생성한 서브쿼리의 조건식에서 사용된 컬럼값을 변경하지 못하도록 설정하는 기능 제공 
• WITH READ ONLY : 검색만 가능하도록 설정하는 기능 제공(단순뷰에서만 사용 가능) 

예시)

EMP_COPY 테이블에서 부서번호가 30인 사원의 사원번호,사원이름,부서번호를 검색하여 EMP_VIEW30 뷰 생성 

SELECT * FROM EMP_COPY;

CREATE VIEW EMP_VIEW30 AS SELECT EMPNO,ENAME,DEPTNO FROM EMP_COPY WHERE DEPTNO=30;--권한 불충분으로 에러 발생

>> CREATE VIEW 시스템 권한이 현재 사용자에게 없으므로 CREATE VIEW 명령 실행시 에러가 발생한다.
 
시스템 관리자(SYSDBA - SYS 계정)로 접속하여 현재 접속 사용자(SCOTT)에게 CREATE VIEW 시스템 권한을 부여

admin으로 접속

sys라는 관리자로 접속 

SCOTT에게 권한 부여

 
시스템 관리자에게 CREATE VIEW 시스템 권한을 부여받은 후 CREATE VIEW 명령 실행 

CREATE VIEW EMP_VIEW30 AS SELECT EMPNO,ENAME,DEPTNO FROM EMP_COPY WHERE DEPTNO=30;--단순뷰

>>EMP_COPY 라는 테이블 하나를 이용해 '단순뷰' 생성

이처럼 뷰를 사용하기 위해선 관리자로부터 권한을 얻어야 한다. 
 
USER_VIEWS : 뷰 정보를 제공하는 딕셔너리 
뷰 목록 확인

SELECT VIEW_NAME,TEXT FROM USER_VIEWS;

 
뷰 검색 (= 테이블의 저장행 검색)

SELECT * FROM EMP_VIEW30;

이처럼 뷰를 통해 간단하게 테이블의 행을 검색할 수 있다.
 
단순뷰는 행에 대한 삽입,삭제,변경이 가능하다.
EMP_VIEW30 뷰에 행 삽입

INSERT INTO EMP_VIEW30 VALUES(1111,'홍길동',30);

>> EMP_COPY 테이블에 행이 삽입되는 것과 같다. 생략된 컬럼에는 컬럼의 기본값이 저장된다.

SELECT * FROM EMP_VIEW30;

SELECT * FROM EMP_COPY;

 
EMP 테이블과 DEPT 테이블에서 부서번호가 10인 사원의 사원번호,사원이름,부서이름을 검색하여 EMP_VIEW10 뷰 생성

CREATE VIEW EMP_VIEW10 AS SELECT EMPNO,ENAME,DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; --복합뷰

복합뷰는 검색만 가능하다.

SELECT * FROM EMP_VIEW10;

EMP 테이블과 DEPT 테이블에서 부서번호가 10인 사원의 사원번호,사원이름,급여,부서이름을 검색하여 EMP_VIEW10 뷰 생성

CREATE VIEW EMP_VIEW10 AS SELECT EMPNO,ENAME,SAL,DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;--에러 발생

>> 기존의 객체와 같은 이름의 뷰를 생성할 경우 에러 발생 
 
CREATE OR REPLACE로 기존뷰를 새로운 뷰로 대체(뷰 변경)하면 에러가 발생하지 않는다.

CREATE OR REPLACE VIEW EMP_VIEW10 AS SELECT EMPNO,ENAME,SAL,DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

 
뷰를 생성하지 않고 SELECT 명령의 서브쿼리를 사용하여 인라인뷰를 생성해 사용 - CREATE VIEW 시스템 권한이 없어도 사용 가능 

SELECT * FROM (SELECT EMPNO,ENAME,SAL,DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO);

 

뷰 삭제

• 형식) DROP VIEW 뷰이름

 
EMP_VIEW30 뷰 삭제 

DROP VIEW EMP_VIEW30;

---

시퀀스(SEQUENCE)

• 숫자값(정수값)을 저장하여 자동 증가되는 값을 제공하는 객체 
• 테이블 컬럼에 고유값을 제공한다.

시퀀스 생성 

• 형식) 

CREATE SEQUENCE 시퀀스명 [START WITH 초기값] [INCREMENT BY 증가값] [MAXVALUE 최대값]
[MINVALUE 최소값] [CYCLE] [CACHE 갯수]

• START WITH 초기값 : 시퀀스에 저장된 초기값 설정 - 생략 시 기본값 : NULL
• INCREMENT BY 증가값 : 자동 증가되는 숫자값 설정 - 생략 시 기본값 : 1 
• MAXVALUE 최대값 : 시퀀스에 저장된 저장 가능한 최대값 설정 - 생략 시 기본값 : 오라클에서 숫자값으로 표현 가능한 최대값 
• MINVALUE 최소값 : 시퀀스에 저장된 저장 가능한 최소값 설정 - 생략 시 기본값 : 1
• CYCLE : 시퀀스에 저장된 값이 최대값을 초과할 경우 최소값부터 다시 제공되도록 반복하는 기능을 제공
• CACHE 갯수 : 임의의 저장 공간에 자동 증가값을 미리 생성하여 제공할 수 있는 갯수 설정 - 생략 시 기본값 : 20 

 
USER2 테이블 생성 - 회원번호(숫자형-PRIMARY KEY),회원이름(문자형),생년월일(날짜형)

CREATE TABLE USER2(NO NUMBER(2) CONSTRAINT USER2_NO_PK PRIMARY KEY,NAME VARCHAR2(20),BIRTHDAY DATE);

DESC USER2;

USER2 테이블의 NO 컬럼값으로 저장되기 위한 자동 증가값을 제공하는 USER2_SEQ 시퀀스 생성

CREATE SEQUENCE USER2_SEQ;

 
USER_SEQUENCES
: 시퀀스 정보를 제공하는 딕셔너리 

시퀀스 확인

SELECT SEQUENCE_NAME,MAX_VALUE,MIN_VALUE,INCREMENT_BY FROM USER_SEQUENCES;

 
시퀀스에 저장된 숫자값 확인 

• 형식) 시퀀스명.CURRVAL

SELECT USER2_SEQ.CURRVAL FROM DUAL;--시퀀스에 NULL이 저장되어 있으므로 에러 발생

 
 시퀀스에 저장된 숫자값을 이용하여 증가된 값을 제공하는 방법 

• 증가된 값 제공후 시퀀스는 증가된 값으로 자동 변경된다.
• 시퀀스에 NULL이 저장되어 있는 경우 시퀀스의 최소값을 제공한 후 시퀀스의 저장값 변경 처리 
• 형식) 시퀀스명.NEXTVAL

SELECT USER2_SEQ.NEXTVAL FROM DUAL;--검색결과 : 1 - 시퀀스에 저장값은 1로 변경 
SELECT USER2_SEQ.CURRVAL FROM DUAL;--검색결과 : 1 
SELECT USER2_SEQ.NEXTVAL FROM DUAL;--검색결과 : 2 - 시퀀스에 저장값은 2로 변경 
SELECT USER2_SEQ.CURRVAL FROM DUAL;--검색결과 : 2

NEXTVAL을 쓸 때 마다,시퀀스에 저장된 값보다 증가된 값이 저장되고 시퀀스에 저장된 값이 변경처리 된다. 
 
USER2 테이블에 행 삽입 - NO 컬럼에는 시퀀스로부터 자동 증가값을 제공받아 삽입 처리

INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'홍길동','00/01/01');
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'임꺽정','00/12/31');
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'전우치',SYSDATE);

 

시퀀스 변경

• 형식) ALTER SEQUENCE 시퀸스명 {MAXVALUE|MINVALUE|INCREMENT BY} 변경값

 
USER2_SEQ 시퀀스의 최대값을 99로 변경하고 증가값을 5로 변경 

ALTER SEQUENCE USER2_SEQ MAXVALUE 99 INCREMENT BY 5;

시퀀스 확인

SELECT SEQUENCE_NAME,MAX_VALUE,MIN_VALUE,INCREMENT_BY FROM USER_SEQUENCES;

 
USER2 테이블에 행 삽입 - NO 컬럼에는 시퀸스로부터 자동 증가값을 제공받아 삽입 처리

SELECT USER2_SEQ.CURRVAL FROM DUAL;--검색결과 : 5
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'일지매','03/09/09');

SELECT * FROM USER2;

SELECT USER2_SEQ.CURRVAL FROM DUAL;--검색결과 : 10

 

 

시퀸스 삭제

• 형식) DROP SEQUENCE 시퀸스명

USER2_SEQ 시퀸스 삭제

DROP SEQUENCE USER2_SEQ;

시퀀스 확인

SELECT SEQUENCE_NAME,MAX_VALUE,MIN_VALUE,INCREMENT_BY FROM USER_SEQUENCES;