영혼과영혼의양식

오라클 함수
1.      숫자 함수(Number Function)
ABS(n)
ABS함수는 절대값을 계산하는 함수입니다.
SQL>SELECT ABS(-10) Absolute FROM dual ;
Absolute
--------
       10

 
CEIL(n)
CEIL함수는 주어진 값보다는 크지만 가장 근접하는 최소값을 구하는 함수입니다.
SQL>SELECT CEIL(10.1) TEST FROM dual ;
  TEST
-------
      11
 
SQL>SELECT CEIL(-10.1) TEST FROM dual ;
   TEST
-------
     -10

 
EXP(n)
EXP함수는 주어진 값의 e의 승수를 나타냅니다.
e는 2.171828183..입니다.
 
FLOOR(n)
FLOOR함수는 주어진 값보다 작거나 같은 최대 정수값을 구하는 함수입니다.
CEIL 함수와 비교해 보세요.
SQL>SELECT FLOOR(10.1) TEST FROM dual ;
    TEST
 -------
       10
 
SQL>SELECT FLOOR(-10.1) TEST FROM dual ;
    TEST
-------
      -11

 
LN(n)
LN함수는 주어진 값의 자연로그 값을 반환합니다.
 
MOD(m, n)
MOD함수는 m을 n으로 나누어 남은 값을 반환한다. n이 0일 경우 m을 반환합니다.
SQL>SELECT MOD(9, 4) TEST FROM dual ;
    TEST
  -------
         1

 
POWER(m, n)
POWER함수는 m의 n승 값을 계산합니다.
SQL>SELECT POWER(4, 2) TEST FROM dual ;
      TEST
   -------
         16

 
ROUND(n, [m])
ROUND함수는 n값의 반올림을 하는 함수로 m은 소숫점 아래 자릿수를 나타냅니다.
SQL>SELECT ROUND(192.123, 1) TEST FROM dual ;
      TEST
   -------
      192.1
 
SQL>SELECT ROUND(192.123, -1) TEST FROM dual ;
     TEST
   -------
       190

 
SIGN(n)
SIGN함수는 n<0일 경우 -1DFM N=0일 경우 0을 N>0일 경우 1을 반환합니다.
 
SQRT(n)
SQRT함수는 n값의 루트값을 계산한다. n은 양수여야 합니다.
 
TRUNC(n, m)
TRUNC함수는 n값을 m 소숫점 자리로 반내림한 값을 반환합니다.
SQL>SELECT TRUNC(7.5597, 2) TEST FROM dual ;
    TEST
 -------
     7.55
 
SQL>SELECT TRUNC (5254.26, -2 ) TEST FROM dual ;
    TEST
 -------
     5200

2.      문자 함수

CONCAT(char1, char2)
CONCAT 함수는 Concatenation의 약자로 두 문자를 결합하는 역할을 합니다. "||" 연산자와 같은 역할을 합니다.
SQL>SELECT CONCAT('Oracle', ' Korea') NAME FROM dual ;
    NAME
   -------------
   Oracle Korea

 
INITCAP(char)
주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 줍니다.
SQL>SELECT INITCAP('kim jung sick') NAME FROM dual ;
     NAME
 -------------
  Kim jung sick

 
LOWER(char)
문자열을 소문자로 변환 시켜 줍니다.
 
UPPER(char)
문자열을 대문자로 변환 시켜 줍니다.
 SQL>SELECT LOWER('KIM JUNG SICK') NAME FROM dual ;
     NAME
-------------
  kim jung sick
 
SQL>SELECT UPPER('kim jung sick') NAME FROM dual ;
    NAME
--------------
KIM JUNG SICK

 
LPAD(char1, n [,char2])
왼쪽에 문자열을 끼어 놓는 역할을 합니다. n은 반환되는 문자열의 전체 길이를 나타내며, char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 반환합니다.
SQL>SELECT LPAD('JUNG-SICK', 10, '*') NAME FROM dual ;
   NAME
------------
 *JUNG-SICK

 
RPAD(char1, n [,char2])
LPAD와 반대로 오른쪽에 문자열을 끼어 놓는 역할을 합니다.
SQL>SELECT RPAD('JUNG-SICK', 10, '*') NAME FROM dual ;
    NAME
------------
  JUNG-SICK*

 
SUBSTR(char, m ,[n])
SUBSTR함수를 이용하여 m 번째 자리부터 길이가 n개인 문자열을 반환한 합니다. m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환합니다.
 SQL>SELECT SUBSTR('JUNG-SICK', 3, 3) NAME FROM dual ;
          NAME
-----------
        NG-
 
-- 뒤에서부터 자를
SQL>SELECT SUBSTR('JUNG-SICK', -3, 3) NAME FROM dual ;
        NAME 
-----------
        ICK

 
LENGTH(char1)
문자열의 길이를 리턴 합니다.
SQL>SELECT LENGTH('JUNG-SICK') TEST FROM dual ;
      TEST
   ----------
           9

 
REPLACE(char1, str1, str2)
REPLACE는 문자열의 특정 문자를 다른 문자로 변환 합니다.
SQL> SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;
Changes
--------------
BLACK and BLUE
 
SQL> SELECT REPLACE('JACK and JUE','JA','BL') "Changes" FROM DUAL
Changes
------------
BLCK and JUE
 
-- 대소문자를 구분한다는 것을 알수 있습니다.
SQL>SELECT REPLACE('JACK and JUE','j','BL') "Changes" FROM DUAL
Changes
------------
JACK and JUE

 
INSTR
문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환합니다. 지정한 문자열이 발견되지 않으면 0이 반환 됩니다.
 -- 지정한 문자 OK가 발견되지 않아서 0이 반환 됩니다.
SQL>SELECT INSTR('CORPORATE FLOOR','OK')  "Instring" FROM DUAL
  Instring
----------
         0
 
-- OR이 있는 위치 2를 반환 합니다. 왼쪽부터 비교를 한다는 것을 알 수 있습니다.
SQL>SELECT INSTR('CORPORATE FLOOR','OR')  "Instring" FROM DUAL
  Instring
----------
         2
 
-- 왼쪽에서 3번째부터 시작을 해서 비교를 합니다. 2번째 OR의 위치가 반환 됩니다.
SQL>SELECT INSTR('CORPORATE FLOOR','OR', 3)  "Instring" FROM DUAL
  Instring
----------
         5
 
-- 왼쪽에서 3번째부터 시작을 해서 비교를 하는데  OR이 두 번째 검색되는 지점의 위치를 반환 합니다.
SQL> SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)  "Instring" FROM DUAL;
  Instring
----------
       14

 
TRIM
특정한 문자를 제거 합니다. 
제거할 문자를 입력하지 않으면 기본적으로 공백이 제거 됩니다.
리턴값의 데이터타입은 VARCHAR2 입니다.
 -- 0을 제거 합니다.
SQL>SELECT TRIM(0 FROM 0009872348900)  "TRIM Example" FROM DUAL;
 TRIM Example
------------
98723489
 
-- 어떤 문자도 입력하지 않으면 기본적으로 공백이 제거 됩니다. 
-- TRIM을 사용한 위에 예제와 사용하지 않은 아래 예제의 결과 값이 다르게 나오는 것을 알 수 있습니다.
SQL>SELECT NVL(TRIM ('  '),'공백')  "TRIM Example"  FROM DUAL
TRIM Example
------------
공백
 
SQL>SELECT NVL('  ','공백')  "TRIM Example" FROM DUAL
TRIM Example
------------
 

 
LTRIM
SQL>SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"  FROM DUAL;
LTRIM example
------------
XxyLAST WORD

 
RTRIM
SQL>SELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM example"     FROM DUAL;
RTRIM examp
-----------
BROWNINGyxX

 
 
3.      날짜 관련함수
 
LAST_DAY(d)
LAST_DAY함수는 달의 마지막 날의 날짜를 반환합니다
 SQL>SELECT SYSDATE TODAY, LAST_DAY(SYSDATE) LASTDAY FROM dual ;
         TODAY             LASTDAY
-----------------------------
05-JUN-2000       30-JUN-2000        

ADD_MONTHS(a, b)
ADD_MONTHS 함수는 a의 날짜에 b의 달을 더한 값을 반환 합니다.
 SQL>SELECT TO_CAHR(ADD_MONTHS(SYSDATE,3),'RRRR/MM/DD' LASTDAY)  "date"
        FROM dual ;
      date
------------
  2000/09/05 

MONTH_BETWEEN(a1, a2)
MONTH_BETWEEN은 a1과 a2 사이의 달의 수를 NUMBER형 타입으로 반환 합니다.
 SQL>SELECT MONTHS_BETWEEN(TO_DATE('2000/06/05') , TO_DATE('2000/09/23'))  "Date" FROM dual ;
            Date
  ----------------
         -3.880635 

ROUND(d[,F])
ROUND 함수는 F에 지정된 단위로 반올림 합니다, F가 연도라면 연도 단위로 반올림 합니다.
 SQL>SELECT ROUND(TO_DATE('1998/09/11'), 'YEAR')  FROM dual ;
    ROUND(TO_
--------------
       99-01-01
 
SQL>SELECT ROUND(TO_DATE('1998/04/11'), 'MONTH') FROM dual ;
    ROUND(TO_
---------------
       98-04-01
 
SQL>SELECT ROUND(TO_DATE('1998/04/11'), 'DAY')  FROM dual ;
    ROUND(TO_
 ---------------
       98-04-11

날짜에 대한 산술 연산
연  산 결과치 사  용  목  적

날짜 + 숫자 날짜   특정한 날로부터 며칠 후의 날짜 계산
ex)select to_date('20060228','YYYYMMDD')+1 from dual
-----------------------------------------------------
2006-03-01

날짜 - 숫자 날짜   특정한 날로부터 며칠 전의 날짜 계산
ex)select to_date('20060228','YYYYMMDD')-1 from dual
------------------------------------------------
2006-02-27

날짜 - 날짜 숫자   두 날짜 사이의 차이를 숫자로 계산
ex)select to_date('20060430')-to_date('20060420') from Dual
---------------------------------------------------
10
 
 
4.      변환 함수
 
TO_CHAR
TO_CHAR함수는 DATE형, NUMBER형을 VARCHAR2형으로 바꺼 줍니다.
 SQL>SELECT TO_CHAR(SYSDATE, 'MONTH') CHARTEST FROM dual ;
       CHARTEST
 --------------
             JUNE
 
SQL>SELECT TO_CHAR(SYSDATE) CHARTEST FROM dual ;
       CHARTEST
 --------------
          00/06/10

TO_DATE
TO_DATE함수는 CHAR, VARCHAR2형을 DATE 타입으로 변환합니다.
 SQL>SELECT TO_DATE('2000/06/16','RRRR/MM/DD') FROM dual ;
       TO_DATE
   ------------ 
      2000/06/16 

TO_NUMBER
TO_NUMBER함수는 CHAR, VARCHAR2의 데이터 타입을 숫자형식으로 변환합니다.
 SQL>SELECT TO_NUMBER('1210616') FROM dual ;
    TO_NUMBER
    --------------
             1210616

5.      기타함수
 
NVL
NVL 함수는 NULL값을 다른 값으로 바꿀 때 쓰입니다.
모든 데이터 타입에 적용 가능합니다.
전환되는 값의 데이터 타입을 일치시켜야 합니다.
 SQL>SELECT empno, NVL(comm, 0) FROM  emp WHERE deptno = 30;
 
         EMPNO  NVL(COMM,0)
       ----------     -----------
          7499              300
          7521              500
          7654             1400
          7698                0
          7844                0
          7900                0

DECODE
DECODE 함수는 데이터 들을 다른 값으로 바꾸어 줍니다.
형식 DECODE(VALUE, IF1, THEN1, IF2, THEN2...)
VALUE 값이 IF1일경우에 THEN1값으로 바꾸어 주고 VALUE값이 IF2일경우에는 THEN2값으로 바꾸어 줍니다.
 SQL> SELECT deptno,
                    DECODE(deptno, 10 , 'ACCOUNTING' ,
                                             20 , 'RESEARCH' ,
                                             30 , 'SALES' ,
                                             40 , 'OPERATIONS')
          FROM emp ;
 
     DEPTNO  DECODE(DEP
     ---------- ----------
        20        RESEARCH
        30        SALES
        30        SALES
        20        RESEARCH
        30        SALES
        30        SALES
        10        ACCOUNTING
        20        RESEARCH

DUMP
DUMP는 바이트 크기와 해당 데이터 타입 코드를 반환합니다..
SQL>SELECT ename, DUMP(ename, 16) "16진수"
        FROM emp
        WHERE ename = 'ALLEN'
 
ename   16진수
------  ------------------------------
ALLEN   Typ=1 Len=5: 41,4c,4c,45,4e

GREATEST
GREATEST함수는 검색값 중에서 가장 큰 값을 반환 합니다.
 SQL>SELECT GREATEST(10, 100, 5, -7) FROM DUAL;
GREATEST(10,100,5,-7)
---------------------
                  100

LEAST
LEAST함수는 GREATEST함수와 반대로 가장 작은 값을 반환합니다.
SQL>SELECT LEAST(10, 100, 5, -7) FROM DUAL; 
LEAST(10,100,5,-7)
------------------
                -7

UID
현재 사용자의 유일한 ID번호를 리턴합니다.
 
USER
현재 오라클을 사용하는 사용자를 VARCHAR2형식으로 리턴합니다.
 SQL> SELECT USER, UID FROM DUAL;
USER          UID
------------- ------
SCOTT         32

USERENV
USERENV 함수는 현재 세션의 환경 정보를 반환합니다.
  - ENTRYID : 사용 가능한 Auditing entry Identifier를 반환합니다.
  - LABEL : 현재 세션의 Label을 반환합니다.
  - LANGUAGE : 현재 세션에서 사용중인 언어와 테리토리 값을 반환합니다.
  - SESSIONID : Auditing(감사) Session ID를 반환 합니다.
  - TERMINAL : 현재 세션 터미널의 OS ID를 반환 합니다. 
SQL> SELECT USERENV('LANGUAGE') FROM DUAL;
USERENV('LANGUAGE')
------------------------
KOREAN_KOREA.KO16KSC5601

VSIZE
해당 문자의 BYTE수를 반환 합니다. 해당 문자가 NULL이면 NULL값이 반환 됩니다.
 SQL> SELECT VSIZE(ename), ename
          FROM emp
          WHERE deptno = 30;
 
VSIZE(ENAME) ENAME
------------ ----------
           5            ALLEN
           4            WARD
           6            MARTIN
           5            BLAKE
 
sign(x)

x값이 0보다 작으면 -1, 0이면 0, 0보다 크면 1을 반환하는 함수.
결과값이 음수인지 양수인지를 판단할때 사용이 가능하다.

select sign(qty)
from table
 
Case-When 구문
case when (b.ipAmt/aa.lastSaleTotalAmt)*100 = d.iprate                        " +
  then 1                          " +
  else d.payRate                        " +  
  end as JangRate
http://blog.naver.com/mura5802/20019026007

달 더하기
TO_Char(ADD_MONTHS(to_date('200604','YYYYMM'),-1),'YYYYMM')||'01'

마지막날 구하기
to_char(last_day(to_date('200604','YYYYMM')),'YYYYMMDD')

특정문자 뒤에서부터 추출
substr(?,INSTR(?,'-',1,2)+1)

숫자를 문자열 포맷에 맞게 문자로 변환
EX) select TO_CHAR(1234,'00099999') from dual
EX) select TO_CHAR(1234,'999,999,999,999') from dual