DML 대상은 RECORD(레코드)
SELECT / DELETE / UPDATE / INSERT : 대상 레코들 (DML)
CREATE / ALTER / DROP : 대상 테이블 (DDL)
테이블, 사용자, 뷰, 시퀸스, 인덱스 = 데이터베이스 객체들
CREATE TABLE ... : 구조 메모리 저장
INSERT : 데이터 메모리 저장
뷰를 왜 사용하는지 봅시다.
아래와 같은 삽입 SQL 을 실행해봅니다.
INSERT INTO emp (101, '강감찬', ... '인사부', '서울', '한국', '아시아');
INSERT INTO emp (102, '홍길동', ... '인사부', '서울', '한국', '아시아'); INSERT INTO emp (103, '이순신', ... '인사부', '서울', '한국', '아시아'); . . . INSERT INTO emp (200, '신사임당', ... '인사부', '서울', '한국', '아시아'); INSERT 정보에서 부서부터 대륙까지는 반복해서 사용하게 됩니다. 이는 과한 메모리 사용으로 효율적이지 못합니다. emp(empno, ename, ... deptno); dept(deptno, name, city, country, region); INSERT INTO emp (101, '강감찬', '10'); INSERT INTO emp (102, '홍길동', '15'); INSERT INTO emp (103, '이순신', '20'); 이렇게 줄여보면 어떨까요? * 두개 이상 테이블 JOIN 이나 SUBQUERY 조회는 복잡한 SELECT 단점이다. Q. 영국 근무 사원 정보 출력
SELECT e.ename, e.sal, e.deptno, d.deptname, d.city, d.country FROM emp e, dept d WHERE e.deptno = d.deptno and d.country = '영국' * 자주 사용하는 복잡한 SUBQUERY 나 JOIN 결과를 저장하고 이름 정의 하고 VIEW를 호출한다. Q. 영국 근무 사원 정보 출력 (자주 사용할 경우)
CREATE VIEW EMP_UK AS SELECT e.ename, e.sal, e.deptno, d.deptname, d.city, d.country FROM emp e, dept d WHERE e.deptno = d.deptno and d.country = '영국' * VIEW 특징
가상 테이블 사용자에게 특정 객체만 조회할 수 있는 권한을 부여할 수 있다. * VIEW 데이터 조회, VIEW 변경,VIEW 삭제
SELECT * FROM EMP_UK; CREATE OR REPLACE VIEW AS SELECT * FROM EMP; cf) purge recylebin; (테이블에 남아있는 쓰레기값 강제) DROP VIEW EMP_UK; * VIEW 생성 권한 부여
1) SYS 계정 접속 2)) GRANT CREATE VIEW TO 계정; * VIEW 실습 Q. 30번 부서 인원 VIEW 생성
CREATE OR REPLACE VIEW EMP_30 Q. 30번 부서, 1000 이상 봉급 VIEW 생성 CREATE OR REPLACE VIEW EMP_30 cf) 만약 OR REPLACE 구문이 없다면 변경을 할 때 에러 발생한다. ALTER VIEW는 없다. * 계정 내 VIEW 목록 확인, 뷰 컬럼 형식 확인 SQL> DESC USER_VIEWS; SQL> SELECT VIEW_NAME, TEXT FROM USER_VIEWS; * 뷰에 INSERT? SQL> INSERT INTO EMP_30 VALUES(7000, '이자바', 'SALES', 'CHICAGO'); SQL 오류: ORA-01776: 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다. 즉, 두개의 테이블(emp,dept)이 조인된 상태에서 해당 데이터 값을 넣는 것은 논리적으로 문제가 있다. 결국 두개의 테이블 조인 상태에서 VIEW에 INSERT는 불가능하다. 단, 단일 테이블은 가능하다. * 단순뷰/복합뷰
그룹 함수의 사용이 불가능 / 그룹 함수의 사용이 가능 DISTINCT 사용이 불가능 / DISTINCT 사용이 가능 ML 사용 가능 / DML 사용 불가능 * FORCE 명령어
CREATE OR REPLACE FORCE VIEW EMP_SAL SQL 명령: CREATE OR REPLACE FORCE SELECT * FROM emp_sal; 결국 오류 발생한다. DESC emp_sal; 뷰는 생성되어 있는 상태다. * ROWNUM 명령어
AS
SELECT empno, ename, dname, loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
and d.deptno = 30;
AS
SELECT empno, ename, dname, loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
and d.deptno = 30
and e.sal > 1000;
하나의 테이블로 생성 / 여러개의 테이블로 생성
AS
SELECT sal, ename, dname
FROM emps e, dept d
WHERE e.deptno= d.deptno
AND d.deptno = 30
AND e.sal > 100;
실패: Warning: 경고와 함께 실행이 완료됨
데이터 조회 순서를 나타내는 함수다.
SQL> SELECT rownum,empno, ename, hiredate, sal FROM emp;
Q. newemp 테이블의 모든 데이터 순번 붙여서 조회
SELECT rownum, empno, enmae, sal, indate
FROM newemp;
Q.newemp 테이블의 모든 데이터 순번 붙여서 조회하되 최근 날짜 먼저 출력
SELECT rownum, empno, enmae, sal, indate
FROM newemp
ORDER BY indate desc;
--> ROWNUM 순서는 뒤죽박죽이 된다. 이는 아래 질의 처리 과정을 보면 이해할 수 있다.
만약 ROWNUM까지 순서대로 하고자 하면 서브쿼리를 이용한다.
* 데이터베이스 질의 처리 과정 (중요)
FROM - WHERE - GROUP BY - HAVING - SELECT(rownum) - ORDER BY (순서)
* TOP-N?
일련의 출력 데이터를 일단 임의의 순서로 정렬한 후에 그중 일부의 데이터만 출력하여 구하는 것.
Q.newemp 테이블의 모든 데이터 순번 붙여서 조회하되 최근 날짜 먼저, ROWNUM도 정렬되어 출력
--> 1단계 조회, 2단계 날짜 최근 정렬 조회, 1번 대상으로 ROWNUM 적용한다.
SELECT rownum, empno, enmae, sal, indate
FROM newemp
ORDER BY indate desc;
SELECT rownum, empno, ename, sal, indate
FROM
(SELECT *
FROM newemp
ORDER BY indate desc);
--> INLINE 뷰
Q. newemp 테이블의 모든 데이터 순번 붙여서 조회하되 급여 가장 많은 사람 출력
SELECT rownum, empno, ename, sal, indate
FROM
(SELECT *
FROM newemp
ORDER BY sal desc);
Q. newemp 테이블의 모든 데이터 순번 붙여서 조회하되 급여 가장 많은 사람 세사람 출력
SELECT rownum, empno, ename, sal, indate
FROM
(SELECT *
FROM newemp
ORDER BY sal desc)
WHERE rownum >=1 and rownum <=3;
cf) rownum은 아래와 같이 적용하면 출력 값이 없다. 크다, 크거나 같다(>, >=)의 비교연산의 경우에는 적용못한다. 단 1은 가능, 2부터 불가능하다. 2부터 가능하려면 서브쿼리를 한단계 더 사용한다.
SELECT rownum, empno, ename, sal, indate
FROM
(SELECT *
FROM newemp
ORDER BY sal desc)
WHERE rownum >=4 and rownum <=6;
Q. newemp 테이블의 모든 데이터 순번 붙여서 조회하되 급여를 네번째부터 여섯번째까지 많은 사람 세사람 출력
SELECT rownum, empno, ename, sal, indate
FROM
(SELECT *
FROM newemp
ORDER BY sal desc)
WHERE rownum >=4 and rownum <=6;
1단계 : 급여 내림차순 정렬
(SELECT *
FROM newemp
ORDER BY sal desc)
2단계 : 1번 대상으로 모든 데이터 rownum alias 설정
SELECT rownum, empno, ename, sal, indate
FROM
(SELECT *
FROM newemp
ORDER BY sal desc)
3단계 : 2번 대상으로 alias 4부터 6번째까지 조회
SELECT r, empno, ename, sal
FROM (
SELECT rownum r, empno, ename, sal, indate
FROM
(SELECT *
FROM newemp
ORDER BY sal desc))
WHERE r>=4 and r<=6;
Q. 인사관리를 위한 시스템 구축중 지역(REGIONS), 국가(COUNTRIES), 위치(LOCATIONS)에 대한 전체 리스트 정보를 빈번하게 사용할 경우가 생겼다. 여러 프로그램에서 매번 반복적으로 사용되며 일부 개발자의 경우 잘못된 조인으로 올바른 결과를 가져오지 못하는 경우가 발생하였다. 이를 해결하기 위해 복잡한 질의를 숨기기 위한 것을 만든다. VIEW 이름은 LOC_DETAILS_view를 사용한다. 위 설명에 해당하는 DB OBJECT를 생성하기 위한 스크립트를 작성하라. [DB OBJECT 조회 결과? 화면에 다음과 같은 결과를 출력할 수 있는 DB OBJECT를 생성하라]
CREATE VIEW LOC_DETAILS_view
AS
SELECT street_address 거리, city 도시, country_name 국가, region_name 대륙
FROM regions r, countries c, locations l
WHERE l.country_id = c.country_id
and c.region_id = r.region_id;
Q. 부서명, 부서별최소급여, 최대급여, 평균 급여를 나타내는 뷰를 작성하십시오.
참고)
1. 뷰의 이름은 DEPT_VIEW 로 합니다.
2. 뷰에 생성 될 컬럼 명은 NAME, MINSAL, MAXSAL, AVGSAL로 합니다.
3. 평균 급여는 소수점 이하를 절삭합니다.
4. 뷰에 들어갈 값은 DEPARTEMNTS 테이블과 EMPLOYEES 테이블을 이용합니다.
CREATE OR REPLACE VIEW DEPT_VIEW
AS
SELECT department_name NAME, MIN(salary) MINSAL, MAX(salary) MAXSAL, TRUNC(AVG(salary)) AVGSAL
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name;
SELECT *
FROM DEPT_VIEW;