쌍용교육 -SQL 수업 20~21일차 (뷰,시퀀스,인덱스,동의어,권한)

뷰

뷰(VIEW) : 논리적으로 하나 이상의 테이블에 있는 데이터의 부분 집합.
 -데이터 엑세스를 제한하기 위해
 -복잡한 질의를 쉽게 작성하기 위해
 -데이터 독립성을 제공하기 위해
 -동일한 데이터로부터 다양한 결과를 얻기 위해
 * 뷰는 가상으로 만들어진 컬럼(Virtual Column)을 제외하면 수정이 가능하고 삭제도 가능함.
    수정하고 삭제하면 원래 테이블에 반영,삽입은 여러 제약 조건과 virtual column 사용으로 제약이 많음.
 
 
 VIEW 생성
 CREATE OR REPLACE VIEW emp10_view--없으면 만들고 있으면 수정해라
 AS SELECT empno id_number, ename name, sal*12 ann_salary
 FROM emp
 WHERE deptno = 10;
 
 SELECT * FROM emp10_view;   
 
 CREATE OR REPLACE VIEW emp_info_view
 AS SELECT e.empno, e.ename,d.deptno,d.loc,d.dname
 FROM emp e, dept d 
 WHERE e.deptno = d.deptno;
 
 SELECT * FROM emp_info_view;
 
 VIEW를 통한 데이터 변경하기
 일반적으로 view는 조회용으로 많이 사용되지만 아래와 같이 데이터를 변경할 수 있음.
UPDATE emp10_view SET name = 'SCOTT' WHERE id_number = 7839; 

INSERT INTO emp10_view(id_number,name,ann_salary)
VALUES (8000,'JOHN',19000); --오류 : 가상 열 때문에 등록이 제한됨, 가상 열을 제외하면 삽입 가능 

 SELECT * FROM emp10_view;
 
 WITH READ ONLY : 읽기전용 뷰를 생성하는 옵션;
 
 CREATE OR REPLACE VIEW emp20_view
 AS SELECT empno id_number, ename name,sal*12 ann_salary
 FROM emp
 WHERE deptno = 20
 WITH READ ONLY;
 
SELECT * FROM emp20_view;

UPDATE emp20_view SET name = 'DAVID' WHERE id_number = 7902; --에러:읽기전용 뷰에는 DML 작업을 수행할 수 없습니다.

VIEW의 수정
CREATE OR REPLACE VIEW emp10_view
(id_number,name,sal,department_id)--sal,department_id 2개를 추가하고 보여줘라
AS SELECT empno,ename,sal,deptno
FROM emp
WHERE deptno = 10;
 SELECT * FROM emp10_view;
 
 VIEW의 삭제
 DROP VIEW emp10_view;
 
 SEQUENCE : 유일한 값을 생성해주는 오라클 객체
                   시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할 수 있음. 
                   보통 PRIMARY KEY 값을 생성하기 위해 사용

시퀀스

시퀀스 생성
CREATE SEQUENCE test_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 100000;
--시작값이 1이고 1씩 증가하고 최대값이 100000이 되는 시퀀스를 생성

CURRVAL : 현재 값을 반환
NEXTVAL : 현재 시퀀스 값의 다음 값 반환

--SELECT test_seq.currval FROM dual; --에러가 나는 이유: nextval보다 먼저 썼기 때문시퀀스가 생성되긴 했지만 데이터가 정의되어있지 않기 때문 NEXTVAL을 먼저 실행해서 시작을 해야됨..

SELECT test_seq.nextval FROM dual;
SELECT test_seq.currval FROM dual; 

sboard 테이블에 데이터를 삽입할 때 시퀀스 활용

INSERT INTO sboard (num,id,content)
VALUES(test_seq.nextval,'tmdghl','여기서는 강남');

SELECT * FROM sboard;   

시퀀스 수정
START WITH는 수정할 수 없음(수정하려면 아예삭제하고 다시 해야됨)

ALTER SEQUENCE test_seq
INCREMENT BY 5;

시퀀스 삭제
DROP SEQUENCE test_seq;

인덱스

인덱스(INDEX) : 인덱스는 데이터 검색을 빨리하기 위해 사용한다.
테이블이 컬럼에 대한 제약조건을 설정할 때 PRIMARY KEY나 UNIQUE로 설정하면 ORACLE은 자동으로 이 컬럼에 대해 UNIQUE로 설정한다.

인덱스 만들기
- 유일한 값을 가지는 컬럼에 인덱스 설정 : UNIQUE INDEX
CREATE UNIQUE INDEX dname_idx ON dept(dname);

-유일한 값을 가지지않는 컬럼에 인덱스 설정 : NON UNIQU E INDEX
CREATE INDEX emp_ename_idx ON emp(ename);

동의어

동의어(객체의 다른 이름) 을생성하여 객체 엑세스를 단순화

동의어 생성
CREATE SYNONYM emp20 FOR emp2_view; --emp2_view가 없으면 에러
SELECT * FROM emp20;

동의어 삭제
DROP SYNONYM emp20;

권한

conn sys/1234 as sysdba

create user user02 identified by 1234;

grant resource, connect, dba to user02;

alter user user02 identified by 5678;

exit ->를 쓰면 cmd창이 나가진다.