일반적으로 배치작업에서 주로 사용되며, 각 테이블을 Sort한 후 Merge 하는 조인을 말한다
-. /*+ use_merge(테이블) */
-. 동시에 각각의 테이블이 자신의 처리범위를 액세스하여 정렬해둠
-. 각 테이블은 어떠한 상수값도 서로 영향을 주지 않으며, 주어진 상수값에 의해서만 각자 범위를 줄이게됨
-. 전체범위처리를하며 부분범위처리를 할수 없음
-. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우에만 Random Access이고, Merge작업은 Scan방식
-. 선택적으로 연결고리가 되는 컬럼은 인덱스를 사용하지 않음
-. 조인의 방향과는 상관없음
-. Equal 조인에서만 가능
-. 처리량이 많은 경우로 Random Access를 하지 않음으로 전체범위처리에 유리
-. 자신의 처리범위를 인덱스를 통해 어떻게 줄이느냐가 관건
-. 상수값을 받아 줄여진 범위가 30%이상이면 Sort Merge가 유리
- 두 개 컬럼 모두 같은 조건( 인덱스가 둘 다 없거나, 둘 다 있는 경우)인 경우에는 FROM절의
가장 오른쪽에 정의된 테이블이 구동테이블이 됨.
SQL>select a.empno , a.ename , a.sal, b.deptno, b.dname, b.loc
from big_emp a , big_dept b
where a.deptno = b.deptno
and b.deptno between 10 and 40
and b.loc = 'DALLAS' ;
테이블을 조인 하는 경우 중첩 루프 조인(Nested Loop Join)이 일어나도록 하는 힌트 문장 입니다. 중첩 루프 조인은 중첩 반복이라고도 하는데 하나의 테이블(outer/driving table)에서 추출된 로우를 가지고 일일이 다른 테이블(inner/probed table)을 반복해서 조회하여 찾아지는 레코드를 최종 데이터로 간주하는 방법 입니다.
즉 조인 입력 한 개를 외부 입력 테이블로 사용하고, 한 개는 내부(최하위) 입력 테이블로 사용하고 외부 루프는 외부 입력 테이블을 행 단위로 사용하고 각 외부 행에 대해 실행되는 내부 루프는 내부 입력 테이블에서 일치되는 행을 검색 하는거죠… 이것을 원시 중첩 루프 조인이라고 하는데 검색에서 인덱스를 사용하는 경우에는 인덱스 중첩 루프 조인이라고 합니다.
예를 들어 EMP 테이블과 DEPT 테이블을 조인하는 경우 dept 테이블이 건수가 작다면 우선 이 테이블을 외부 루프로 해서 하나씩 읽으면서 이에 대응하는 emp 테이블의 데이터를 추출 하는 경우라면 중첩 루프 조인에 해당 합니다. 이때 emp 테이블의 경우 건수가 많다고 가정을 하면 대부분 인덱스를 이용하도록 emp 테이블의 외래키인 deptno 컬럼은 대부분 인덱스를 걸게 되죠^^
중첩 루프 조인은 테이블중 적어도 하나의 조인 컬럼에 대해 인덱스(or Hash Index)가 존재할 때 연관되는 방식으로 이 중첩 루프 조인에서 테이블중 하나의 테이블 또는 중간 결과 셋을 대상으로 FULL SCAN이 일어나게 됩니다. 이 테이블이 드라이빙 테이블이 되는데… 이 테이블의 데이터 건마다 나머지 테이블에서 원하는 데이터를 추출하기 위해 대부분 인덱스를 사용하게 되는 겁니다.
보통 USE_NL 힌트 구문은 ORDERED 힌트 구문과 같이 사용되는데 USE_NL이 취하는 인자는 FROM절에서 두번째 나오는 테이블(비드라이빙 테이블, inner/probed table)을 명시해 주어야 합니다. 안수로 사용되지 않은 첫 번째 테이블은 outer/driving table이 되는 것입니다.
선행적 특징을 작는데 먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정됨
Driving Table에 의해 범위가 결정되며 Driving Table의 범위가 적을수록 수행속도는 빨라진다
고로 Driving Table을 어던 테이블로 결정하느냐가 중요하다
-. /*+ use_nl (테이블) */
-. 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스하게됨, 즉 값을 받아서 처리범위가 정해짐
-. Driving Table의 인덱스 액세스는 첫번 로우만 Random Access이고, 나머지는 Scan, 연결작업은 Random Access임
-. 연결되는 방향에 따라 사용되는 인덱스들이 달라질 수 있음
-. 연결고리 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 있음
-. 연결작업 수행 후 체크되는 조건으로 부분범위처리를 하는 경우에는 조건의 범위가 넓거나 없다면 오히려 빨라짐
-. 전체가 아닌 부분범위 처리를 하는 경우 유리함
-. 조인되는 테이블중 어느 한쪽의 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리함
-. Driving Table의 처리량이 많거나 연결 테이블의 Random Access량이 많을 경우에는 분리함
-. 일반적으로 처리량이 적은 경우로서 Random Access를 많이 하므로, 온라인 어플리에서 유리함
-. Driving Table의 선택이 관건임
■ Nested-Loop Join 의 경우
- 한쪽 컬럼에만 인덱스가 생성되어 있는 경우에는 FROM절에 열거된 테이블의 순서와는 상관없
이 인덱스 없는 테이블이 우선순위가 됨
(Join 문장에는 WHERE 조건절이 없기 때문에 어떤 경우라도 하나의 테이블이 전체스캔 되어야
하는데 인덱스가 있는 테이블을 구동테이블로 선택시 인덱스를 전체 스캔하고 다시 테이블을 전
체 스캔을 시켜야 하기 때문에 오히려 인덱스가 없는 테이블을 전체 스캔하는 경우보다 더 많은
액세스를 할 수 있기 때문)
SQL>create index i_dept_deptno_loc On big_dept(deptno,loc) ;
SQL>select a.empno , a.ename , a.sal, b.deptno, b.dname, b.loc
from big_emp a , big_dept b
where a.deptno = b.deptno
and b.deptno between 10 and 40
and b.loc = 'DALLAS' ;
Rows Row Source Operation
------- ---------------------------------------------------
14728 NESTED LOOPS
28956 TABLE ACCESS FULL BIG_EMP <= InnerTable
14728 TABLE ACCESS BY INDEX ROWID BIG_DEPT <= Driving Table
43683 INDEX RANGE SCAN (object id 27502)
- 조인에 참여되는 공통컬럼 중 한쪽만 인덱스가 생성되어 있고 하나의 비조인 컬럼에 유일 인덱스
가 생성되어 있는 경우 유일 인덱스를 가진 테이블이 구동 테이블이 된다.
(Unique Index는 검색방법 중 대단히 좋은 성능을 보장해 주는 Index Type 임)
SQL>create unique index i_emp_empno on big_emp(empno);
SQL>create index i_emp_deptno on big_dept(deptno);
SQL>select big_emp.ename , big_dept.dname
from big_emp,big_dept
where big_emp.deptno = big_dept.deptno
and big_emp.empno = 4000;
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
2 TABLE ACCESS BY INDEX ROWID BIG_EMP <= driving table
2 INDEX UNIQUE SCAN (object id 27505)
1 TABLE ACCESS BY INDEX ROWID BIG_DEPT <= inner table
2 INDEX RANGE SCAN (object id 27506)
- 조인에 참여하는 공통컬럼이 모두 인덱스가 생성되어 있는 경우 FROM절로 부터 가장 오른쪽에 정
의된 테이블이 구동테이블.
SQL>create index i_emp_deptnoon on big_emp(deptno);
SQL>create index i_dept_deptno on big_dept(deptno);
SQL>select big_emp.ename , big_dept.dname
from big_emp,big_dept
where big_emp.deptno = big_dept.deptno
and big_emp.empno = 4000 ;
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
290 TABLE ACCESS FULL BIG_DEPT <= driving table
1 TABLE ACCESS BY INDEX ROWID BIG_EMP <= inner table
18806 INDEX RANGE SCAN (object id 27508)
/*
** Shared_pool의 hit ratio보는 스크립트.. <박제용>
**
** 이 영역은 SQL 쿼리문이 저장되고, 유저별 사용 영역과, 데이터 딕셔너리등이 저장된다.
** 만일 적게 할당되면 유저의 접속이 많아질수록 throughput에 큰 영향을 준다.
** hit ratio는 95% 이상을 유지시켜야 한다.
**
*/
select sum(gets) "Gets", sum(getmisses) "Misses",
(1-(sum(getmisses) / (sum(gets)+sum(getmisses))))*100
"HitRate"
from v$rowcache;
SGA에 큰 영향을 주는 파라미터들의 설정을 표시해주는 SQL
/*
** DB의 주요 메모리 사용 조회 <박제용>
**
** DB의 주요 메모리 사용을 보여준다. DB가 사용하는 메모리는
** v7.3의 경우 OS메모리의 2/5 를, v8.x 버젼의 경우 1/2 정도를
** 할당해 주는 것이 좋다.
**
*/
select name, value
from v$parameter
where name in('db_block_buffers','db_block_size','shared_pool_size','sort_area_size');
SGA중에 Block buffer의 현재 사용량과 빈공간을 보고싶을 때
/*
** DB_BLOCK_BUFFERS의 현재 사용 현황을 보여줌. <박제용>
**
** block_buffer를 튜닝하기 전에 현재의 사용현황을 보여준다.
** 이 데이터를 주기적으로 보관하여 분석한다.
*/
select decode(state, 0, 'FREE',
1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state)
"BLOCK STATUS", count(*)
from x$bh
group by decode(state,0, 'FREE',
1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state);
SGA의 Block buffer의 hitratio를 연산해주는 SQL
/*
** DB_BLOCK_BUFFERS의 hit ratio보는 스크립트.. <박제용>
**
** 이 영역은 유저의 쿼리 내용이 버퍼링 되는 공간으로 크기가 적으면
** 유저별로 과도한 disk read를 발생시킨다.
** hit ratio는 90~95% 이상을 유지시켜야 한다.
**
*/
select 1-(sum(decode(name, 'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0)) +
(sum(decode(name, 'consistent gets', value,0))))) * 100
"Read Hit Ratio"
from v$sysstat;
한 유저 스키마의 모든 객체를 Analyze 해주는 SQL
/*
** 테이블 analyze 스크립트 2.. <박제용>
** 한 유저에 속한 모든 객체를 analyze한다.
**
** 사용방법 SQL>@analyze0 [유저ID]
** 유저ID는 반드시 대문자로.
*/
exec dbms_utility.analyze_schema('&1','DELETE');
exec dbms_utility.analyze_schema('&1','COMPUTE');
지정한 테이블을 다시 Analyze 해주는 SQL
/*
** 테이블 analyze 스크립트.. <박제용>
** Query를 파싱하는 Optimizer로 하여금 더욱 정확하고, 빠른 파싱을 유도하기위해 Analyze를 한다.
**
** 사용방법 SQL>@analyze1 [테이블명]
*/
analyze table &1 delete statistics;
analyze table &1 compute statistics;
- Predefined Oracle Server
; 오라클에서 미리 정한 ERROR...(NO_DATA_FOUND등...)
- Non-predefined Oracle Server
; 자주일어나는 error가 아니어서 미리 define되어 있지 않은 error
- User-defined
; 오라클 서버 error가 아니라 user가 업무상 rule에 위배되는 사항을 exception처리 하는 것.
2. Predefined Exception Names
ACCESS_INTO_NULL ORA-06530 초기화되지 않은 object의 속성에 값을 assign하라.
ACCESS_INTO_NULL ORA-06530 초기화되지 않은 object의 속성에 값을 assign하라.
COLLECTION_IS_NULL ORA-06531 초기화되지 않은 nested table에 EXISTS와 다른 method를 적용시켜라.
CURSOR_ALREADY_OPEN ORA-06511 OPEN CURSOR가 이미 open되어 있다.
DUP_VAL_ON_INDEX ORA-00001 중복된 값을 insert하려고 한다.
INVALID_CURSOR ORA-01001 잘못된 cursor연산자를 발생시켰다.
INVALID_NUMBER ORA-01722 문자열을 number type으로 convertion 실패
LOGIN_DENIED ORA-01017 오라클 login시 username or password를 잘못 입력했다.
NO_DATA_FOUND ORA-01403 single row SELECT된 데이터가 없다.
NOT_LOGGED_ON ORA-01012 PL/SQL이 오라클에 connect되지 않은 상태에서 database를 호출하려고 한다.
PROGRAM_ERROR ORA-06501 PL/SQL이 내부적인 문제가 있다.
ROWTYPE_MISMATCH ORA-06504 Host cursor변수와 PL/SQL cursor변수가 return type과 일치하지 않는다.
BEGIN SELeCT ... COMMIT ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement ;
WHEN TOO_MANY_ROWS THEN
statement ;
WHEN OTHERS THEN
statement ;
END ;
4. Non-Predefined Error
DECLARE
e_products_invalid EXCEPTION ;
PRAGMA EXCEPTION_INIT (e_products_invalid, -2292 ) ;
v_message VARCHAR2(50) ;
BEGIN
...
EXCEPTION
WHEN e_products_invalid THEN
:g_message := 'Product code specified is not valid.' ;
...
END ;
--> -2292 error code를 e_products_invalid 라는 이름으로 정의 하겠다.
5. User-Defined Exception
DECLARE
e_amount_remaining EXCEPTION ;
...
BEGIN
...
RAISE e_amount_remaining ; -- 여기서부터 e_amount_remaining을 발생 시킨다.
...
EXCEPTION
WHEN e_amount_remaining THEN
:g_message := 'There is still an amount in stock.' ;
...
END ;
Why Join ? DB를 설계할 때 정규화(Normalization)가 기본 개념이듯이, SQL에서는 서로 다른 테이블들에 흩어져 있는 데이터를 주어진 조건에 의해 연결하는 조인(Join)이 기본 개념이라 할 수 있다.
예를 들어, 서울에 위치한 부서명과 소속된 사원명을 출력하라는 쿼리가 있을 때에 부서명은 DEPT 테이블에 있고, 사원명은 EMP 테이블에 있다. 따라서 조인이 필요할 것이고, 조인에 사용되는 연결조건은 소속관계이므로 DEPT 테이블의 부서번호와 EMP 테이블의 부서번호가 동일하다는 조건이 될 것이다.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno ß 조인조건
Join Order 조인은 두개 이상의 테이블을 주어진 조인조건으로 연결한다. 이때 연결은 순차적으로 수행되므로 만약 2개의 테이블을 조인하면 2종류의 순서가 가능하고, 3개의 테이블을 조인하면 6종류의 순서가 가능하다. 이때 항상 성능상 유리한 순서 하나로 수행되도록 할 필요가 있다.
RBO에서는 FROM 절의 가장 오른쪽부터 왼쪽으로 풀어나간다. 그러나 CBO를 사용하는 것이 권장된다.
1. 많이 걸러지는 순서대로
테이블에 주어지는 조건에 따라 많이 걸러지는 테이블을 먼저 처리하도록 해야 한다. 많이 걸러진다는 것은 조인조건으로 연결할 소스데이터가 그만큼 줄어든다는 것을 뜻하는 것이므로 성능상 유리하기 때문이다. 많이 걸러지는 것은 대개 그 테이블에 주어지는 WHERE 조건으로 판단하며, 많이 걸러질 수 있는 것은 데이터 상황으로 파악해야 할 것이다.
예를 들면, 위의 첫번째 SQL예제에서 DEPT 테이블을 먼저 처리하는 것이 좋은지, EMP 테이블을 먼저 처리하는 것이 좋은지 살펴보자. 먼저 DEPT 테이블에 대해서 loc 컬럼에 대한 조건이 있는 반면에, EMP 테이블에 대해서는 조인조건 이외의 조건은 없으므로 DEPT 테이블이 먼저 처리되는 것이 좋을 것이다. 만약 EMP 테이블에 다른 조건이 있고, DEPT 테이블에도 조건이 있는 경우에는 데이터 상황으로 비추어 많이 걸러지는 것을 우선적으로 처리하는 것이 좋다.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno
AND e.job = ‘MANAGER’
2. ORDERED 힌트
ORDERED라는 힌트가 주어지면 FROM 절의 왼쪽부터 오른쪽으로 순서대로 조인을 하라는 것이다. 흔히 SQL개발자가 데이터 상황을 보았을 때 가장 유리한 순서를 알고 있다면 FROM 절에 왼쪽부터 오른쪽으로 테이블들을 배치하고 SELECT에 ORDERED 힌트를 준다.
예를 들면 위의 예에서 d.loc = ‘서울’ 이라는 조건이 e.job = ’MANAGER’라는 조건보다 더 많이 걸러진다고 판단되면 아래와 같이 FROM절에 dept 다음에 emp를 기술하고 ORDERED 힌트를 명시한다.
SELECT /*+ ORDERED */ d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno
AND e.job = ‘MANAGER’
그러나 항상 조인조건에 사용될 액세스경로를 항상 주의하여야 하고, 만약 중간에 적합치 않은 액세스 경로가 있다면 원하는 순서대로 풀리지 않을 경우가 많으므로 인덱스생성등의 조치를 통해서 액세스경로를 정상으로 만들어야 한다.
예를 들면, 위의 예에서 e.deptno에 인덱스가 없고 d.deptno에는 인덱스가 있는 경우에는 어쩔 수 없이 emp 테이블이 먼저 풀린다. 이때에는 e.deptno에 인덱스를 만드는 것을 고려해야 한다.
Join Operations 조인은 테이블에 존재하는 데이터들을 주어진 조인조건에 만족되는 데이터들끼리 연결하는 것을 의미하는데, 그 조인조건을 만족하는 로우들을 매핑하기 위한 물리적인 방식에는 세가지가 존재한다. 이 방식은 옵티마이저가 인덱스의 존재 유무라든지, 데이터의 분포도를 고려해서 플랜을 작성하지만, SQL 개발자가 힌트를 제공함으로서 원하는 조인방식으로 유도할 수도 있다.
1. NESTED-LOOPS
대부분의 조인에서 많이 사용되는 방식이다. 한 테이블이 먼저 풀리고, 풀린 결과의 각 건에 대해서 다른 테이블에 조인조건이 만족되는 건이 있는지를 검사하는 방식이다. 먼저 풀리는 테이블을 드라이빙(driving) 테이블이라 하고, 나중에 조인조건을 검사하는 테이블을 이너(inner) 테이블이라 한다.
NESTED-LOOPS 조인의 수행성능을 좋게 하기 위해서는 반복적으로 발생하는 이너 테이블에 대한 조인조건 검사 수행이 최적화되어야 한다. 다시말해서, 인덱스가 생성되어 있어야 한다. 인덱스가 없는 상태에서 NESTED-LOOPS 방식으로 플랜이 생성되지 않을 가능성이 크지만, 만약 플랜이 NESTED-LOOPS 조인이면서 이너테이블이 FULL TABLE SCAN이라면 조인성능은 매우 느리다.
만약 SQL 개발자가 조인을 NESTED-LOOPS 방식으로 유도하기 위해서는 USE_NL 힌트를 사용할 수 있으나, 이너 테이블에 조인조건을 검사하기 위한 인덱스가 존재하는지 확인해야 하고, 없다면 생성해야 한다.
SELECT /*+ USE_NL(e) */ ß emp 테이블을 이너 테이블로 하는 NL 조인
d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno ß emp 테이블의 deptno 컬럼에 인덱스 필요
2. HASH
한쪽 테이블에 대해서 조인조건에 사용된 컬럼값들을 이용하여 해쉬 테이블을 만들고 다른 쪽 테이블에 대해서 그 해쉬함수를 적용하여 조인조건을 만족하는 건들을 찾아가는 방법이다.
해쉬테이블을 만드는데 오버헤드가 많이 들 수 있으므로, 이 방법은 주로 한쪽 테이블의 풀린 결과가 매우 적은 경우에 사용된다. 그리고 다른 쪽 테이블이 매우 큰 대용량의 경우에 해쉬방식이 NESTED-LOOPS 방식보다 빠르게 수행되는 편이다.
이 방법은 Equi-Join (조인조건이 =로 되어 있는 경우)에서만 사용될 수 있으며, 힌트로는 USE_HASH가 있다.
3. SORT-MERGE
양쪽 테이블을 풀은 결과를 조인컬럼에 의해서 정렬한 이후에 순서대로 내려가면서 조인조건을 비교하는 방식이다. 인덱스가 없어서 NESTED-LOOPS 방식을 사용하기 어려우면서 양쪽 테이블의 풀은 결과가 작지 않아서 HASH 방식도 어려운 경우에 사용될 수 있다.
이 방식도 equijoin에서만 사용할 수 있고, 힌트로는 USE_MERGE가 있다.
4. Cluster
조인하고자 하는 두 테이블이 조인 연결조건에 의해서 클러스터링 되어 있는 경우에 단순히 클러스터만 읽어서 조인을 할 수 있는 방법이다. 이 방식도 equijoin에서만 사용 가능하다.
Join Methods 1. Outer-Join 두 테이블을 조인할 때 비록 조인조건을 만족하지 않더라도 조인결과에 나오고 싶게 하고 싶은 경우가 있다. 이럴때 아우터조인을 사용할 수 있는데, 항상 기준 테이블을 주고, 기준 테이블에 있는 건들은 조인조건에 의해 버려지지 않도록 한다. 아우터조인의 대상이 되는 테이블의 조건에는 (+) 기호를 붙인다. 그러면 아우터조인의 대상이 되는 테이블의 컬럼값들은 조인조건에 만족되면 있는 값들이 출력되고, 만족되지 않는 경우에는 NULL로 출력된다.
예를 들어, 부서명과 사원명을 출력하되 사원이 하나도 없는 부서명도 출력하라.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
만약, 아우터조인의 대상이 테이블에 조건을 주어야 하는 경우에는, 그 조건에 있는 컬럼에도 (+) 기호를 붙여야 하며, 이 의미는 조인조건을 만족치 못하지만 아우터조인에 의해서 살아난 경우에는 그 조건을 검사하지 말라는 의미이다. 그 조건에 (+) 기호를 붙이지 않는 경우에는 그 조건에 의해서 아우터조인의 효과가 무력화된다.
예를 들어, 모든 부서를 나열하되, 매니저가 있는 경우에 매니저이름을 보여라.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
AND e.job(+) = ‘MANAGER’
그러나 아우터조인의 대상 테이블에 OR 또는 IN의 조건을 적용해야 하는 경우에는 (+) 기호를 붙이지 못한다.
예를 들어, 모든 부서를 나열하되, 매니저 또는 ANALYST가 있는 경우에는 그 이름을 출력하라.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
AND e.job(+) IN (‘MANAGER’, ‘ANALYST’)
위의 예를 수행하면 “ORA-01719: outer join operator(+) not allowed in operand of OR or IN” 에러가 발생한다. 이러한 경우에는 인라인뷰를 사용하는 등의 방법으로 우회해야 한다.
아우터조인의 경우, 조인의 순서는 항상 기준 테이블이 먼저 풀린다.
2. Self-Join 자기 자신 테이블과 조인하는 것을 말하는 것으로, 대개 Self Relationship을 가지는 설계에서 많이 사용된다.
예를 들어, 사원번호 100번의 사원의 이름과 그의 관리자명을 구하라는 쿼리에서
SELECT e.ename, m.ename
FROM emp e, emp m
WHERE e.empno = 100
AND e.mgr = m.empno
만약 위의 예제에서 관리자들을 최상위까지 나열하라. 단, 최상위까지는 3명의 관리자가 있을 수 있다. 라고 한다면 아래와 같이 할 수 있다.
SELECT e.ename, m1.ename, m2.ename, m3,ename
FROM emp e, emp m1, emp m2, emp m3
WHERE e.empno = 100
AND e.mgr = m1.empno
AND m1.mgr = m2.empno
AND m2.mgr = m3.empno
그러나 몇 명의 관리자가 있는지 SQL 개발단계에서 알 수 없다면 셀프조인으로 해결할 수는 없고, CONNECT BY로서 해결해야 한다.
SELECT LEVEL, e.ename
FROM emp
START WITH e.empno = 100
CONNECT BY PRIOR mgr = empno
3. Non-equijoin
조인은 항상 FK에 의해 참조되는 컬럼과의 = 로서만 조인되는 것은 아니다. 때로는 크기비교 연산자나 LIKE 등에 의해 데이터가 연결될 수도 있다.
예를 들어, 사원들의 직급과 연봉수준을 출력하라는 쿼리가 있을때에 SALGRADE 테이블과 EMP 테이블은 BETWEEN으로 연결될 것이다.
SELECT e.ename, e.job, g.level
FROM emp e, salgrade g
WHERE e.sal BETWEEN g.losal AND g.hisal
때로 조인을 하다보면 조인조건이 SUBSTR()에 의해 조인되는 컬럼의 변형(Suppress)를 가하게 되어 인덱스를 사용하지 못하는 경우가 생길 수 있다. 이러한 경우에는 더 유리한 플랜으로 유도하기 위해서는 SUBSTR()으로 잘라서 비교하는 것이 아니고 반대편을 ‘%’를 붙여서 LIKE로 비교하면 변형이 발생하지 않고 인덱스를 사용하는 조인으로 유도할 수 있다.
4. Semi-Join EXISTS를 사용한 서브쿼리에서 SEMI-JOIN 형식으로 풀린다.
5. Anti-Join NOT IN을 사용한 서브쿼리에서 ANTI-JOIN 형식으로 풀린다.
To Prevent Join 1. 비정규화(Denormalization)
설계 당시에 자주 발생할 소지가 있는 조인에 대해서 미리 조인한 형태의 설계를 하는 것을 말한다. 그러나 데이터 관리 및 정합성을 보장하기 어려울 수 있다.
예, 추천종목 테이블에서 종목코드와 더불어 종목명과 시장구분을 가지고 있다.
2. PL/SQL Function 사용 비정규화는 데이터의 생성시에 부담을 줄 수 있다. 따라서 비정규화가 어려운 경우에는 Function을 사용해서 조인을 피할 수 있다.
예, 주문내역테이블의 경우 주문이 발생할 때마다 종목명과 시장구분을 세팅하기가 어려우므로 쿼리시에 get_stockname(stkcd)이라는 함수를 이용한다.
4. 기타함수(miscellaneous single row function)
4-1) BFILENAME 함수
4-2) COALESCE 함수
4-3) DECODE 함수
4-4) DEPTH 함수
4-5) DUMP 함수
4-6) EMPTY_BLOB 함수
4-7) EMPTY_CLOB 함수
4-8) EXISTSNODE 함수
4-9) EXTRACT(XML) 함수
4-10) EXTRACTVALUE 함수
4-11) GREATEST 함수
4-12) LEAST 함수
4-13) NLS_CHARSET_DECL_LEN 함수
4-14) NLS_CHARSET_ID 함수
4-15) NLS_CHARSET_NAME 함수
4-16) NULLIF 함수
4-17) NVL2 함수
4-18) PATH 함수
4-19) SYS_CONNECT_BY_PATH 함수
4-20) SYS_CONTEXT 함수
4-21) SYS_DBURIGEN 함수
4-22) SYS_EXTRACT_UTC 함수
4-23) SYS_GUID 함수
4-24) SYS_XMLAGG 함수
4-25) SYS_XMLGEN 함수
4-26) UID 함수
4-27) USER 함수
4-28) USERENV 함수
4-29) VSIZE 함수
4-30) XMLAGG 함수
4-31) XMLCOLATTVAL 함수
4-32) XMLCONCAT 함수
4-33) XMLFOREST 함수
4-34) XMLELEMENT 함수
5.그룹함수 Aggregate 함수
5-1) AVG* 함수
5-2) CORR* CORR* 함수
5-3) COUNT* 함수
5-4) COVAR_POP 함수
5-5) COVAR_SAMP 함수
5-6) CUME_DIST 함수
5-7) DENSE_RANK 함수
5-8) FIRST 함수
5-9) GROUP_ID 함수
5-10) Grouping 함수
5-11) GROUPING_ID 함수
5-12) LAST 함수
5-13) MAX 함수
5-14) MIN 함수
5-15) PERCENTILE_CONT 함수
5-16) PERCENTILE_DISC 함수
5-17) PERCENT_RANK 함수
5-18) RANK 함수
5-19) REGR_(linear regression) function* 함수
5-20) STDDEV 함수
5-21) STDDEV_POP 함수
5-22) STDDEV_SAMP 함수
5-23) SUM 함수
5-24) VAR_POP 함수
5-25) VAR_SAMP 함수
5-26) VARIANCE 함수
5-27) Grouping sets 함수
6. Analytic 함수
6-1) AVG* 함수
6-2) CORR* CORR* 함수
6-3) COUNT* 함수
6-4) COVAR_SAMP 함수
6-5) CUME_DIST 함수
6-6) DENSE_RANK 함수
6-7) FIRST 함수
6-8) FIRST_VALUE 함수
6-9) LAG 함수
6-10) LAST_VALUE 함수
6-11) LEAD 함수
6-12) NTILE 함수
6-13) RATIO_TO_REPORT 함수
6-14) ROW_NUMBER 함수
7. 객체 참조 함수
7-1) REF 타입
8. PseudoColumn을 의미하는 것
8-1) ROWID 컬럼
8-2) ROWNUM 컬럼
4-1) BFILENAME 함수
--------------------------------------------------------------------------------
서버 파일 시스템 상에 실제로 위치한 LOB 바이너리 파일의 위치한 BFILE locator를 반환한다.
【형식】
bfilename ('디렉토리‘,’파일이름‘)
【예제】BFILE을 insert하는 예
SQL> connect system/manager
SQL> host mkdir /export/home/oracle/bfile
SQL> create directory bfile_dir as '/export/home/oracle/bfile';
Directory created.
SQL> grant read on directory bfile_dir to jijoe;
Grant succeeded.
SQL> connect jijoe/joe_password
SQL> create table bfile_doc (id number, doc bfile);
SQL> insert into bfile_doc
1 values(1111,bfilename('bfile_dir','unix.hwp'));
1 row created.
SQL>
4-2) COALESCE 함수
--------------------------------------------------------------------------------
이 함수는 나열된 값 중에서 NULL이 아닌 첫 번째 값을 반환한다.
【예제】
SQL> select coalesce('','','arirang','kunsan') from dual;
COALESC
-------
arirang
SQL>
4-3) DECODE 함수
--------------------------------------------------------------------------------
DECODE는 일반적인 프로그래밍 언어의 IF문을 SQL 문자 또는 PL/SQL 안으로 끌어들여 사용하기 위하여 만들어진 오라클 함수이다.
따라서 일반 프로그래밍 언어의 IF문이 수행 할 수 있는 기능을 포함하고 있다.
select시의 decode 사용은 from 절만 빼고 어디에서나 사용할 수 있다.
일반 프로그래밍과 decode 함수를 서로 비교하여 보자.
IF문 Decode 함수 IF A=B THEN RETURN 'T';END IF; DECODE(A,B,'T') IF A=B THENRETURN 'T';ELSIF A=C THENRETURN 'F';ELSERETURN 'X';END IF; DECODE(A,B,'T',C,'F','X')
【형식】
DECODE(검색컬럼,조건1,결과값1,
조건2,결과값2,...,기본값);
SQL> insert into aa values(1234,'kunsan','jijoe')
SQL> insert into aa values(3456,'seoul','sunny')
SQL> select * from aa;
PID ADDR NAME
---------- -------------------- ----------
1234 kunsan jijoe
3456 seoul sunny
SQL> select decode(pid,1234,name) name from aa;
NAME
----------
jijoe
SQL>
【예제】
SQL> desc ddd
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NUMBER(4)
NAME VARCHAR2(10)
HIRDATE DATE
DEPTNO NUMBER(5)
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-6) CUME_DIST 함수
--------------------------------------------------------------------------------
이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.
【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)
【예제】
SQL> select cume_dist(230) within group
2 (order by salary ) from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-7) DENSE_RANK 함수
--------------------------------------------------------------------------------
그룹 내에서 순위를 반환한다.
【예제】
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL> select dense_rank(230, .05) within group
2 (order by salary, bonus) "Dense Rank"
3 from employees;
Dense Rank
----------
2
SQL>
5-8) FIRST 함수
--------------------------------------------------------------------------------
first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
DENSE_RANK FIRST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;
Worst Best
---------- ----------
220 250
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-9) GROUP_ID 함수
--------------------------------------------------------------------------------
GROUP() 함수는 group by로 분리되어 복제된 번호로 복제 횟수를 구분하도록 출력한다.
번호가 0부터 시작되므로 n번 복제되었으면 n-1의 번호가 출력된다.
【예제】
SQL> select dept_no, group_id() from employees
2 group by dept_no;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-10) Grouping 함수
--------------------------------------------------------------------------------
Grouping 함수는 Rollup이나 cube 연산자랑 함께 사용하여
grouping 함수에서 기술된 컬럼이 그룹핑시 사용되었는지 보여주는 함수이다.
특별히 연산의 기능은 없으며,
rollup이나 cube 연산 후 출력된 결과에 대한 사용자의 이해를 높이기 위해 사용된다.
즉, grouping 함수를 이용할 경우 출력되는 결과값 중 null 값이 있다면,
이 null 값이 rollup이나 cube 연산의 결과로 생성된 값인지,
원래 테이블상에 null 값으로 저장된 것인지 확인할 수 있다.
. grouping 함수는 인수로 하나의 값만을 가진다.
. grouping 함수에 사용된 인수는 group by 절에 기술된 값 중에 하나와 반드시 일치해야 한다.
. grouping 함수의 결과값으로 0 또는 1을 반환한다.
0은 해당인수로 쓰인 값이 rollup이나 cube 연산에 사용되었음을 나타나고,
1은 사용되지 않았음을 의미한다.
【형식】
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명,...
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호
【예제】
SQL> select grade,deptno,sum(salary),GROUPING(deptno)
2 from aaa
3 group by rollup(grade,deptno);
5-12) LAST 함수
--------------------------------------------------------------------------------
first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
DENSE_RANK LAST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;
Worst Best
---------- ----------
220 250
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-13) MAX 함수
--------------------------------------------------------------------------------
이 함수는 최대 값을 반환한다.
【형식】
MAX ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]
【예제】
SQL> select max(salary) over (partition by dept_no)
2 from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-17) PERCENT_RANK 함수
--------------------------------------------------------------------------------
이 함수는 CUME_DIST 함수와 유사하게 percent_rank 값을 반환한다.
【형식】
PERCENT_RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
[NULLS {FIRST|LAST}],...)
또는
PERCENT_RANK() OVER( [query_partition_clause] order_by_clause)
【예제】
SQL> select percent_rank(230,0.05) within group
2 (order by salary,bonus) from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-18) RANK 함수
--------------------------------------------------------------------------------
이 함수는 그룹 내에서 위치를 반환한다.
【형식】
RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
[NULLS {FIRST|LAST}],...)
또는
RANK() OVER( [query_partition_clause] order_by_clause)
【예제】
SQL> select rank(230,0.05) within group
2 (order by salary,bonus) from employees;
RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)
----------------------------------------------
2
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
5-19) REGR_(linear regression) function* 함수
--------------------------------------------------------------------------------
선형 회귀(linear regression) 함수 ordinary-least squares regression line을 찾도록 한다.
사용되는 회귀함수는 자음 중 하나이다.
REGR_SLOPE REGR_INTERCEPT REGR_COUNT
REGR_R2 REGR_AVGX REGR_AVGY
REGR_SXX REGR_SYY REGR_SXY
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-27) Grouping sets 함수
--------------------------------------------------------------------------------
Grouping sets 함수는 Group by의 확장된 형태로 하나의 쿼리문에서 원하는 그룹핑 조건을 여러 개 기술할 수 있으며,
grouping sets 함수 사용이 불가능한 이전 버전에서
복잡한 union all 연산자를 사용하여 기술하던 것을 간단하게
한 문장 안에서 해결할 수 있어 검색시 효율성이 증대 된다.
다시 말해서, grouping sets 함수를 사용하면,
group by ... union all을 사용한 것보다
SQL 문이 간단해지고 또한 실행시 성능이 빨라진다.
【형식】
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명, ...
[GROUPING SETS (컬럼명,컬럼명, ...), ...]
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호
【예제】
SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grouping sets(grade,deptno);
12 rows selected.
SQL>
【예제】Union all을 사용한 경우
SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grade,deptno
4 union all
5 select grade,deptno,sum(salary)
6 from aaa
7 group by grade,deptno;
composite columns란 rollup, cube, grouping sets 절과 같은 기능을 사용하면
표현되는 각 컬럼이 하나가 아닌 복수 개의 컬럼으로 정의되는 경우이며
다음 표를 보고 이해하자.
composite column 문의 경우 group by 문의 경우 group by grouping sets(a,b,c) group by a union allgroup by b union allgroup by c group by grouping sets(a,b,(b,c)) group by a union allgroup by b union allgroup by b,c group by grouping sets((a,b,c)) group by a,b,c group by grouping sets(a,(b),()) group by a union allgroup by b union allgroup by () group by grouping sets(a,rollup(b,c)) group by a union allgroup by rollup(b,c) group by rollup(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a) union allgroup by () group by cube(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a,c) union allgroup by (b,c) union allgroup by (a) union allgroup by (b) union allgroup by (c) union allgroup by ()
6-1) AVG* 함수
--------------------------------------------------------------------------------
조건을 만족하는 행(row)의 평균을 값을 반환하며,
aggregate 함수나
analytic 함수로 사용된다.
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
6-5) CUME_DIST 함수
--------------------------------------------------------------------------------
이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.
【형식】
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)
【예제】
SQL> select cume_dist(230) within group
2 (order by salary ) from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
6-6) DENSE_RANK 함수
--------------------------------------------------------------------------------
그룹 내에서 순위를 반환한다.
【예제】
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL> select dense_rank(230, .05) within group
2 (order by salary, bonus) "Dense Rank"
3 from employees;
Dense Rank
----------
2
SQL>
6-7) FIRST 함수
--------------------------------------------------------------------------------
first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
DENSE_RANK FIRST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;
Worst Best
---------- ----------
220 250
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-8) FIRST_VALUE 함수
--------------------------------------------------------------------------------
이 함수는 서열화된 값에서 첫 번째를 출력한다.
【형식】
FIRST_VALUE ( expr ) OVER ( analytic_절)
【예제】
SQL> select salary,first_value(name)
2 over (order by salary asc)
3 from (select * from employees
4 where dept_no = 20
5 order by salary);
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-9) LAG 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서,
self join하지 않고 하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.
【형식】
LAG ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )
【예제】
SQL> select name,salary,LAG(salary,1,0)
2 OVER (ORDER BY salary) FROM employees;
NAME SALARY LAG(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- ----------------------------------
jijoe 220 0
Joe 240 220
Cho 250 240
kim 250 250
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-10) LAST_VALUE 함수
--------------------------------------------------------------------------------
이 함수는 서열화된 값에서 마지막 번째를 출력한다.
【형식】
LAST_VALUE ( expr ) OVER ( analytic_절)
【예제】
SQL> select salary,last_value(name)
2 over (order by salary asc)
3 from (select * from employees
4 where dept_no = 20
5 order by salary);
SALARY LAST_VALUE
---------- ----------
220 jijoe
240 Joe
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-11) LEAD 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서, self join하지 않고
하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.
【형식】
LEAD ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )
【예제】
SQL> select name,salary,LEAD(salary,1,0)
2 OVER (ORDER BY salary) FROM employees;
NAME SALARY LEAD(SALARY,1,0)OVER(ORDERBYSALARY)
---------- ---------- -----------------------------------
jijoe 220 240
Joe 240 250
Cho 250 250
kim 250 0
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-12) NTILE 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서, 데이터를 주어진 bucket 수 expr로 분리한다.
【형식】
NTILE ( expr ) OVER ([query_partition_clause] order_by_clause )
【예제】
SQL> select name,salary,NTILE(3) OVER (ORDER BY salary DESC)
2 FROM employees;
NAME SALARY NTILE(3)OVER(ORDERBYSALARYDESC)
---------- ---------- -------------------------------
Cho 250 1
kim 250 1
Joe 240 2
jijoe 220 3
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-13) RATIO_TO_REPORT 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서, 데이터 합에 대한 구성비를 계산한다.
【형식】
RATIO_TO_REPORT ( expr ) OVER ([query_partition_clause])
【예제】
SQL> select name,salary,RATIO_TO_REPORT(salary) OVER ()
2 FROM employees;
NAME SALARY RATIO_TO_REPORT(SALARY)OVER()
---------- ---------- -----------------------------
Cho 250 .260416667
Joe 240 .25
kim 250 .260416667
jijoe 220 .229166667
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
6-14) ROW_NUMBER 함수
--------------------------------------------------------------------------------
이 함수는 analytic 함수로서, 각 행(row)에 unique 번호를 부여한다.
【형식】
ROW_NUMBER () OVER ([query_partition_clause] order_by_clause )
【예제】
SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary DESC),name
2 FROM employees;
ROW_NUMBER()OVER(ORDERBYSALARYDESC) NAME
----------------------------------- ----------
1 Cho
2 kim
3 Joe
4 jijoe
SQL> select * from employees;
ID DEPT_NO NAME SALARY BONUS
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
SQL>
7-1) REF 타입
--------------------------------------------------------------------------------
테이블의 어떤 컬럼이 독립된 다른 객체 테이블을 참조하는 데이터 타입을 의미한다.
일반적으로 테이블을 만들때 사용하는 외부키(foreign-key)를 이용한 참조관계와 유사하다.
REF 타입의 컬럼 데이터를 읽을 때는 반드시 DEREF 함수를 사용한다.
REF 타입으로 정의된 컬럼에는 실제 데이터가 저장되는 것이 아니고 참조되는 객체가 존재하는 포인트정보만 가지고 있기 때문에 객체가 삭제 된다면, 해당 컬러은 정상적으로 검색되지 못한다.
이러한 현상을 REF의 Dangling 현상이고 한다.
이러한 dangling 현상을 방지하기 위해서는 삭제된 객체 정보를 참조하는 행의 컬럼 정보를 analyze 명령어를 이용하여 null 값으로 변경해 주어야 한다.
【예제】
SQL> connect jijoe/jijoe_password
connected
SQL> create type person_type as object(
2 first_name varchar2(10),
3 last_name varchar2(10),
4 phone varchar(12),
5 birthday varchar2(12));
6 /
Type created.
SQL> create type emp_type as object (
2 empno number,
3 emp person_type);
4 /
Type created.
SQL> create table emp2 of emp_type
2 oidindex emp_oid;
Table created.
SQL> insert into emp2 values(
2 emp_type(1000,person_type('junik','joe','123-1234','20-jul-04')));
1. 문자 함수
1-1) CHR
1-2) CONCAT 함수
1-3) INITCAP 함수
1-4) LOWER 함수
1-5) LPAD 함수
1-6) LTRIM 함수
1-7) NLS_INITCAP 함수
1-8) NLS_LOWER 함수
1-9) NLSSORT 함수
1-10) NLS_UPPER 함수
1-11) REPLACE 함수
1-12) RPAD 함수
1-13) RTRIM 함수
1-14) SOUNDEX 함수
1-15) SUBSTR 함수
1-16) TRANSLATE 함수
1-17) TREAT 함수
1-18) TRIM 함수
1-19) UPPER 함수
1-20) ASCII 함수
1-21) INSTR 함수
1-22) LENGTH 함수
2. 날짜 처리함수(datetime function)
2-1) ADD_MONTHS 함수
2-2) CURRENT_DATE 함수
2-3) URRENT_TIMESTAMP 함수
2-4) DBTIMEZONE 함수
2-5) EXTRACT(datetime) 함수
2-6) FROM_TZ 함수
2-7) LAST_DAY 함수
2-8) LOCALTIMESTAMP 함수
2-9) MONTHS_BETWEEN 함수
2-10) NEW_TIME 함수
2-11) NEXT_DAY 함수
2-12) NUMTODSINTERVAL 함수
2-13) NUMTOYMINTERVAL 함수
2-14) ROUND(date) 함수
2-15) SESSIONTIMEZONE 함수
2-16) SYS_EXTRACT_UTC 함수
2-17) SYSDATE 함수
2-18) SYSTIMESTAMP 함수
2-19) TO_DSINTERVAL 함수
2-20) TO_TIMESTAMP 함수
2-21) TO_TIMESTAMP_TZ 함수
2-22) TO_YMINTERVAL 함수
2-23) TRUNC(date) 함수
2-24) TZ_OFFSET 함수
3.데이터 형 변환 함수(conversion function)
3-1) ASCIISTR 함수
3-2) BIN_TO_NUM 함수
3-3) CAST 함수
3-4) CHARTOROWID 함수
3-5) COMPOSE 함수
3-6) CONVERT 함수
3-7) HEXTORAW 함수
3-8) NUMTODSINTERVAL 함수
3-9) NUMTOYMINTERVAL 함수
3-10) RAWTOHEX 함수
3-11) RAWTONHEX 함수
3-12) ROWIDTOCHAR 함수
3-13) ROWIDTONCHAR 함수
3-14) TO_CHAR(character) 함수
3-15) TO_CLOB 함수
3-16) TO_DSINTERVAL 함수
3-17) TO_LOB 함수
3-18) TO_MULTI_BYTE 함수
3-19) TO_NCHAR(character) 함수
3-20) TO_NCHAR(datetime) 함수
3-21) TO_NCHAR(number) 함수
3-22) TO_NCLOB 함수
3-23) TO_NUMBER 함수
3-24) TO_SINGLE_BYTE 함수
3-25) TO_YMINTERVAL 함수
3-26) TRANSLATE ... USING 함수
3-27) UNISTR 함수
1-1) CHR 함수
--------------------------------------------------------------------------------
입력된 수의 바이너리 코드에 해당하는 문자를 반환한다.
【예제】
SQL> select chr(75)||chr(79)||chr(82)||chr(69)||chr(65)
2 from dual;
CHR(7
-----
KOREA
SQL>
1-2) CONCAT 함수
--------------------------------------------------------------------------------
입력되는 두 문자열을 연결하여 반환한다.
입력되는 두 문자열의 타입이 다를 경우 다음과 같이 반환된다.
첫 번째 문자열 타입 두 번째 문자열 타입 반환되는 문자열 타입 CLOB NCLOB NCLOB NCLOB NCHAR NCLOB NCLOB CHAR NCLOB NCHAR CLOB NCLOB
【예제】
SQL> select concat('Republic of',' KOREA') from dual;
CONCAT('REPUBLICO
-----------------
Republic of KOREA
SQL>
1-3) INITCAP 함수
--------------------------------------------------------------------------------
initcap('string‘) 함수는 입력 문자열 중에서
각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환한 스트링을 반환한다
【예제】
SQL> select initcap('beautiful corea') from dual;
INITCAP('BEAUTI
---------------
Beautiful Corea
SQL>
1-4) LOWER 함수
--------------------------------------------------------------------------------
ower(string) 함수는 입력된 문자열을 소문자로 반환한다.
【예제】
SQL> select lower('Beautiful COREA') from dual;
LOWER('BEAUTIFU
---------------
beautiful corea
SQL>
1-5) LPAD 함수
--------------------------------------------------------------------------------
pad(char1,n,char2) 함수는
지정된 길이 n에서 문자 char1으로 채우고
남은 공간은 왼쪽부터 char2로 채워서 출력한다.
【형식】
pad (char1, n [, char2] )
【예제】
SQL> select lpad ('Corea', 12, '*') from dual;
LPAD('COREA'
------------
*******Corea
SQL>
1-6) LTRIM 함수
--------------------------------------------------------------------------------
LTRIM(문자열, 문자)함수는 문자열중
좌측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.
【형식】
trim(char [,set] )
【예제】
SQL> select LTRIM('xyxXxyLAST WORD','xy') from dual;
LTRIM('XYXXX
------------
XxyLAST WORD
SQL>
1-7) NLS_INITCAP 함수
--------------------------------------------------------------------------------
nls_initcap(‘string’) 함수는 입력 문자열 중에서
각 단어의 첫 글자를 대문자로
나머지는 소문자로 변환한 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.
【형식】
nls_initcap ( char [,'nlsparam'] )
【예제】
SQL> select nls_initcap('beautiful corea', 'nls_sort=binary')
2 from dual;
NLS_INITCAP('BE
---------------
Beautiful Corea
SQL> select nls_initcap('beautiful corea','nls_sort=XDutch')
2 from dual;
NLS_INITCAP('BE
---------------
Beautiful Corea
SQL>
1-8) NLS_LOWER 함수
--------------------------------------------------------------------------------
nls_lower(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.
【형식】
nls_lower ( char [,'nlsparam'] )
【예제】
SQL> select nls_lower('CITTA''','nls_sort=XGerman') from dual;
NLS_LO
------
citta'
SQL>
1-9) NLSSORT 함수
--------------------------------------------------------------------------------
nlssort(‘string’) 함수는 입력 문자열을 소팅하여 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.
【형식】
nlssort ( char [,'nlsparam'] )
【예제】
SQL> select * from emp
2 order by nlssort(name, 'nls_sort=XDanish');
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1104 jijoe 220 100
1103 kim 250 100
SQL>
1-10) NLS_UPPER 함수
--------------------------------------------------------------------------------
nls_upper(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.
【형식】
nls_upper ( char [,'nlsparam'] )
【예제】
SQL> select nls_upper('gro?e') from dual;
NLS_U
-----
gro?e
SQL> select nls_upper('gro?e','nls_sort=XGerman')
2 from dual;
NLS_UP
------
grosse
SQL>
1-11) REPLACE 함수
--------------------------------------------------------------------------------
이 함수는 문자열에서 지정한 문자를 다른 문자로 치환한다.
치환될 문자를 지정하지 않으면 해당 문자를 삭제한다.
【예제】
SQL> select replace('aaabb','a','b') from dual;
REPLA
-----
bbbbb
SQL> select replace('aaabb','a') from dual;
RE
--
bb
SQL>
1-12) RPAD 함수
--------------------------------------------------------------------------------
rpad(char1,n,char2) 함수는 지정된 길이 n에서 문자 char1으로 채우고
남은 공간은 오른쪽부터 char2로 채워서 출력한다.
【형식】
rpad (char1, n [, char2] )
【예제】
SQL> select rpad('Corea',12,'*') from dual;
RPAD('COREA'
------------
Corea*******
SQL>
1-13) RTRIM 함수
--------------------------------------------------------------------------------
RTRIM(문자열, 문자)함수는 문자열중
우측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.
【형식】
rtrim(char [,set] )
【예제】
SQL> select RTRIM('BROWINGyxXxy','xy') "RTRIM example" from dual;
RTRIM exam
----------
BROWINGyxX
SQL>
1-14) SOUNDEX 함수
--------------------------------------------------------------------------------
soundex(‘char’) 함수는 char과 같은 발음의 이름을 표현한다.
【예제】
SQL> select name from emp;
NAME
----------
Cho
Joe
kim
jijoe
SQL> select name from emp
2 where soundex(name) = soundex('jo');
NAME
----------
Joe
SQL>
1-15) SUBSTR 함수
--------------------------------------------------------------------------------
substr(str,m,n) 함수는 문자열 str 중에서 특정 위치 m으로부터 특정 길이n 만큼의 문자를 출력한다.
m이 0이나 1이면 문자열의 첫글자를 의미하고,
n이 생략되면 문자열의 끝까지를 의미한다.
m이 음수이면 뒤쪽으로부터의 위치를 의미한다.
SUBSTRB는 character 대신 byte를 사용하고,
SUBSTDC는 unicode를 사용하며,
SUBSTR2는 UCS2 codepoint를 사용하고,
SUBSTR4는 UCS4 codepoint를 사용한다.
【형식】
{SUBSTR|SUBSTRB|SUBSTRC|SUNBSTD2|SUBSTR4}
( string, position [,substring_length] )
【예제】
SQL> select substr('abcdesfg', 3,2) from dual;
SU
--
cd
SQL> select substr('abcdefg',3) from dual;
SUBST
-----
cdefg
SQL> select substr('abcdefg', -3,2) from dual; ☜ 뒤에서 3번째부터 2글자를 의미한다.
SU
--
ef
SQL>
1-16) TRANSLATE 함수
--------------------------------------------------------------------------------
TRANSLATE (‘char’,‘from_string’,‘to_string’) 함수는
char 내에 포함된 문자중 from_string에 지정한 모든 각각의 문자를
to_string문자로 각각 변경한다.
【형식】
TRANSLATE ('char','from_string','to_string')
【예제】
SQL> select translate('ababccc','c','d') from dual;
TRANSLA
-------
ababddd
SQL> select translate('2KRW229',
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
3 '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;
TRANSLA
-------
9XXX999
SQL> select translate('2KRW229',
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')
3 from dual;
TRAN
----
2229
SQL>
1-17) TREAT 함수
--------------------------------------------------------------------------------
TREAT 함수는 선언된 타입을 변경함으로써, 수퍼타입을 서브타입인 것처럼 처리할 수 있도록 한다.
【형식】
TREAT ( expr AS [ REF] [schema . ] type )
【예제】
SQL> select x.p.empno from person_table p;
select x.p.empno from person_table p
*
ERROR at line 1:
ORA-00904: "X"."P"."EMPNO": invalid identifier
SQL> select treat(x.p as employee).empno empno,
2 x.p.last_name last_name
3 from person_table x;
EMPNO LAST_NAME
---------- --------------------
Seoul
1234 Inchon
5678 Arirang
SQL>
【예제】
SQL> select name, TREAT(VALUE(p) AS employee_t).salary salary
2 FROM person p;
NAME SALARY
---------------------- ---------
Bob
Joe 100000
Tim 1000
SQL>
1-18) TRIM 함수
--------------------------------------------------------------------------------
이 함수는 LTRIM과 RTRIM 함수를 결합한 형태로
문자값의 왼쪽 또는 오른쪽 부분에 정의한 문자를 절삭하여 출력한다.
LEADING은 LTRIM처럼 문자열 왼쪽의 문자를 지정하여 절삭하고,
TRAILING은 RTRIM처럼 문자열 오른쪽 문자를 지정하여 절삭한다.
BOTH는 왼쪽과 오른쪽 문자를 지정하여 절삭한다.
【형식】
TRIM ([{{{LEADING|TRAILING|BOTH} [trim_char] } | trim_char} FROM]
trim_source )
【예제】
SQL> select trim (0 from 000123400) from dual;
TRIM
----
1234
SQL> select trim(trailing 'a' from 'abca') from dual;
TRI
---
abc
SQL> select trim(leading 'a' from 'abca') from dual;
TRI
---
bca
SQL> select trim(both 'a' from 'abca') from dual;
TR
--
bc
SQL>
1-19) UPPER 함수
--------------------------------------------------------------------------------
upper(string) 함수는 입력된 문자열을 대문자로 반환한다.
【예제】
SQL> select upper('Beautiful COREA') from dual;
UPPER('BEAUTIFU
---------------
BEAUTIFUL COREA
SQL>
1-20) ASCII 함수
--------------------------------------------------------------------------------
ASCII
ascii(‘char’) 함수는 주어진 char의 첫 글자의 아스키 값을 반환한다.
char의 타입은 char, varchar2, nchar, nvarchar2중의 하나이어야 한다.
【예제】
SQL> select ascii('Korea') from dual;
ASCII('KOREA')
--------------
75
SQL> select ascii('K') from dual;
ASCII('K')
----------
75
SQL>
1-21) INSTR 함수
--------------------------------------------------------------------------------
이 함수는 문자 스트링 중에서
지정한 문자가 가장 처음 나타나는 위치를 숫자로 출력한다.
【형식】
{INSTR|INSTRB|INSTRC|INSTR2|INSTR4}
( string, substring [, position [,occurrence] ] )
【예제】
SQL> select instr('Corea','e') from dual;
INSTR('COREA','E')
------------------
4
SQL> select instr('corporate floor','or',3,2) from dual;
1-22) LENGTH 함수
--------------------------------------------------------------------------------
LENGTH(char) 함수는 char의 길이를 반환한다.
LENGTHB는 character 대신 byte를 사용하고,
LENGTHC는 unicode를 사용하며,
LENGTH2는 UCS2 codepoint를 사용하고,
LENGTH4는 UCS4 codepoint를 사용한다.
2-3) URRENT_TIMESTAMP 함수
--------------------------------------------------------------------------------
이 함수는 현재 session의 날짜와 시간 정보를 반환한다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04
SQL>
2-4) DBTIMEZONE 함수
--------------------------------------------------------------------------------
데이터베이스 timezone을 반환한다.
【예제】
SQL> select dbtimezone from dual;
SYSDATE last day Days Left
--------- --------- ----------
04-AUG-04 31-AUG-04 27
SQL>
2-8) LOCALTIMESTAMP 함수
--------------------------------------------------------------------------------
이 함수는 timestamp의 현재 날짜와 시각을 출력한다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
【형식】
ocaltimestamp [(timestamp_precision)]
【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04
SQL>
【예제】오류가 발생하는 이유를 잘 이해하자.
SQL> CREATE TABLE local_test(col1 TIMESTAMP WITH LOCAL TIME ZONE);
Table created.
SQL> INSERT INTO local_test VALUES
2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'))
*
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string
SQL> INSERT INTO local_test VALUES
2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
1 row created.
SQL> select * from local_test;
COL1
--------------------------------------------------------------------------
04-AUG-04 11.33.58.183398 AM
SQL>
2-9) MONTHS_BETWEEN 함수
--------------------------------------------------------------------------------
MONTHS_BETWEEN(date1,date2) 함수는 date1과 date로 나타내는
날짜와 날짜 사이의 개월 수를 출력한다.
2-10) NEW_TIME 함수
--------------------------------------------------------------------------------
NEW_TIME(date,zone1,zone2) 함수는 date, zone1 시간대를 zone2 시간대로 출력한다.
여기서 사용되는 zone은 다음 중의 하나이다.
AST,ADT : Atlantic Standard or Daylight Time
BST,BDT : Bering Standard or Daylight Time
CST,CDT : Central Standard or Daylight Time
EST,EDT : Eastern Standard or Daylight Time
GMT : Greenwich Mean Time
HST,HDT : Alaska-Hawaii Standard or Daylight Time
MST,MDT : Mountain Standard or Daylight Time
NST : Newfoundland Standard Time
PST,PDT : Pacific Standard or Daylight Time
YST,YDT : Yukon Standard or Daylight Time
【예제】
SQL> alter session set nls_date_format =
2 'DD-MON-YYYY HH24:MI:SS';
ENAME HIREDATE NUMTODSIN
---------- --------- ---------
SMITH 17-DEC-80 27-MAR-81
ALLEN 20-FEB-81 31-MAY-81
WARD 22-FEB-81 02-JUN-81
JONES 02-APR-81 11-JUL-81
MARTIN 28-SEP-81 06-JAN-82
BLAKE 01-MAY-81 09-AUG-81
CLARK 09-JUN-81 17-SEP-81
SCOTT 19-APR-87 28-JUL-87
KING 17-NOV-81 25-FEB-82
TURNER 08-SEP-81 17-DEC-81
ADAMS 23-MAY-87 31-AUG-87
JAMES 03-DEC-81 13-MAR-82
FORD 03-DEC-81 13-MAR-82
MILLER 23-JAN-82 03-MAY-82
14 rows selected.
SQL>
2-13) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
【예제】
SQL> select numtoyminterval(30,'month') from dual;
ENAME HIREDATE NUMTOYMIN
---------- --------- ---------
SMITH 17-DEC-80 17-JUN-83
ALLEN 20-FEB-81 20-AUG-83
WARD 22-FEB-81 22-AUG-83
JONES 02-APR-81 02-OCT-83
MARTIN 28-SEP-81 28-MAR-84
BLAKE 01-MAY-81 01-NOV-83
CLARK 09-JUN-81 09-DEC-83
SCOTT 19-APR-87 19-OCT-89
KING 17-NOV-81 17-MAY-84
TURNER 08-SEP-81 08-MAR-84
ADAMS 23-MAY-87 23-NOV-89
JAMES 03-DEC-81 03-JUN-84
FORD 03-DEC-81 03-JUN-84
MILLER 23-JAN-82 23-JUL-84
14 rows selected.
SQL>
2-14) ROUND(date) 함수
--------------------------------------------------------------------------------
이 함수는 날짜를 주어진 형식으로 반올림하는 함수이다.
날짜 형식이 없으면 가장 가까운 날을 출력한다.
【형식】
ROUND( date [,fmt] )
【예제】
SQL> select localtimestamp, round(sysdate,'year') from dual;
2-20) TO_TIMESTAMP 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 timestamp 형식으로 변환하여 출력한다
【형식】
to_timestamp ( char [,fmt ['nlsparam'] ] )
【예제】
SQL> select to_timestamp('2004-8-20 1:30:00', 'YYYY-MM-DD HH:MI:SS')
2 from dual;
TO_TIMESTAMP('2004-8-201:30:00','YYYY-MM-DDHH:MI:SS')
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM
SQL>
2-21) TO_TIMESTAMP_TZ 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 timestamp with time zone 형식으로 변환하여 출력한다.
3-4) CHARTOROWID 함수
--------------------------------------------------------------------------------
이 함수는 char, varchar2, nchar, ncharvar2형 데이터 타입을 rowid 형 데이터 타입으로 변경한다.
【예제】
SQL> select name from emp
2 where rowid = chartorowid('AAAHZ+AABAAAMWiAAF');
NAME
----------
jijoe
SQL> select rowid,name from emp;
ROWID NAME
------------------ ----------
AAAHZ+AABAAAMWiAAA Cho
AAAHZ+AABAAAMWiAAB Joe
AAAHZ+AABAAAMWiAAC kim
AAAHZ+AABAAAMWiAAF jijoe
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHZ+ AAB AAAMWi AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-5) COMPOSE 함수
--------------------------------------------------------------------------------
입력된 스트링을 unicode로 나타낸다.
【예제】
SQL> select compose('aa' || unistr('\0308') ) from dual;
CO
--
aa
SQL>
3-6) CONVERT 함수
--------------------------------------------------------------------------------
입력된 문자열을 지정한 코드로 변환한다.
공용 문자셋은 살펴보자.
US7ASCII US 7-bit ASCII 문자 WE8DEC 서유럽 8비트 문자 WE8HP HP 서유럽 레이져젯 8비트 문자 F7DEC DEC 프랑스 7비트 문자 WE8EBCDIC500 IBM 서유럽 EBCDIC 코드 페이지 500 WE8PC850 IBM PC 코드 페이지 850 WE8ISO8859P1 ISO 8859 서유럽 8비트 문자
【예제】
SQL> select convert('arirang','we8pc850') from dual;
CONVERT
-------
arirang
SQL>
3-7) HEXTORAW 함수
--------------------------------------------------------------------------------
HEXTORAW(char) 함수는 char, varchar2, nchar, nvarchar2 따위의 문자로 주어지는
hexadecimal digit을 raw 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL>
3-8) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------
NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’
【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;
ENAME HIREDATE NUMTODSIN
---------- --------- ---------
SMITH 17-DEC-80 27-MAR-81
ALLEN 20-FEB-81 31-MAY-81
WARD 22-FEB-81 02-JUN-81
JONES 02-APR-81 11-JUL-81
MARTIN 28-SEP-81 06-JAN-82
BLAKE 01-MAY-81 09-AUG-81
CLARK 09-JUN-81 17-SEP-81
SCOTT 19-APR-87 28-JUL-87
KING 17-NOV-81 25-FEB-82
TURNER 08-SEP-81 17-DEC-81
ADAMS 23-MAY-87 31-AUG-87
JAMES 03-DEC-81 13-MAR-82
FORD 03-DEC-81 13-MAR-82
MILLER 23-JAN-82 03-MAY-82
14 rows selected.
SQL>
3-9) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
【예제】
SQL> select numtoyminterval(30,'month') from dual;
ENAME HIREDATE NUMTOYMIN
---------- --------- ---------
SMITH 17-DEC-80 17-JUN-83
ALLEN 20-FEB-81 20-AUG-83
WARD 22-FEB-81 22-AUG-83
JONES 02-APR-81 02-OCT-83
MARTIN 28-SEP-81 28-MAR-84
BLAKE 01-MAY-81 01-NOV-83
CLARK 09-JUN-81 09-DEC-83
SCOTT 19-APR-87 19-OCT-89
KING 17-NOV-81 17-MAY-84
TURNER 08-SEP-81 08-MAR-84
ADAMS 23-MAY-87 23-NOV-89
JAMES 03-DEC-81 03-JUN-84
FORD 03-DEC-81 03-JUN-84
MILLER 23-JAN-82 23-JUL-84
14 rows selected.
SQL>
3-10) RAWTOHEX 함수
--------------------------------------------------------------------------------
RAWTOHEX(raw) 함수는 raw 값을 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtohex(raw_col) from test;
RAWTOHEX(RAW_COL)
--------------------
7D
SQL>
3-11) RAWTONHEX 함수
--------------------------------------------------------------------------------
RAWTONHEX(raw) 함수는 raw 값을 nvarchar2형 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtonhex(raw_col) from test;
RAWTONHEX(RAW_COL)
--------------------
7D
SQL>
3-12) ROWIDTOCHAR 함수
--------------------------------------------------------------------------------
RAWIDTOCHAR(rowid) 함수는 rowid 값을 varchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-13) ROWIDTONCHAR 함수
--------------------------------------------------------------------------------
RAWIDTONCHAR(rowid) 함수는 rowid 값을 nvarchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
2 from test;
3-20) TO_NCHAR(datetime) 함수
--------------------------------------------------------------------------------
이 함수는 date, timestamp, timestamp with time zone, timestamp with local time zone,
interval month to year, interval day to second 형식의 데이터를
nchar 형식의 데이터로 변환한다.
