PL/SQL 커서

오라클 서버에서 사용자의 sql문장을 처리하는 모든 서버 프로세스는 sql문을 실행할 때마다 주어진 sql 문장의 처리(parse, execution) 를 위해 SGA 내부에 Context Area 라는 개별적인 메모리 공간을 사용한다.

Cursor란 Context Area라는 메모리 공간에 있는 데이터를 사람이 접근 할 수 있도록 연결해주는 일종의 연결통로이며 포인터라고 부르기도 한다.

즉 사용자가 요청하는 데이터를 데이터베이스 버퍼 캐쉬에서 PL/SQL이 사용하는 개별적인 메모리공간으로 복사 해 온 후(복사 한 데이터를 Actice Set이라함) 커서를 통해 Context Area 있는 Active Set 중에서 원하는 데이터에 접근한 후 필요한 데이터를 추출해(Fetch) PL/SQL 변수에 담고 후속 작업을 하게 된다는 뜻.

커서 종류에는 두가지와 각각의 속성들이 있다.

묵시적 커서(Implicit Cursor)

오라클이 자동적으로 선언하여 사용하고 자동으로 정리된다(clean up). 사용자 입장에서는 생성 유무를 알수 없고 PL/SQL블록 내에서의 SELECT문, DML문이 실행될 때마다 묵시적 커서가 선언된다. (세션 내 단 한개만이 선언되어 사용되다가 문장이 종료됨과 동시에 정리된다)

  • SQL%ROWCOUNT : 해당 커서에서 실행한 총 행의 개수를 반환(가장 마지막 행이 몇 번째 행인지 카운트)

  • SQL%FOUND : 해당 커서 안에 아직 수행해야 할 데이터가 있을 경우 TRUE값을 반환하고 없을 경우 FALSE값 반환

  • SQL%NOTFOUND : 해당 커서 안에 수행해야 할 데이터가 없을 경우 TRUE값을 반환하고 있을 경우 FALSE의값 반환

  • SQL%ISOPEN : 현재 묵시적 커서가 메모리에 OPEN 되어 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환

사용예

SCOTT>SELECT sal
2 FROM emp
3 ORDER BY 1;
SAL
-----
800
950
1100
1250
1250
1300
1500
1600
2450
 
 
SCOTT>DECLARE
2 BEGIN
3    delete emp
4    WHERE sal < 1000;
5      DBMS_OUTPUT.PUT_LINE('=====================================');
6      DBMS_OUTPUT.PUT_LINE('1000 LESS THEN : '||SQL%ROWCOUNT||' rows deleted.');
7
8    delete emp
9    WHERE sal between 1000 and 2000 ;
10     DBMS_OUTPUT.PUT_LINE('=====================================');
11     DBMS_OUTPUT.PUT_LINE('1000 - 2000 : '||SQL%ROWCOUNT||' rows deleted.');
12 END;
13 /
============================
1000 LESS THEN : 2 rows deleted.
============================
1000 - 2000 : 6 rows deleted.
PL/SQL procedure successfully completed.
SCOTT> DECLARE
2 BEGIN
3       DBMS_OUTPUT.PUT_LINE(' Information about department name and location');
4       DBMS_OUTPUT.PUT_LINE('-------------------------');
5     FOR dept IN ( SELECT dname , build
6     FROM department
7     WHERE build IS NOT NULL
8     ORDER BY 1)
9   LOOP
10      DBMS_OUTPUT.PUT_LINE(dept.dname||' ---> '|| dept.build);
11  END LOOP;
12 END ;
13 /
 
Information about department name and location
----------------------------------------------
Chemical Engineering ---> Chemical Experiment Bldg
Computer Engineering ---> Information Bldg
Electronic Engineering ---> Electronic Control Bldg
Library and Information science ---> College of Liberal Arts
Mechanical Engineering ---> Machining Experiment Bldg
Multimedia Engineering ---> Multimedia Bldg
Software Engineering ---> Software Bldg
PL/SQL procedure successfully completed.
 
PL/SQL procedure successfully completed.

명시적 커서(Explicit Cursor)

사용자가 선언하여 생성 후 사용하고 주로 여러 개의 행을 처리하고자 할 경우 사용한다. (변수에 데이터를 담기 이전에 사용되는것이므로 복합변수와 헷갈리면 안된다)
많은 데이터를 변수에 담으려면 복합변수를 써야 하는데 이때 명시적 커서를 사용해야 성능이 더 좋아진다.
묵시적 커서와는 다르게 동시에 여러 개가 선언되어 사용될 수 있다.

  • 커서이름%ROWCOUNT : FETCH 문에 의해 읽혀진 데이터의 총 행 수(가장 마지막에 처리된 행이 몇 번째 인지를 반환)

  • 커서이름%FOUND : FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환

  • 커서이름%NOTFOUND : FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 없을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환

  • 커서이름%ISOPEN : 명시적 커서가 메모리에 확보(선언)되어 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환

명시적 커서 처리단계
1.CURSOR
2.OPEN
3.FETCH
4.CLOSE
1. 명시적 커서 선언(Declaration)
CURSOR 커서명
IS
   커서에 담고 싶은 내용을 가져오는 서브쿼리

해당 커서를 사용 하겠다 라고 PL/SQL에 알려주는 역할만 하며 실제 메모리 할당이 이뤄지는 것은 아님.

2. 명시적 커서 열기(OPEN)
OPEN  커서_이름;

커서 선언 시 기술했던 서브쿼리를 수행해서 데이터를 커서로 가져 온다. 실제 메모리가 할당됨. 이때, 명시적 커서 영역에 자리잡은 데이터의 첫 번째 행에 커서 포인터가 설정되고 이 포인터 위치의 데이터 행을 다음 단계인 fetch에서 읽게됨

3. 명시적 커서로부터 데이터 읽어 변수로 할당하기(FETCH)
FETCH  커서_이름  INTO  변수

명시적 커서의 데이터들(Active Set)로부터 데이터를 한 건씩 읽어 변수로 할당하기 위해 FETCH문을 사용함. 읽게되는 데이터행은 포인터에 의해 지정되고 한 행에 FETCH되면 자동적으로 포인터는 다음 행으로 이동하게 됨.
보통 명시적 커서에는 데이터가 여러 건 들어 있기 대문에 많은 데이터들을 읽어 처리하기 위해 FETCH 문은 반복문과 함께 사용하는 경우가 많다.

4. 명시적 커서 닫기(CLOSE)
CLOSE  커서_이름;

작업이 끝난 메모리 공간을 반환하고 정리한다.


사용예
(emp 테이블에서 deptno 가 10번인 사람들의 empno , ename, sal 의 정보를 명시적 커서로 가져 온 후 변수에 저장하고 화면에 출력)

SCOTT> DECLARE
2     v_empno NUMBER(5) ;
3     v_ename VARCHAR2(30) ;
4     v_sal NUMBER(6) ;
5          CURSOR cur1 IS
6          SELECT empno , ename , sal
7          FROM emp
8          WHERE deptno=10 ;
9 BEGIN
10     OPEN cur1 ;
11 LOOP
12     FETCH cur1 INTO v_empno , v_ename , v_sal ;
13     EXIT WHEN cur1%NOTFOUND ;
14     DBMS_OUTPUT.PUT_LINE(v_empno||' '||v_ename||' '||v_sal) ;
15 END LOOP ;
16     CLOSE cur1 ;
17 END;
18 /
 
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
 
PL/SQL procedure successfully completed.


명시적 커서와 Cursor FOR LOOP 문 활용하기

사용자가 별도의 변수선언, OPEN , FETCH , CLOSE 문을 수행하지 않아도 됨.

FOR record_name IN cursor_name LOOP
   -- 명시적 커서의 OPEN, FETCH가 자동적으로 수행된다.
   statement1;
   statement2;
   ...
END LOOP;  -- 루프문을 빠져 나갈 때 자동적으로 커서가 CLOSE된다.

사용예

SCOTT>DECLARE
2   CURSOR cur_emp IS
3     SELECT empno , ename
4     FROM emp;
5 BEGIN
6     FOR emp_cnt IN cur_emp
7      LOOP
8         DBMS_OUTPUT.PUT_LINE(emp_cnt.empno||' '||emp_cnt.ename);
9      END LOOP;
10 END ;
11 /
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
 
PL/SQL procedure successfully completed.
저작자표시 비영리 변경금지

'Oracle > plsql' 카테고리의 다른 글

[PL/SQL] 2. 변수  (0) 2017.05.07
[PL/SQL] 1. 개요  (0) 2017.05.07

PL/SQL 변수 종류

VARIABLE
PL/SQL_variable
Non_PL/SQL_variable
Bind_variable
Simple_variable
LOB
Complex_variable
Scalar_variable
Reference_variable
Record_Type
Table_Type
%TYPE_variable
%ROWTYPE_variable

1. Simple Variable - 1건의 데이터만 저장하는 변수

  • Scalar 변수

  • Reference 변수

    • %type 변수

    • %rowtype 변수

(1) Scalar 변수

확정된 형태의 하나의 값을 가지는 변수의 데이터 형을 직접 지정해 주는 변수. (숫자, 문자, 날짜등)

v_no    NUMBER(5,3)  -- 총5자리, 소수점 이하 3자리를 의미하는 숫자형 데이터를 저장하는 변수
v_name  VARCAHR2(10)  -- 총10byte길이의 문자형 데이터를 저장할수 있는 변수
v_day   DATE          -- 9999년 12월 31일까지의 날짜를 저장할수 있는 날짜형 변수
V_RATE CONSTANT NUMBER := 1.15 ;      -- 상수로 선언하기 위한 키워드로 기본적으로 초기값이 반드시 지정되어야 함
V_NAME VARCHAR2(14) NOT NULL := ‘이순신’;
V_DATE DATE DEFAULT SYSDATE;          -- 기본값 할당
Scalar 변수의 데이터 타입

  • CHAR [(최대길이)]
    이 타입은 고정 길이 문자 형으로 저장하며 최대 32,767 바이트값을 저장.
    기본 값은 1로 설정되어 있다.

  • VARCHAR2 (최대길이)
    이 타입은 가변 길이 문자 형으로 저장하며 최대 32,767 바이트 값을 저장.
    기본 값은 없음.

  • NUMBER [(전체자리수, 소수점이하 자리수)]
    전체 자리수의 범위는 1부터 38까지, 소수점 이하 자리수의 범위는 -84 부터 127 까지.

  • BINARY_INTEGER
    이 타입은 -2,147,483,647 - 2,147,483,647 사이의 정수를 저장하는 타입.

  • PLS_INTEGER
    이 타입은 -2,147,483,647 - 2,147,483,647 사이의 부호 있는 정수에 대한 기본 유형.
    PLS_INTEGER 값은 NUMBER 값보다 저장 공간이 적게 필요하고 연산 속도가 더 빠름.
    Oracle Database 11g에서는 PLS_INTEGER 및 BINARY_INTEGER 데이터 유형은 동일.
    PLS_INTEGER 및 BINARY_INTEGER 값의 산술 연산은 NUMBER 값보다 빠름.

  • BOOLEAN
    이 타입은 논리적 계산에 사용 가능한 세 가지 값(TRUE, FALSE, NULL)중 하나를 저장.

  • BINARY_FLOAT
    이 타입은 IEEE 754 형식의 부동 소수점 수.
    값을 저장하기 위해 5바이트가 필요.

  • BINARY_DOUBLE
    이 타입은 IEEE 754 형식의 부동 소수점 수.
    값을 저장하기 위해 9바이트가 필요.

(2) Reference 변수

데이터 형을 직접 지정하지 않고 원본 데이터의 데이터형을 참조하게끔 설정해주는 변수.

v_no        emp.empno%TYPE     -- emp테이블의 empno와 동일한 데이터형으로 선언함
v_rowtype   emp%ROWTYPE        -- emp테이블의 여러컬럼을 한꺼번에 저장할 변수로 선언함



%ROWTYPE 참조변수

예를들어 tno NUMBER , tname VARCHAR2(10) ,tday DATE 로 이루어진 test 테이블이 있을 경우 test%ROWTYPE 으로 선언되면 하나의 변수에 위의 세가지(NUMBER, VARCHAR2(10) ,DATE )를 모두 한번에 저장 할 수 있는 변수로 선언이 된다는 뜻.

%TYPE 형태의 변수로 만들어야 할 경우 변수를 3 개를 만들어야 하지만 %ROWTYPE 으로 만들 경우 1개의 변수만 있어도 됨.

참조 변수를 쓸 경우에는 원본 데이터 타입이 변경이 될 경우 참조 변수의 데이터 타입도 함께 변경이 되기 때문에 사용자가 수동으로 변경을 해 주지 않아도 된다는 장점이 있다.


하지만 데이터 타입이 변경이 될 경우 원본 테이블의 데이터 형이 변경되거나 컬럼이 변경되면(참조하는 컬럼 말고 다른 컬럼이 변경될 경우에도) 해당 컬럼에 엑세스하는 프로시저는 사용불능(Invalid) 상태가 될 수 있으므로 조심해야한다.



ex1) dept 테이블에서 deptno가 10 번인 부서의 deptno, dname , loc 를 출력하세요

SCOTT>SET SERVEROUTPUT ON;
SCOTT>DECLARE
2 vno   dept.deptno%TYPE ;
3 vname dept.dname%TYPE ;
4 vloc  dept.loc%TYPE ;
5
6 BEGIN
7    SELECT deptno , dname , loc INTO vno , vname , vloc
9    FROM dept
10   WHERE deptno=10 ;
11
12   DBMS_OUTPUT.PUT_LINE(vno||' / '||vname||' / '||vloc);
13 END;
14 /
SCOTT>DECLARE
2 v_row    dept%ROWTYPE ;
3
4 BEGIN
5     SELECT * INTO v_row
6     FROM dept
7     WHERE deptno=10 ;
8
9   DBMS_OUTPUT.PUT_LINE(v_row.deptno||' / '||v_row.dname||' / '||v_row.loc);
10 END ;
11 /

%ROWTYPE 의 경우는 원본 테이블의 모든 컬럼의 데이터를 다 조회할 경우에만 사용 가능함. 만약 원본 테이블의 컬럼 중 일부만 가져와야 한다면 %ROWTYPE 변수를 사용할 수 없고 각 컬럼을 %TYPE 변수로 선언 한 후 데이터를 가져와야 한다.



ex2) %ROWTYPE 변수를 활용한 데이터의 입력 및 변경

SCOTT>select * from t_rowtype1;
 
        NO NAME                 HIREDATE
---------- -------------------- ------------
        10 APPLE                07-MAY-17
        20 BANANA               07-MAY-17
        30 BERRY                07-MAY-17
 
SCOTT>select * from t_rowtype2;
 
no rows selected
 
SCOTT>
SCOTT>
SCOTT>
SCOTT>
SCOTT>DECLARE
  2   v_record   t_rowtype1%ROWTYPE;
  3  BEGIN
  4     SELECT * INTO v_record
  5     FROM t_rowtype1
  6     WHERE no = 10;
  7
  8     INSERT INTO t_rowtype2
  9     VALUES v_record;
 10  END;
 11  /
 
PL/SQL procedure successfully completed.
 
SCOTT>SELECT * FROM t_rowtype2;
 
        NO NAME                 HIREDATE
---------- -------------------- ------------
        10 APPLE                07-MAY-17
 
SCOTT>DECLARE
2 v_record t_rowtype1%ROWTYPE;
3 BEGIN
4     SELECT * INTO v_record
5     FROM t_rowtype1
6     WHERE no=10 ;
7
8     v_record.name := 'ORANGE' ;
9
10     UPDATE t_rowtype2
11     SET row=v_record
12     WHERE no=10;
13 END ;
14 /
 
PL/SQL procedure successfully completed.
 
SCOTT>SELECT * FROM t_rowtype2 ;
 
    NO NAME     HIREDATE
------ ------ ----------
    10 ORANGE 06-AUG-13


2. Complex Variable - 여러 건의 데이터를 저장할 수 있는 변수

  • Record Type : 여러가지 데이터타입의 데이터를 여러 건을 저장할 때

  • Table Type(컬렉션 타입) : 동일한 데이터타입의 데이터를 여러건 저장할 때

하나의 변수를 만들 때 사용자가 원하는 여러 가지 다른 유형의 데이터를 포함해서 사용자가 원하는 새로운 형태로 만들 수 있다.

마치 뷰 처럼 사용자가 원하는 컬럼을 모아서 하나의 테이블처럼 만들어서 여러 가지 데이터를 한꺼번에 관리 할 수 있다.


(1) Record Type 변수

사용자가 원하는 컬럼들을 직접 지정해 만들어 프로그래머 정의 기반 Record Type 이라고 함.

예를 들어 반찬으로 김치, 멸치, 콩나물을 담아 도시락을 싼다고 하자.
이럴 경우 김치를 담기 위해 김치통을 준비하고 멸치는 멸치통에, 콩나물은 콩나물통에 따로 담아 가는 것이 %TYPE 변수를 활용하는 방법이다.
반찬이 많을 경우 통이 많아져서 불편하므로 하나의 통에 칸막이를 쳐서 반찬들을 한꺼번에 다 담아 간편하게 싸는 방법이 이 Record Type 복합변수라고 생각하면 된다.



문법

  • 정의부분
    TYPE type_name IS RECORD
    ( field_declaration[, field_declaration]...);

  • 선언부분
    Identifier type_name


예시1

SCOTT>DECLARE
  2    TYPE dept_record IS RECORD           -- 정의
  3    (  deptno    dept.deptno%TYPE,
  4       dname     dept.dname%TYPE   );    
  5
  6     v_dept  dept_record;                -- 선언
  7
  8  BEGIN
  9     SELECT deptno, dname INTO v_dept
 10     FROM dept
 11     WHERE deptno = 10;
 12
 13     DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' / '||v_dept.dname);
 14  END;
 15  /
 
10 / ACCOUNTING
 
PL/SQL procedure successfully completed.


위의 예시에서 RECORD TYPE (dept_record) 변수의 형태

  DEPTNO  

  DNAME  



두개의 컬럼이 하나의 변수처럼 사용됨.



예시2
사용자에게 교수 번호를 입력 받은 후 교수번호, 교수이름, 직급, 입사일, 급여를 출력하시오. (단 직급이 없는 사원은 직급을 사원으로 표시해서 출력)

SCOTT>DECLARE
  2    TYPE prof_info IS RECORD         
  3    (  vprofno       professor.profno%TYPE,
  4       vname         professor.name%TYPE,    
  5       vposition     professor.position%TYPE,
  6       vhiredate     professor.hiredate%TYPE,    
  7       vpay          professor.pay%TYPE    );
  8  
  9     v_prof    prof_info;
 10  
 11     v_profno  professor.profno%TYPE := &PROFNO;
 12 BEGIN
 13      SELECT profno, name, position, hiredate, pay INTO v_prof
 14      FROM professor
 15      WHERE profno = v_profno;
 16            DBMS_OUTPUT.PUT_LINE('PROFNO : '||v_prof.vprofno);
 17            DBMS_OUTPUT.PUT_LINE('NAME : '||v_prof.vname);
 18            DBMS_OUTPUT.PUT_LINE('POSITION : '||v_prof.vposition);
 19            DBMS_OUTPUT.PUT_LINE('HIREDATE : '||v_prof.vhiredate);
 20            DBMS_OUTPUT.PUT_LINE('PAY : '||v_prof.vpay);
 21 END;
 22 /
 
 Enter value for profno: 1001
 
 PROFNO : 1001
 NAME : Audie Murphy
 POSITION : a full professor
 HIREDATE : 23-JUN-80
 PAY : 550
 
 PL/SQL procedure successfully completed.

(2) Table Type 변수

대부분의 경우 1개의 컬럼만 저장을 하지만 Row Type을 사용하여 Record Type 처럼 여러 가지 유형의 데이터 컬럼을 가질 수도 있다. 만약 테이블의 모든 컬럼을 다 가지고 와서 변수에 담을 경우 %ROWTYPE 을 사용하여 변수를 생성하면 편하게 사용할 수 있다.

  • 연관 배열

  • 중첩 테이블

  • VARRAY

위 3가지 종류중 연관배열이 가장 많이 사용됨.


연관배열

  KEY    VALUE  




두 개의 컬럼으로 이루어진 형태로 Key 컬럼은 가상의 Primary Key가 되어 인덱스로 사용되고 데이터를 구분하게 된다.
Key컬럼의 값은 사람이 임의로 조정 할 수 없다.(pl/sql이 자동으로 생성함)



  • Key 컬럼에 들어가는 데이터 유형은 아래 두가지이다.

    • 숫자 : BINARY_INTEGER 또는 PLS_INTEGER
      이 두가지 숫자 데이터 유형은 NUMBER 보다 적은 저장 영역이 필요하며 해당 데이터 유형에 대한 산술 연산은 NUMBER로 하는 산술 연산보다 빠르다. (시작은 0번부터)

    • 문자 : VARCHAR2 또는 하위 유형 중 하나가 올 수 있다.

  • VALUE 컬럼은 실제 값이 들어가는 곳으로 입력되는 데이터의 종류에 따라 스칼라 데이터유형 또는 레코드 데이터유형 일 수 있다.



문법

  • 정의부분
    TYPE type_name IS TABLE OF
    { column_type | variable%type | table.column%type } [NOT NULL] | table%ROWTYPE
    [INDEX BY BINARY_INTEGER];

  • 선언부분
    Identifier type_name


동일한 유형의 데이터(또는 데이터구조)들을 하나의 연속적인 메모리 공간에 확보하기 위해 사용한다.

Index by 절은 그 배열내의 요소에 접근하기 위한 첨자(위치) 값으로 사용됨.



예시
Table Type 변수를 사용하여 교수번호가 2001 인 교수의 이름을 조회해서 Table Type 변수에 저장 한 후 출력하시오. (단 Table Type 명은 tbl_prof)

SCOTT>DECLARE
  2   v_name VARCHAR2(20);
  3   TYPE tbl_prof IS TABLE OF
  4     professor.name%TYPE
  5   INDEX BY BINARY_INTEGER;
  6
  7   profname  tbl_prof;
  8
  9  BEGIN
 10      SELECT name INTO v_name
 11      FROM professor
 12      WHERE profno = 2001;
 13
 14     profname(0) := v_name||'_0';
 15     profname(1) := v_name||'_1';
 16
 17     DBMS_OUTPUT.PUT_LINE(profname(0));
 18     DBMS_OUTPUT.PUT_LINE(profname(1));
 19  END;
 20  /
 
Winona Ryder_0
Winona Ryder_1
 
PL/SQL procedure successfully completed.
 

Non PL/SQL 변수

PL/SQL 안에서만 사용되는 것이 아니라 외부에서도 사용될 수 있는 변수


Bind Variable

호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 하며, VARIABLE 키워드를 사용해 생성되고 SQL문과 PL/SQL 블록에서 사용된다.

콜론(:) 을 사용해 값을 참조하며 PRINT 명령을 사용해 값을 출력한다.



예시
교수번호가 2001인 교수의 연봉을 계산하여 바인드변수에 할당 한 후 출력하시오.

SCOTT>VARIABLE v_bind NUMBER;      --SQL에서 변수 선언
SCOTT>BEGIN
  2    SELECT (pay*12)+NVL(bonus,0) INTO :v_bind    -- PL/SQL에서 값 입력
  3    FROM professor
  4    WHERE profno = 2001;
  5  END;
  6  /
 
PL/SQL procedure successfully completed.
 
SCOTT>PRINT v_bind;         --SQL에서 바인드 변수에 담긴 값 출력
 
    V_BIND
----------
      3000

PRINT 문장을 매번 실행하기 번거로우면 SET AUTOPRINT ON 설정 후 사용하면 자동으로 바인드 변수 값을 출력 해서 보여준다.


변수의 적용 범위

외부 블록에서 선언한 변수는 내부 블록에서도 사용할 수 있지만 내부블록에서 선언된 변수는 외부 블록에서 사용할 수 없다.

저작자표시 비영리 변경금지

'Oracle > plsql' 카테고리의 다른 글

[PL/SQL] 3. CURSOR  (3) 2017.05.14
[PL/SQL] 1. 개요  (0) 2017.05.07

PL/SQL

procedural language/SQL 의 약자로 block 구조의 언어
(Pascal -> Ada -> PL/SQL)

프로그래밍적 요소와 함께 데이터 트랜잭션 처리능력, 데이터에 대한 보안 및 예외처리 기능, 객체지향 등 데이터베이스와 관련된 중요한 모든 기능을 지원해 오라클 데이터베이스와 연관된 업무를 처리하기에 최적화된 언어.



PL/SQL 동작원리

1.PLSQL_BLOCK
2.SQL
PL/SQL
3.PL/SQL+SQL_RESULT
SQL_RESULT

  1. 사용자가 pl/sql BLOCK을 실행하면 해당 블록이 오라클 서버로 전달되서 오라클 서버 내 메모리에 상주해 있는 PL/SQL 엔진이 해당 블록을 받게됨.

  2. 그 후 해당 블록에 있든 모든 SQL 문장들은 분리가 되서 오라클 서버 프로세스에게 전달되 먼저 수행됨.

  3. PL/SQL 엔진은 해당 SQL 문장이 수행되어 결과가 돌아 올 때까지 기다렸다가 그 결과를 받아서 PL/SQL 엔진에 만들어 둔 변수에 데이터를 담아 놓고 변수에 담긴 SQL 실행 결과값을 사용해 나머지 PL/SQL 문장을 실행함.
    (이때 사용되는 변수는 Client가 PL/SQL Block을 작성할 때 미리 계획해서 선언해야 하며 이 변수를 잘못 선언 할 경우 데이터베이스에서 처리된 결과를 PL/SQL 엔진이 사용할 수 없게된다.)



PL/SQL 블록의 기본 구조

  • Anonymous Block

  • Named Block : procedure, function, package, trigger, object, ..

declare       -- 옵션
    선언부
begin         --  필수
    실행부
exception     -- 옵션
    예외처리부
end;          -- 필수
/
create or replace [procedure|function] 이름
is
 
begin         -- 필수
 
exception     -- 옵션
 
end;          -- 필수
/


PL/SQL 내에서 SELECT 문장 사용하기

ex) 사원번호 입력해 사원이름과 월급 조회하기

create or replace procedure proc1(p_empno number)
  is
    v_ename emp.ename%type;
    v_sal   emp.sal%type;
  begin
    select ename, sal into v_ename, v_sal
    from emp
    where empno = p_empno;
 
     dbms_output.put_line('ENAME : '||v_ename);
     dbms_output.put_line('SAL : '||v_sal);
  exception
    when no_data_found then
      dbms_output.put_line(p_empno||' does not exists!');
  end;
  /


PL/SQL 내에서 DML(insert, update, delete, merge) 문장 사용하기

  • INSERT
    ex) 사용자로부터 번호(no) , 이름(name) , 연락처(tel) 값을 입력 받은 후 t_plsql 테이블에 입력하는 PL/SQL 문장을 작성하시오.

declare
 
v_no   NUMBER := &no;
v_name VARCHAR2(10) := '&name';
v_tel  NUMBER := &tel;
 
begin
 INSERT INTO t_plsql VALUES(v_no, v_name, v_tel);
end;
/
 
SCOTT>SELECT * FROM t_plsql ;
SCOTT>commit;

  • UPDATE, DELETE

SQL>BEGIN
2     UPDATE t_plsql
3     SET name='CCC'
4     WHERE no = 2 ;
5 END ;
6 /
SQL>commit;
 
SQL>BEGIN
2     DELETE FROM t_plsql
3     WHERE no = 1 ;
4 END ;
5 /
SQL>commit;

  • MERGE

 
SQL>SELECT * FROM t_merge1;
  NO  FRUIT
----- -------------
    1 APPLE
    2 BANANA
 
 
SQL>SELECT * FROM t_merge2 ;
  NO  FRUIT
----- -------------
    3 MELON
    1 CHERRY
 
 
SQL> BEGIN
2      MERGE INTO t_merge2 t2
3      USING t_merge1 t1
4      ON(t1.no = t2.no)
5   WHEN MATCHED THEN
6      UPDATE SET
7      t2.fruit = t1.fruit
8   WHEN NOT MATCHED THEN
9      INSERT VALUES(t1.no , t1.fruit);
10 END ;
11 /
 
SQL>SELECT * FROM t_merge2 ;
   NO FRUIT
----- ----------
    3 MELON
    1 APPLE     <- 원래 CHERRY 였는데 UPDATE 됨
    2 BANANA    <- 이 줄은 추가 됨
 
SQL>commit;



PL/SQL 에서 사용하는 주요 용어와 연산자들

  • 식별자: 객체에게 부여되는 이름 (테이블명, 변수명등)

  • 구분자: 특별한 의미를 가진 기호


    • 기호

    		의미
    +,-,*,/,=,<>,!=연산자
    @원격 엑세스 표시자
    --단일행 주석표시
    /*, */주석 시작과 종료
    :=할당 연산자
    ll연결 연산자
    ;명령문 종료

  • 리터럴 : 변수에 할당되는 모든 값 (식별자가 아닌 모든 문자, 숫자, 부울, 또는 날짜 값)


저작자표시 비영리 변경금지

'Oracle > plsql' 카테고리의 다른 글

[PL/SQL] 3. CURSOR  (3) 2017.05.14
[PL/SQL] 2. 변수  (0) 2017.05.07

Constraint

테이블에 올바른 데이터만 입력받고 잘못된 데이터는 들어오지 못하도록 컬럼별로 정해진 규칙.(학번은 중복되지 않게 하거나, 학년에는 1-4까지의 숫자만 들어오게 하는 등).


 제약조건

NOT NULL

null값 허용X

UNIQUE

중복값 허용X

PRIMARY KEY

(NOT NULL + UNIQUE) 테이블내 데이터끼리 유일성보장.

테이블당 1개만 설정가능

FOREIGN KEY

다른 테이블의 컬럼을 참조.

CHECK

이 조건에서 설정된 값만 입력허용됨.



하나의 제약 조건이 여러 컬럼에 중복으로 설정 될 수 있고, 또 하나의 컬럼에 여러 개의 제약 조건들이 중복으로 설정 될 수 있음.(primary key는 테이블당 1개만 설정가능)



  • UNIQUE, PRIMARY KEY : 이 조건의 특징은 해당 컬럼에 있는 데이터들이 서로 중복되지 않고, 자동으로 해당 컬럼에 UNIQUE INDEX를 생성함. (인덱스 생성작업으로 인한 시간과 부하 주의)
  • FOREIGN KEY :  두개의 테이블을 서로 참조하도록 설정됨





    • 저작자표시 비영리 변경금지

    'Oracle > sql' 카테고리의 다른 글

    [SQL] 6. DML  (0) 2016.09.16
    [SQL] 5. DDL과 Data Dictionary  (0) 2016.09.15
    [SQL] 4. JOIN  (0) 2016.09.12
    [SQL] 3. 복수행함수(그룹함수)  (1) 2016.09.05
    [SQL] 2. 단일행함수(정규식표현)  (0) 2016.09.05

    DML


    테이블안의 데이터들을 관리하는 명령어





    INSERT

    테이블에 새로운 데이터를 입력할 때 사용하는 명령어 숫자 값 이외에는 홑따옴표 써야함.


    (1) 1행씩 입력하기

    INSERT INTO table (col1, co2, ...,)
VALUES (value1, value2, ...,) ;

  • dept2 테이블에 새로운 부서정보 입력

    • SCOTT>INSERT INTO dept2 (dcode, dname, pdept, area)
  2  VALUES (9000, 'temp_1', 1006, 'Temp Area');

    모든 컬럼에 데이터를 입력할 경우에는 테이블명 뒤에 컬럼 이름 생략가능.



  • NULL 값 입력

    • 데이터 입력시 컬럼에 값을 입력하지 않으면 자동으로 null 값이 들어감. (값에 null 입력해도됨)


  • 음수 값 입력 테스트 (양수와 동일함)
    • SCOTT>CREATE TABLE t_minus
  2  ( no1 NUMBER,
  3    no2 NUMBER(3),       // 정수 3자리.
  4    no3 NUMBER(3,2));  // 소수점이하 2자리까지.

Table created.

SCOTT>INSERT INTO t_minus VALUES(1, 1, 1);

1 row created.

SCOTT>INSERT INTO t_minus VALUES(1.1, 1.1, 1.1);

1 row created.

SCOTT>INSERT INTO t_minus VALUES(-1.1, -1.1, -1.1);

1 row created.

SCOTT>SELECT * FROM t_minus;

       NO1        NO2        NO3
---------- ---------- ----------
         1          1          1
       1.1          1        1.1
      -1.1         -1       -1.1



    (2) 서브쿼리를 사용해 여러행 입력하기

    SCOTT>CREATE TABLE professor3
  2  AS
  3    SELECT * FROM professor
  4    WHERE 1 = 2;            //  데이터는 입력안되도록

Table created.

SCOTT>SELECT * FROM professor3;

no rows selected

SCOTT>INSERT INTO professor3
  2  SELECT * FROM professor;   // professor3과 컬럼의 개수, 데이터 형이 동일해야함.

16 rows created.

    위처럼 서브쿼리를 사용해 여러 데이터를 가져와 입력하는 방법을 현업에서는 ITAS 라고 부름. 조건도 사용가능




    (3) INSERT ALL 을 이용해 여러 테이블에 여러 행 입력하기


    예제


  • 다른 테이블의 데이터 가져와서 입력


    • Professor 테이블에서 교수번호가 1000 번 에서 1999번까지 인 교수의 번호와 교수이름은 prof_3 테이블에 입력하고 교수번호가 2000 번에서 2999 번까지 인 교수의 번호와 이름은 prof_4 테이블에 입력.

    SCOTT>CREATE TABLE prof_3
  2  ( profno  NUMBER,
  3    name    VARCHAR2(25));

Table created.

SCOTT>CREATE TABLE prof_4
  2  ( profno  NUMBER,
  3    name    VARCHAR2(25));

Table created.

SCOTT>INSERT ALL
  2  WHEN profno BETWEEN 1000 AND 1999 THEN
  3       INTO prof_3 VALUES(profno, name)
  4  WHEN profno BETWEEN 2000 AND 2999 THEN
  5       INTO prof_4 VALUES(profno, name)
  6  SELECT profno, name
  7  FROM professor;
  • 다른 테이블에 동시에 같은 데이터 입력


    • prof_3 과 prof_4 테이블의 데이터를 TRUNCATE 로 삭제한 후 Professor 테이블에서 교수번호가 3000번 에서 3999 번인 교수들의 교수 번호와 이름을 prof_3테이블과 prof_4 테이블에 동시에 입력

    SCOTT>TRUNCATE TABLE prof_3;

Table truncated.

SCOTT>TRUNCATE TABLE prof_4;

Table truncated.

SCOTT>INSERT ALL
  2     INTO prof_3 VALUES (profno, name)
  3     INTO prof_4 VALUES (profno, name)
  4  SELECT profno, name
  5  FROM professor
  6  WHERE profno BETWEEN 3000 AND 3999;





    UPDATE

    UPDATE table
SET column = value
WHERE 조건;


    예제

    1) Professor 테이블에서 직급이 조교수(assistant professor) 인 교수들의 BONUS 를 200 만원으로 인상하시오.

    SCOTT>UPDATE professor
  2  SET bonus = 200
  3  WHERE position = 'assistant professor';


    2) Professor 테이블에서 'Sharon Stone' 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250 만원이 안 되는 교수들의 급여를 15% 인상하시오.

    SCOTT>UPDATE professor
  2  SET pay = pay * 1.15
  3  WHERE position = ( SELECT position
  4                     FROM  professor
  5                     WHERE name = 'Sharon Stone' )
  6  AND pay < 250;




    DELETE

    DELETE FROM table
WHERE 조건;


    예제

    Dept2 테이블에서 부서번호(DCODE)가 9000 번에서 9999 번 사이인 매장들을 삭제하시오.

    SCOTT>DELETE FROM dept2
  2  WHERE dcode >= 9000 AND dcode <= 9999;




    MERGE

    여러 테이블의 데이터를 병합.

    MERGE INTO table1
  USING table2
  ON (병합 조건절)
  WHEN MATCHED THEN
         UPDATE SET 업데이트내용
         DELETE  WHERE 조건
  WHEN NOT MATCHED THEN
         INSERT VALUES(컬럼명);

    table1 테이블과 table2 데이터를 합쳐 table1(집계테이블)에 병합. 병합조건이 만족하면 UPDATE 또는 DELETE 수행, 만족 안하면 INSERT수행.
    집계 테이블에 데이터가 많아질수록 merge 작업수행 속도는 늦어짐.(조건절에 index가 잘 만들어져 있어야 빨리 수행됨)



    사용예



    일별 사용 요금 테이블인 charge_01과 charge_02 테이블이 있고 집계 테이블인 ch_total 테이블이 있다. 매일 새벽에 일별 사용 요금 테이블과 요금 집계테이블을 MERGE 한다고 가정.


    실습을 위해 두 테이블을 생성하고 데이터 입력.

    SCOTT>CREATE TABLE charge_01
  2  ( u_date  VARCHAR2(6),
  3    cust_no NUMBER,
  4    u_time  NUMBER,
  5    charge  NUMBER ) ;

Table created.

SCOTT>CREATE TABLE charge_02
  2  ( u_date  VARCHAR2(6),
  3    cust_no NUMBER,
  4    u_time  NUMBER,
  5    charge  NUMBER ) ;

Table created.


    
SCOTT>INSERT INTO charge_01 VALUES('141001', 1000, 2, 1000);

1 row created.

SCOTT>INSERT INTO charge_01 VALUES('141001', 1001, 2, 1000);

1 row created.

SCOTT>INSERT INTO charge_01 VALUES('141001', 1002, 1, 500);

1 row created.

SCOTT>INSERT INTO charge_02 VALUES('141002', 1000, 3, 1500);

1 row created.

SCOTT>INSERT INTO charge_02 VALUES('141002', 1001, 4, 2000);

1 row created.

SCOTT>INSERT INTO charge_02 VALUES('141002', 1003, 1, 500);

1 row created.

SCOTT>COMMIT;


    집계용 테이블 생성

    SCOTT>CREATE TABLE ch_total
  2  ( u_date  VARCHAR2(6),
  3    cust_no NUMBER,
  4    u_time  NUMBER,
  5    charge  NUMBER ) ;

Table created.


    MERGE 1 (charge_01 과 ch_total 병합)

    SCOTT>MERGE INTO ch_total total
  2  USING charge_01 ch01
  3  ON (total.u_date = ch01.u_date)
  4  WHEN MATCHED THEN
  5     UPDATE SET total.cust_no = ch01.cust_no
  6  WHEN NOT MATCHED THEN
  7     INSERT VALUES (ch01.u_date, ch01.cust_no, ch01.u_time, ch01.charge);

3 rows merged.


    MERGE 2 (charge_02 과 ch_total 병합)

    SCOTT>MERGE INTO ch_total total
  2  USING charge_02 ch02
  3  ON (total.u_date = ch02.u_date)
  4  WHEN MATCHED THEN
  5     UPDATE SET total.cust_no = ch02.cust_no
  6  WHEN NOT MATCHED THEN
  7     INSERT VALUES (ch02.u_date, ch02.cust_no, ch02.u_time, ch02.charge);

3 rows merged.


    결과 조회

    U_DATE    CUST_NO     U_TIME     CHARGE
------ ---------- ---------- ----------
141001       1002          1        500
141001       1001          2       1000
141001       1000          2       1000
141002       1003          1        500
141002       1001          4       2000
141002       1000          3       1500

    주의할점:
    ON 절에 해당하는 조건이 중복되면 에러발생. 그래서 일반적으로 집계테이블 조건 컬럼에는 PK나 UNIQUE INDEX를 많이 설정함.




    TRANSACTION

    논리적인 작업단위.쉽게말하면 여러 DML 작업들을 하나의 단위로 묶어둔것.

    트랜잭션의 시작은 DML, 완료는 TCL, DCL, DDL

  • COMMIT : 트랜잭션 내의 작업의 결과를 확정하는 명령어
  • ROLLBACK : 트랜잭션 내의 모든 명령어들을 취소하는 명령어



    • [출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

    저작자표시 비영리 변경금지

    'Oracle > sql' 카테고리의 다른 글

    [SQL] 7. Constraint(제약조건)  (0) 2016.09.18
    [SQL] 5. DDL과 Data Dictionary  (0) 2016.09.15
    [SQL] 4. JOIN  (0) 2016.09.12
    [SQL] 3. 복수행함수(그룹함수)  (1) 2016.09.05
    [SQL] 2. 단일행함수(정규식표현)  (0) 2016.09.05

     명령어

     DDL

    (Data Definition Language)

    CREATE

    ALTER

    TRUNCATE

    DROP 

    생성

    수정

    잘라내기

    삭제 

     DML

    (Data Manipulation Language)

    INSERT

    UPDATE

    DELETE

    MERGE

    입력

    변경

    삭제

    병합 

     DCL

    (Data Control Language)

    GRNAT

    REVOKE 

    권한주기

    권한뺏기 

     TCL

    (Transaction Control Language)

    COMMIT

    ROLLBACK 

    확정

    취소 

     

     

     



    DDL (Data Definition Language)


    CREATE

    새로운 오브젝트나 스키마를 생성. (이번장에서는 table만)

    테이블 생성시 제한사항
    1. 테이블 이름은 반드시 문자로 시작. 특수문자도 가능하지만 테이블 생성시 “ (겹따옴표) 로 감싸야 하며 권장하지 않음.
    2. 테이블 이름이나 컬럼 이름은 최대 30 bytes 까지 가능. 즉 한글로 테이블 이름을 생성할 경우 최대 15글자 까지만 가능.
    3. 테이블 이름은 한 명의 사용자가 다른 오브젝트들의 이름과 중복으로 사용할 수 없음. ( 예를 들어 scott 사용자가 테이블명을 test 로 생성한 후 다른 테이블 이름을 test 로 동일하게 사용할 수 없다는 것. 그러나 다른 사용자인 hr 사용자는 test 테이블 생성할 수 있음)
    4. 테이블 이름이나 오브젝트 이름을 오라클이 사용하는 키워드를 사용하지 말것. (미리 정해진 SELECT , FROM 등과 같은 키워드로 생성이 안되는 것은 아니지만 사용하지 않는것을 권장.)


    (1) 일반 테이블 생성하기

    SCOTT>CREATE TABLE new_table
  2  ( no    NUMBER(3),
  3    name  VARCHAR2(10),
  4    birth DATE ) ;
Table created.

    No 컬럼은 숫자(NUMBER) 데이터만 들어갈 수 있고 최대 길이는 3자리. Name 컬럼은 가변형 문자(VARCHAR2), 최대 길이는 10 bytes 까지. Birth 컬럼은 날짜(DATE) 데이터.

    값을 입력하지 않을경우 NULL값이 자동으로 입력됨. 


    (2) 기본 입력 값 설정과 함께 테이블 생성하기

     SCOTT>CREATE TABLE tt02
  2  ( no        NUMBER(3,1)    DEFAULT 0,
  3    name      VARCHAR2(10)   DEFAULT 'NO NAME',
  4    hiredate  DATE           DEFAULT SYSDATE ) ;

  Table created.

    사용자가 값을 입력하지 않을 경우 DEFAULT 뒤에 적힌 값을 자동으로 넣으라는 의미


    (3) Global Temporary Table (임시 테이블) 생성하기

    CREATE GLOBAL TEMPORARY TABLE 테이블명
( COL1  데이터타입,
  COL2  테이터타입,  ...., )
ON COMMIT [delete | preserve] ROWS ;
  • delete : COMMIT시 데이터삭제(default)
  • preserve : 세션종료시 데이터삭제

    • 1. 저장 목적이 아닌 임시 작업용(테스트나 조회용)으로 이 테이블은 마치 뷰 처럼 테이블을 생성하면 그 정의만 딕셔너리에 저장돼 있다가 사용자가 해당 테이블에 엑세스하면 메모미 상에 해당 테이블을 만들고 데이터를 가져옴.
    2. 예를 들어 어떤 기능을 테스트를 하기 위해 데이터가 잠시 필요할 경우 일반 테이블을 만들고 데이터를 입력하면 리두 로그가 생성돼 시간도 오래 걸리는데 Temporary Table 을 사용할 경우 아주 빠르게 생성됨. 그리고 작업이 끝나면 자동으로 내용을 삭제까지 해주므로 편함.
    3.그리고 세션별로 만들어지기 때문에 다른 세션에서 테이블을 공유할 수 없다.

    다른 특징들

    1. Redo Log 를 생성하지 않음.
    2. Index , View , Trigger 를 생성할 수 있으나 이 오브젝트들의 타입도 전부 Temporary 임.
    3. 이 테이블에 들어 있는 데이터는 이전이나 백업을 할 수 없음.


    실습1. 터미널 2개 열어서 한쪽에서 생성후 다른쪽에서 조회하기

  • 세션1
    • SCOTT>CREATE GLOBAL TEMPORARY TABLE temp01
  2  (  no    NUMBER ,
  3     name  VARCHAR2(10) )
  4  ON COMMIT delete ROWS;

Table created.

SCOTT>INSERT INTO temp01 VALUES(1, 'AAAAA');

1 row created.

SCOTT>SELECT * FROM temp01;

        NO NAME
---------- ----------
         1 AAAAA
  • 세션2
    • SQL> SELECT * FROM temp01;

no rows selected

    이와같이 다른 터미널에서는 조회 안됨. 같은 계정이라도 다른 창에서는 다른 사람이 어떤 작업을 하는지 알수 없다.
    세션 1에서 아직 COMMIT 을 수행하지 않았기 때문에 temp01 테이블에 데이터가 있지만 TEMPORARY TABLE 생성 옵션이 ON COMMIT delete ROWS 이므로 커밋을 하면 모두 삭제됨


    실습2. 생성되어 있는 temporary table 조회하기

    SCOTT>SELECT temporary, duration
  2  FROM user_tables
  3  WHERE table_name = 'TEMP01';

T DURATION
- ------------------------------
Y SYS$TRANSACTION
  • Temporary 여부 -> Y(YES)
  • Duration -> transaction 이므로 commit 이나 rollback을 수행하는 동안 유지된다는 뜻.


    • (4) 테이블 복사하기 (CTAS)

    새로운 테이블을 생성 할 때 기존에 만들어져 있는 테이블을 참조하여 생성하는 방법.


    모든 컬럼 다 복사하기 / 특정 컬럼만 복사하기

    SCOTT>CREATE TABLE dept3
  2  AS
  3    SELECT * FROM dept2;

Table created.

SCOTT>CREATE TABLE dept4
  2  AS
  3    SELECT dcode, dname
  4    FROM dept2;

Table created.

    테이블의 구조(컬럼)만 가져오기 (데이터X)

    SCOTT>CREATE TABLE dept5
  2  AS SELECT * FROM dept2
  3     WHERE 1 = 2 ;
Table created.

SCOTT>SELECT * FROM dept5;
no rows selected

    이 방법은 주로 데이터는 필요 없이 테이블 구조만 가져올 때 많이 사용하는 방식. 3번 WHERE 줄에 틀린 조건을 줄 경우 그 조건에 해당되는 데이터가 없기 때문에 데이터는 못 가져오고 테이블 구조만 생성하는 것


    (5) 가상 컬럼 테이블 생성하기

  • step1 : 가상 컬럼을 가지는 vt1 테이블 생성
    • SCOTT>CREATE TABLE vt1
  2  ( col1 NUMBER,
  3    col2 NUMBER,
  4    col3 NUMBER GENERATED ALWAYS AS (col1 + col2) );

Table created.

    col3은 col1+col2의 값을 가지는 가상컬럼.

  • step2 : vt1 테이블에 데이터 입력
    • SCOTT>INSERT INTO vt1 VALUES (1,2,3);
INSERT INTO vt1 VALUES (1,2,3)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

SCOTT>INSERT INTO vt1 (col1, col2) VALUES (1,2);

1 row created.

    위와 같이 가상 컬럼에는 사용자가 데이터를 입력못함

  • step3 : 입력된 데이터 조회
    • SCOTT>SELECT * FROM vt1 ;

      COL1       COL2       COL3
---------- ---------- ----------
         1          2          3

    col3에는 데이터를 입력하지 않았지만 자동으로 테이블생성시 계산된 식의값으로 입력됨을 알수있음

  • step4 : 기존 값 변경후 가상 컬럼에 반영되는지 확인
    • SCOTT>UPDATE vt1
  2  SET col1 = 5;

1 row updated.

SCOTT>SELECT * FROM vt1;

      COL1       COL2       COL3
---------- ---------- ----------
         5          2          7

    기존컬럼 값이 변경될 경우 즉시 가상 컬럼에도 반영됨

  • step5 : 새로운 가상 컬럼 추가
    • SCOTT>ALTER TABLE vt1
  2  ADD (col4 GENERATED ALWAYS AS ((col1+col2)+col2));

Table altered.

SCOTT>SELECT * FROM vt1;

      COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
         5          2          7          9
  • step6 : 테이블에서 가상컬럼 내역조회
    • SCOTT>SELECT column_name,
  2          data_type,
  3          data_default
  4  FROM  user_tab_columns
  5  WHERE table_name = 'VT1'
  6  ORDER BY column_id;

COLUMN_NAM DATA_TYPE  DATA_DEFAULT
---------- ---------- -------------------------
COL1       NUMBER
COL2       NUMBER
COL3       NUMBER     "COL1"+"COL2"
COL4       NUMBER     "COL1"+"COL2"+"COL2"
  • step7 : 조건절을 활용한 가상 컬럼 생성
    • SCOTT>CREATE TABLE sales10
  2  ( no     NUMBER,
  3    pcode  CHAR(4),
  4    pdate  CHAR(8),
  5    pqty   NUMBER,
  6    pbungi NUMBER(1)
  7     GENERATED ALWAYS AS
  8      (
  9        CASE
 10            WHEN SUBSTR(pdate,5,2) IN ('01','02','03') THEN 1
 11            WHEN SUBSTR(pdate,5,2) IN ('04','05','06') THEN 2
 12            WHEN SUBSTR(pdate,5,2) IN ('07','08','09') THEN 3
 13            ELSE 4
 14        END  ) virtual );

Table created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(1,'100','20110112',10);

1 row created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(2,'200','20110505',20);

1 row created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(3,'300','20110812',30);

1 row created.

SCOTT>INSERT INTO sales10 (no, pcode, pdate, pqty)
  2  VALUES(4,'400','20111024',40);

1 row created.

SCOTT>COMMIT;

Commit complete.

SCOTT>SELECT * FROM sales10;

        NO PCOD PDATE          PQTY     PBUNGI
---------- ---- -------- ---------- ----------
         1 100  20110112         10          1
         2 200  20110505         20          2
         3 300  20110812         30          3
         4 400  20111024         40          4



    ALTER

    만들어져 있는 오브젝트를 변경하는 명령어.
    테이블의 경우, 컬럼을 추가하거나 삭제하고 컬럼이름이나 테이블 이름을 바꾸는 등의 작업을 할때 사용함.(부하가 많이 걸리는 명령어니 사용량이 많은 시간에 수행하는 것은 위험)


    (1) 새로운 컬럼 추가하기

    SCOTT>CREATE TABLE dept6
  2  AS
  3    SELECT dcode, dname
  4    FROM   dept2
  5    WHERE  dcode IN(1000,1001,1002);

Table created.

SCOTT>SELECT * FROM dept6;

DCODE  DNAME
------ ------------------------------
1000   Management Support Team
1001   Financial Management Team
1002   General affairs

    위의 코드에서 LOCATION 컬럼 추가

    SCOTT>ALTER TABLE dept6
  2  ADD (location VARCHAR2(10));

Table altered.

SCOTT>SELECT * FROM dept6;

DCODE  DNAME                          LOCATION
------ ------------------------------ ----------
1000   Management Support Team
1001   Financial Management Team
1002   General affairs

    기본값으로 NULL값으로 입력됨. NULL 이외의 다른 값을 기본값으로 입력하고 싶다면 DEFAULT 값 지정하면 됨.

    SCOTT>ALTER TABLE dept6
  2  ADD (location2 VARCHAR2(10) DEFAULT 'SEOUL');

Table altered.

SCOTT>SELECT * FROM dept6;

DCODE  DNAME                          LOCATION   LOCATION2
------ ------------------------------ ---------- ----------
1000   Management Support Team                   SEOUL
1001   Financial Management Team                 SEOUL
1002   General affairs                           SEOUL


    (2) 컬럼이름 변경하기

    SCOTT>ALTER TABLE dept6
  2  RENAME COLUMN location2
  3  TO loc;

    SCOTT>RENAME dept6 TO dept7; // 테이블 이름변경


    (3) 컬럼의 데이터 크기 변경하기

    SCOTT>DESC dept7;
 Name              Null?    Type
 ----------------- -------- ------------------------
 DCODE                        VARCHAR2(6)
 DNAME                        NOT NULL VARCHAR2(30)
 LOCATION                     VARCHAR2(10)
 LOC                          VARCHAR2(10)

SCOTT>ALTER TABLE dept7
  2  MODIFY (loc  VARCHAR2(20));

Table altered.

SCOTT>DESC dept7;
 Name              Null?    Type
 ----------------- -------- ------------------------
 DCODE                        VARCHAR2(6)
 DNAME                        NOT NULL VARCHAR2(30)
 LOCATION                     VARCHAR2(10)
 LOC                          VARCHAR2(20)


    (4) 컬럼 삭제하기

    SCOTT>ALTER TABLE dept7
  2  DROP COLUMN loc;

SCOTT>ALTER TABLE dept7
  2  DROP COLUMN loc CASCADE CONSTRAINTS;

    참조키로 설정돼있는 부모테이블의 컬럼을 삭제 할 경우에는 두번째처럼 제약조건을 설정하면됨.


    (5) 읽기 전용 테이블로 변경하기

    SCOTT>CREATE TABLE t_readyonly
  2  ( no NUMBER,
  3    name VARCHAR2(10) ) ;

Table created.

SCOTT>INSERT INTO t_readyonly
  2  VALUES (1, 'AAA');

1 row created.

SCOTT>COMMIT;

Commit complete.

SCOTT>SELECT * FROM t_readyonly;

        NO NAME
---------- ----------
         1 AAA

SCOTT>ALTER TABLE t_readyonly read only;

Table altered.

    읽기전용으로 변경후 테이블에 데이터 INSERT하거나 컬럼추가 하려고 하면 에러남. DROP TABLE로 테이블은 삭제가능.

    읽기전용인 테이블을 다시 읽기/쓰기 모드로 변경하기

    SCOTT>ALTER TABLE t_readonly read write;

    현재 테이블이 읽기 전용인지를 조회하려면 user_tables 의 read_only 컬럼의 값이 YES 이면 읽기전용이고 NO 이면 읽기/쓰기 모드.



    TRUNCATE

    테이블의 데이터를 전부삭제. 테이블은 유지.
    해당 테이블의 데이터가 모두 삭제되지만 테이블 자체는 지워지는 것이 아니며 생성되어 있던 인덱스의 내용도 함께 TRUNCATE됨.

    SCOTT>TRUNCATE TABLE dept7;



    DROP

    테이블 자체를 삭제하는 명령어

    SCOTT>DROP TABLE dept7;



    DELETE, TRUNCATE, DROP 비교



  • DELETE : 데이터만 지워짐. 디스크상의 공간은 그대로가짐 원하는 데이터만 삭제가능.(테이블 용량 줄어들지 않음)
  • TRUNCATE : 최초에 테이블이 만들어졌던 상태로. 모든 데이터를 삭제하고 컬럼값만 남겨놓음. 전부삭제(용량도 줄어들고 인덱스도 truncate)
  • DROP : 데이터와 테이블을 전부 삭제. 사용하고 있던 공간도 모두 반납. 인덱스, 제약조건, 오브젝트 모두삭제.



    • Data Dictionary

    오라클은 데이터베이스를 운영하기 위한 정보들을 모두 특정테이블들에 모아두고 관리하는데 그 테이블들을 데이터 딕셔너리 라고 한다. 이 딕셔너리에는 아래와 같은 주요 정보들이 저장됨.

  • 오라클 데이터베이스의 메모리 구조와 파일에 대한 구조 정보들
  • 각 오브젝트들이 사용하고 있는 공간들의 정보들
  • 제약 조건 정보들
  • 사용자에 대한 정보들
  • 권한이나 프로파일 , 롤에 대한 정보들
  • 감사(Audit) 에 대한 정보들

    • 이외에도 많은 정보들이 저정되어 있고, 이 정보들은 사람으로 비유하면 두뇌에 해당 되는 정보들이기 때문에 만약 장애나 잘못 관리 될 경우 오라클 데이터베이스를 사용할 수 없고 더 심할 경우 장애 시 아예 복구조차 할 수 없게 될 수도 있다. 그래서 이 딕셔너리를 Base Table 과 Data Dictionary View 로 나누어 두고 Base Table 은 DBA 라 할 지라도 접근을 못하게 막아놨다. 그리고 사용자(DBA 포함)들은 Data Dictionary View 를 통해서만 딕셔너리를 SELECT 할 수 있게 허용함.

    만약 데이터베이스에 변경 사항이 생겨 (예를 들어 새로운 테이블이 생성된다든지 하는 상황) 딕셔너리 내용을 변경해야 할 경우 사용자가 직접 수동으로 딕셔너리를 변경하지 못하고 해당 DDL 문장을 수행하는 순간 SERVER Process 가 사용자를 대신해서 해당 딕셔너리 내용을 변경해 줌.



  • Base Table 은 Database 를 생성하는 시점에 자동으로 만들어 짐 (Create Database 나 DBCA를 이용하거나 마찬가지)
  • 반면 Data Dictionary View 는 Catalog.sql 이란 파일이 수행되어야만 만들어 짐. 이 파일은 DBCA 로 Database 를 생성 할 때는 자동으로 수행되지만 CREATE DATABASE 라는 명령어로 수동으로 Database 를 생성할 때는 수행되지 않으므로 DBA 가 수동으로 생성해야만 함.


    • Data Dictionary View 는 크게 2가지 종류가 있다.

    • Static Data Dictionary View
      • USER_XXX : 해당 사용자가 생성한 오브젝트만 조회 할수 있음.
      • ALL_XXX : 해당 사용자가 생성한 오브젝트를 포함하여 해당 사용자가 접근 가능한 모든 오브젝트를 조회 할 수 있음.
      • DBA_XXX : 데이터베이스 내의 거의 모든 오브젝트들을 볼 수 있지만 DBA권한을 가진 사람만 이 딕셔너리를 조회 할 수 있음.
    • Dynamic Performance View
      • v$XXXX


    STATIC DATA DICTIONARY VIEW


    연습용 테이블 static_table 생성하고 데이터입력
    SCOTT>CREATE TABLE st_table
  2  (no number);

Table created.

SCOTT>BEGIN
  2     FOR i IN 1..1000 LOOP
  3        INSERT INTO st_table VALUES (i);
  4     END LOOP;
  5  COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.
    데이터 딕셔너리를 조회해 해당 테이블에 데이터가 몇 건 있는지 확인
    SCOTT>SELECT COUNT(*) FROM st_table;

  COUNT(*)
----------
      1000

SCOTT>SELECT num_rows, blocks
  2  FROM user_tables
  3  WHERE table_name='ST_TABLE';

  NUM_ROWS     BLOCKS
---------- ----------

    실제 데이터는 1000건이 있지만 딕셔너리 내용이 변경이 안되서 값이 조회 되지 않음.

    딕셔너리 수동으로 업데이트 한 후 다시 조회
    SCOTT>ANALYZE TABLE st_table COMPUTE STATISTICS;

Table analyzed.

SCOTT>SELECT num_rows, blocks
  2  FROM user_tables
  3  WHERE table_name='ST_TABLE';

  NUM_ROWS     BLOCKS
---------- ----------
      1000          5

    ANALYZE 명령어는 실제 테이블이나 인덱스, 클러스터 등을 하나씩 조사해서 그 결과를 딕셔너리에 반영시키는 명령어


    Dynamic Performance View

    Static Data Dictionary 와 반대로 Dynamic Performance View 는 정보를 실시간으로 조회하게 되며 사용자가 Dynamic Performance View 에 조회를 하게 되면 오라클은 해당 정보를 Control file 이나 현재 메모리에서 조회하여 보여준다.









    연습문제

    1) 아래와 같은 구조의 일반 테이블을 생성하시오.






    2) 위 1번 문제에서 생성한 new_emp 테이블에서 NO , NAME , HIREDATE 컬럼만 가져와서 아래 그림과 같이 new_emp2 테이블을 생성하는 쿼리를 쓰세요.




    3) 위 2번 문제에서 생성한 new_emp2 테이블과 동일한 구조의 테이블을 new_emp3 이름으로 생성하되 테이블 구조만 가져오고 데이터는 가져오지 않도록 하는 쿼리를 쓰세요.





    4) 위 2번 문제에서 생성한 new_emp2 테이블에 DATE 타입을 가진 BIRTHDAY 컬럼을 추가하는 쿼리를 쓰세요. 단 해당 컬럼이 추가될 때 기본값으로 현재날짜 ( SYSDATE ) 가 자동으로 입력되도록 하세요.





    5) 위 4번 문제에서 생성한 new_emp2 테이블의 BIRTHDAY 컬럼 이름을 BIRTH 로 변경하는 쿼리를 쓰세요.





    6) 위 4번 문제에서 생성한 new_emp2 테이블의 NO 컬럼의 길이를 NUMBER(7) 로 변경하는 쿼리를 쓰세요





    7) new_emp2 테이블의 컬럼 중에서 BIRTH 컬럼을 삭제하는 쿼리를 쓰세요.





    8) new_emp2 테이블의 컬럼은 남겨 놓고 데이터만 지우는 쿼리를 쓰세요.





    9) new_emp2 테이블을 완전히 삭제하는 쿼리를 쓰세요









    [출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

    저작자표시 비영리 변경금지

    'Oracle > sql' 카테고리의 다른 글

    [SQL] 7. Constraint(제약조건)  (0) 2016.09.18
    [SQL] 6. DML  (0) 2016.09.16
    [SQL] 4. JOIN  (0) 2016.09.12
    [SQL] 3. 복수행함수(그룹함수)  (1) 2016.09.05
    [SQL] 2. 단일행함수(정규식표현)  (0) 2016.09.05

     JOIN



    정규화를 수행하면 하나의 테이블이 여러 개의 테이블들로 나누어져서 저장되고, 이때 여러 테이블에 흩어져 있는 데이터들을 조합해서 가져오는 기술.

    Join 문법은Oracle 용 문법이 있고 모든 제품들에서 공통적으로 사용 가능한 표준 (ANSI ) join 문법이 있음.



  • ORACLE 문법

    SQL> SELECT a.col1, b.col1         // 테이블별칭.컬럼명
2  FROM   table1 a, table2 b     // table1의 별칭 = a
3  WHERE a.col2 = b.col2;

  • ANSI 문법(표준)

    SQL> SELECT a.col1, b.col1
2  FROM   table1 a [INNER] JOIN table2 b
3  ON a.col2 = b.col2;

    • INNER : default. 조인에 참여하는 모든 테이블에 존재하는 데이터만 출력(equi join, non-equi join)
    • OUTER : 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 출력(outer join)



    조인이 수행될 때는 두 개 이상의 테이블이 사용되는데 이때 둘 중 하나의 테이블을 먼저 읽고 조인 조건 절을 확인하여 나머지 테이블에 가서 데이터를 가져 오게 됨.

    먼저 읽는 테이블을 선행 테이블 (driving table 또는 Inner table) 이라고 하고 뒤에 읽는 테이블을 후행 테이블 (driven table 또는 Outer table) 이라고 함. 그리고 선행 테이블은 조회할 데이터가 적은 테이블로 선택해야 속도 면에서 유리.






    Cartesian Product (카티션 곱)

    join 쿼리 중에 WHERE 절에 기술하는 join 조건이 잘못 되었거나 지정하지 않을경우 해당 테이블에 대한 모든 데이터를 전부 가져오게됨. (ANSI SQL 에서는 CROSS JOIN 이라고도 함)




    예제
  • step1. 테스트용 테이블생성 및 데이터입력
    • SQL>CREATE TABLE cat_a (no NUMBER , name VARCHAR2(1));
Table created.
SQL>INSERT INTO cat_a VALUES (1,'A');
1 row created.
SQL>INSERT INTO cat_a VALUES (2,'B');
1 row created.

SQL>CREATE TABLE cat_b (no NUMBER, name VARCHAR2(1));
Table created.
SQL>INSERT INTO cat_b VALUES (1,'C');
1 row created.
SQL>INSERT INTO cat_b VALUES (2,'D');
1 row created.

SQL>CREATE TABLE cat_c (no NUMBER, name VARCHAR2(1));
Table created.
SQL>INSERT INTO cat_c VALUES (1,'E');
1 row created.
SQL>INSERT INTO cat_c VALUES (2,'F');
1 row created.

SQL>commit;
Commit complete.
  • step2. 생성된 테이블확인
    • SQL>SELECT * FROM cat_a;

        NO NAME
---------- -----
         1 A
         2 B

SQL>SELECT * FROM cat_b;

        NO NAME
---------- -----
         1 C
         2 D

SQL>SELECT * FROM cat_c;

        NO NAME
---------- -----
         1 E
         2 F
  • step3. 2개의 테이블로 정상적인 조인 수행
    • SQL>SELECT a.name, b.name
  2  FROM cat_a a, cat_b b
  3  WHERE a.no = b.no;

NAME  NAME
----- -----
A     C
B     D
  • step4. 2개의 테이블로 카티션 곱 생
    • SQL>SELECT a.name, b.name
  2  FROM cat_a a, cat_b b;         // where 조건 빠짐

NAME  NAME
----- -----
A     C
A     D
B     C
B     D
  • step5. 3개의 테이블로 정상적인 조인 수행
    • SQL>SELECT a.name, b.name, c.name
  2  FROM cat_a a, cat_b b, cat_c c
  3  WHERE a.no = b.no
  4  AND a.no = c.no;

NAME  NAME  NAME
----- ----- -----
A     C     E
B     D     F
  • step6. 3개의 테이블을 조회하되 조인 조건절은 2개 테이블에만 줘서 카티션 곱 생성
    • SQL>SELECT a.name, b.name, c.name
  2  FROM cat_a a, cat_b b, cat_c c
  3  WHERE a.no = b.no;              // cat_c 테이블 조건이 빠짐

NAME  NAME  NAME
----- ----- -----
A     C     E
A     C     F
B     D     E
B     D     F

    테스트용 테이블에 1만 건 정도 insert 한 후 카티션 곱을 사용하면 1만 X 1만 = 1억 건의 데이터가 순식간에 생기게 되는데 이처럼 현업에서 쿼리 성능을 테스트할때 의도적으로 대량의 테이블을 생성해 테스트 하는 경우도 있음.



    EQUI Join (등가 Join)

    가장 많이 사용되는 조인으로 선행 테이블에서 데이터를 가져 온 후 조인 조건절을 검사해서 동일한 조건을 가진 데이터를 후행 테이블에서 꺼내 오는 방법. (조건절에서 Equal 연산자 (=) 를 사용해서 EQUI Join)




    ex1) emp 테이블과 dept 테이블 조회하여 이름과 부서명 출력하기
  • Oracle Join
    •     SQL>SELECT e.empno, e.ename, d.dname
    2  FROM emp e, dept d
    3  WHERE e.deptno = d.deptno;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7782 CLARK      ACCOUNTING
      7839 KING       ACCOUNTING
      7566 JONES      RESEARCH
      7369 SMITH      RESEARCH
      7654 MARTIN     SALES
      7844 TURNER     SALES
  • ANSI Join
    •     SQL>SELECT e.empno, e.ename, d.dname
    2  FROM emp e JOIN dept d
    3  ON e.deptno = d.deptno;

    컬럼 이름이 하나의 테이블에만 있을 경우에는 테이블 이름을 생략해도 자동으로 테이블 이름을 찾아서 실행 하기도 함. 그러나 양쪽 테이블에 모두 있는 컬럼일 경우는 반드시 테이블 이름을 적어야 함.


    ex2) 학생 테이블(student)과 교수 테이블(professor)을 join 하여 학생의 이름과 지도교수 이름 출력하기
  • Oracle Join
    • SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno;

STU_NAME                       PROF_NAME
------------------------------ --------------------
James Seo                      Audie Murphy
Billy Crystal                  Angela Bassett
Richard Dreyfus                Angela Bassett
Tim Robbins                    Winona Ryder
Rene Russo                     Winona Ryder
Nicholas Cage                  Michelle Pfeiffer
Sandra Bullock                 Julia Roberts
Demi Moore                     Meryl Streep
Macaulay Culkin                Meryl Streep
Wesley Snipes                  Susan Sarandon
Danny Glover                   Nicole Kidman
  • ANSI Join
    • SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s JOIN professor p
  3  ON s.profno = p.profno;

    Equi join 의 특성:
    양쪽 테이블에 모두 데이터가 존재해야 결과가 나오는데 위의예제에서 5명의 학생은 지도 교수가 결정이 안된 상태라서 결과에서 빠졌음. 만약 이렇게 한쪽테이블에 없는 데이터까지 모두 출력하고 싶다면 Outer Join 을 사용하면 됨.


    ex3) 학생 테이블(student)과 학과 테이블(department) , 교수 테이블(professor) 을 Join하여 학생의 이름과 학생의 학과이름, 학생의 지도교수 이름 출력하기
  • Oracle Join
    • SQL>SELECT s.name "STU_NAME", d.dname "DEPT_NAME", p.name "PROF_NAME"
  2  FROM student s, department d, professor p
  3  WHERE s.deptno1 = d.deptno
  4  AND s.profno = p.profno;

STU_NAME             DEPT_NAME                           PROF_NAME
-------------------- ----------------------------------- --------------------
James Seo            Computer Engineering                Audie Murphy
Billy Crystal        Computer Engineering                Angela Bassett
Richard Dreyfus      Computer Engineering                Angela Bassett
Tim Robbins          Multimedia Engineering              Winona Ryder
Rene Russo           Multimedia Engineering              Winona Ryder
Nicholas Cage        Multimedia Engineering              Michelle Pfeiffer
Sandra Bullock       Software Engineering                Julia Roberts
Macaulay Culkin      Electronic Engineering              Meryl Streep
Demi Moore           Electronic Engineering              Meryl Streep
Wesley Snipes        Electronic Engineering              Susan Sarandon
Micheal Keaton       Mechanical Engineering              Nicole Kidman
Danny Glover         Mechanical Engineering              Nicole Kidman
  • ANSI Join
    • SQL>SELECT s.name "STU_NAME", d.dname "DEPT_NAME", p.name "PROF_NAME"
  2  FROM student s JOIN department d
  3  ON s.deptno1 = d.deptno
  4  JOIN professor p
  5  ON s.profno = p.profno5

    ANSI Join 의 경우는 쿼리의 2번과 3번 행의 조건으로 먼저 Join 을 수행 후 나온 결과 값을 가지고 4번과 5번 행의 조건으로 Join 을 수행. Join 테이블이 더 늘어날 경우 계속 JOIN~ON 조건을 추가 하면 됨.



    ex4) student 테이블을 조회하여 1전공(deptno1)이 101번인 학생들의 이름과 각 학생들의 지도교수 이름 출력하기.
  • Oracle Join
    • SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno
  4  AND s.deptno1 = 101;

STU_NAME             PROF_NAME
-------------------- --------------------
James Seo            Audie Murphy
Richard Dreyfus      Angela Bassett
Billy Crystal        Angela Bassett
  • ANSI Join
    • SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s JOIN professor p
  3  ON s.profno = p.profno
  4  AND s.deptno1 = 101;

    3번 줄은 Join 을 수행하기 위한 조건이라서 Join 조건이라 부르고 4번 줄은 검색을 하는 용도로 사용되기 때문에 검색 조건이라고 부름. 위와 같이 Join조건과 검색조건이 동시에 있을 경우 당연히 검색 조건을 먼저 검색해서 데이터의 검색 범위를 줄여 놓고 Join 작업을 수행하게 된다. 검색 조건을 잘 활용해야 Join 작업의 속도가 빨라짐.





    Non-Equi Join (비등가 Join)

    같은 조건이 아닌 크거나 작거나 하는 경우의 조건으로 조회를 해야 할 경우.




    ex1) Customer 테이블과 gift 테이블을 Join하여 고객별로 마일리지 포인트를 조회한 후 해당 마일리지 점수로 받을 수 있는 상품을 조회하여 고객의 이름과 받을 수 있는 상품 명 출력하기
  • Oracle Join
    • SQL> SELECT c.gname "C_NAME", TO_CHAR(c.point, '999,999') "POINT",
  2         g.gname "G_NAME"
  3  FROM customer c, gift g
  4  WHERE c.point BETWEEN g.g_start AND g.g_end;      // 비트윈보다 비교연산자가 더 성능좋음

C_NAME                         POINT    G_NAME
------------------------------ -------- ------------------------------
Bill Pullman                     65,000 Tuna Set
Mel Gibson                       73,000 Tuna Set
Brad Pitt                       110,000 Shampoo Set
Samuel Jackson                  153,000 Shampoo Set
Arnold Scharz                   265,000 Car wash Set
Ahnjihye                        273,000 Car wash Set
Jim Carrey                      315,000 Kitchen Supplies Set
  • ANSI Join
    • SQL> SELECT c.gname "C_NAME", TO_CHAR(c.point, '999,999') "POINT",
  2         g.gname "G_NAME"
  3  FROM customer c JOIN gift g
  4  ON c.point >= g.g_start
  5  AND c.point <= g.g_end;           //    비교연산자 사용



    ex2) Student 테이블과 score 테이블 , hakjum 테이블을 조회하여 학생들의 이름과 점수와 학점 출력하기
  • Oracle Join
    • SQL> SELECT s.name "STU_NAME", r.total "SCORE", h.grade "GRADE"
  2  FROM student s, score r, hakjum h
  3  WHERE s.studno = r.studno
  4  AND r.total >= h.min_point
  5  AND r.total <= h.max_point;

STU_NAME                            SCORE GRA
------------------------------ ---------- ---
James Seo                              97 A+
Macaulay Culkin                        95 A0
Richard Dreyfus                        89 B+
Anthony Hopkins                        84 B0
Sandra Bullock                         83 B0
Bill Murray                            79 C+
Rene Russo                             78 C+
Demi Moore                             62 D
  • ANSI Join
    • SQL> SELECT s.name "STU_NAME", r.total "SCORE", h.grade "GRADE"
  2  FROM student s JOIN score r
  3  ON s.studno = r.studno
  4  JOIN hakjum h
  4  ON r.total >= h.min_point
  5  AND r.total <= h.max_point;







    OUTER Join

    Inner Join(등가,비등가조인)과는 반대로 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 출력하게 하는 방법. (Oracle Join 과 ANSI Join 구문이 차이가 있음.)




    ex1) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름을 출력. 단, 지도교수가 결정되지 않은 학생의 명단도 함께 출력하기
  • Oracle Join
    • SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno(+);      //데이터가 없는쪽에 (+)표시 추가

STU_NAME                       PROF_NAME
------------------------------ --------------------
James Seo                      Audie Murphy
Richard Dreyfus                Angela Bassett
Billy Crystal                  Angela Bassett
Tim Robbins                    Winona Ryder
Rene Russo                     Winona Ryder
Nicholas Cage                  Michelle Pfeiffer
Sandra Bullock                 Julia Roberts
Macaulay Culkin                Meryl Streep
Demi Moore                     Meryl Streep
Wesley Snipes                  Susan Sarandon
Steve Martin                   Nicole Kidman
Micheal Keaton                 Nicole Kidman
Danny Glover                   Nicole Kidman
Daniel Day-Lewis               Jodie Foster
Bill Murray                    Jodie Foster
Anthony Hopkins
Charlie Sheen
Christian Slater
Sean Connery
Danny Devito
  • ANSI Join
    • SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s LEFT OUTER JOIN professor p
  3  ON s.profno = p.profno;

    Oracle Outer Join 의 경우는 데이터가 없는 쪽에 (+) 표시를 하지만 ANSI Outer Join 은 데이터가 존재하는 쪽에 표시를 함. ON 조건절중에 = 기호를 기준으로 왼쪽인 학생 데이터는 존재하고 교수는 존재하지 않으니까 LEFT.




    ex2) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름 출력하기. 단 지도학생이 결정되지 않은 교수의 명단도 함께 출력
  • Oracle Join
    • SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno(+) = p.profno
  4  ORDER BY s.name;

STU_NAME                       PROF_NAME
------------------------------ --------------------
Bill Murray                    Jodie Foster
Billy Crystal                  Angela Bassett
Daniel Day-Lewis               Jodie Foster
Danny Glover                   Nicole Kidman
Demi Moore                     Meryl Streep
James Seo                      Audie Murphy
Macaulay Culkin                Meryl Streep
Micheal Keaton                 Nicole Kidman
Nicholas Cage                  Michelle Pfeiffer
Rene Russo                     Winona Ryder
Richard Dreyfus                Angela Bassett
Sandra Bullock                 Julia Roberts
Steve Martin                   Nicole Kidman
Tim Robbins                    Winona Ryder
Wesley Snipes                  Susan Sarandon
                               Jessica Lange
                               Andie Macdowell
                               Meg Ryan
                               Holly Hunter
                               Sharon Stone
                               Whoopi Goldberg
                               Emma Thompson
  • ANSI Join
    • SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s RIGHT OUTER JOIN professor p
  3  ON s.profno = p.profno;





    ex3) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름 출력하기. 단 지도학생이 결정 안 된 교수 명단과 지도 교수가 결정 안된 학생 명단을 한꺼번에 출력

    Oracle에서는 양쪽 데이터를 모두 출력하려면 두 Outer Join 을 각 각 수행 한 후 Union 을 사용하여 결과를 인위적으로 합쳐서 출력을 시키는 방법을 많이 사용함.(ANSI 에서는 FULL OUTER JOIN 사용하면됨)

  • Oracle Join
    • SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno(+)
  4  UNION
  5  SELECT s.name "STU_NAME", p.name "PROF_NAME"
  6  FROM student s, professor p
  7  WHERE s.profno(+) = p.profno;
  • ANSI Join
    • SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s FULL OUTER JOIN professor p
  3  ON s.profno = p.profno;

STU_NAME                       PROF_NAME
------------------------------ --------------------
James Seo                      Audie Murphy
Rene Russo                     Winona Ryder
Sandra Bullock                 Julia Roberts
Demi Moore                     Meryl Streep
Danny Glover                   Nicole Kidman
Billy Crystal                  Angela Bassett
Nicholas Cage                  Michelle Pfeiffer
Micheal Keaton                 Nicole Kidman
Bill Murray                    Jodie Foster
Macaulay Culkin                Meryl Streep
Richard Dreyfus                Angela Bassett
Tim Robbins                    Winona Ryder
Wesley Snipes                  Susan Sarandon
Steve Martin                   Nicole Kidman
Daniel Day-Lewis               Jodie Foster
Danny Devito
Sean Connery
Christian Slater
Charlie Sheen
Anthony Hopkins
                               Emma Thompson
                               Andie Macdowell
                               Whoopi Goldberg
                               Meg Ryan
                               Sharon Stone
                               Jessica Lange
                               Holly Hunter

    OUTER Join을 조심해야 하는 이유
    모든 데이터를 다 출력할 수 있기 때문에 좋을 것 같지만 이 Join방식은 DB 성능에 아주 나쁜 영향을 줄 수 있다. 왜냐면 예를들어, A 테이블과 B 테이블을 Outer Join 을 수행해서 A 테이블에 있는 데이터를 다 검색하는 경우 만약 A 테이블에 인덱스가 있어도 인덱스를 쓰지 않고 Full Scan 을 하기 때문. 그리고 아우터 조인이 발생할 경우 튜닝에서 아주 중요하게 생각하는 조인 순서가 고정되어 사용자의 뜻대로 변경할 수 없기 때문에 심각한 문제를 유발 할 수 있다.

    작성한 SQL 이 속도가 늦을 경우 실행계획을 확인하는 방법을 예시를 통해 알아보자.

  • step1 : sys 계정으로 로그인해 plustrace 라는 권한을 생성
    • SCOTT> conn / as sysdba ;
SYS>@?/sqlplus/admin/plustrce.sql
SYS>
SYS>drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SYS>create role plustrace;
Role created.

SYS>grant select on v_$sesstat to plustrace;
Grant succeeded.

SYS>grant select on v_$statname to plustrace;
Grant succeeded.

SYS>grant select on v_$mystat to plustrace;
Grant succeeded.

SYS>grant plustrace to dba with admin option;
Grant succeeded.

SYS>
SYS>set echo off
  • step2 : plustrace 라는 권한을 scott 에게 할당
    • SYS>GRANT plustrace TO scott;
Grant succeeded.
  • step3 : scott 으로 로그인 한 후 실행계획을 저장할 plan table 을 생성
    • SYS>CONN scott/tiger
Connected.
SCOTT>
SCOTT>@?/rdbms/admin/utlxplan.sql
Table created.

    실행계획 확인할 준비 끝! 이제 쿼리를 수행하기 전에 실행계획을 사용하라고 설정을 한 후 쿼리를 수행하면 된다.

    SCOTT>SET autot traceonly;
SCOTT>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno(+) = p.profno
  4  AND p.profno > 4004;

Execution Plan
----------------------------------------------------------
Plan hash value: 427587263

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    36 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN OUTER             |              |     1 |    36 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PROFESSOR    |     1 |    18 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0011128 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | STUDENT      |     2 |    36 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."PROFNO"(+)="P"."PROFNO")
   3 - access("P"."PROFNO">4004)
   4 - filter("S"."PROFNO"(+)>4004)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        608  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

    실행계획 해석하는 방법 :
    먼저 윗줄과 아랫줄을 비교해서 오른쪽으로 들여쓰기 된 줄을 먼저 실행한다. 그리고 만약 윗줄과 아랫줄이 들여쓰기가 같다면 윗 줄부터 실행한다.
    위 실행계획(Execution Plan)에서 id 가 0 번줄과 1번줄을 비교하면 1번줄을 먼저 해야 하고 1번 줄과 2번 줄을 비교하면 2번줄을 먼저 수행해야 하는데 2번 밑에 3번줄이 있기 때문에 2번줄을 수행하기 전에 3번 줄과 한번 더 비교한다. 2번 줄과 3번 줄은 들여쓰기가 같으므로 위 실행계획에서 가장 먼저 실행되는 것은 id 가 2 번인 줄이며 TABLE ACCESS FULL 은 테이블 전체를 다 읽었다라는 뜻이다.
    실행계획의 Name 컬럼을 보면 PROFESSOR 테이블을 먼저 읽었고 Rows 컬럼을 보면 3건을 가져왔다는 것을 알 수 있다.(즉 Professor 테이블을 전부 읽어서 데이터를 3 건 찾아오고 student 테이블도 전부 읽어서 2 건의 데이터를 가져왔다는 의미.)
    현재 professor 테이블의 profno 컬럼에 인덱스가 있는 상황에서 일부 교수들만 찾으라고 쿼리를 작성했지만 위 화면에서 아래에서 두 번째 줄을 보면 TABLE ACCESS FULL PROFESSOR 부분에서 테이블을 모두 읽었다고 알려주고 있다. (예를 들어 교수 테이블에 100 만 건 데이터가 있고 그 중에서 1 건만 읽으라고 시켜도 OUTER Join 은 필요 없는 100 만 건 데이터를 모두 다 읽고 그 중에서 1 건을 선택을 한다는 의미)



    만약 아우터 조인이 아닌 일반 조인으로 작업을 했을 경우에 해당 테이블에 인덱스가 있을 경우 아래와 같이 인덱스를 사용하는 방법을 선택. 일반적으로 이 방법이 더 빠른 방법. 


    동일한 테이블에 동일한 조건으로 쿼리를 수행했을 때 OUTER Join 이 아닐경우는 속도가 빠른 방법을 선택하지만 OUTER Join 을 사용할 경우 속도가 늦어 진다는 것을 확인.



    결론: OUTER JOIN은 쓰지말자.








    SELF Join

    원하는 데이터가 하나의 테이블에 다 들어 있을 경우(데이터를 가지고 있는 하나의 테이블을 메모리에서 별명을 두 개로 사용해서 호출하는 방법으로 2개의 테이블로 만든 후 일반적인 Join 작업을 수행함.)




    ex) 사원들의 상사이름 출력하기



  • Oracle Join
    • SQL> SELECT e1.ename "ENAME", e2.ename "MGR_ENAME"
  2  FROM emp e1, emp e2
  3  WHERE e1.mgr = e2.empno;

ENAME      MGR_ENAME
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
WARD       BLAKE
MARTIN     BLAKE
ALLEN      BLAKE
MILLER     CLARK
CLARK      KING
JONES      KING
BLAKE      KING
SMITH      FORD
  • ANSI Join
    • SQL> SELECT e1.ename "ENAME", e2.ename "MGR_ENAME"
  2  FROM emp e1 JOIN emp e2
  3  ON e1.mgr = e2.empno;



    연습문제



    1) Emp2 테이블과 p_grade 테이블을 조회하여 사원들의 이름과 나이, 현재 직급 , 예상 직급 을 출력하시오. 예상 직급은 나이로 계산하며 해당 나이가 받아야 하는 직급을 의미함. 나이는 오늘(sysdate)을 기준으로 하되 trunc 로 소수점 이하는 절삭해서 계산.



  • ANSI
  • ORACLE




    • 2) customer 테이블과 gift 테이블을 Join하여 고객이 자기 포인트보다 낮은 포인트의 상품 중 한가지를 선택할 수 있다고 할 때 Notebook 을 선택할 수 있는 고객명과 포인트, 상품명을 출력하시오.



  • ANSI
  • ORACLE





    • 3) professor 테이블에서 교수번호, 교수이름, 입사일, 자신보다 입사일 빠른 사람 인원수를 출력하시오. 단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력.








    [출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

    저작자표시 비영리 변경금지

    'Oracle > sql' 카테고리의 다른 글

    [SQL] 6. DML  (0) 2016.09.16
    [SQL] 5. DDL과 Data Dictionary  (0) 2016.09.15
    [SQL] 3. 복수행함수(그룹함수)  (1) 2016.09.05
    [SQL] 2. 단일행함수(정규식표현)  (0) 2016.09.05
    [SQL] 2. 단일행함수(일반함수)  (0) 2016.09.02







    내일이다아!!!

    저작자표시 비영리 변경금지

    '스크랩' 카테고리의 다른 글

    [여행] 죽기전에 도전하고 싶은 여행 버킷리스트 13  (0) 2016.01.09
    [노오븐디저트] 단호박 시트 치즈케이크  (0) 2016.01.05
    [KINTEX] NASA - A Human Adventure Exhibition  (0) 2015.12.30
    저커버그의 성공 비지니스를 위한 16가지 명언  (0) 2015.12.09

    티스토리툴바