오라클 서버에서 사용자의 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값 반환
사용자가 선언하여 생성 후 사용하고 주로 여러 개의 행을 처리하고자 할 경우 사용한다. (변수에 데이터를 담기 이전에 사용되는것이므로 복합변수와 헷갈리면 안된다)
많은 데이터를 변수에 담으려면 복합변수를 써야 하는데 이때 명시적 커서를 사용해야 성능이 더 좋아진다.
묵시적 커서와는 다르게 동시에 여러 개가 선언되어 사용될 수 있다.
커서이름%ROWCOUNT : FETCH 문에 의해 읽혀진 데이터의 총 행 수(가장 마지막에 처리된 행이 몇 번째 인지를 반환)
커서이름%FOUND : FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환
커서이름%NOTFOUND : FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 없을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환
커서이름%ISOPEN : 명시적 커서가 메모리에 확보(선언)되어 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값 반환
명시적 커서 처리단계
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 의 정보를 명시적 커서로 가져 온 후 변수에 저장하고 화면에 출력)
확정된 형태의 하나의 값을 가지는 변수의 데이터 형을 직접 지정해 주는 변수. (숫자, 문자, 날짜등)
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
%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]...);
대부분의 경우 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 설정 후 사용하면 자동으로 바인드 변수 값을 출력 해서 보여준다.
변수의 적용 범위
외부 블록에서 선언한 변수는 내부 블록에서도 사용할 수 있지만 내부블록에서 선언된 변수는 외부 블록에서 사용할 수 없다.
procedural language/SQL 의 약자로 block 구조의 언어
(Pascal -> Ada -> PL/SQL)
프로그래밍적 요소와 함께 데이터 트랜잭션 처리능력, 데이터에 대한 보안 및 예외처리 기능, 객체지향 등 데이터베이스와 관련된 중요한 모든 기능을 지원해 오라클 데이터베이스와 연관된 업무를 처리하기에 최적화된 언어.
PL/SQL 동작원리
사용자가 pl/sql BLOCK을 실행하면 해당 블록이 오라클 서버로 전달되서 오라클 서버 내 메모리에 상주해 있는 PL/SQL 엔진이 해당 블록을 받게됨.
그 후 해당 블록에 있든 모든 SQL 문장들은 분리가 되서 오라클 서버 프로세스에게 전달되 먼저 수행됨.
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
연결 연산자
;
명령문 종료
리터럴 : 변수에 할당되는 모든 값 (식별자가 아닌 모든 문자, 숫자, 부울, 또는 날짜 값)
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>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.
테이블 이름은 반드시 문자로 시작. 특수문자도 가능하지만 테이블 생성시 “ (겹따옴표) 로 감싸야 하며 권장하지 않음.
테이블 이름이나 컬럼 이름은 최대 30 bytes 까지 가능. 즉 한글로 테이블 이름을 생성할 경우 최대 15글자 까지만 가능.
테이블 이름은 한 명의 사용자가 다른 오브젝트들의 이름과 중복으로 사용할 수 없음. ( 예를 들어 scott 사용자가 테이블명을 test 로 생성한 후 다른 테이블 이름을 test 로 동일하게 사용할 수 없다는 것. 그러나 다른 사용자인 hr 사용자는 test 테이블 생성할 수 있음)
테이블 이름이나 오브젝트 이름을 오라클이 사용하는 키워드를 사용하지 말것. (미리 정해진 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.그리고 세션별로 만들어지기 때문에 다른 세션에서 테이블을 공유할 수 없다.
다른 특징들
Redo Log 를 생성하지 않음.
Index , View , Trigger 를 생성할 수 있으나 이 오브젝트들의 타입도 전부 Temporary 임.
이 테이블에 들어 있는 데이터는 이전이나 백업을 할 수 없음.
실습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 줄에 틀린 조건을 줄 경우 그 조건에 해당되는 데이터가 없기 때문에 데이터는 못 가져오고 테이블 구조만 생성하는 것
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
---------- ----------
ANALYZE 명령어는 실제 테이블이나 인덱스, 클러스터 등을 하나씩 조사해서 그 결과를 딕셔너리에 반영시키는 명령어
Dynamic Performance View
Static Data Dictionary 와 반대로 Dynamic Performance View 는 정보를 실시간으로 조회하게 되며 사용자가 Dynamic Performance View 에 조회를 하게 되면 오라클은 해당 정보를 Control file 이나 현재 메모리에서 조회하여 보여준다.
연습문제
1) 아래와 같은 구조의 일반 테이블을 생성하시오.
SCOTT>CREATE TABLE new_emp
2 ( no NUMBER(5),
3 name VARCHAR2(20),
4 hiredate DATE,
5 bonus NUMBER(6,2) );
Table created.
2) 위 1번 문제에서 생성한 new_emp 테이블에서 NO , NAME , HIREDATE 컬럼만 가져와서 아래 그림과 같이 new_emp2 테이블을 생성하는 쿼리를 쓰세요.
SCOTT>CREATE TABLE new_emp2
2 AS
3 SELECT no, name, hiredate
4 FROM new_emp;
3) 위 2번 문제에서 생성한 new_emp2 테이블과 동일한 구조의 테이블을 new_emp3 이름으로 생성하되 테이블 구조만 가져오고 데이터는 가져오지 않도록 하는 쿼리를 쓰세요.
SCOTT>CREATE TABLE new_emp3
2 AS
3 SELECT no, name, hiredate
4 FROM new_emp2
5 WHERE 1 = 2;
4) 위 2번 문제에서 생성한 new_emp2 테이블에 DATE 타입을 가진 BIRTHDAY 컬럼을 추가하는 쿼리를 쓰세요. 단 해당 컬럼이 추가될 때 기본값으로 현재날짜 ( SYSDATE ) 가 자동으로 입력되도록 하세요.
SCOTT>ALTER TABLE new_emp2
2 ADD (birthday DATE DEFAULT SYSDATE);
5) 위 4번 문제에서 생성한 new_emp2 테이블의 BIRTHDAY 컬럼 이름을 BIRTH 로 변경하는 쿼리를 쓰세요.
SCOTT>ALTER TABLE new_emp2
2 RENAME COLUMN birthday TO birth;
6) 위 4번 문제에서 생성한 new_emp2 테이블의 NO 컬럼의 길이를 NUMBER(7) 로 변경하는 쿼리를 쓰세요
SCOTT>ALTER TABLE new_emp2
2 MODIFY (no NUMBER(7));
정규화를 수행하면 하나의 테이블이 여러 개의 테이블들로 나누어져서 저장되고, 이때 여러 테이블에 흩어져 있는 데이터들을 조합해서 가져오는 기술.
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 을 생성
실행계획 확인할 준비 끝!
이제 쿼리를 수행하기 전에 실행계획을 사용하라고 설정을 한 후 쿼리를 수행하면 된다.
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
SQL> SELECT e.name "NAME",
2 TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 "AGE",
3 e.position "CURR_POSITION",
4 p.position "BE_POSITION"
5 FROM emp2 e RIGHT OUTER JOIN p_grade p
6 ON TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 BETWEEN s_age AND e_age;
ORACLE
SQL> SELECT e.name "NAME",
2 TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 "AGE",
3 e.position "CURR_POSITION",
4 p.position "BE_POSITION"
5 FROM emp2 e, p_grade p
6 WHERE TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 >= s_age
7 AND TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 <= e_age;
2) customer 테이블과 gift 테이블을 Join하여 고객이 자기 포인트보다 낮은 포인트의 상품 중 한가지를 선택할 수 있다고 할 때 Notebook 을 선택할 수 있는 고객명과 포인트, 상품명을 출력하시오.
ANSI
SQL> SELECT c.gname "CUST_NAME", c.point "POINT",
2 g.gname "GIFT_NAME"
3 FROM customer c JOIN gift g
4 ON g.gname = 'Notebook'
5 AND c.point <= g.g_end
6 AND c.point >= g.g_start ;
ORACLE
SQL> SELECT c.gname "CUST_NAME", c.point "POINT",
2 g.gname "GIFT_NAME"
3 FROM customer c, gift g
4 WHERE g.gname = 'Notebook'
5 AND c.point <= g.g_end
6 AND c.point >= g.g_start ;
3) professor 테이블에서 교수번호, 교수이름, 입사일, 자신보다 입사일 빠른 사람 인원수를 출력하시오. 단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력.
SQL> SELECT p.profno, p.name, p.hiredate,
2 COUNT(p2.hiredate) "COUNT"
3 FROM professor p, professor p2
4 WHERE p2.hiredate < p.hiredate
5 GROUP BY p.profno, p.name, p.hiredate
6 ORDER BY 4;