오늘의 코딩순서 + 코딩 포인트
1. VIEW
1. 뷰(view)란?
- 하나 이상의 기본 테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블
- 가상테이블?
- 테이블은 디스크에 공간이 할당되어 데이터를 저장, 물리적으로 테이블에 저장하는 것은 아님
- 하지만 전혀 저장하지 못하는 것은 아님, 실체화되는 뷰도 있다
↳ Materialized view: 물리적으로 존재하는 테이블에 저장되는 뷰, (많이 사용되지는 않음)
어떤 결과를 뽑아 내는 쿼리가 너무나도 빈번히 사용 될 경우, Query 실행 시간의 수행속도 향상을위하여 , 여러 가지의 Aggregate View를 두어, 미리 비용이 많이 드는 조인이나, Aggregate Operation 을 처리하여야 하는 SQL을 위해, 데이터베이스의 한 테이블로 저장 하며, 그 테이블을 조회 하도록 하는 것
- 하지만 전혀 저장하지 못하는 것은 아님, 실체화되는 뷰도 있다
- 뷰는 데이터 딕셔너리 테이블에 뷰에 대한 정의만 저장
- 디스크 저장공간 할당이 안됨
- 테이블은 디스크에 공간이 할당되어 데이터를 저장, 물리적으로 테이블에 저장하는 것은 아님
- 가상테이블?
- 전체 데이터중에서 일부만 접근할 수 있도록 제한
- 테이블에서 파생된 객체 테이블과 유사하게 사용
- 장점
- ⭐⭐⭐Security(보안)을 위해 사용, 전체의 데이터 중에서 일부만 접근할 수 있도록 제한
ex) 교수 테이블에서 급여나 보직수당은 개인적인 정보이므로 학생들의 접근을 제한하기 위해 뷰를 사용함 - 사용자 편의성(flexibility)
- 사용자의 인터랙션(상호작용)을 최대한 포용하고, 실수를 방지할 수 있도록 제작
- 오류예방(Error Prevention), 실수포용(Forgiveness), 오류 감지(Error Detectability)
- ex) 교수 테이블에서 급여와 보직수당을 제외한 정보를 뷰로 만들어서 학생들에게 제공
- (실무관점) 고급기술자가 초급기술자 SQL 능력을 Cover할 수 있음, 초급기술자는 고급기술자의 기술을 배울 수 있음
- ⭐⭐⭐Security(보안)을 위해 사용, 전체의 데이터 중에서 일부만 접근할 수 있도록 제한
- 단점: 성능(performance)은 더 저하됨(처리속도 등)
- 뷰에 대한 수정결과는 뷰를 정의한 기본 테이블에 적용
- 뷰를 정의한 기본 테이블에서 정의된 무결성 제약 조건 상속
2. view 생성 및 사용법
- CREATE 명령문 사용
- 뷰 생성시 칼럼 이름을 명시하지 않으면 기본 테이블의 칼럼 이름을 상속
- 함수나 표현식에 의해 정의된 칼럼은 별도로 이름을 명시(별명 사용)
CREATE OR REPLACE( FORCE/NOFORCE) VIEW view_tbl_name
AS
SELECT [(alias1 , alias2, ...)], SUBQUERY...
FROM TBL NAME
;- OR REPLACE: 기존 뷰와 동일한 이름으로 뷰를 재생성하는 경우
- FORCE: 기본 테이블의 존재 여부에 상관없이 뷰 생성
- NOFORCE; 기본 테이블이 존재할 경우에만 뷰 생성, 기본 값
3. view의 종류
1. 단순 뷰(simple view)
- 하나의 기본 테이블에 의해 정의한 뷰
- 단순 뷰에 DML 명령문을 실행하면 기본 테이블에 반영됨
- 제약조건이 없으면 VIEW를 통한 입력이 가능함, 반대로 제약조건이 있다면 VIEW를 통한 입력이 불가능함
CREATE OR REPLACE VIEW view_PROFESSOR
AS
SELECT profno , name , userid , position , hiredate , deptno
--sal,comm은 뺌: 보안을 위해 + 학생들 제공용으로 만들기 위해
FROM professor
;
SELECT * FROM VIEW_PROFESSOR; --조회하는 순간 PROFESSOR가 받아서 전체적으로 실행
INSERT INTO view_professor VALUES(2000, 'view' , 'userid' , 'position' , sysdate,101);
--제약조건에 걸리지 않는다면 뷰를 통한 입력 가능INSERT INTO view_professor (profno , userid , position , hiredate , deptno)
VALUES(2001,'userid2','position2','sysdate,101');
-- PROFESSOR TBL 편집>> 열>> NAME에 NULL체크하기>> 실행
--name에 제약조건 not null이 있는데 name을 입력하지 않아서 에러!
문제 1-1. VIEW 이름 v_emp_sample : emp(empno , ename , job, mgr,deptno)
CREATE OR REPLACE VIEW v_emp_sample
AS
SELECT empno , ename , job , mgr , deptno
FROM emp
;INSERT INTO v_emp_sample (empno , ename , job , mgr , deptno)
VALUES(2001,'userid2','position2',7839,10);
--EMP TBL에는 기본키와 외래키뿐, 제약조건이 없었음
--제약조건이 없다면 VIEW를 통한 입력이 가능함
2. 복합 뷰(complex view)
- 두 개 이상의 기본 테이블로 구성한 뷰
- 무결성 제약조건, 표현식, GROUP BY절의 유무에 따라 DML명령문의 제한적 사용
- DISTINCT, 그룹함수, GROUP BY, START WITH CONNECT BY, ROWNUM을 포함할 수 없음
- INSERT 문제가 잦아서 자주 쓰이지는 않음, INSERT를 넣을때는 위험성이 높으니 아예 쓰지 않는것을 권장함
문제 1-2. 통계뷰>> v_emp_complex(emp + dept)
CREATE OR REPLACE VIEW v_emp_complex
AS
SELECT * --e.deptno, d.deptno 넣으면 오류남
FROM emp NATURAL JOIN dept --다른 JOIN을 이용하여 세 가지 방법이 더 가능함
;
INSERT INTO v_emp_complex (empno , ename , deptno)
VALUES(1500, '홍길동', 20)
;
--SQL 오류: ORA-01776: cannot modify more than one base table through a join view
--어느 TBL의 deptno인지 알 수 없어서 나온 오류INSERT INTO v_emp_complex(empno , ename , deptno , dname , loc)
VALUES(1500, '홍길동', 77, '공무원', '낙성대')
;
--같은 오류 발생: 사실! 복합 view는 INSERT가 상황에 따라 되지 않을 때가 있다
+++ 다른 JOIN 사용
CREATE OR REPLACE VIEW v_emp_complex3
AS
SELECT e.empno , e.ename, e.job , e.deptno , d.dname , d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
; --- 다른 JOIN 사용INSERT INTO v_emp_complex3(empno , ename , deptno , dname , loc)
VALUES(1503, '홍길동3', 77, '공무팀', '낙성대');
+++ 순서바꾸기+다른 JOIN
CREATE OR REPLACE VIEW v_emp_complex4
AS
SELECT d.deptno , d.dname , d.loc , e.empno , e.name, e,job
FROM dept d, emp e
WHERE d.deptno = e.deptno
;INSERT INTO v_emp_complex4(empno , ename)
VALUES(1601, '홍길동1');
INSERT INTO v_emp_complex4(empno, ename, deptno)
VALUES(1602,'홍길동2', 20);
INSERT INTO v_emp_complex4(empno, ename, deptno , dname , loc)
VALUES(1603,'홍길동3', 77, '공무팀', '낙성대');2. 계층적 질의문 (Hierarchial Query)
1.정의
- 관계형 데이터 베이스 모델은 평면적인 2차원 테이블 구조
- 관계형 데이터 베이스에서 데이터간의 부모 관계를 표현할 수 있는 칼럼을 지정하여 계층적인 관계를 표현
- 하나의 테이블에서 계층적인 구조를 표현하는 관계를 순환관계(recursive relationship)
- 계층적인 데이터를 저장한 칼럼으로부터 데이터를 검색하여 계층적으로 출력 기능 제공
- 상위를 부모, 하위를 자식으로 연결시켜 열 위치에 의해 의사결정을 함
- 항상 현재 부모 행에 관련된 CONNECT BY 조건에 의해 자식을 선택함
2. 사용법
SELECT LEVEL , column , expression...
FROM table
WHERE conditions(s))
START WITH conditions
CONNECT BY PRIOR conditions(s)
;- SELECT 명령문에서 START WITH와 CONNECT BY 절을 이용
- 계층적 질의문에서는 계층적인 출력 형식과 시작 위치 제어
- 출력 형식은 top-down 또는 bottom-up
- 참고) CONNECT BY PRIOR 및 START WITH절은 ANSI SQL 표준이 아님, 하지만 종종 사용된다
3. Top-down과 Bottom-up과 Level
3-1. Top-down: 루트 노드부터 먼저 출력
CONNECT BY PRIOR column1 = column2
-- column1: 자식 키
-- column2: 부모 키
문제 2-1. 계층적 질의문을 사용하여 부서 테이블에서 학과,학부,단과대학을 검색하여 단대,학부, 학과순으로 top-down 형식의 계층 구조로 출력하여라. 단, 시작 데이터는 10번 부서
SELECT LEVEL , deptno , dname , college
FROM department
START WITH deptno = 10
CONNECT BY PRIOR deptno = college
;
3-2. Bottom-up: 단말 노드부터 먼저 출력
CONNECT BY PRIOR column1 = column2
-- column1: 부모 키
-- column2: 자식 키문제 2-2. 계층적 질의문을 사용하여 부서 테이블에서 학과,학부,단과대학을 검색하여 학과,학부, 단대 순으로 bottom-up 형식의 계층 구조로 출력하여라. 단, 시작 데이터는 102번 부서이다
SELECT deptno , dname , college
FROM department
START WITH deptno = 102
CONNECT BY PRIOR college = deptno
;
3-3. Level
문제 2-3) 계층적 질의문을 사용하여 부서 테이블에서 부서 이름을 검색하여 단대, 학부, 학과순의 top-down 형식으로 출력하여라. 단, 시작 데이터는 ‘공과대학’이고, 각 LEVEL(레벨)별로 우측으로 2칸 이동하여 출력
SELECT LPAD(' ',(LEVEL-1)*2) || dname 조직도
FROM department
START WITH dname = '공과대학'
CONNECT BY PRIOR deptno = college
;
4. Table Space
: 데이터베이스 오브젝트 내 실제 데이터를 저장하는 공간
데이터베이스의 물리적인 부분이며, 세그먼트로 관리되는 모든 DBMS에 대해 저장소(세그먼트segment)를 할당
tip) 1년이 지나면 병목현상이 발생한다
테이블을 분할하는 이유
- 속도가 빨라짐
- 데이터를 효과적으로 관리할 수 있음 => 때문에 보통은 업무별로 Table를 분할함
4-1. TableSpace 생성
CREATE Tablespace user1 Datafile 'C:\BACKUP\tableSpace\user1.ora' SIZE 100M;
CREATE Tablespace user2 Datafile 'C:\BACKUP\tableSpace\user2.ora' SIZE 100M;
CREATE Tablespace user3 Datafile 'C:\BACKUP\tableSpace\user3.ora' SIZE 100M;
CREATE Tablespace user4 Datafile 'C:\BACKUP\tableSpace\user4.ora' SIZE 100M;
4-2. TableSpace 수정 및 변경
#테이블의 INDEX와 Table의 테이블 스페이스 조회
SELECT INDEX_NAME, TABLE_NAME, TABLESPACE_NAME
FROM USER_INDEXES; -- 인덱스에 대한 스페이스ALTER INDEX PK_RELIGIONNO3 REBUILD TABLESPACE USER1; --인덱스 수정
↑Table과 Index↓ 분리
SELECT TABLE_NAME, TABLESPACE_NAME
FROM USER_Tables; -- 테이블에 대한 스페이스ALTER TABLE JOB3 MOVE TABLESPACE user2; --테이블 수정
4-3. TableSpace Size(용량) 변경
ALTER Database Datafile 'C:\BACKUP\tableSpace\user4.ora' RESIZE 200M;
4-4. 백업방법
1. system에 접속
2. dba 폴더 확인하여, dba와 폴더를 잇기 위해 새로 지을 명칭과 중복되는 것이 있는지 확인하기
SELECT * FROM dba_directories;
3. C드라이브에 Backup 폴더 새로 만들고, 그 안에 dmp파일 백업할 폴더 따로 만들기
>> 이거 먼저 만들어야 아래거 진행 가능!!
4. dba와 폴더를 잇기 위한 새 명칭과 백업할 폴더 경로 작성
CREATE OR REPLACE Directory mdBackupS202430101 as 'C:\BACKUP\S202430101_Backup';
5. S202430101에 백업할 수 있는 권한 주기
GRANT Read,Write On Directory mdBackupS202430101 To S202430101; --백업 권한을 S202430101에게 주겠다
6. CMD창 키고 CD와 함께 백업할 폴더명 적기
cd C:\BACKUP\S202430101Backup
7. DMP 형태로 전체 Backup하기
EXPDP S202430101/tiger Directory=mdBackupS202430101 DUMPFILE=S202430101.dmp
8. DMP 형태로 전체 Restore(복구)하기
IMPDP S202430101/tiger Directory=mdBackupS202430101 DUMPFILE=S202430101.dmp
4-5.Oracle 부분 Backup후 부분 Restore
부분 백업
exp scott/tiger file=address.dmp tables=address -- 세미콜론(;) 안들어감
부분 복구
imp scott/tiger file=address.dmp tables=address -- 세미콜론(;) 안들어감
파일만들기: 메모장으로 bat파일을 만들기>> 아래 입력후 저장후 실행하면 dmp파일이 만들어짐
- .bat파일: Batch File, 사용자 입력이나 개입없이 순서대로 처리되는 명령 모음 또는 파일 리스트
일반적으로 프로그램을 로드하거나, 한 번에 여러 프로세스를 실행하거나, 일반적이거나 반복적인 작업을 수행하는 데 사용된다 - .dmp: Dump File, 오류의 원인을 추적하기 위해 응용 프로그램이 충돌 할 때 메모리 데이터를 기록하는 파일
exp scott/tiger file=address.dmp tables=address
exp scott/tiger file=bonus.dmp tables=bonus3. TRIGGER 트리거
1. 정의
- 어떤 사건이 발생했을 때 내부적으로 실행되도록 데이터베이스에 저장된 프로시저(0703일지 참고)
- 트리거가 실행되어야 할 이벤트 발생시 자동으로 실행되는 프로시저
- 트리거링 사건(Triggering Event), 즉 오라클 DML 문인 INSERT, DELETE, UPDATE이 실행되면 자동으로 실행
2. 오라클 트리거 사용 범위
- (원칙) 데이터베이스 테이블 생성하는 과정에서 참조 무결성과 데이터 무결성 등의 복잡한 제약 조건 생성하는 경우
↳ (실무관점) but, 무결성으로 걸면 실수하는 경우 문제가 생기거나, 조건에 따라 작동이 되다 말았다 할 수 있으므로 추천하지 않음 - (추천) 데이터베이스 테이블의 데이터에 생기는 작업의 감시, 보완
- 데이터베이스 테이블에 생기는 변화에 따라 필요한 다른 프로그램을 실행하는 경우
- 불필요한 트랜잭션을 금지하기 위해
- 컬럼의 값을 자동으로 생성되도록 하는 경우
CREATE OR REPLACE TRIGGER triger_test
BEFORE -- 밑의 값을 업데이트 하기 '전'
UPDATE ON dept
FOR EACH ROW -- old, new를 사용하기 위한 명령어
BEGIN
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값 : '||:old.dname); --변경 전 dname값
DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값 : '||:new.dname); --변경 후 dname값
END
;
상단 메뉴의 보기>>DBMS 출력눌러서 하단 창으로 확인하기
UPDATE dept
SET dname = '회계3팀'
WHERE deptno = 72
;
문제 3-1. emp Table의 급여가 변화시 화면에 출력하는 Trigger 작성( emp_sal_change) (현장HW)
-- emp Table 수정전
-- 조건 : 입력시는 empno가 0보다 커야함
--출력결과 예시
-- 이전급여 : 10000
-- 신 급여 : 15000
-- 급여 차액 : 5000
>>내가 한 것
CREATE OR REPLACE TRIGGER emp_sal_change
BEFORE
UPDATE ON EMP
FOR EACH ROW
BEGIN
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('이전 급여 : '||:old.sal);
DBMS_OUTPUT.PUT.LINE('신 급여 : '||:new.sal);
END
;
UPDATE emp
SET SAL = 15000
WHERE empto =
;>>정답
CREATE OR REPLACE Trigger emp_sal_change
BEFORE DELETE OR INSERT OR UPDATE ON emp
For EACH ROW
WHEN (new.empno > 0) --사번이 minus일수는 없으니까
DECLARE
sal_diff number;
BEGIN
sal_diff := :new.sal - :old.sal;
DBMS_OUTPUT.PUT_LINE('이전 급여 : '||:old.sal);
DBMS_OUTPUT.PUT_LINE('신 급여 : '||:new.sal);
DBMS_OUTPUT.PUT_LINE('급여 차액 : '|| sal_diff);
END
;
3. Trigger로 조사하기
예시) EMP 테이블에 INSERT,UPDATE,DELETE문장이 하루에 몇 건의 ROW가 발생되는지 조사
문제 3-2. 조사 내용은 EMP_ROW_AUDIT에 ID ,사용자 이름, 작업 구분,작업 일자시간을 저장하는 트리거를 작성
1. SEQUENCE로 작성하는 방법
--DROP SEQUENCE emp_row_seq;
CREATE SEQUENCE emp_row_seq;
2. Audit Table로 작성하는 방법
--DROP TABLE emp_row_audit;
CREATE TABLE emp_row_audit(
e_id NUMBER(6) CONSTRAINT emp_row_pk PRIMARY KEY,
e_name VARCHAR2(30),
e_gubun VARCHAR2(10),
e_date DATE
);
3. Trigger로 작성하는 방법
: 삽입, 수정, 삭제 등을 할때 각 값이 테이블에 있는 값인지 꼭! 확인하기
CREATE OR REPLACE TRIGGER emp_row_aud
AFTER insert OR update OR delete ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO emp_row_audit
VALUES(emp_row_seq.NEXTVAL,:new.ename,'inserting',SYSDATE);
ELSIF UPDATING THEN
INSERT INTO emp_row_audit
VALUES(emp_row_seq.NEXTVAL,:old.ename,'updating',SYSDATE);
ELSIF DELETING THEN
INSERT INTO emp_row_audit
VALUES(emp_row_seq.NEXTVAL,:old.ename,'deleting',SYSDATE);
END IF;
END
;
1. Inserting
INSERT INTO emp(empno , ename , sal , deptno)
values(3000,'유지원',3500, 55); --여기서 deptno값은 emp TBL에 있는 값으로 해야함
-- 없는 값으로 하면 실행 X
2. Updating
UPDATE emp
SET ename = '황보슬'
WHERE empno = 2001
;
3. Deleting
DELETE emp
WHERE empno = 1502
;수업교재 P73 ~P303
73 80 82 86 88 94 97 101 102 104 106 107 109 110 114 116 119 122
130 132 133 134 137 139 140 141
146 150 152 153 154 155 158
167 168 170 172
178 187 191 195
215 221 224 226 228 232 234 235
242 246 249 258 261
266 275 277 283
291 295 296 298 303
현장과제
오늘의 숙제
<<view HW>>
문제 1) 학생 테이블에서 101번 학과 학생들의 학번, 이름, 학과 번호로 정의되는 단순 뷰를 생성
--- 뷰 명 : v_stud_dept101
>>내가 한 것
CREATE OR REPLACE VIEW v_stud_dept101
AS
SELECT studno , name , deptno
FROM student
;
>>선생님이 한 것
CREATE OR REPLACE VIEW v_stud_dept101
AS
SELECT studno , name , deptno
FROM student
WHERE deptno = 101
;
문제 2) 학생 테이블과 부서 테이블을 조인하여 102번 학과 학생들의 학번, 이름, 학년, 학과 이름으로 정의되는 복합 뷰를 생성
-- 뷰 명 : v_stud_dept102
>>내가 한 것
CREATE OR REPLACE VIEW v_stud_dept102
AS
SELECT s.studno , s.name , s.grade , d.dname
FROM student s, department d
WHERE s.deptno = d.deptno
;
>>선생님이 한 것
CREATE OR REPLACE VIEW v_stud_dept102
AS
SELECT s.studno , s.name , s.grade , d.dname
FROM student s, department d
WHERE s.deptno = d.deptno
AND s.deptno = 102
;
문제 3) 교수 테이블에서 학과별 평균 급여와 총계로 정의되는 뷰를 생성
-- 뷰 명 : v_prof_avg_sal Column 명 : avg_sal sum_sal
>>내가 한 것
CREATE OR REPLACE VIEW v_prof_avg_sal
AS
SELECT AVG(sal) as avg_sal , SUM(sal) as sum_sal
FROM professor
GROUP BY deptno
;
>>선생님이 한 것
CREATE OR REPLACE VIEW v_prof_avg_sal
AS
SELECT deptno , AVG(sal) as avg_sal , SUM(sal) as sum_sal
FROM professor
GROUP BY deptno
;
'DB > Oracle' 카테고리의 다른 글
| 2024_07_05_금~07_08_월 (0) | 2024.07.05 |
|---|---|
| 2024_07_03_수~ 07_04_목 (0) | 2024.07.03 |
| 2024_07_01_월 (0) | 2024.07.01 |
| 2024_06_28_금 (0) | 2024.06.28 |
| 2024_06_27_목 (0) | 2024.06.27 |
