VIEW(뷰)
- scott계정에 뷰 생성 권한을 부여하기
cmd -> sqlplus / as sysdba
GRANT CREATE VIEW TO scott; - 뷰 생성하기
CREATE OR REPLACE VIEW v_st_prof AS SELECT s.name sname, s.deptno1, p.name profname, d.dname FROM student s, professor p, department d WHERE s.profno = p.profno AND s.deptno1 = d.deptno; - 뷰는 원래의 테이블을 바라보고 있는 상태이기 때문에
원래의 테이블에 새로운 데이터가 입력이 되면 뷰에서도 추가된 데이터를 확인할 수 있다.
SEQUENCE(시퀀스)
데이터베이스에 값을 입력할 때 숫자가 자동으로 증가하여 입력되기를 원하는 경우에 시퀀스를 사용한다.
- 시퀀스 생성하기
CREATE SEQUENCE T_ITEM_LIST_PK_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;SYNONYM(동의어)
- 다른 계정이 생성한 테이블을 사용할 경우 테이블오너.테이블명으로 사용해야 하는데 번거롭기 때문에 별명을 생성해서 접근하기 쉽게하는 용도로 사용한다.
- SYNONYM 생성하기
CREATE PUBLIC SYNONYM order_history FOR scott.order_history;--VIEW
SELECT * FROM t_member_point;
INSERT INTO t_member_point VALUES (3, 1, 'new', 100, SYSDATE);
--scott계정에 VIEW 생성 권한 부여
-- cmd -> sqlplus / as sysdba
-- GRANT CREATE VIEW TO scott;
CREATE OR REPLACE VIEW v_member_point1
AS
SELECT id, no, member_id
FROM t_member_point;
--원 테이블에 새로운 데이터를 입력하면 뷰에서도 데이터가 추가된 것을 확인할 수 있다.
--뷰는 원테이블 자체를 바라보고 있는 상태이기 때문에.
SELECT * FROM v_member_point1;
CREATE OR REPLACE VIEW v_st_prof
AS
SELECT s.name sname, s.deptno1, p.name profname, d.dname
FROM student s, professor p, department d
WHERE s.profno = p.profno
AND s.deptno1 = d.deptno;
SELECT * FROM v_st_prof;
--VIEW에 대한 정보를 확인
SELECT * FROM user_views;
--student 테이블이 관여된 뷰를 확인한다.
SELECT * FROM user_views
WHERE text_vc like '%student%';
SELECT * FROM user_views
WHERE text_vc like '%t_member_point%';
--
select * from user_source;
--VIEW 연습문제
--1. professor, department
--교수번호, 교수이름 , 소속 학과이름을 조회하는 view를 생성
--view 이름은 v_prof_dept2
CREATE OR REPLACE VIEW v_prof_dept2
AS
SELECT p.profno, p.name, d.dname
FROM professor p, department d
WHERE P.deptno = d.deptno;
SELECT * FROM v_prof_dept2;
--2. student, department 학과별로 학생들의 최대 키와 최대 몸무게, 학과이름을 출력
SELECT d.dname, a.maxheight, a.maxweight
FROM (SELECT deptno1, MAX(height) maxheight, MAX(weight) maxweight
FROM student
GROUP BY deptno1) a, department d
WHERE a.deptno1 = d.deptno;
--3. student, department
--학과이름, 학과별 최대키, 학과별로 가장 키가 큰 학생들의 이름과 키
--인라인뷰
SELECT c.dname, a.maxheight, b.name, b.height
FROM (
SELECT deptno1, MAX(height) maxheight
FROM student
GROUP BY deptno1 ) a, student b, department c
WHERE a.deptno1 = b.deptno1 AND a.maxheight = b.height
AND a.deptno1 = c.deptno;
--스칼라 서브 쿼리
SELECT (SELECT dname FROM department d WHERE d.deptno = a.deptno1), a.maxheight, b.name, b.height
FROM (
SELECT deptno1, MAX(height) maxheight
FROM student
GROUP BY deptno1 ) a, student b
WHERE a.deptno1 = b.deptno1 AND a.maxheight = b.height;
SELECT d.dname, s.height max_height, s.name, s.height
FROM student s, department d
WHERE (s.deptno1, s.height) IN(SELECT deptno1, MAX(height) height
FROM student
GROUP BY deptno1)
AND s.deptno1 = d.deptno;
--4. student
--학생의 키가 동일 학년의 평균 키 보다 큰 학생들의 학년과 이름과 키, 해당 학년의 평균키
SELECT a.grade, s.name, s.height, a.avgheight
FROM (SELECT grade, AVG(height) avgheight
FROM student
GROUP BY grade) a, student s
WHERE s.grade = a.grade
AND s.height > a.avgheight
ORDER BY grade;
--5. professor
--교수들의 급여순위와 이름과 급여를 출력, 급여순위 1~5위까지
SELECT ranking, name, pay
FROM (SELECT RANK() OVER(ORDER BY pay DESC) ranking, name, pay
FROM professor)
WHERE ranking <= 5;
SELECT name, pay,
RANK() OVER(ORDER BY pay DESC) rank
FROM professor
WHERE rank <= 5;
SELECT ROWNUM, name, pay
FROM(
SELECT name, pay
FROM professor
ORDER BY pay DESC)
WHERE ROWNUM <= 5;
--6. 교수번호로 정렬한 후 3건씩 묶어서 합계와 평균을 출력
SELECT CEIL(ROWNUM/3),
ROWNUM, profno, name, pay,
SUM(pay), ROUND(AVG(pay), 1)
FROM (SELECT profno, name, pay
FROM professor
ORDER BY profno
)
GROUP BY CEIL(ROWNUM/3), ROLLUP( (ROWNUM, profno, name, pay) );
--SEQUENCE
--자동으로 카운트가 증가하면서 데이터가 입력이 되는 것을 원하는 경우
CREATE TABLE order_history
(
no NUMBER(4),
total number(10),
order_date DATE DEFAULT SYSDATE
);
ALTER TABLE order_history
ADD PRIMARY KEY(no);
SELECT count(*)+1 FROM order_history;
SELECT * FROM order_history;
INSERT INTO order_history VALUES (1, 123, SYSDATE);
INSERT INTO order_history VALUES (2, 456, SYSDATE);
INSERT INTO order_history VALUES ( (SELECT count(*)+1 FROM order_history), :total, SYSDATE);
--데이터가 지워질 수도 있는 상황에서는 위와 같이 쓸 수 없다.
--대신 컬럼을 추가해서 유효한 데이터인지 구분할 수 있다.(STATE 1:정상, 2:무효)
DELETE FROM order_history WHERE no = 5;
--중간에 데이터가 삭제되어도 문제 없이 데이터가 입력된다.
INSERT INTO order_history VALUES ( (SELECT MAX(no)+1 FROM order_history), :total, SYSDATE);
--DELETE
SELECT * FROM order_history;
--데이터가 없는 상태에서도 입력할 수 있다.
--참조하는 테이블이 있으면 데이터가 삭제가 되었을 때 참조하는 쪽에서도 데이터가 같이 수정되거나 하는
--처리의 문제가 있다.
INSERT INTO order_history VALUES ( (SELECT NVL(MAX(no), 0) + 1 FROM order_history), :total, SYSDATE);
--시퀀스 생성
CREATE SEQUENCE order_history_seq
INCREMENT BY 1
START WITH 1;
--값이 호출될 때마다 값이 증가한다.
SELECT order_history_seq.nextval
FROM dual;
--시퀀스의 현재 값 조회
SELECT order_history_seq.currval
FROM dual;
--시퀀스의 값을 1부터 다시 시작하고 싶은 경우
ALTER SEQUENCE order_history_seq MINVALUE 0;
ALTER SEQUENCE order_history_seq INCREMENT BY -6;
ALTER SEQUENCE order_history_seq INCREMENT BY 1;
--값이 호출되어도 변경되지 않게끔 시퀀스 값을 조회.
--LAST_NUMBER
SELECT *
FROM user_sequences;
INSERT INTO order_history VALUES ( order_history_seq.nextval, :total, SYSDATE);
SELECT * FROM order_history;
--SYNONYM
--권한 부여하기
GRANT CREATE SYNONYM TO scott;
GRANT CREATE PUBLIC SYNONYM TO scott;
SELECT *
FROM ALL_TABLES
WHERE OWNER = 'SCOTT';
--다른 계정이 다른오너.테이블명으로 사용하기 번거로울때
--별명을 부여해서 사용한다.
--원테이블명과 헷갈리기때문에 가급적 사용하지 않는 것이 좋다.
--SELECT * FROM scott.order_history;
SELECT * FROM order_history;
CREATE SYNONYM my_oh FOR order_history;
SELECT * FROM my_oh;
--SYNONYM 삭제
DROP SYNONYM my_oh;
--scott계정이 생성한 order_history에 대한 SYNONYM 생성
--scott계정이 아닌 다른 계정으로 접근할 때 편하게 하기 위해서 SYNONYM을 생성
CREATE PUBLIC SYNONYM order_history FOR scott.order_history;
GRANT DROP PUBLIC SYNONYM TO scott;
DROP PUBLIC SYNONYM order_history;
--SEQUENCE 연습문제
--1. 아래 조건에 테이블 생성
* item_name 은 null 불가
* create_date 는 기본값 현재
* PK 는 no
T_ITEM_LIST 테이블
no NUMBER(6)
item_name VARCHAR2(24)
price NUMBER(6)
create_date DATE
CREATE TABLE T_ITEM_LIST
(
no NUMBER(6) PRIMARY KEY,
item_name VARCHAR2(24) NOT NULL,
price NUMBER(6),
create_date DATE DEFAULT SYSDATE
);
/*
2. 다음 조건에 맞는 시퀀스를 생성하시오.
시퀀스명 : T_ITEM_LIST_PK_SEQ
*상세조건
1부터 시작하며 1씩 증가한다.
값의 범위는 1~999999
순환하지 않도록 한다.
*/
CREATE SEQUENCE T_ITEM_LIST_PK_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;
/*
3. 생성한 시퀀스의 값을 불러서 사용하는 방법을 작성하시오.
*/
SELECT T_ITEM_LIST_PK_SEQ.nextval
FROM DUAL;
SELECT T_ITEM_LIST_PK_SEQ.currval
FROM DUAL;
/*
4. 해당 시퀀스를 활용하여, 테이블에 INSERT 처리 하는 쿼리문을 작성하시오.
*/
INSERT INTO T_ITEM_LIST (no, item_name, price, create_date) VALUES (T_ITEM_LIST_PK_SEQ.nextval, :item_name, :price, SYSDATE);
SELECT * FROM T_ITEM_LIST;
--시퀀스의 번호가 순차적이지 않을 경우에 쓸 수 있는 방법
SELECT ROWNUM, no, item_name, price, create_date
FROM (
SELECT no, item_name, price, create_date
FROM T_ITEM_LIST
ORDER BY no );
--하지만 pk값은 알고 있어야 한다.
SELECT *
FROM t_item_list
--WHERE item_name = '모니터';
WHERE no = 4;