Oracle/SQL2009. 12. 14. 09:42


한번에 쿼리로 여러 개의 테이블에 데이터를 입력한다.

실습을 함으로써 이해해 볼 수 있다.


drop table t1 purge;
create table t1 (empno number, col1 varchar2(30));
select * from emp where empno = 7788;
insert all into t1 values (empno, ename)
           into t1 values (empno, job)
           into t1 values (empno, sal)
select * from emp where empno = 7788;
select * from t1;


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

timezone  (0) 2009.12.15
rollup, cube, grouping sets 연산자  (1) 2009.12.14
index를 사용하여 쿼리를 튜닝해보자  (0) 2009.11.16
WITH .. AS  (0) 2009.11.15
계층형 쿼리(Hierachical select)  (0) 2009.11.14
Posted by 자수성가한 부자
Oracle/SQL2009. 11. 16. 19:03


아래와 같은 비효율적인 쿼리를 바꿔보자

select deptno, max(sal)
from emp
where deptno = 20
group by deptno;



첫번째 방법

MVIEW(Materialized view)를 이용한다.
일반 VIEW는 SELECT문이 저장되지만, MVIEW와 같은 경우는 SELECT문의 결과값이 물리적으로 디스크에 저장된다.
그래서 데이터가 바뀌거나 하면 다른 결과를 얻을 수 있다.
-> 이 문제를 해결하기 위해서 DBMS_MVIEW패키지를 이용하여 Refresh할 수 있다.
    사용방법은 여기를 참조할 것


create materialized view v1
as
select deptno, max(sal) as max_sal
from emp
group by deptno;


잘 되는지 확인해보자

select * from v1;

 

두번째 방법

index를 사용한다.

create index emp_deptno_sal_idx on emp(deptno, sal);


위와 같은 index는 다음과 같은 구조를 가진다.

select deptno, sal, rowid
from emp order by 1, 2, 3;



 수정된 쿼리는 다음과 같다.

select /*+ index(emp emp_deptno_sal_idx) */
 deptno, sal
from emp
where deptno = 20
and rownum = 1;


index를 내림차순으로 읽도록 변화

select /*+ index_desc(emp emp_deptno_sal_idx)*/
 deptno, sal
from emp
where deptno = 20
and rownum = 1;



이하는 function내에 정의해서 사용자 정의함수로 사용. 아래의 사용자 정의함수는 oracle이 제공하는 single row function과 같다.

create or replace function uf_emp_max_sal
  (p_deptno emp.deptno%type)
  return emp.sal%type
is
  v_sal emp.sal%type;
begin
  select /*+ index(emp emp_deptno_sal_idx) */
 sal into v_sal
  from emp
  where deptno = 20
  and rownum = 1;

  return v_sal;
end;
/


제대로 되는지 확인해보자

select deptno, dname, loc, uf_emp_max_sal(deptno) as max_sal
from dept;


참고 : mview : http://kr.forums.oracle.com/forums/thread.jspa?threadID=453711
 

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

rollup, cube, grouping sets 연산자  (1) 2009.12.14
multi table insert  (0) 2009.12.14
WITH .. AS  (0) 2009.11.15
계층형 쿼리(Hierachical select)  (0) 2009.11.14
입력 소요시간 비교(insert 여러건 vs connect by 이용)  (0) 2009.11.11
Posted by 자수성가한 부자
Oracle/SQL2009. 11. 15. 17:02
오라클에서 WITH ..AS의 역할과 특징은 무엇일까?

한 복잡한 쿼리내에 SELECT구문에서 같은 쿼리 블럭이 한번 이상 발생할 때 사용할 수 있다.

쿼리 블럭의 결과를 회수하고, 사용자 테이블 스페이스에 그것을 저장한다. 그래서 WITH 항을 사용하면 성능이 개선된다.


예제)

WITH test AS
(
    SELECT 'A1' a, 'A2' b, 1 c FROM dual
    UNION ALL
    SELECT 'A2', 'A3', 2 FROM dual
    UNION ALL
    SELECT 'A2', 'A4', 2 FROM dual
    UNION ALL
    SELECT 'A4', 'A5', 3 FROM dual
    UNION ALL
    SELECT 'A5', 'A6', 2 FROM dual
    UNION ALL
    SELECT 'A4', 'A7', 2 FROM dual
    UNION ALL
    SELECT 'A11', 'A7', 3 FROM dual
    UNION ALL
    SELECT 'A1', 'A11', 2 FROM dual
)
SELECT a, b, c,
    (SELECT EXP(SUM(LN(c)))
     FROM test
     START WITH b = t.b
     CONNECT BY PRIOR a = b) d
FROM test t
START WITH a = 'A1'
CONNECT BY PRIOR b = a;


참조 : 오라클 교재 SQL FundmentalⅡ  6 - 22

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

multi table insert  (0) 2009.12.14
index를 사용하여 쿼리를 튜닝해보자  (0) 2009.11.16
계층형 쿼리(Hierachical select)  (0) 2009.11.14
입력 소요시간 비교(insert 여러건 vs connect by 이용)  (0) 2009.11.11
Merge  (0) 2009.11.09
Posted by 자수성가한 부자
Oracle/SQL2009. 11. 14. 23:22
connect by
start with

자료의 구조가 계층적으로 이루어진 경우
상위자료에서부터 하위자료로의 자료 전개 및
하위자료에서 상위자료로의 역 전개를 위하여 사용되는 SELECT 형식이다.


예)
오라클에서 제공하는 샘플테이블이 있는 경우 사용 가능한 예제


-- Top-down 방식

select level, lpad(empno, level*4, '_') as empno1, e.*
from emp e
start with empno = 7839
connect by PRIOR empno = mgr;


LEVEL EMPNO1 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 7839 7839 KING PRESIDENT   81/11/17 5000   10
2 ____7566 7566 JONES MANAGER 7839 81/04/02 2975   20
3 ________7788 7788 SCOTT ANALYST 7566 82/12/09 3000   20
4 ____________7876 7876 ADAMS CLERK 7788 83/01/12 1100   20
3 ________7902 7902 FORD ANALYST 7566 81/12/03 3000   20
4 ____________7369 7369 SMITH CLERK 7902 80/12/17 800   20
2 ____7698 7698 BLAKE MANAGER 7839 81/05/01 2850   30
3 ________7499 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
3 ________7521 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
3 ________7654 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
3 ________7844 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
3 ________7900 7900 JAMES CLERK 7698 81/12/03 950   30
2 ____7782 7782 CLARK MANAGER 7839 81/06/09 2450   10
3 ________7934 7934 MILLER CLERK 7782 82/01/23 1300   10


-- Bottom-up방식

select level, lpad(empno, level*4, '_') as empno1, e.*
from emp e
start with empno = 7369
connect by empno = PRIOR mgr;

LEVEL EMPNO1 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 7369 7369 SMITH CLERK 7902 80/12/17 800   20
2 ____7902 7902 FORD ANALYST 7566 81/12/03 3000   20
3 ________7566 7566 JONES MANAGER 7839 81/04/02 2975   20
4 ____________7839 7839 KING PRESIDENT   81/11/17 5000   10


-- SIBLING 키워드(계층형 구조의 order by,  같은 층 끼리만 비교함)

select level, lpad(empno, level*4, '_') as empno1, ename, sal, job, sys_connect_by_path(ename, '/')
from emp e
start with empno = 7839
connect by PRIOR empno = mgr
order SIBLINGS by sal desc;

LEVEL EMPNO1 ENAME SAL JOB SYS_CONNECT_BY_PATH(ENAME,'/')
1 7839 KING 5000 PRESIDENT /KING
2 ____7566 JONES 2975 MANAGER /KING/JONES
3 ________7788 SCOTT 3000 ANALYST /KING/JONES/SCOTT
4 ____________7876 ADAMS 1100 CLERK /KING/JONES/SCOTT/ADAMS
3 ________7902 FORD 3000 ANALYST /KING/JONES/FORD
4 ____________7369 SMITH 800 CLERK /KING/JONES/FORD/SMITH
2 ____7698 BLAKE 2850 MANAGER /KING/BLAKE
3 ________7499 ALLEN 1600 SALESMAN /KING/BLAKE/ALLEN
3 ________7844 TURNER 1500 SALESMAN /KING/BLAKE/TURNER
3 ________7521 WARD 1250 SALESMAN /KING/BLAKE/WARD
3 ________7654 MARTIN 1250 SALESMAN /KING/BLAKE/MARTIN
3 ________7900 JAMES 950 CLERK /KING/BLAKE/JAMES
2 ____7782 CLARK 2450 MANAGER /KING/CLARK
3 ________7934 MILLER 1300 CLERK /KING/CLARK/MILLER



계층형 쿼리 관련 함수

-- sys_connect_by_path() 
    : 계층형 구조의 전체 경로를 나타냄, 
     두번째 인수인 구분자는 열값의 일부의 문자로는 사용불가.
     아래의 쿼리를 예로 들면 ename중에 /가 들어가는 이름이 있으면 /는 사용 불가


select level, lpad(empno, level*4, '_') as empno1, ename, sal, job, sys_connect_by_path(ename, '/')
from emp e
start with empno = 7839
connect by PRIOR empno = mgr;
LEVEL EMPNO1 ENAME SAL JOB SYS_CONNECT_BY_PATH(ENAME,'/')
1 7839 KING 5000 PRESIDENT /KING
2 ____7566 JONES 2975 MANAGER /KING/JONES
3 ________7788 SCOTT 3000 ANALYST /KING/JONES/SCOTT
4 ____________7876 ADAMS 1100 CLERK /KING/JONES/SCOTT/ADAMS
3 ________7902 FORD 3000 ANALYST /KING/JONES/FORD
4 ____________7369 SMITH 800 CLERK /KING/JONES/FORD/SMITH
2 ____7698 BLAKE 2850 MANAGER /KING/BLAKE
3 ________7499 ALLEN 1600 SALESMAN /KING/BLAKE/ALLEN
3 ________7521 WARD 1250 SALESMAN /KING/BLAKE/WARD
3 ________7654 MARTIN 1250 SALESMAN /KING/BLAKE/MARTIN
3 ________7844 TURNER 1500 SALESMAN /KING/BLAKE/TURNER
3 ________7900 JAMES 950 CLERK /KING/BLAKE/JAMES
2 ____7782 CLARK 2450 MANAGER /KING/CLARK
3 ________7934 MILLER 1300 CLERK /KING/CLARK/MILLER


-- connect_by_root() : 상관관계 쿼리에서 level이 0인 최상위 로우의 정보를 얻어올 수 있다.

SELECT LPAD('   ', 4*(LEVEL-1)) || ename ename, empno, 
CONNECT_BY_ROOT  empno "Root empno", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;

ENAME EMPNO Root empno LEVEL
KING 7839 7839 1
JONES 7566 7839 2
SCOTT 7788 7839 3
ADAMS 7876 7839 4
FORD 7902 7839 3
SMITH 7369 7839 4
BLAKE 7698 7839 2
ALLEN 7499 7839 3
WARD 7521 7839 3
MARTIN 7654 7839 3
TURNER 7844 7839 3
JAMES 7900 7839 3
CLARK 7782 7839 2
MILLER 7934 7839 3



-- connect_by_isleaf() : 계층형 구조에서 로우의 최하위 레벨 여부를 반환한다.

SELECT LPAD('', 4*(LEVEL-1)) || ename ename, empno, 
CONNECT_BY_ISLEAF "leaf", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY NOCYCLE  PRIOR empno=mgr;


ENAME EMPNO leaf LEVEL
KING 7839 0 1
JONES 7566 0 2
SCOTT 7788 0 3
ADAMS 7876 1 4
FORD 7902 0 3
SMITH 7369 1 4
BLAKE 7698 0 2
ALLEN 7499 1 3
WARD 7521 1 3
MARTIN 7654 1 3
TURNER 7844 1 3
JAMES 7900 1 3
CLARK 7782 0 2
MILLER 7934 1 3


참조 : http://www.oracleclub.com/lecture/1903

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

index를 사용하여 쿼리를 튜닝해보자  (0) 2009.11.16
WITH .. AS  (0) 2009.11.15
입력 소요시간 비교(insert 여러건 vs connect by 이용)  (0) 2009.11.11
Merge  (0) 2009.11.09
분석함수(Anaylitic Function)  (0) 2009.11.09
Posted by 자수성가한 부자
Oracle/SQL2009. 11. 11. 23:20

create table t1
(col1 number);

set timing on                          -- 소요시간을 표시하기 위한 세팅


# insert문 백만개로 백만건 입력시 소요시간

  begin
    FOR i IN 1..1000000 LOOP
      insert into t1 values (i);
    END LOOP;
  end;
/

경과시간: 00:00:31.14

#connect by 이용 백만건 입력시 소요시간

  insert into t1
  select level
  from dual
  connect by level <= 1000000;

경과시간: 00:00:03.46 (9배나 빠름)

이렇게 데이터를 입력할 일이 있으면 꼭 connect by를 이용하도록 해야겠다.

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

WITH .. AS  (0) 2009.11.15
계층형 쿼리(Hierachical select)  (0) 2009.11.14
Merge  (0) 2009.11.09
분석함수(Anaylitic Function)  (0) 2009.11.09
단일행 함수(Single Row Function)  (0) 2009.11.09
Posted by 자수성가한 부자
Oracle/SQL2009. 11. 9. 11:42

Merge는
Update와 Insert를 합친 것이라고 볼 수 있다. 그래서 때로는 Upsert라고 부르기도 한다.
9i부터 등장

다음과 같이 t1테이블과 t2 테이블이 있다고 가정하자.

select * from t1;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 80/12/17 800   20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975   20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850   30
7782 CLARK MANAGER 7839 81/06/09 2450   10
7788 SCOTT ANALYST 7566 82/12/09 3000   20

select * from t2;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 80/12/17 800   20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975   20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850   30
7782 CLARK MANAGER 7839 81/06/09 2450   10
7788 SCOTT ANALYST 7566 82/12/09 3000   20
7839 KING PRESIDENT   81/11/17 5000   10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100   20
7900 JAMES CLERK 7698 81/12/03 950   30
7902 FORD ANALYST 7566 81/12/03 3000   20
7934 MILLER CLERK 7782 82/01/23 1300   10
4444         4444    


merge 명령을 실행한다.
t1테이블의 empno와 t2테이블의 empno가 같으면 update를 하고 다를 경우 insert를 하는 것이다.

MERGE INTO t1 a
USING           t2 b                                                   -- 재료가 되는 테이블, 서브 쿼리도 들어올 수 있음.
ON (a.empno = b.empno)
WHEN MATCHED THEN
    UPDATE SET a.sal = b.sal, 
                        a.comm = b.comm
WHEN NOT MATCHED THEN
    INSERT (a.empno, a.ename, a.sal, a.job, a.deptno)
    VALUES (b.empno, b.ename, b.sal, b.job, b.deptno);


결과가 다음과 같이 바뀌었다.

select * from t1;

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 80/12/17 900   20
7499 ALLEN SALESMAN 7698 81/02/20 1700 300 30
7521 WARD SALESMAN 7698 81/02/22 1350 500 30
7566 JONES MANAGER 7839 81/04/02 3075   20
7654 MARTIN SALESMAN 7698 81/09/28 1350 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2950   30
7782 CLARK MANAGER 7839 81/06/09 2550   10
7788 SCOTT ANALYST 7566 82/12/09 3100   20
7844 TURNER SALESMAN     1500   30
7839 KING PRESIDENT     5000   10
4444         4444    
7934 MILLER CLERK     1300   10
7902 FORD ANALYST     3000   20
7876 ADAMS CLERK     1100   20
7900 JAMES CLERK     950   30

Posted by 자수성가한 부자
Oracle/SQL2009. 11. 9. 10:35

오라클 10g부터 분석함수란 것이 등장했다.
랭킹, 누적합 또는 표준편차 등을 구하는 것을 9i까지는 복잡하게 SQL을 가공해서 사용했었는데,
10g부터는 아래의 함수를 사용함으로써 쉽게 구할 수 있게 되었다.
order by 항 이외에는 마지막으로 처리된다.

- 누적합        

SQL> SELECT deptno,  ename,  sal,
        SUM(sal) OVER (PARTITION BY DEPTNO
                                ORDER BY deptno, ename)
        FROM emp;


SQL> select d.loc, d.deptno, e.empno, e.ename, e.sal
         from emp e, dept d
         where e.deptno = d.deptno;

 

SQL> select d.loc, d.deptno, e.empno, e.ename, e.sal,
         sum(sal) over (partition by d.loc
                               order by empno, sal) as running_total
         from emp e, dept d
         where e.deptno = d.deptno;


- Rank 구하기

SQL> SELECT deptno,  ename,  sal,
                      rank() over (order by sal)        as rank,
                     dense_rank() over (order by sal)  as dense_rank,
                     row_number() over (order by sal)  as row_number
         FROM emp;

 

SQL> select *
         from (SELECT deptno,  ename,  sal,
                               rank() over (order by sal)              as rank,
                               dense_rank() over (order by sal)  as dense_rank
                 FROM emp) t
        where rank <= 10;


 

SQL> select *
         from (SELECT deptno,  ename,  sal,
                              rank() over (order by sal)              as rank,
                              dense_rank() over (order by sal)  as dense_rank
                 FROM emp) t
         where dense_rank <= 10;


 

SQL> SELECT deptno,  ename,  sal,
                     rank() over (partition by deptno
                                        order by deptno, sal)       as rank,
                     dense_rank() over (partition by deptno
                                                   order by deptno, sal) as dense_rank,
                     row_number() over (partition by deptno
                                                    order by deptno, sal) as row_number
          FROM emp
          order by deptno, sal;

 


-- 이동 평균

SQL> SELECT deptno "Deptno", ename "Ename", sal "Sal",
                      avg(SAL) OVER (ORDER BY ename) as 누적평균
         FROM emp;

 

SQL> SELECT deptno "Deptno", ename "Ename", sal "Sal",
                      avg(SAL) OVER (ORDER BY ename
                                               rows 2 preceding) as 세개이동평균
          FROM emp;



-- 최고값, 최소값

SQL> SELECT deptno, ename, sal,
                      FIRST_VALUE(ename) OVER (ORDER BY sal ASC) AS MIN_SAL_HAS,
                      FIRST_VALUE(sal)   OVER (ORDER BY sal ASC) AS MIN_SAL,
                      FIRST_VALUE(ename) OVER (ORDER BY sal DESC) AS MAX_SAL_HAS,
                      FIRST_VALUE(sal)   OVER (ORDER BY sal DESC) AS MAX_SAL,
                      LAST_VALUE(sal)    OVER (ORDER BY sal
                                             range between unbounded preceding and unbounded following) as min_sal2
         FROM emp;

   ※ 아래의 의미는 :  나를 기준으로 처음부터 끝까지
    range between unbounded preceding and unbounded following


기타참고사항

SQL*PLUS TIP

SQL> break on deptno skip1                   -- 컬럼과 컬럼 사이에 빈칸을 둠
SQL> break on deptno skip page             -- 부서 번호가 바뀔 경우 페이지로 구분
SQL> clear break                                   -- 설정한 값 해제



참조 :
 - 메뉴얼 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1007779
 - 방형욱 강사님 까페 : http://cafe.naver.com/gseducation.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=44

Posted by 자수성가한 부자
Oracle/SQL2009. 11. 9. 09:44


오라클에서 쓰이는 단일행 함수를 정리해 보았다.
단일행 함수를 알고 적재적소에 잘 쓰는 것이 SQL의 실력을 한층 더 높여주는 것이라 했다.
필요할 때 또는 심심할 때 한번씩 읽어봐도 괜찮을 것 같다.

* Numeric Functions

1. ABS 
     : ABS(n)은 n의 절대값을 반환한다.

   - usage : ABS(n)

   - example : SELECT ABS(-37) AS absolute FROM DUAL;

                 ABSOLUTE
               ----------
                       37

2. ACOS
      : ACOS(n)은 n의 역코사인(arc cosine)값을 반환한다. 인수 n은 -1부터 1까지의 범위를 갖는다.

   - usage : ACOS(n)

   - example : SELECT ACOS(.7) AS arc_cosine FROM DUAL;

               ARC_COSINE
               ----------
                .79539883

3. ASIN
      : ASIN(n)은 n의 역사인(arc sine)값을 반환한다. 인수 n은 -1부터 1까지의 범위를 갖는다.

   - usage : ASIN(n)

   - example : SELECT ASIN(.7) AS arc_sine FROM DUAL;

                 ARC_SINE
               ----------
               .775397497
                  
4. ATAN
      : ATAN(n)은 n의 역탄젠트(arc tangent)값을 반환한다. 인수 n은 -1부터 1까지의 범위를 갖는다.

   - usage : ATAN(n)

   - example : SELECT ATAN(.7) AS arc_tangent FROM DUAL;

               ARC_TANGENT
               -----------
                .610725964

5. ATAN2
      : ATAN2(m,n)은 ATAN2(m/n)과 같으며, m/n의 역탄젠트(arc tangent)값을 반환한다.
           인수 n은 범위가 제한이 없다.
 
   - usage : ATAN2(m,n), ATAN2(m/n)

   - example : SELECT ATAN2(.5, .3) AS arc_tangent2 FROM DUAL;

               ARC_TANGENT2
               ------------
                 1.03037683

6. BITAND
     : BITAND(m, n)은 인수 m과 인수 n의 비트에 대한 AND연산을 수행하여 정수를 반환한다.
       m과 n은 음이 아닌 정수로 변환되어, AND 연산을 계산하여 정수를 반환한다.
       이 함수는 일반적으로 DECODE함수와 함께 이용한다. AND연산은 2개의 비트를 비교한다.
       값이 같을 경우에는 1을 반환하고 값이 다를 경우에는 0을 반환한다.
       예를 들어, 정수 5(이진수 101)과 1(이진수 001)의 AND연산은 1(이진수 1)이다.

   - usage : BITAND(m, n)

   - example : SELECT BITAND(5, 1) AS bitand FROM DUAL;

                   BITAND
               ----------
                        1

7. CEIL
     : CEIL은 인수에서 지정한 수를 올림하여 정수를 구하는 함수이다. 음의 수치를 부여하면
       음수측의 가장 가까운 정수를 반환한다.

   - usage : CEIL(n)

   - example : SELECT CEIL(8.6) AS ceiling FROM DUAL;

                  CEILING
               ----------
                        9

8. COS
     : COS은 파라미터 n(라디안으로 표현되는 각도)의 코사인값을 반환한다.

   - usage : COS(n)

   - example : SELECT COS(180 * 3.14159265359/180) AS "cosine of 180" FROM DUAL;

               cosine of 180
               -------------
                          -1

9. COSH
     : COSH은 파라미터 n(라디안으로 표현되는 각도)의 쌍곡 코사인값(hyperbolic cosine)을 반환한다.

   - usage : COSH(n)

   - example : SELECT COSH(0) AS "hyperbolic cosine of 0" FROM DUAL;

               hyperbolic cosine of 0
               ----------------------
                                    1

10. EXP
      : EXP는 e의 n 제곱 값을 반환한다. (e=2.71828183) 이 함수는 인수와 같은 타입의 값을 반환한다.

    - usage : EXP(5)

    - example : SELECT EXP(5) AS "e to the 5th power" FROM DUAL;

               e to the 5th power
               ------------------
                       148.413159

11. FLOOR 
      : FLOOR는 지정한 숫자보다 작거나 같은 정수 중에서 최대값을 반환한다.

    - usage : FLOOR(n)

    - example : SELECT FLOOR(8.6) AS floor FROM DUAL;

                    FLOOR
                ---------
                        8

12. LN
      : LN함수는 입력값의 자연 로그 값을 반환한다. LN은 0보다 큰 값이다.

    - usage : LN(n)

    - example : SELECT LN(37) AS "Natural log of 37" FROM DUAL;

                Natural log of 37
                -----------------
                       3.61091791

13. LOG
      : LOG(m,n)에서 밑을 m으로 한 n의 로그 값을 반환한다. 밑 m은 0 또는 1 이외의 정수값, n은 양수값이다.

    - usage : LOG(m, n)

    - example : SELECT LOG(5, 125) AS "Log base 5 of 125" FROM DUAL;

                Log base 5 of 125
                -----------------
                                3
 
14. MOD
       : MOD는 m을 n으로 나눈 나머지값을 반환한다. n이 0이면, m를 반환한다.
          MOD 공식은 m - n * TRUNC(m/n)이다.
         
    - usage : MOD(m, n)

    - example : SELECT MOD(17, 5) AS modulus FROM DUAL;

                    MODULUS
                -----------
                          2

15. NANVL
       : NANVL는 10g에서 추가된 BINARY_FLOAT, BINARY_DOUBLE과 같은 datatype에만 적용되는 함수이다.
         m의 값이 NaN(Not A Number)일 경우에 n을 반환하고, NaN이 아닐 경우 그냥 m를 반환한다.
         이 함수는 NaN값을 Null으로 매핑하는데 위해 유용하다.

    - usage : NANVL(m, n)

    - example : CREATE TABLE float_point_demo (
             dec_num NUMBER(10,2)
          , bin_double BINARY_DOUBLE
          , bin_float BINARY_FLOAT
           ) 

           INSERT INTO float_point_demo VALUES (1234.56, 1234.56, 1234.56)

         INSERT INTO float_point_demo VALUES (0, 'NaN', 'NaN')

           SELECT * FROM float_point_demo

                DEC_NUM BIN_DOUBLE  BIN_FLOAT
               -------- ---------- ----------
                1234.56 1.235E+003 1.235E+003
                      0        Nan        Nan

                SELECT bin_float, NANVL(bin_float, 0)
             FROM float_point_demo

            BIN_FLOAT NANVL(BIN_FLOAT,0)
               ---------- ------------------
        1.235E+003         1.235E+003
                      Nan                  0

16. POWER
      : POWER는 m의 n승 값을 반환한다. m와 지수 n은 임의의 수이나, m이 음수이면, n은 정수여야 한다.

    - usage : POWER(m, n)

    - example : SELECT POWER(3, 3) AS raised FROM DUAL;

                   RAISED
                ---------
                       27

17. REMAINDER 
      : REMAINDER는 m를 n으로 나눈 나머지를 반환한다. MOD와 유사한데 공식은 m - n * ROUND(m/n)이다.

    - usage : REMAINDER(m, n)

    - example : SELECT MOD (13, 5), REMAINDER(13, 5) AS remainder FROM DUAL;

                   MOD(13,5)     REMAINDER
                ------------  ------------
                           3            -2

18. SIGN
      : SIGN은 파라미터로 주어지는 값의 부호(+, -, 0)에 따라 다음과 같이 반환한다.
           n < 0 일 경우 -1 을 반환
           n = 0 일 경우 0 을 반환
           n > 0 일 경우 1 을 반환

    - usage : SIGN(n)

    - example : SELECT SIGN(-13) AS sign FROM DUAL;

                     SIGN
                ---------
                       -1

19. SIN
      : SIN은 인수 n의 사인(sine)값을 반환한다.(라디안으로 표현되는 각도)
        datatype이 BINARY_FLOAT일 경우, 반환값의 datatype은 BINARY_DOUBLE, 그 외에는 숫자 값을 반환한다.

    - usage : SIN(n)

    - example : SELECT SIN(50 * 3.14159265359/180) AS "sine of 50" FROM DUAL;

                sine of 50
                ----------
                .766044443

20. SINH
       : SINH함수는 인수 n의 쌍곡선 사인(hyperbolic sine)을 반환한다.
         SINH(n)의 값은 (EXP(n) - EXP(-n))/2 의 값과 같다.

    - usage : SINH(n)

    - example : SELECT SINH(2) AS "hyperbolic sine of 2" FROM DUAL;

                hyperbolic sine of 2
                --------------------
                          3.62686041

21. SQRT
       : SQRT는 n의 제곱근을 반환한다. n이 number 타입일 경우에는 n은 음수가 되어서는 안된다.
         BINARY_FLOAT 또는 BINARY_DOUBLE 타입일 경우에는 다음과 같은 값을 반환한다.
           n >= 0 일 경우 양수를 반환
           n = -0 일 경우 -0 을 반환
           n < 0 일 경우 NaN 을 반환

    - usage : SQRT(n)

    - example : SELECT SQRT(7) AS "square root" FROM DUAL;

                square root
                -----------
                 2.64575131

22. TAN
       : TAN은 인수 n의 사인(tangent)값을 반환한다.(라디안으로 표현되는 각도)

    - usage : TAN(n)

    - example : SELECT TAN(105 * 3.14159265359 / 180) AS "tangent of 105" FROM DUAL;

                tangent of 105
                --------------
                    -3.7320508

23. TANH 
       : TANH는 인수 n의 쌍곡선 탄젠트(hyperbolic tangent)을 반환한다.
         TANH(n)의 값은 (EXP(n) - EXP(-n))/(EXP(n) + EXP(-n)) 의 값과 같다.

    - usage : TANH(n)

    - example : SELECT TANH(.7) AS "Hyperbolic tangent of .7" FROM DUAL;

                Hyperbolic tangent of .7
                ------------------------
                              .604367777

* Character Functions Returning Character Values

1. CHR
     : CHR는 해당 DB의 character set 에서 파라미터로 주어지는 10진수 n에 대응하는 아스키코드를 반환한다.

   - usage : CHR(n)

   - example : SELECT CHR(68)||CHR(66) AS oracle FROM DUAL;

               ORACLE
               ------
                   DB

2. NCHR
     : NCHR은 유니코드 문자를 반환한다. 이 함수는 USING NCHAR_CS구문을 가진 CHR과 동등하다.

   - usage : NCHR(n)

   - example : SELECT NCHR(187) AS nchr, CHR(187 USING NCHAR_CS) AS chr FROM DUAL;

               NCHR    CHR
               ----    ---
               >       >

3. NLS_INITCAP 
     : NLS_INITCAP는 각 단어의 처음 문자를 대문자로, 나머지 문자를 소문자로 변환하여 char를 반환한다.
       단어는 공백 또는 영숫자 이외의 문자로 단락을 구분한다.

   - usage : NLS_INITCAP('c', 'nlsparam')

   - example : SELECT NLS_INITCAP ('database') AS initcap FROM DUAL;

                INITCAP
               --------
               Database

4. NLS_LOWER
     : NLS_LOWER는 모든 문자를 소문자로 변환하여 반환한다.
      'nlsparam'은 NLS_INITCAP함수와 같은 형태와 목적을 가질수 있다.

   - usage : NLS_LOWER('c', 'nlsparam')

   - example : SELECT NLS_LOWER('ORACLE''', 'NLS_SORT = XGerman') AS lowercase FROM DUAL;

               LOWERCASE
               ---------
                  oracle

5. NLSSORT
     : NLSSORT는 char 소트에 이용되는 bytes의 문자열을 반환한다.

   - usage : NLSSORT('c', 'nlsparam')

   - example : CREATE TABLE t1 (name VARCHAR2(15));
        INSERT INTO t1 VALUES ('Gaardiner');
        INSERT INTO t1 VALUES ('Gaberd');
        INSERT INTO t1 VALUES ('Gaasten');

               SELECT * FROM t1 ORDER BY name;

               NAME
               ----------
               Gaardiner
               Gaasten
               Gaberd

               SELECT * FROM t1 ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');

               NAME
               ---------------
               Gaberd
               Gaardiner
               Gaasten

6. NLS_UPPER
     : NLS_UPPER는 입력 문자열을 모두 대문자로 변환한 문자열을 반환한다.

   - usage : NLS_UPPER('c', 'nlsparam')

   - example : SELECT NLS_UPPER ('grobe') AS uppercase FROM DUAL;

               UPPERCASE
               ---------
                   GROBE

7. SOUNDEX
     : SOUNDEX는 char의 음성 표현을 가지는 문자열을 반환한다.
       이 함수는 스펠링이 틀려도 발음이 유사한 영단어를 비교할수 있다.

   - usage : SOUNDEX('c')

   - example : SELECT last_name, first_name FROM employees
               WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE');

               LAST_NAME  FIRST_NAME
               ---------- ----------
                  Smith      Lindsey
                  Smith      William

8. TRANSLATE
     : TRANSLATE는 첫번째 문자열 expr에서 두번째 문자열(from_string) 각각의 문자를 순서대로
       세번째 문자열(to_string)의 대응되는 각각의 문자로 바꿔준다. 즉, 문자단위로 일대일 변환을 해준다.
        from_string은 to_string 보다 더 많은 문자를 가져야 하며, from_string 의 이 여분의 문자들
        (to_string에 대응되는 문자가 없는)이 expr에 있는 경우, 해당 문자를 삭제하는 기능을 한다.

   - usage : TRANSLATE(expr, from_string, to_string)

   - example : SELECT TRANSLATE ('SQL*Plus User''s Guide', ' */''', '___') AS translate FROM DUAL

               TRANSLATE              
               --------------------
               SQL_Plus_Users_Guide

9. ASCII
     : ASCII는 주어진 char의 첫 문자의 아스키 값에 상응하는 10진수값을 반환한다.

   - usage : ASCII('c')

   - example : SELECT ASCII('A') AS ascii FROM DUAL;
 
               ASCII
               -----
                  65

* Datetime Functions

1. CURRENT_DATE
     : CURRENT_DATE는 현재 세션의 날짜 정보를 Date 데이터 형으로 반환한다.

   - usage : CURRENT_DATE

   - example : ALTER SESSION SET TIME_ZONE = '-5:0';
        ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
               SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

               SESSIONTIMEZONE CURRENT_DATE
               --------------- --------------------
               -05:00          08-APR-2009 13:14:03

2. DBTIMEZONE
     : DBTIMEZONE는 데이터 베이스 time zone의 값을 반환한다.

   - usage : DBTIMEZONE

   - example : SELECT DBTIMEZONE FROM DUAL;

        DBTIME
               ------
               +00:00

3. EXTRACT
     : EXTRACT는 특정 날짜,시간 값이나 날짜 값 표현식으로부터 지정된 날짜 영역의 값을 추출하여 반환한다.

   - usage : EXTRACT(t FROM value_expr)

   - example : SELECT EXTRACT(YEAR FROM DATE '2009-03-07') FROM DUAL;

               EXTRACT(YEARFROMDATE'2009-03-07')
               ---------------------------------
                                            2009

4. SESSIONTIMEZONE
     : SESSIONTIMEZONE은 현재 세션의 시간대역(time zone)을 반환한다.

   - usage : SESSIONTIMEZONE

   - example : SELECT SESSIONTIMEZONE FROM DUAL;

               SESSION
               -------
               -08:00

5. SYS_EXTRACT_UTC
     : SYS_EXTRACT_UTC는 time zone offset 또는 time zone region name을 포함한 datetime값으로 부터
       협정 세계시간 UTC (Coordinated Universal Time?formerly Greenwich Mean Time)을 추출한다.

   - usage : SYS_EXTRACT_UTC(datetime)

   - example : SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00') AS utc FROM DUAL;

               UTC
               ---------------------
               28-MAR-00 07.30.00 PM

* General Comparison Functions

1. GREATEST
     : GREATEST는 하나 이상의 인수중에서 가장 큰 값을 반환한다.

   - usage : GREATEST(expr)

   - example : SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD') AS greatest FROM DUAL;
 
               GREATEST
               --------
               HARRY

2. LEAST
     : LEAST는 인수 EXPR의 리스트 중에서 가장 작은 값을 반환을 한다.

   - usage : LEAST(expr)

   - example : SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD') AS least FROM DUAL;
 
               LEAST
               ------
               HAROLD

* Conversion Functions

1. ASCIISTR
    : ASCIISTR은 주어진 문자열의 아스키 문자열을 반환한다.       

   - usage : ASCIISTR('c')

   - example : SELECT ASCIISTR('ABACDE') AS asciistr FROM DUAL;

               ASCIISTR
               ----------
               AB\00C4CDE

2. BIN_TO_NUM
     : BIN_TO_NUM는 비트(2진수) 벡터를 동등한 수(10진수)로 변환한다.

   - usage : BIN_TO_NUM(expr)

   - example : SELECT BIN_TO_NUM(1,1,0) FROM DUAL;

               BIN_TO_NUM(1,1,0)
               -----------------
                               6

3. CHARTOROWID
     : CHARTOROWID는 CHAR, VARCHAR2, NCHAR, or NVARCHAR2 데이터형태의 값으로부터 ROWID형으로 변환한다.

   - usage : CHARTOROWID('c')

   - example : SELECT last_name FROM employees WHERE ROWID = CHARTOROWID('AAAFd1AAFAAAABSAA/');
 
               LAST_NAME
               -----------
               Greene

* Collection Functions

1. CARDINALITY
    : CARDINALITY는 중첩 테이블(nested table)에서 원소의 수를 반환한다.
                 반환된 형식은 number이고 만약 nested table이 공백이거나 null 집합인 경우 null을 반환한다.

   - usage : CARDINALITY(nested_table)

   - example : SELECT product_id, CARDINALITY(ad_textdocs_ntab) FROM print_media;

               PRODUCT_ID CARDINALITY(AD_TEXTDOCS_NTAB)
               ---------- -----------------------------
                     3060                             3
               2056                             3
               3106                             3
                 2268                             3

2. SET
     : SET은 중첩 테이블에서 중복을 배제하여 반환한다. 반환된 중첩 테이블은 입력된 중첩 테이블과 같은 형태이다.

   - usage : SET(nested_table)

   - example : SELECT customer_id, SET(cust_address_ntab) address FROM customers_demo;

               CUSTOMER_ID ADDRESS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
               ----------- -------------------------------------------------------------------------------------------------
                    101 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'))
                       102 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'))
               103 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))
               104 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('6445 Bay Harbor Ln', '46254', 'Indianapolis', 'IN', 'US'))
               105 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('4019 W 3Rd St', '47404', 'Bloomington', 'IN', 'US'))

* Hierarchical Function

1. SYS_CONNECT_BY_PATH
     : SYS_CONNECT_BY_PATH는 계층적 쿼리에서 유효하다. 이 함수는 루트로부터 node로 열의 값 Path를 반환환다.
                         column 값은 CONNECT BY 조건에 의해 반환되는 각행을 char에 의해 분리된다.

   - usage : SYS_CONNECT_BY_PATH(column, 'c')

   - example : SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') AS path FROM employees
               START WITH last_name = 'Kochhar' CONNECT BY PRIOR employee_id = manager_id;

               PATH
               -------------------------------
                /Kochhar
                  /Kochhar/Greenberg
                    /Kochhar/Greenberg/Faviet
         /Kochhar/Greenberg/Chen
           /Kochhar/Greenberg/Sciarra
        /Kochhar/Greenberg/Urman
               /Kochhar/Greenberg/Popp
      /Kochhar/Whalen
                  /Kochhar/Mavris
                  /Kochhar/Baer
                  /Kochhar/Higgins
                    /Kochhar/Higgins/Gietz

* Encoding and Decoding Functions

1. DUMP
    : DUMP는 지정한 데이터의 위치와 길이 등을 지정한 형식으로 반환한다. return_fmt는 반환값의 서식을 지정한다.

   - usage : DUMP(expr, return_fmt, start_position, length)

   - example : SELECT DUMP('abc', 1016) AS dump FROM DUAL;

               DUMP                        
               ------------------------------------------
               Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63

2. VSIZE
    : VSIZE는 expr의 내부 표현에서 바이트의 수를 반환한다. 만약 expr이 Null이라면, 이 함수는 Null을 반환한다.

   - usage : VSIZE(expr)

   - example : SELECT last_name, VSIZE (last_name) AS bytes FROM employees WHERE department_id = 10;
 
               LAST_NAME            BYTES
               --------------- ----------
               Whalen                   6

* NULL-Related Functions

1. LNNVL
     : LNNVL은 조건식에 주어지는 항의 한쪽 혹은 양쪽에 NULL 값이 나타날 경우에, 조건식을 평가하는 간결한 방법을
       제공한다.
       LNNVL은L where 절에만 사용할 수 있다. LNNVL 함수는 조건식을 파라미터로 가지며 조건식이 FALSE나
       UNKNOWN 일 경우에 TRUE 를 반환하고, 조건식이 TRUE 일 경우에 FALSE 를 반환한다. LNNVL 함수는
       IS [NOT] NULL, AND, OR 조건식을 사용할수 없으나 잠재적으로 NULL 값이 나올 수 있는 상황에서 NULL 값을
       처리하기 위해 사용할 수 있다.  파라미터로 주어지는 조건식에는 AND, OR, BETWEEN 을 포함하는
       다중조건식(compound condition)은 올 수 없다.

   - usage : LNNVL(condition)

   - example : SELECT COUNT (*) FROM emp WHERE LNNVL (comm >= 500)

               COUNT(*)
               --------
                     12

* Environment and Identifier Functions

1. UID
    : UID는 세션 사용자의 유일한 식별하는 정수를 반환한다.(로그인 유저)

   - usage : UID

   - example : SELECT UID FROM DUAL;
                     
                      UID
               ----------
                        0

2. USER
    : USER는 VARCHAR2 형태를 가지는 세션 사용자(로그인 유저)의 이름을 반환한다.

   - usage : USER

   - example : SELECT USER FROM DUAL;
                     
               USER
               ----------
               SYS   

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

Merge  (0) 2009.11.09
분석함수(Anaylitic Function)  (0) 2009.11.09
Pairwise comparions VS Nonpairwise comparions  (0) 2009.11.07
[펌] Within a namespace, no two objects can have the same name.  (0) 2009.10.29
[펌]sqlplus column 관련 명령어  (0) 2009.10.28
Posted by 자수성가한 부자
Oracle/SQL2009. 11. 7. 11:51


Multiple column comparions은 subquery를 포함할 수 있는데,

Pairwise comparions 와 Nonpairwise comparions가 있습니다.

글자 그대로 해석을 해보면 쌍으로 비교(pairwise comparisons),
안쌍으로 비교(nonpairwise comparisons)이다.

표현이 좀 무식하지만 그렇게 이해를 해보면 될 것 같다.

예제를 보면 잘 이해가 될 것이다.

우선 테이블 t1과 t2의 데이터는 아래와 같다.

T1) 
      COL1 COL2
---------- ----------
            10 A
            20 A
            10 B
            20 B

T2)
      COL1 COL2
---------- ----------
            10 A
            20 B



1) Pairwise comparions
   SELECT   * 
   FROM     t1
   
WHERE (col1, col2) IN (SELECT col1, col2
                                      FROM t2) 


   결과)
        COL1 COL2
---------- ----------
            10 A
            20 B

2) Nonpairwise comparions
    SELECT *
    FROM t1
    WHERE col1 IN (SELECT col1 FROM t2) AND
                col2 IN (select col2 from t2)
  
   결과)
        COL1 COL2
---------- ----------
            10 B
            10 A
            20 B
            20 A


내용 참고 : 오라클 교재(Oracle Database 10g SQL Fundmental Ⅱ 6-3 ~ 6-6)
예제 출처 : http://cafe.naver.com/gseducation.cafe

Posted by 자수성가한 부자
Oracle/SQL2009. 10. 29. 22:46

Within a namespace, no two objects can have the same name.

 

# The following schema objects share one namespace:

 

 - Tables

 - Views

 - Sequences

 - Private synonyms

 - Stand-alone procedures

 - Stand-alone stored functions

 - Packages

 - Materialized views

 - User-defined types

 

# Each of the following schema objects has its own namespace:

 

 - Indexes

 - Constraints

 - Clusters

 - Database triggers

 - Private database links

 - Dimensions

 

Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.

 

Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.

 

# Each of the following nonschema objects also has its own namespace:

 

 - User roles

 - Public synonyms

 - Public database links

 - Tablespaces

 - Profiles

 - Parameter files (PFILEs) and server parameter files (SPFILEs)

 

Because the objects in these namespaces are not contained in schemas, these namespaces span the entire database.

 

출처 : Schema Object Names and Qualifiers

Posted by 자수성가한 부자