ICT블로그

- 묻지마 블록(Anonymous block)의 경우 실행 시 마다 항상 컴파일을 수행해야 함. 이런 불편함을 줄이기 위해 1회 컴파일 후 반복해서 사용할 수 있는 블록을 오라클 객체로 제공함. --> 이것이 PL/SQL 서브 프로그램임.

- PL/SQL 서브프로그램 (PL/SQL subprogram)

  * 파리미터 고유의 이름을 가진 PL/SQL 블록

  * 내장 프로시져 및 함수를 통칭

  * 함수는 결과값을 반환하므로 SELECT나 WHERE 절 등에 위치 할 수 있음.

-- 함수 (내장 함수, 사용자 정의 함수, User Defined Function)

(예제1)

CREATE OR REPLACE FUNCTION hr.emp_salaries (emp_id number)

           RETURN NUMBER IS

    nSalaries NUMBER(9);        -- 여기까지 함수 명세부(specification)

BEGIN                           -- 여기서부터 구현부(body)

  nSalaries := 0;

   SELECT salary

     INTO nSalaries

     FROM hr.employees

    WHERE employee_id = emp_id;

    RETURN nSalaries;

  END;

(예제2)

SELECT 절에 함수 사용하기

select employee_id, first_name, emp_salaries(employee_id)

  from employees

 where department_id = 100;

-- 함수 활용하기

employees 테이블에서 각 사원의 부서명을 알고 싶을 경우

1) employees 테이블, departments 테이블 조인

2) select 절에 서브쿼리 사용 (서브쿼리 내에서는 조인이 사용됨)

3) FUNCTION 생성 후 사용

1. 함수만들기

create or replace function get_dep_name (dep_id number)

                 return varchar2 is

               sDepName varchar2(30);

          begin

              select department_name

                into sDepName

                from departments

               where DEPARTMENT_ID = dep_id;

          RETURN sDepName;

END;

2. 사용하기

select employee_id, first_name||' '||last_name names, get_dep_name(department_id) dep_names

from employees

where department_id = 100;

--> 함수를 사용할 경우 조인이 발생하지 않는다!

이처럼 함수를 사용할 경우 성능상 이점이 발생하나, 함수 개수가 많아지는 등 관리상의 문제가 발생할 수 있으므로 경우에 따라 조인, 서브쿼리, 함수중에서 적절한 것을 택해 사용한다.

참고. 서브쿼리 사용시

select a.employee_id, a.first_name||' '||a.last_name names, (select b.department_name

from departments b

where a.department_id = b.department_id) dep_names

from employees a

where a.department_id = 100;

-- 프로시져 (내장 프로시져, Stored procedure)

(예제) 신입사원 등록 프로시져 만들기

create or replace procedure register_emp (

                                         f_name VARCHAR2, -- *자리수 명시 X

                                         l_name VARCHAR2,

                                         e_acct VARCHAR2,

                                         j_id   VARCHAR2)  IS

begin

  insert_into employees (employee_id, first_name, last_name, email, hire_date, job_id)

                 values (employee_seq.nextval, f_name, l_name, e_acct, sysdate, j_id);

commit;

exception when others then  

  dbms_output.put_line(f_name||' '||l_name||' register is failed!');

rollback;

end;

* 프로시저의 파라미터로 명시할 때는 VARCHAR2만 명시한다. 구체적인 자리수(BYTES)까지 명시하게 되면 오류가 발생한다.

create or replace procedure register_emp (

                                         f_name VARCHAR2,

                                         l_name VARCHAR2,

                                         e_acct VARCHAR2,

                                         j_id   VARCHAR2)  IS

begin

  insert into employees (employee_id, first_name, last_name, email, hire_date, job_id)

                 values (employees_seq.nextval, f_name, l_name, e_acct, sysdate, j_id);

commit;

exception when others then  

  dbms_output.put_line(f_name||' '||l_name||' register is failed!');

rollback;

end;

(예제) 프로시져 사용하기

exec register_emp ('조영', '대', 'djoyoung', 'IT_PROG');

*홍형경 저/ 뇌를 자극하는 오라클 PL/SQL 프로그래밍

-- IF문

set serveroutput on

declare

  grade char(1);

begin

  grade := 'B';

  if grade = 'A' THEN

     dbms_output.put_line('Excellent');

  ELSIF grade = 'B' THEN

     dbms_output.put_line('Good');

  ELSIF grade = 'C' THEN

     dbms_output.put_line('Fair');

  ELSIF grade = 'D' THEN

     dbms_output.put_line('Poor');

  END IF;

END;

-- CASE문

declare

  grade char(1);

begin

  grade := 'B';

CASE grade

  WHEN 'A' THEN

     dbms_output.put_line('Excellent');

  WHEN 'B' THEN

     dbms_output.put_line('Good');

  WHEN 'C' THEN

     dbms_output.put_line('Fair');

  WHEN 'D' THEN

     dbms_output.put_line('Poor');

  ELSE

     dbms_output.put_line('Not Found');

  END CASE;

END;

-- LOOP문 (1)

declare

  test_number integer;

  result_num  integer;

begin

  test_number := 1;

  loop

    result_num := 2 * test_number;

    if result_num > 20 then

      exit;

    ELSE

      dbms_output.put_line(result_num);

    end if;

    test_number := test_number + 1;

  end loop;

end;

-- LOOP문(2)

declare

  test_number integer;

  result_num  integer;

begin

  test_number := 1;

  loop

  result_num := 2* test_number;

  exit when result_num > 20;

  dbms_output.put_line(result_num);

  test_number := test_number +1;

  end loop;

end;

※ (2)번 예제에서처럼 EXIT WHEN 문을 쓰면 IF 구문을 추가로 사용하지 않아도 되기 때문에 구문이 깔끔해진다.

-- WHILE LOOP 문

declare

  test_number integer;

  result_num  integer;

begin

  test_number := 1;

  result_num := 0;

while result_num <  20 loop

  result_num := 2 * test_number;

  dbms_output.put_line(result_num);

  test_number := test_number + 1;

  end loop;

end;

-- FOR ... LOOP 문

declare

  test_number integer;

  result_num  integer;

begin

  test_number := 1;

  result_num := 0;

  dbms_output.put_line('<<first>>');

  <<first>>

  for test_number in 1..10 loop

    result_num := 2 * test_number;

    dbms_output.put_line(result_num);

  end loop;

  dbms_output.put_line('<<second>>');

  result_num := 0;

  <<second>>

  for test_number in reverse 1..10 loop

    result_num := 2 * test_number;

    dbms_output.put_line(result_num);

  end loop;

  end;

-- GOTO문

declare

  test_number integer;

  result_num  integer;

begin

  test_number := 1;

  result_num := 0;

  GOTO second;

  dbms_output.put_line('<<first>>');

  <<first>>

  FOR test_number IN 1..10 LOOP

    result_num := 2 * test_number;

    dbms_output.put_line(result_num);

  end loop;

  <<second>>

  result_num := 0;

  dbms_output.put_line('<<second>>');

  FOR test_number IN REVERSE 1..10 LOOP

    result_num := 2 * test_number;

    dbms_output.put_line(result_num);

  end loop;

END;ㅈ

-- NULL 문

NULL 문이 사용되는 목적 : 보통 NULL 문장은 예외처리시 자주 사용된다. 즉 사용자가 정의한 특정 예외가 발생하였을 경우나 EXCEPTION WHEN OTHERS, 즉 EXCEPTION 절에서 명시한 예외 이외의 오류가 발생하였을 경우에 NULL 문을 명시한다.

예제(1) IF문을 사용했을 경우

  --> IF문 사용시 예외값에 대한 처리(ELSE절)이 없더라도 오류를 발생시키지 않는다.

declare

  grade char(1);

begin

  grade := 'Z';

  if grade = 'A' THEN

     dbms_output.put_line('Excellent');

  ELSIF grade = 'B' THEN

     dbms_output.put_line('Good');

  ELSIF grade = 'C' THEN

     dbms_output.put_line('Fair');

  ELSIF grade = 'D' THEN

     dbms_output.put_line('Poor');

  END IF;

END;

예제(2) CASE문을 사용했을 경우

  --> CASE문 사용시 1. WHEN절이 모든 가능한 경우를 포함하거나 

                         2. 예외값에 대한 처리(ELSE절)가 반드시 포함되어야 한다.

                         그렇지 않을 경우 에러가 발생하게 된다.

declare

  grade char(1);

begin

  grade := 'Z';

CASE grade

  WHEN 'A' THEN

     dbms_output.put_line('Excellent');

  WHEN 'B' THEN

     dbms_output.put_line('Good');

  WHEN 'C' THEN

     dbms_output.put_line('Fair');

  WHEN 'D' THEN

     dbms_output.put_line('Poor');

  END CASE;

END; 

오류 보고 -

ORA-06592: CASE not found while executing CASE statement

ORA-06512: at line 6

06592. 00000 -  "CASE not found while executing CASE statement"

*Cause:    A CASE statement must either list all possible cases or have an

           else clause.

*Action:   Add all missing cases or an else clause.

이 경우 NULL 절을 이용하면 간단히 처리할 수 있다.

declare

  grade char(1);

begin

  grade := 'Z';

CASE grade

  WHEN 'A' THEN

     dbms_output.put_line('Excellent');

  WHEN 'B' THEN

     dbms_output.put_line('Good');

  WHEN 'C' THEN

     dbms_output.put_line('Fair');

  WHEN 'D' THEN

     dbms_output.put_line('Poor');

  ELSE NULL; --> 아무것도 처리하지 않음

  END CASE;

END;

-- 커서

 정의: 쿼리에 의해 반환되는 결과셋(*메모리 상에 위치)에 접근하기 위해 사용되는 Cursor.

  * 명시적 커서(explicit cursor)란 사용자가 직접 쿼리의 결과에 접근해서 이를 사용하기 위해 명시적으로 선언한 커서를 말한다.

   커서 선언 --> 커서 오픈 --> 패치 --> 커서 닫기

  * 묵시적 커서(implicit cursor)란 오라클 내부에서 각각의 쿼리 결과에 접근하여 사용하기 위한 내부적 커서라 할 수 있다.

(예제) 명시적 커서

DECLARE                                 -- 커서 선언부

  CURSOR emp_csr IS

  SELECT employee_id

    FROM hr.employees

   WHERE department_id = 100;

   emp_id hr.employees.employee_id%TYPE;

BEGIN

    OPEN emp_csr;                        -- 커서 오픈

 LOOP

    FETCH emp_csr INTO emp_id;           -- 커서 패치

EXIT WHEN emp_csr%NOTFOUND;

          dbms_output.put_line(emp_id);

      END LOOP;

      CLOSE emp_csr;                     -- 커서 닫기

  END;

(참고) 명시적 커서
  이런식으로 쉽게 활용이 가능하다

  DECLARE                                 -- 커서 선언부

  CURSOR emp_csr IS

  SELECT employee_id, first_name, last_name

    FROM hr.employees

   WHERE department_id = 100;

   emp_id hr.employees.employee_id%TYPE;

   emp_f_name hr.employees.first_name%TYPE;

   emp_l_name hr.employees.last_name%TYPE;

BEGIN

    OPEN emp_csr;                        -- 커서 오픈

 LOOP

    FETCH emp_csr INTO emp_id, emp_f_name, emp_l_name;           -- 커서 패치

EXIT WHEN emp_csr%NOTFOUND;

          dbms_output.put_line(emp_id);

          dbms_output.put_line(emp_f_name);

          dbms_output.put_line(emp_l_name);

      END LOOP;

      CLOSE emp_csr;                     -- 커서 닫기

  END;

--명시적 커서의 속성

%FOUND : 커서 오픈시 NULL, 패치 시 TRUE, 패치 완료시 FALSE 반환

%ISOPEN : 커서 오픈시 TRUE 반환

%NOTFOUND : 더 이상 패치할 로우가 없음을 의미

%ROWCOUNT: 커서 오픈시 0, 패치 1건당 1씩 증가

-- 묵시적 커서(Implicit Cursor)

  선언, 오픈 등의 작업을 할 필요가 없음

  명시적 커서의 속성을 묵시적 커서에도 동일하게 사용할 수 있음

  가장 최근에 실행된 SQL문장에 대한 커서 = SQL커서

(예제1)

  DECLARE

    count1 NUMBER;

    count2 NUMBER;

  BEGIN

    SELECT count(*)

      INTO count1

      FROM hr.employees

     WHERE department_id = 100;

     count2 := SQL%ROWCOUNT;

     dbms_output.put_line('SELECT COUNT IS '||count1);

     dbms_output.put_line('ROW COUNT IS '||count2);

  END;

*뇌를 자극하는 오라클 프로그래밍 (홍형경 저)의 정리입니다.

(블럭1: Counter Is Null)

declare

  counter integer;

begin

  counter := counter +1;

  if counter is null then

    dbms_output.put_line('Result : COUNTER IS Null');

end if;

end;

/

set serveroutput on;

(블럭2: 구구단 출력)

declare

  counter integer;

  i integer;

begin

  for i in 1..10 loop

  counter := (2 * i);

  dbms_output.put_line(' 2 * '||i||' = '||counter);

  end loop;

end;

/

(블럭3: 예외출력하기)

- 수정 전

declare

  counter integer;

begin

  counter := 10;

  counter := counter / 0;

  dbms_output.put_line(counter);

end;

- 수정 후

declare

  counter integer;

begin

  counter := 10;

  counter := counter / 0;

  dbms_output.put_line(counter);

exception when others then

  dbms_output.put_line('ERRORS');

end;

- 수정 후 (2) - 에러 내용 명시

declare

  counter integer;

begin

  counter := 10;

  counter := counter / 0;

  dbms_output.put_line(counter);

exception when zero_divide then

  dbms_output.put_line('ERRORS');

end;

/

- 수정 후 (3) - 0으로 나누기 시도시 1로 나누도록

declare

  counter integer;

begin

  counter := 10;

  counter := counter / 0;

  dbms_output.put_line(counter);

exception when zero_divide then

  counter := counter / 1;

  dbms_output.put_line(counter);

end;

<콜렉션의 사용>

(예제)

declare

  type varray_test is varray(3) of integer;

    -- integer 형 요소 3개로 구성된 varray 타입 선언

  type nested_test is table of varchar2(10);

    -- varchar2(10)형 요소로 구성된 nested table 타입 선언 (최대값이 없다)

  type assoc_array_num_type is table of number index by pls_integer;

    -- 키는 PLS_INTEGER 형이며 값은 number형인 요소들로 구성된 associative array 타입1

  type assoc_array_str_type is table of varchar2(32) index by pls_integer;

    -- 키는 PLS_INTEGER 형이며 값은 varchar2(32)형인 요소들로 구성된 associative array 타입2

  type assoc_array_str_type2 is table of varchar2(32) index by varchar2(64);

  -- 키는 VARCHAR2(64) 형이며 값은 varchar2(32)형인 요소들로 구성된 associative array 타입3

  varray1 varray_test; -- varray1 : 변수 / varray_test : 타입(헷갈리면 안됨)

  nested1 nested_test; 

  assoc1 assoc_array_num_type; 

  assoc2 assoc_array_str_type;

  assoc3 assoc_array_str_type2;

  begin

  varray1 := varray_test(1,2,3);

  nested1 := nested_test('A','B','C','D');

  assoc1(3) := 33; -- 키는 3, 값은 33

  assoc2(2) := 'TT'; -- 키는 2 값은 TT

  assoc3('O') := 'ORACLE'; -- 키는 O, 값은 ORACLE

  assoc3('K') := 'KOREA'; -- 키는 K, 값은 KOREA

  dbms_output.put_line(varray1(1)); -- varray_test의 첫번째 요소값을 출력, 결과는 1

  dbms_output.put_line(nested1(2)); -- nested_test의 두번째 요소값을 출력, 결과는 B

  dbms_output.put_line(assoc1(3)); -- 키 값이 3인 요소값을 출력, 결과는 33

  dbms_output.put_line(assoc2(2)); -- 키 값이 2인 요소값을 출력, 결과는 TT

  dbms_output.put_line(assoc3('O')); -- 키 값이 O인 요소값을 출력, 결과는 ORACLE

  dbms_output.put_line(assoc3('K')); -- 키 값이 K인 요소값을 출력, 결과는 KOREA

  end;

  /

<사용자 정의 데이터 타입(User Defined Data Type, TYPE 객체>

콜렉션을 필요할 때마다 다시 정의해서 사용하는 것은 번거롭기 때문에, 자주 사용되는 콜렉션은 데이터베이스 객체 (TYPE)으로 지정하여 사용할 수 있다. 이것을 사용자 정의 데이터 타입이라고 한다.

  create type alphabet_typ as varray(26) of varchar(2);

  declare

    test_alph alphabet_typ;

  begin

    test_alph := alphabet_typ('A','B','C','D');

  dbms_output.put_line(test_alph(2));

  end;

  /

<콜렉션과 레코드>

콜렉션과 레코드: PL/SQL에서 제공하는 데이터 타입.

(1)콜렉션 : PL/SQL에서 사용되는 배열(Array)형태의 데이터 타입을 통칭하여 콜렉션이라고 한다.

  *배열(Array)이란? : 같은 데이터타입으로 구성된 요소(element)들의 집합.

  +콜렉션의 종류 : 총 3가지

    1. varray(variable array) : 고정 길이를 가진 배열. 선언시 전체 크기가 명시되어야 하며, 각 요소가 순서대로 참조(정의)되어야 한다. 각 요소는 인덱스(=순서번호)를 통해 접근된다.

    2. 중첩테이블(nested table) : varray와 흡사하나 전체 크기를 명시할 필요가 없다는 점, 각 요소가 순서대로 참조(정의) 될 필요가 없다는 점에서 차이점을 갖는다.

    3. Associative array(연관배열, index-by table) : 키와 값의 쌍으로 구성된 콜렉션. 각 요소는 키에 의해 접근된다.

  +참고: 콜렉션은 다른 프로그래밍 언어에서 사용하는 1차원 배열형태의 구조를 갖고 있음.

(2)레코드 : 테이블 형태(2차원)의 데이터 타입. 여러 개의 필드(=컬럼)으로 구성되어 있으며, 해당 필드는 각기 다른 데이터 타입을 가질 수 있다.

  +쓰임 : 레코드가 테이블 형태의 구조를 갖고 있으므로, 실제 대부분의 경우 테이블의 데이터를 읽어오거나 조작하기 위해 PL/SQL 블록 내에서 임시적인 데이터 저장소 역할을 수행한다.

   +참고: 레코드는 다른 프로그래밍 언어에서 사용하는 구조체(Structure) 형태를 갖고 있음.

<콜렉션과 레코드 - 구문형식>

  1. VARRAY

      TYPE 타입명 IS {VARRAY | VARYING ARRAY } (크기) OF 요소데이터 타입 [NOT NULL];

  2. Nested Table

      TYPE 타입명 IS TABLE OF 요소데이터타입 [NOT NULL];

  3. associative array

      TYPE 타입명 IS TABLE OF 요소데이터타입 [NOT NULL] INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(크기)]

  4. 레코드

      TYPE 레코드이름 IS RECORD (필드1 데이터타입1, 필드2 데이터타입2, ...);

  참고1. %TYPE

      변수명 스키마명.테이블명.컬럼명%TYPE;

  참고2. %ROWTYPE

      레코드이름 스키마명.테이블명%ROWTYPE;

  참고3. 커서%ROWTYPE을 이용한 레코드 선언

      레코드이름 커서명%ROWTYPE;

예제- 레코드

declare

    -- type으로 선언한 레코드

    type record1 is record (dep_id number not null := 300,

                             dep_name varchar2(30),

                             man_id number, loc_id number);

    -- 위에서 선언한 record1을 받는 변수 선언

    rec1 record1;

    -- 테이블명%ROWTYPE을 이용한 레코드 선언

    rec2 hr.departments%rowtype;

    cursor c1 is

       select department_id, department_name, location_id

       from hr.departments

       where location_id = 1700;

    -- 커서명%rowtype을 이용한 레코드 선언

    rec3 c1%rowtype;

    begin

     -- record1 레코드 변수rec1의 dep_name 필드에 값 할당.

     rec1.dep_name := '레코드부서1';

     -- rec2 변수에 값 할당

     rec2.department_id := 400;

     rec2.department_name := '레코드부서2';

     rec2.location_id := 2700;

     -- rec1의 레코드 값을 departments 테이블에 INSERT

     INSERT INTO hr.departments values rec1;

     -- rec2의 레코드 값을 departments 테이블에 INSERT

     INSERT INTO hr.departments values rec2;

     -- 커서 오픈

     OPEN c1;

     loop

     -- 커서값을 rec3에 할당한다. 개별 값이 아닌 department_id, department_name, location_id 값이 레코드 단위로 할당된다.

     fetch c1 into rec3;

     dbms_output.put_line(rec3.department_id);

     exit when c1%notfound;

     end loop

     commit;

     exception when others then

     rollback;

     end;

     /

(정의) 인라인뷰: SQL문장에서 FROM 절에 사용된 서브쿼리

데이터베이스 객체인 뷰와 비슷하나, 인라인 뷰는 서브쿼리라는 특성 때문에 하나의 SQL 문장에서만 사용가능하며 데이터베이스 객체가 아니다. 임시성 뷰라고 생각할 수 있다.

인라인 뷰의 필요성(예제)

select a.employee_id, a.first_name||' '||a.last_name, a.salary

from employees a

where a.salary >= (select avg(salary) from employees)

and a.salary <= (select max(salary) from employees)

order by a.salary desc;

--> 만약 평균 급여, 최대 급여까지 함께 보여 주고 싶다면?

select a.employee_id, a.first_name||' '||a.last_name names, a.salary, round(b.avgs), b.maxs

from employees a, (select avg(salary) avgs, max(salary) maxs from employees) b

where a.salary between b.avgs and b.maxs

order by a.salary desc;

* from 절에 2개의 table이 명시되었으나, Join 구문이 포함되지 않았으므로 양 테이블의 catenation 곱으로 표현되었다. (총 레코드 개수(51) = a레코드 개수(51) * b레코드 개수(1))

인라인 뷰의 필요성(예제2)

월별 입사 현황 구하기

*레코드 개수 12개 결과 만들기

select 

decode(to_char(hire_date,'mm'), '01', count(*), 0) "1월",

decode(to_char(hire_date,'mm'), '02', count(*), 0) "2월",

decode(to_char(hire_date,'mm'), '03', count(*), 0) "3월",

decode(to_char(hire_date,'mm'), '04', count(*), 0) "4월",

decode(to_char(hire_date,'mm'), '05', count(*), 0) "5월",

decode(to_char(hire_date,'mm'), '06', count(*), 0) "6월",

decode(to_char(hire_date,'mm'), '07', count(*), 0) "7월",

decode(to_char(hire_date,'mm'), '08', count(*), 0) "8월",

decode(to_char(hire_date,'mm'), '09', count(*), 0) "9월",

decode(to_char(hire_date,'mm'), '10', count(*), 0) "10월",

decode(to_char(hire_date,'mm'), '11', count(*), 0) "11월",

decode(to_char(hire_date,'mm'), '12', count(*), 0) "12월"

from employees

group by to_char(hire_date,'mm')

order by to_char(hire_date,'mm');

*레코드 개수 1개 결과 만들기

select sum(m1) "1월", sum(m2) "2월", sum(m3) "3월", sum(m4) "4월", sum(m5) "5월", sum(m6) "6월", sum(m7) "7월", sum(m8) "8월", sum(m9) "9월", sum(m10) "10월", sum(m11) "11월", sum(m12) "12월"

from (

select decode(to_char(hire_date,'mm'), '01', count(*), 0) m1,

decode(to_char(hire_date,'mm'), '02', count(*), 0) m2,

decode(to_char(hire_date,'mm'), '03', count(*), 0) m3,

decode(to_char(hire_date,'mm'), '04', count(*), 0) m4,

decode(to_char(hire_date,'mm'), '05', count(*), 0) m5,

decode(to_char(hire_date,'mm'), '06', count(*), 0) m6,

decode(to_char(hire_date,'mm'), '07', count(*), 0) m7,

decode(to_char(hire_date,'mm'), '08', count(*), 0) m8,

decode(to_char(hire_date,'mm'), '09', count(*), 0) m9,

decode(to_char(hire_date,'mm'), '10', count(*), 0) m10,

decode(to_char(hire_date,'mm'), '11', count(*), 0) m11,

decode(to_char(hire_date,'mm'), '12', count(*), 0) m12

from employees

group by to_char(hire_date,'mm')

);

인라인 뷰의 필요성(예제3)

select *

from (

select employee_id, first_name, last_name, salary

from employees

order by salary desc

)

where rownum <= 10;

*뇌를 자극하는 오라클 프로그래밍(홍형경 저) 참조함

연관성있는 서브쿼리의 대표적인 예는 EXISTS 연산자를 사용하는 서브쿼리이다.

예제)

*연관성 없는 서브쿼리

select count(*) from employees

where department_id in (select department_id

from departments

where manager_id is not null);

--> EXISTS로 전환

*연관성 있는 서브쿼리

select count(*) from employees emp

where exists (select 1

from departments dep

where dep.manager_id is not null

and emp.department_id = dep.department_id);

--> 데이터의 연관성이 있다는 의미는 서브쿼리와 메인쿼리 사이에서 조인이 사용되었음을 의미한다.

* 연관성 있는 서브쿼리의 경우에는 조인을 수반하므로 반드시 별칭을 사용해야 한다.

(정의) 연관성 있는 서브쿼리: 메인쿼리에 독립적이지 않고 연관관계, 즉 조인을 통해 연결되어 있는 쿼리를 말한다.

<연관성 있는 서브쿼리의 활용>

예제)

* 일반 조인 사용 시

select emp.first_name||' '||emp.last_name emp_names,

emp.department_id,

dep.department_name

from employees emp, departments dep

where emp.department_id=dep.department_id;

* 연관성 있는 서브쿼리 사용 시

select emp.first_name||' '||emp.last_name emp_names,

emp.department_id,

(select dep.department_name

from departments dep

where dep.department_id = emp.department_id) dep_name

from employees emp;

연관성 있는 서브쿼리는 이와 같이 select list에 자주 쓰인다. 연관성 없는 서브 쿼리도 select list에 올 수는 있지만 실제로 그런 경우는 거의 없다.

왜 그럴까?

연관성 없는 서브쿼리의 경우 다중 로우를 추출하는 경우가 많음.

반면 연관성 있는 서브쿼리의 경우 메인쿼리와의 연관성(=조인)이 있기 때문에 SELECT LIST에 올 수 있으며, 이러한 경우 단일 로우를 추출하게 된다.

* 뇌를 자극하는 오라클 프로그래밍 (홍형경 저) 참조함.

문제: 부서 번호가 30, 60, 90인 직원 출력하기

(1) IN 연산자 활용시 
select department_id, employee_id from employees

where  department_id in (30,60,90)

order by 1, 2;

(2) EXISTS 연산자 활용 시

select emp.department_id, emp.employee_id

from employees emp

where exists

(select dep.department_id

from departments dep

where dep.department_id in (30, 60, 90)

and emp.department_id=dep.department_id)

order by 1, 2;

--> 변환(동일한 결과값 출력됨)

(3) EXISTS 연산자 활용 시

select emp.department_id, emp.employee_id

from employees emp

where exists

(select 1

from departments dep

where dep.department_id in (30, 60, 90)

and emp.department_id=dep.department_id)

order by 1, 2;

<EXISTS의 특징>

(1)EXISTS는 오직 서브쿼리만 쓸 수 있다.

(2)서브쿼리에서 조인을 사용한다.

(3)EXISTS는 서브쿼리의 결과가 있느냐 없느냐만 체크한다.

(IN 이 WHERE 조건절에 해당하는 레코드의 컬럼값을 출력하는 것과는 대비하여-)

  *EXISTS는 존재하느냐 존재하지 않느냐의 여부만 체크하기 때문에 서브쿼리의 SELECT 리스트에는 등장하는 값과는 상관없이 서브쿼리의 결과로 반환되는 로우가 있느냐 없느냐만 중요한 것이다.

<NOT IN과 NOT EXISTS>

IN과 EXISTS는 동일한 결과값을 출력한다.

반면 NOT IN과 NOT EXISTS는 결과값이 다를 수 있다. 바로 NOT IN 은 NULL 값을 포함하지 않기 때문이다.

예제)

department_id 가 null인 레코드 1건 존재-->

select employee_id, department_id

from employees

where department_id is null;

1개 recored

NOT IN -->

select department_id, employee_id from employees

where  department_id not in (30,60,90)

order by 1, 2;

92개 records

NOT EXISTS -->

select emp.department_id, emp.employee_id

from employees emp

where not exists

(select 1

from departments dep

where dep.department_id in (30, 60, 90)

and emp.department_id=dep.department_id)

order by 1, 2;

93개 records

※차이가 발생하는 이유

IN은 OR이다.

department_id IN (30, 60, 90) --> department_id = 30 or department_id = 60 or department_id = 90 으로 바꿔 쓸 수 있다.

NOT IN의 경우 (AUB)^C 이므로 A^C교집합B^C으로 쓸 수 있다.

department_id NOT IN (30, 60, 90) --> (a)department_id <> 30 and (b)department_id <> 60 and (c)department_id <> 90 으로 바꿔 쓸 수 있다.

문제는 null 값은 '=', '<>' 연산을 했을때 TRUE/FALSE를 반환하지 않고 UNKNOWN을 반환한다는 것에 있다. 따라서 department_id가 null인 178번 사원은 (a), (b), (c) 어디에도 포함되지 않고, 따라서 반환되지 않는다.

반면 EXISTS의 경우 해당 로우의 존재여부만 체크하기 때문에, NOT EXISTS는 department_id 값이 30, 60, 90이 아닌 모든 로우를 조회하게 되며, 따라서 department_id가 null인 값도 출력에 포함되는 것이다.

<IN과 EXISTS의 활용>

컬럼값에 대해 비교할 값이 문자나 숫자 등의 상수 리스트를 사용할 경우 IN을 사용하도록 하고, 만약 서브쿼리를 사용한다면 IN보다는 EXISTS를 사용하는 것이 훨씬 성능이 좋다.

* 뇌를 자극하는 오라클 프로그래밍 (홍형경 저) 참조함.