Oracle 19일차 - 서브쿼리

2020.11.10(화)

 

서브쿼리, PL/SQL (Procedure, Function, Trigger) : MongoDB
1. 서브쿼리

 = 조인 => 여러개의 테이블에서 필요한 데이터 모아서 수집

                단점 : SELECT에서만 사용 가능

 = 서브쿼리 => 여러개의 SQL문장을 한개로 통합

                      DML 전체에서 사용이 가능

 = 종류

  1) 단일행 서브쿼리 : 서브쿼리의 결과값이 1개

  2) 다중행 서브쿼리 : 서브쿼리의 결과가 여러개

  3) 인라인 뷰 : FROM (SELECT~)

  4) 스칼라 서브쿼리 : 컬럼대신 사용

     SELECT empno,ename,(SELECT ~) as dname

 

단일행

SELECT * FROM emp WHERE empno=7369;

SELECT * FROM emp ====> 메인쿼리
WHERE deptno=(SELECT ~) ====> 서브쿼리
서브쿼리에서 실행된 결과를 메인쿼리가 받아서 처리

 

예) emp테이블 => 사원의 평균급여 => 평균보다 적게 받는 사원 정보를 출력

*하기 코드는 오류 : 단일행과 그룹행을 섞을 수 없음

SELECT * FROM emp WHERE sal<AVG(sal);

---

단일행 2개

SELECT AVG(sal) FROM emp;
SELECT * FROM emp WHERE sal<2073;

 

서브쿼리는 ( )안에

SELECT * FROM emp WHERE sal<(SELECT AVG(sal) FROM emp);

---

예2) SCOTT => scott가 근무하는 부서에 같이 근무하는 사원의 모든 정보 출력

 

단일행 2개

SELECT deptno FROM emp WHERE ename='SCOTT';
SELECT * FROM emp WHERE deptno=20;

 

서브쿼리로 하나로 줄임!

SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT');

---

* 단일행은 반드시 서브쿼리의 결과값 1개

 

단일행 서브쿼리의 연산자

=> 비교연산자 (= , !=(<>) , <= , >= , < , >)

 

-- DISTINCT (중복제거)

SELECT * FROM emp WHERE deptno=(SELECT DISTINCT deptno FROM emp);
SELECT DISTINCT deptno FROM emp;

 

-- GROUP BY

-- 사원의 평균 급여 보다 높은 부서의 부서번호, 인원수를 출력

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING AVG(sal)<(SELECT AVG(sag) FROM emp);

 

---

-- 스칼라 서브쿼리 => 결과값이 한개(컬럼)

SELECT empno, ename, job, dname, loc
FROM emp, dept
WHERE emp.deptno=dept.deptno;

---

예3) 단일행2개 as로 컬럼명을 바꿔준다 dname, loc으로

SELECT empno,ename,job,(SELECT dname FROM dept d WHERE d.deptno=e.deptno) as dname, 
(SELECT loc FROM dept d WHERE d.deptno=e.deptno) as loc FROM emp e;
SELECT empno,ename,job,sal,grade FROM emp, salgrade WHERE sal BETWEEN losal AND hisal;

=> 서브쿼리

SELECT empno,ename,job,sal,
(SELECT grade FROM salgrade WHERE sal BETWEEN losal AND hisal) as grade 
FROM emp;

 

---

예4) 사원 정보 : 사번, 이름, 직위, 입사일, 부서명, 근무지, 급여등급 ==> KING사원과 같은 부서의 사원 출력

SELECT empno, ename, job, hiredate,
(SELECT dname FROM dept d WHERE d.deptno=e.deptno) as dname,
(SELECT loc FROM dept d WHERE d.deptno=e.deptno) as loc,
(SELECT grade FROM salgrade WHERE sal BETWEEN losal AND hisal) as grade
FROM emp e
WHERE deptno=(SELECT deptno FROM emp WHERE ename='KING');

---

---

-- 다중형 서브쿼리

데이터가 여러개인 경우

= 데이터 전체를 처리 ==> IN

SELECT empno,ename,job,deptno
FROM emp
WHERE deptno IN(SELECT DISTINCT deptno FROM emp);

= 최대값 => ALL, MAX()

= 최소값 => ANY, MIN()

 

=> >ANY() => 수행된 결과중에 최소값 >ANY(SELECT deptno FROM dept) ANY(10,20,30,40,50) => 10

                    (SELECT MIN(deptno)_FROM dept)

                    (SELECT MAX(deptno)_FROM dept)

     <ANY() => 수행된 결과중에 최대값 >ANY(SELECT deptno FROM dept) ANY(10,20,30,40,50) => 50

     >ALL() => 수행된 결과중에 최대값 >ALL(SELECT deptno FROM dept) ANY(10,20,30,40,50) => 50

     <ALL() => 수행된 결과중에 최소값 >ALL(SELECT deptno FROM dept) ANY(10,20,30,40,50) => 10

 

>ANY 최소값보다 큰 deptno애들만 불러옴 deptno = 20, 30

<ANY 최대값보다 작은 deptno애들만 불러옴 deptno = 10, 20

SELECT empno,ename,job,deptno
FROM emp
WHERE deptno>ANY(SELECT DISTINCT deptno FROM emp); --최소값 (10,20,30) => 10
 
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno<ANY(SELECT DISTINCT deptno FROM emp); --최대값 (10,20,30) => 30

 

ALL 출력안됨

SELECT empno,ename,job,deptno
FROM emp
WHERE deptno<ALL(SELECT DISTINCT deptno FROM emp); --최소값 (10,20,30) => 30
 
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno>ALL(SELECT DISTINCT deptno FROM emp); --최대값 (10,20,30) => 10

 

ANY는 IN과 같이 OR로 하나라도 만족하면 값을 불러오지만

ALL은 AND로 모든 값이 조건을 충족해야 값을 불러옴!

---

-- 인라인뷰, TOP-N(rownum) => 중간에 데이터를 자를 수 없다

 

중간에 데이터 자르면 출력 안됨

SELECT ename,job,sal,rownum FROM emp 
WHERE rownum BETWEEN 5 AND 10;

 

==> 인라인뷰 페이징기법

SELECT ename,job,sal,rownum
FROM (SELECT ename,job,sal,rownum as num
FROM (SELECT ename,job,sal FROM emp ORDER BY sal DESC))
WHERE num BETWEEN 5 AND 10;

---

-- 인기순위, 베스트댓글

SELECT ename,job,sal,rownum FROM emp;
SELECT ename,job,sal,rownum FROM emp
WHERE rownum<=5;

 

급여가 많은 사람 5명

SELECT ename,job,sal,rownum
FROM (SELECT ename,job,sal FROM emp ORDER BY sal DESC)
WHERE rownum<=5;

---

PROCEDURE, FUNCTION, PACKAGE, TRIGGER를 제작할 때 사용하는 언어

함수 : 사용자 정의로 사용 => 목적 (재사용)

- PROCEDURE : 리턴형이 없는 => 자바스크립트 function func_name()

- FUNCTION : 리턴형이 있는 함수

= 함수 독립

= 메소드 클래스 종속

- PACKAGE : 관련된 PROCEDURE, FUNCTION 모아서 둔 곳

- TRIGGER : 이미 지정된 이벤트 발생시에 자동 처리

                                =====

                                INSERT, UPDATE, DELETE

-- 스칼라 변수
SET serveroutput on;
DECLARE 
    vempno NUMBER(4);
    vename VARCHAR2(20);
    vjob VARCHAR2(20);
    vhiredate DATE;
    vsal NUMBER(7,2);
BEGIN 
    SELECT empno,ename,job,hiredate,sal INTO vempno,vename,vjob,vhiredate,vsal
    FROM emp
    WHERE empno=7788;
    DBMS_OUTPUT.PUT_LINE('===== 결과 =====');
    DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('직위:'||vjob);
    DBMS_OUTPUT.PUT_LINE('입사일:'||vhiredate);
    DBMS_OUTPUT.PUT_LINE('급여:'||vsal);
END;
 
 
-- %TYPE
DECLARE 
    vempno emp.empno%TYPE;
    vename emp.ename%TYPE;
    vjob emp.job%TYPE;
    vhiredate emp.hiredate%TYPE;
    vsal emp.sal%TYPE;
BEGIN 
    SELECT empno,ename,job,hiredate,sal INTO vempno,vename,vjob,vhiredate,vsal
    FROM emp
    WHERE empno=7788;
    -- 출력
    DBMS_OUTPUT.PUT_LINE('===== 결과 =====');
    DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('직위:'||vjob);
    DBMS_OUTPUT.PUT_LINE('입사일:'||vhiredate);
    DBMS_OUTPUT.PUT_LINE('급여:'||vsal);
END;
 
 
 
-- %ROWTYPE
DESC emp;
/*
이름       널?       유형           
-------- -------- ------------ 
EMPNO    NOT NULL NUMBER(4)    
ENAME             VARCHAR2(10) 
JOB               VARCHAR2(9)  
MGR               NUMBER(4)    
HIREDATE          DATE         
SAL               NUMBER(7,2)  
COMM              NUMBER(7,2)  
DEPTNO            NUMBER(2)  
*/
DECLARE
    vemp emp%ROWTYPE;
BEGIN
    SELECT * INTO vemp
    FROM emp
    WHERE empno=7788;
    -- 출력
    DBMS_OUTPUT.PUT_LINE('===== 결과 =====');
    DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('직위:'||vjob);
    DBMS_OUTPUT.PUT_LINE('입사일:'||vhiredate);
    DBMS_OUTPUT.PUT_LINE('급여:'||vsal);
END;
 
-- RECORD : 사용자 정의
DECLARE
    TYPE empdept IS RECORD(
    empno emp.empno%TYPE,
    ename emp.ename%TYPE,
    dname dept.dname%TYPE,
    loc dept.loc%TYPE
    );
    ed empdept;
BEGIN
    SELECT empno,ename,dname,loc INTO ed
    FROM emp, dept
    WHERE emp.deptno=dept.deptno
    AND empno=7788;
    DBMS_OUTPUT.PUT_LINE('===== 결과 =====');
    DBMS_OUTPUT.PUT_LINE('사번:'||ed.empno);
    DBMS_OUTPUT.PUT_LINE('이름:'||ed.ename);
    DBMS_OUTPUT.PUT_LINE('부서명:'||ed.dname);
    DBMS_OUTPUT.PUT_LINE('근무지:'||ed.loc);
END;
 
 

 

-- 제어문

1. 조건문

 = 단일 조건문

    if(조건문) THEN => 비교연산자(=, !=, <, >, <=, >=), 논리연산자(NOT, OR, AND)

      처리

    END IF;

DECLARE 
-- 선언부(변수선언)
    vename emp.ename%TYPE;
    vjob emp.job%TYPE;
    vdname dept.dname%TYPE;
    vdeptno emp.deptno%TYPE;
BEGIN
-- 구현부
-- 변수에 값 대입 => := 
    SELECT deptno,ename,job INTO vdeptno,vename,vjob
    FROM emp
    WHERE ename='KING';
    
    IF(vdeptno=10) THEN
        vdname:='개발부';
    END IF;
    
    IF(vdeptno=20) THEN
        vdname:='영업부';
    END IF;
    
    IF(vdeptno=30) THEN
        vdname:='총무부';
    END IF;
    
    -- 결과값 출력
    DBMS_OUTPUT.PUT_LINE('===== 결과값 =====');
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('부서:'||vdname);
 
-- 예외처리
END;

 

 

 = 선택 조건문

     IF(조건문) THEN
            처리 (조건문이 TRUE)
          ELSE
            처리
          END IF;

DECLARE 
-- 선언부(변수선언)
    vename emp.ename%TYPE;
    vjob emp.job%TYPE;
    vdname dept.dname%TYPE;
    vdeptno emp.deptno%TYPE;
BEGIN
-- 구현부
-- 변수에 값 대입 => := 
    SELECT deptno,ename,job INTO vdeptno,vename,vjob
    FROM emp
    WHERE ename='KING';
    
    IF(vdeptno=10) THEN
        vdname:='개발부';
    ELSIF(vdeptno=20) THEN
        vdname:='영업부';
    ELSIF(vdeptno=30) THEN
        vdname:='총무부';
    END IF;
    
    -- 결과값 출력
    DBMS_OUTPUT.PUT_LINE('===== 결과값 =====');
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('부서:'||vdname);
 
-- 예외처리
END;

 

 

 = 다중 조건문

    IF(조건문) THEN

      처리

    ELSIF(조건문) THEN

      처리

    ELSIF(조건문) THEN

      처리

    ELSE

      처리

    END IF;

DECLARE
    vename emp.ename%TYPE;
    vcomm emp.comm%TYPE;
    vsal emp.sal%TYPE;
BEGIN
    SELECT ename, comm, sal INTO vename, vcomm, vsal
    FROM emp 
    WHERE ename='MARTIN';
    -- 연산처리 => NULL값일 경우에는 결과값이 NULL이다.
    IF(vcomm>0) THEN
        DBMS_OUTPUT.PUT_LINE(vename||'님의 성과급은 '||vcomm||' 입니다');
    ELSE
        DBMS_OUTPUT.PUT_LINE(vename||'님의 성과급은 없습니다');
    END IF;
END;

 

==> 선택문

    CASE = TRIGGER

    CASE(조건) 

      WHEN 조건 THEN 결과

      WHEN 조건 THEN 결과

      WHEN 조건 THEN 결과

    ELSE

      처리

    END;

-- CASE = TRIGGER
DECLARE
 vempno emp.empno%TYPE;
 vename emp.ename%TYPE;
 vdeptno emp.deptno%TYPE;
 vdname dept.dname%TYPE;
BEGIN
    SELECT empno,ename,deptno INTO vempno,vename,vdeptno
    FROM emp
    WHERE ename='SMITH';
    
    vdname:=CASE vdeptno
        WHEN 10 THEN '개발부'
        WHEN 20 THEN '총무부'
        WHEN 30 THEN '자재부'
        WHEN 40 THEN '신입'
    END;
    DBMS_OUTPUT.PUT_LINE('===== 결과 =====');
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
    DBMS_OUTPUT.PUT_LINE('부서:'||vdname);
END;

---

2. 반복문

 = while

    WHILE 조건 LOOP

      처리

    END LOOP;

-- 반복문
WHILE 조건 LOOP
    처리문장
    처리문장
END LOOP;
 
DECLARE
    i NUMBER:=1; -- 초기값
BEGIN
    WHILE i<=10 LOOP
    IF(MOD(i,2)=0) THEN
    DBMS_OUTPUT.PUT_LINE(i);
    END IF;
    -- 증가식 ** =주면 안되고 := 줘야함!
    i:=i+1;
        
    -- i++ (X)
    END LOOP;
END;

 

** 변수를 쓰려면 DECLARE를 사용

** IN순차 <=> IN REVERSE 역순

 

 = for IN은증가 REVERSE는감소

    FOR counter IN 1..9

    FOR i IN(REVERSE) 1..9 LOOP

      처리

    END LOOP;

-- FOR
/*
=============================================
형식)
    FOR 변수명 IN lo..hi LOOP       // 1..10
        출력
    END LOOP;
============================================= 코틀린
*/
 
BEGIN
    FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
 
BEGIN
    FOR i IN REVERSE 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
/

 

 = basic (loop)

    LOOP

      문장

      문장

    END LOOP;

DECLARE
    i NUMBER:=1;
BEGIN
    LOOP
    IF(MOD(i,2)=0) THEN
    DBMS_OUTPUT.PUT_LINE(i);
    END IF;
    i:=i+1;
    -- 종료
    EXIT WHEN i>=10;
    END LOOP;
END;

 

예) 정수를 입력받아서 해당 구구단을 출력하는 프로그램을 작성하시오

 

ACCEPT pno PROMPT '몇단?'
DECLARE
    vno NUMBER:=&pno;
BEGIN
    FOR i IN 1..9 LOOP
        DBMS_OUTPUT.PUT_LINE(vno||'*'||i||'='||vno*i);
    END LOOP;
END;

 

---

금일 수업 요약

1. 변수 선언

   스칼라 변수

   %TYPE

2. 연산자

   비교연산자 ( = , != , < , > , <= , >= )

   논리연산자 ( OR, AND, NOT )

3. 제어문

   IF , IF ~ ELSE

   FOR 

4. SELECT에서 실행된 결과값 받는 경우 INTO

5. 변수에 값 설정 ===> :=

 

=> 단일행 서브 쿼리

     스칼라 서브 쿼리

     인라인 뷰

 

     커서 : 여러개 데이터를 저장하는 공간

     CURSOR emp_cur IS

       SELECT * FROM emp;