8. 뷰 및 시퀀스, 시노님
■ 뷰 및 시퀀스, 시노님
※ 뷰(VIEW)
: 데이터를 가지고 있지 않은 가상의 테이블( = 논리적인 테이블)
: 쿼리만 저장 쿼리만 가지고 있는 가상의 테이블
: 뷰를 만들수 있는 권한이 있어야 뷰를 만들 수 있다.
: 권한이 없는 경우 다음의 에러가 발생
ORA-01031: 권한이 불충분합니다.
: 뷰를 통해서만 데이터 접근 함으로써 보안적인 부분에서 안전하게 데이터를 보호할 수 있다.
: 조인 불가.
bPrice *qty amt => 수식이 있는 경우 별명만 주면 문제가 생기지 않음 -> 기본내용에 추가
-------------------------------------------------------
▼ 뷰 만들기
--1 sky 계정의 권한 확인
SELECT * FROM user_sys_privs;
--2 뷰 만들기
CREATE VIEW empView
AS
SELECT empNo, name,TO_DATE(SUBSTR(rrn, 1, 6),'RRMMDD') birth,
DECODE(MOD(SUBSTR(rrn,8,1),2),0, '여자','남자') gender
FROM emp;
=> 에러발생 : 뷰를 만들 수 있는 권한이 없다.
-------------------------------------------------------
-- 3 관리자 계정 (sys / system)
-- sky 게정에게 뷰를 만들 수 있는 시스템 권한 부여
GRANT CREATE VIEW TO sky;
-------------------------------------------------------
-- 4 sky 계정 : 권한 확인
SELECT * FROM user_sys_privs;
-- 5 뷰를 이용하여 판매현황뷰 만들기
SELECT [테이블명1.]컬럼명, [테이블명2.]컬럼명 ....
FROM 테이블명1, 테이블명2
WHERE 테이블명1.컬럼명 = 테이블명2.컬럼명 [AND 조건]
-- 판매현황 : 책코드(bCode), 책이름(bName), 책가격(bPrice), 출판사번호(pNum),
출판사이름(pName), 판매일자(sDate), 판매고객번호(cNum),
판매고객이름(cName), 판매수량(qty), 금액(bPrice * qty)
SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName,
qty
FROM book b
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum;
CREATE VIEW panmai
AS
SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName,
qty
FROM book b
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum;
SELECT * FROM panmai;
-------------------------------------------------------
-- 6 뷰 확인
SELECT * FROM tab;
--TAbTABLE : VIEW
SELECT * FROM col WHERE tname = 'panmai'; => 컬럼목록확인1
DESC panmai; => 컬럼목록확인2
SELECT view_name, text FROM user_views;
-- 소스확인
-------------------------------------------------------
▼ 뷰 수정
-- OR REPLACE : 없으면 만들고, 있으면 수정
CREATE OR REPLACE VIEW panmai
AS
SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName,
qty, bPrice *qty amt
FROM book b
JOIN pub p ON b.pNum = p.pNum
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.sNum = s.sNum
JOIN cus c ON s.cNum = c.cNum;
SELECT * FROM panmai;
SELECT bCode, bName, SUM(amt)
FROM panmai
GROUP BY bCode, bName;
-------------------------------------------------------
-- emp
CREATE OR REPLACE VIEW empView
AS
SELECT empNo, name,
DECODE(MOD(SUBSTR(rrn,8,1),2),0,'여자','남자') gender,
TO_DATE(
CASE
WHEN SUBSTR(rrn,8,1) IN (1,2,5,6) THEN '19'
WHEN SUBSTR(rrn,8,1) IN (3,4,7,8) THEN '20'
END || SUBSTR(rrn,1,6),'YYYYMMDD'
) birth,
hireDate, sal, bonus, sal+bonus tot_pay,
TRUNC(
CASE
WHEN sal+bonus >= 3000000 THEN 0.03
WHEN sal+bonus >= 3000000 THEN 0.02
ELSE 0
END * (sal+bonus), -1
) tax
FROM emp;
SELECT * FROM empView;
SELECT empNo, name, gender, birth,
TRUNC(MONTHS_BETWEEN(SYSDATE, birth)/12) age
FROM empView;
-------------------------------------------------------
-- 뷰를 이용한 레코드 추가 삭제
CREATE TABLE test1 (
id VARCHAR2(30) NOT NULL,
name VARCHAR2(30) NOT NULL,
memo VARCHAR2(1000)
);
ALTER TABLE test1 ADD PRIMARY KEY(id);
CREATE TABLE test2(
num NUMBER PRIMARY KEY,
id VARCHAR2(30) NOT NULL,
score NUMBER(3) NOT NULL,
FOREIGN KEY(id) REFERENCES test1(id)
);
INSERT INTO test1(id, name, memo) VALUES ('a','aaa','aaaaa');
INSERT INTO test1(id, name, memo) VALUES ('b','bbb','bbbbb');
INSERT INTO test1(id, name, memo) VALUES ('c','ccc','ccccc');
INSERT INTO test2(num, id, score) VALUES ('1','a', 80);
INSERT INTO test2(num, id, score) VALUES ('2','b', 90);
COMMIT;
-- 뷰 만들기 : 복합 뷰
CREATE VIEW testView1
AS
SELECT t1.id, num, name, score, memo
FROM test1 t1
JOIN test2 t2 ON t1.id = t2.id;
SELECT * FROM testView1;
INSERT INTO testView1(id, name, memo) VALUES ('d', 'ddd', NULL);
-- 에러 : 복합뷰는 추가, 수정, 삭제 불가
-- 뷰 만들기 : 단순 뷰
CREATE VIEW testView2
AS
SELECT id, name
FROM test1;
SELECT * FROM testView2;
INSERT INTO testView2(id, name) VALUES ('d', 'ddd');
-- 제약조건을 위반하지 않으면 단순뷰는 추가, 수정등이 가능
-------------------------------------------------------
-- 뷰 삭제 : 테이블과 테이블의 데이터는 지워지지 않음
DROP VIEW testView1;
SELECT * FROM tab;
DROP VIEW testView2;
-- test1 테이블 삭제
DROP TABLE test1 CASCADE CONSTRAINTS PURGE;
=> 자식테이블이 존재해도 관계를 제거하고 삭제
DROP TABLE test2 PURGE;
※ 시퀀스(sequence)
-- 연속적인 유일의 정수값 생성(1, 2, 3, ...)
-- 시퀀스 값을 기본키(자동으로 생성)의 값으로 사용 할 수 있다.
-- 트랜잭션의 커밋 또는 롤백과 상관없이 시퀀스는 증가한다.
-- 12C 이상부터는 테이블 생성시 DEFAULT 값으로 시퀀스 값을 할당 할 수 있다.
-- 시퀀스 목록 확인
SELECT * FROM seq;
-- 시퀀스 값 가져오기
시퀀스이름.NEXTVAL : 다음 시퀀스 값
시퀀스이름.CURRVAL : 현재 시퀀스 값
-------------------------------------------------------
--
-- 1부터 1씩 증가하는 시퀀스 만들기
CREATE SEQUENCE test_seq => 이것만 써도 생성 가능. 하기 옵션은 선택사항
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;
-- 시퀀스 확인
SELECT * FROM user_sequences;
SELECT * FROM seq; => 이걸 주로 사용
-- 시퀀스의 다음 값 가져오기
SELECT test_seq.NEXTVAL FROM dual; --1 => 이 숫자는 계속 증가. 시퀀스는 유일하기 때문
SELECT test_seq.NEXTVAL FROM dual; --2
SELECT test_seq.NEXTVAL FROM dual; --3
-- 시퀀스의 현재 값
SELECT test_seq.CURRVAL FROM dual; --3
-- 동일한 SELECT, INSERT에서 NEXTVAL을 여러번 사용해도 동일한 값이 출력되거나 추가됨
SELECT test_seq.NEXTVAL, test_seq.NEXTVAL, test_seq.NEXTVAL FROM dual; -- 4 4 4
-- 시퀀스 삭제
DROP SEQUENCE 시퀀스 이름;
DROP SEQUENCE test_seq;
SELECT * FROM seq;
-- 시퀀스 이용
CREATE TABLE guest (
num NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
content VARCHAR2(4000) NOT NULL,
reg_date DATE DEFAULT SYSDATE
);
CREATE SEQUENCE guest_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;
INSERT INTO guest(num, name, content, reg_date)
VALUES (guest_seq.NEXTVAL, '송유댕', '안농', SYSDATE);
INSERT INTO guest(num, name, content, reg_date)
VALUES (guest_seq.NEXTVAL, '조벙구', '똥개', SYSDATE);
INSERT INTO guest(num, name, content, reg_date)
VALUES (guest_seq.NEXTVAL, '조벙만', '멍멍', SYSDATE);
SELECT * FROM guest;
ROLLBACK;
SELECT * FROM guest;
INSERT INTO guest(num, name, content, reg_date)
VALUES (guest_seq.NEXTVAL, '송유댕', '바이', SYSDATE);
COMMITl
SELECT * FROM guest;
--num 이 4로 나옴
-- 지나간 시퀀스는 다시 돌아 오지 않음
-------------------------------------------------------
-- 1부터 증가하는 시퀀스. 기본 20개 캐시 : 20개를 미리 만들어 놓음. 만약 현재 시퀀스가 3인 상태에서
오라클 서버가 재실행하면 다음 시퀀스는 21번이 됨
CREATE SEQUENCE test_seq;
SELECT * FROM seq;
-------------------------------------------------------
-- 10~20 까지 2씩 증가하는 시퀀스. 캐시 5개. 20 넘으면 오류
CREATE SEQUENCE test_seq
INCREMENT BY 2
START WITH 10
MINVALUE 10
MAXVALUE 20
CACHE 5;
SELECT test_seq.NEXTVAL FROM dual; => 횟수를 초과하면 오류가 생김
DROP SEQUENCE test_seq;
-- 10부터 시작하여 20까지 3씩 증가하는 시퀀스. 캐시 5개. 최소값 1, 최대값 도달하면 처음부터
CREATE SEQUENCE test_seq
INCREMENT BY 3
START WITH 10
MINVALUE 1
MAXVALUE 20
CYCLE
CACHE 5;
SELECT test_seq.NEXTVAL FROM dual; => 횟수를 초과하면 다시 돌아옴
DROP SEQUENCE test_seq;
※ 시노님(synonym) - 관리자 영역
-------------------------------------------------------
- 객체 테이블에게 주는 이름을 시노님이라고 한다
- 동의어 객체 사용
- PRIVATE : 개별 사용자를 위한 비공개 동의어
- PUBLIC : 공용 사용자 그룹이 소유 모든사용자가 공유
권한이 있어야 한다
-- sky계정 : hr계정의 jobs 테이블의 값을 확인
SELECT * FROM jobs; -- sky계정의 jobs 테이블이 없음
SELECT * FROM hr.jobs; --에러. 테이블이 없다고 나옴. hr의 jobs테이블을 SELECT 할 수 있는 권한이 없다.
-- hr계정 : sky계정에 jobs 테이블을 SELECT 할 수 있는 권한 부여
1) hr 계정으로 이동
GRANT SELECT ON jobs TO sky;
2) sky 계정으로 이동
SELECT * FROM hr.jobs; --> 권한을 부여받은 뒤에는 조회 가능
-------------------------------------------------------
-- 관리자 계정 : sys
-- sky계정에서 시노님 작성권한 부여
GRANT CREATE SYNONYM TO sky;
-------------------------------------------------------
-- sky 계정
-- 시스템 권한 확ㅇ니
SELECT * FROM user_sys_privs;
-- hr.jobs를 jobs라는 시노님 작성
CREATE SYNONYM jobs FOR hr.jobs;
SELECT * FROM jobs;
-- 시노님 목록 확인
SELECT * FROM syn;
-- 시노님 삭제
DROP SYNONYM jobs;
SELECT * FROM syn;