오라클 DBMS 정리 07

SELECT 컬럼명, ...
FROM   테이블명
UNION [ALL]
SELECT 컬럼명, ...
FROM   테이블명

-- UNION : 중복데이터가 조회되지 않음, PK가 있다면 정렬해서 보여줌

-- emp : 미국본사, cp_emp3 : 한국지사라 가정했을 때
-- 미국본사와 한국지사 연봉이 2000이하인 사원급여를 올려주고 싶어
-- 2000이하인 사원의 사원번호, 사원명, 연봉, 직무를 조회
SELECT empno, ename, sal, job
FROM   emp
WHERE  sal <= 2000
UNION
SELECT empno, ename, sal, job
FROM   cp_emp3
WHERE  sal <=2000;

-- cp_emp3에 emp테이블에서 30번 부서 사원정보를 모두 추가
INSERT INTO cp_emp3(empno, ename, deptno, sal, job)
(SELECT empno, ename, deptno, sal, job
 FROM   emp
 WHERE  deptno=30);

-- UNION ALL : 중복데이터가 조회됨, 정렬 안된채로 조회
SELECT empno, ename, sal, job
FROM   emp
WHERE  sal <= 2000
UNION ALL
SELECT empno, ename, sal, job
FROM   cp_emp3
WHERE  sal <=2000;

-- 컬럼의 갯수가 일치하지 않으면 에러
SELECT empno, ename, sal
FROM   emp
WHERE  sal <= 2000
UNION
SELECT empno, ename
FROM   cp_emp3
WHERE  sal <= 2000;

-- 데이터형이 일치하지 않으면 에러
SELECT empno, ename, sal
FROM   emp
WHERE  sal <= 2000
UNION ALL
SELECT empno, ename, job
FROM   cp_emp3
WHERE  sal <=2000;

-- 미국본사의 사원정보와 한국지사의 사원정보 중 일치하는 정보만 조회
SELECT empno, ename, sal, job
FROM   emp
WHERE  sal <= 2000
INTERSECT
SELECT empno, ename, sal, job
FROM   cp_emp3
WHERE  sal <=2000;

-- MINUS : 상위 SELECT에서 조회된 결과에서 하위 SELECT에서 조회된 결과를 뺀 나머지를 조회
SELECT empno, ename, sal, job
FROM   emp
MINUS
SELECT empno, ename, sal, job
FROM   cp_emp3
WHERE  sal <=2000;

-- 테이블간 같은 이름의 컬럼이 없으면 그냥 컬럼명으로 조회 -> 어느 테이블의 컬럼인지 확인이 힘들어 권장하지 않음! 
-- 테이블간 같은 이름의 컬럼명이 있을 땐 테이블명.컬럼명 으로 조회
-- 테이블명이 길다면 alias 붙일 수 있다 - 권장
-- 테이블은 8개 이상 조인하지 않는다(엄청 느려짐)

-- ANSI INNER JOIN
SELECT 테이블1.컬럼명, 조인할테이블.컬럼명, ...
FROM   테이블1 alias
INNER JOIN 조인할테이블 alias
   ON 조인조건;

-- Oracle INNER JOIN
SELECT 테이블1.컬럼명, 조인할테이블.컬럼명,...
FROM   테이블1 alias, 조인할테이블 alias
WHERE  (조인조건) AND 검색조건

-- 사원번호, 사원명, 부서번호, 부서명, 부서위치, 연봉 조회
-- ANSI INNER JOIN
SELECT     e.empno, e.ename, e.deptno,
           d.dname, d.loc, e.sal
FROM       emp e
INNER JOIN dept d
        ON e.deptno = d.deptno;

-- ORACLE INNER JOIN
SELECT e.empno, e.ename, e.deptno,
       d.dname, d.loc, e.sal
FROM   emp e, dept d
WHERE  e.deptno=d.deptno;

SELECT     e.empno, e.ename, e.deptno,
           d.dname, d.loc, e.sal
FROM       emp e
INNER JOIN dept d
        ON e.deptno = d.deptno;

-- ANSI
SELECT 컬럼명, 테이블명.컬럼명, alias.컬럼명, ...
FROM   테이블명
LEFT OUTER JOIN 조인할테이블명
             ON 조인조건
--RIGHT OUTER JOIN / FULL OUTER JOIN 바꿔서 사용할 수 있음
-- LEFT, RIGHT로 모두 보고 싶은 테이블(데이터가 있는 방향을) 설정
 

-- ORACLE
-- 조인조건에 레코드가 없는 테이블에 (+)를 붙여줌
-- FK에 붙여주면 됨
-- 조인조건 양쪽에 (+)를 붙이면 error 발생
SELECT 컬럼명, 테이블명.컬럼명, alias.컬럼명, ...
FROM   테이블명 alias, 테이블명2 alias, ...
WHERE  alias.컬럼명(+) = alias.컬럼명;

-- OUTER JOIN : 어느 한쪽 테이블에만 레코드가 존재하더라도 조회
-- 모든 부서의 부서번호, 부서명, 사원번호, 사원명, 입사일 조회
-- ANSI) LEFT나 RIGHT를 쓰되 레코드가 있는 쪽 테이블을 지정
-- 레코드의 구조를 잘 모른다면 FULL OUTER JOIN을 하여 양쪽 테이블 모두 조회가능
SELECT d.deptno, d.dname, e.empno, e.ename, e.hiredate
FROM dept d
LEFT OUTER JOIN emp e
  ON e.deptno = d.deptno;

-- ORACLE) 
SELECT d.deptno, d.dname, e.empno, e.ename, e.hiredate
FROM   dept d, emp e
WHERE  e.deptno(+) = d.deptno;

-- FULL OUTER JOIN은 ANSI문법만 지원(ORACLE 문법은 지원 안함)
SELECT d.deptno, e.deptno, d.dname, e.empno, e.ename, e.hiredate
FROM   dept d
FULL OUTER JOIN emp e
ON     e.deptno = d.deptno;

-- SELF JOIN은 두 테이블명이 같음, alias로 구분해서 사용
SELECT 컬럼명, 테이블명.컬럼명, alias.컬럼명, ...
FROM   테이블명 alias, 조인할테이블명 alias
WHERE  조인조건

-- 사원테이블에서 'SCOTT'인 사원보다 연봉을 작게 받는 사원의
-- 사원번호, 사원명, 입사일, 연봉을 조회

-- 서브쿼리를 사용하는 방법
SELECT empno, ename, hiredate, sal
FROM   emp
WHERE  sal < (SELECT sal
              FROM   emp
              WHERE  ename='SCOTT');

-- SELF JOIN을 사용하는 방법
-- 테이블 한개는 조회용으로만, 또다른 테이블은 비교(조건)용
SELECT e1.empno, e1.ename, e1.hiredate, e1.sal
FROM   emp e1, emp e2
WHERE  (e1.sal < e2.sal) AND e2.ename='SCOTT';

-- 조인한 복수행 서브쿼리로 얻어진 결과(INLINE VIEW)를 조회할 땐 컬럼명을 그냥 쓴다
SELECT ename, sal, dname
FROM(SELECT e.ename, e.sal, d.dname
     FROM   emp e, dept d);

CREATE SEQUENCE 시퀀스명 
 INCREMENT BY 증가값 
 START WITH 시작값
 MAXVALUE 끝값
 CACHE 메모리에올려놓을수
 CYCLE[NOCYCLE]

시퀀스명.nextval
시퀀스명.currval

-- SEQUENCE : 순차적으로 증가하는 수를 사용하는 객체
-- 1에서부터 9999999까지 1씩 증가하는 시퀀스를 생성
CREATE SEQUENCE seq_test
 INCREMENT BY 1
 START WITH 1
 MAXVALUE 999999;

-- nextval 안하고 먼저 currval 사용하면 error!
-- currval은 메모리에 올라와 있는 시퀀스의 현재 번호를 얻는 일을 함
SELECT seq_test.currval
FROM   DUAL;

-- 서버가 종료되면 메모리에 존재하던 시퀀스는 사라짐
-- 정의된 LAST_NUMBER부터 시작

-- 쿼리문의 실행 실패에도 시퀀스는 사라진다
INSERT INTO cp_emp(empno, ename, hiredate, sal)
VALUES(seq_test.nextval, '안기모씨', SYSDATE, 300);

-- ename의 크기가 10이라 한글4글자가 안들어감, 위에 INSERT 쿼리 여러 시도 후 (실패)
-- 제대로 넣으면 실패한 만큼 시퀀스 수가 올라가 있는걸 확인 가능하다

-- 시퀀스 삭제
DROP SEQUENCE seq_test;