일반 오라클 함수 정리

★. 숫자 처리함수                                                                                                                   

1. ROUND(45.926, 2) --> 45.93 : 소수점 두자리수까지 보여주면서 반올림한다. 

2. TRUNC(45.926, 2) --> 45.92 : 소수점 두자리까지만 보여주고 나머지는 버린다. 

3. MOD(1600,300) --> 100 : 1600을 300으로 나누고 나머지를 리턴한다.

* ROUND예제(WHOLE NUMBER:정수)

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL

==> 45.92 46 50

* TRUNC예제

SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) FROM DUAL

==> 45.92 45(n이 생략된면 일의 자리까지 남기고 버린다.) 40

* SYSTEM 날짜를 갖고 오는 방법.

SELECT sysdate FROM dual

★.  Date                                                                                                

Date 계산( 날짜를 숫자로 저장)
date + number : date에 number만큼 후의 날자를 보여준다.

date - number : date에 number만큼 전의 날자를 보여준다.

date1 - date2 : date1에서 date2 까지의 총 일수를 보여준다.( date1+date2는 X )

date1 + 숫자/24 : date1에서 시간을 더해 날짜를 보여준다.

Date Functions

MONTHS_BETWEEN('01-SEP-95','11-JAN-94') --> 19.6774194

; 두날짜 사이의 달수를 보여준다.

ADD_MONTHS('11-JAN-94', 6) --> 11-JUL-94

; 날짜에 6개월을 더한 날자를 보여준다.

NEXT_DAY('01-SEP-95','FRIDAY') --> '08-SEP-95'

; 해당일 다음에 오는 FRIDAY의 일자를 보여준다.

('SUNDAY'는 1, 'MONDAY'는 2...이런식으로 숫자를 써줘도 된다.)

LAST_DAY('01-SEP-95') --> '30-SEP-95'

; 해당월의 마지막날자를 보여준다.

ROUND('25-JUL-95','MONTH')--> 01-AUG-95

ROUND('25-JUL-95','YEAR')--> 01-JAN-96

TRUNC('25-JUL-95','MONTH') --> 01-JUL-95

TRUNC('25-JUL-95','YEAR') --> 01-JAN-95

★.  Conversion Functions                                                                                                          

nlsparams : 십진수, 그룹구분자, 지역 통화 기호, 국제 통화 기호

TO_CHAR(date,['format'],[nlsparams]) : date를 format에 맞게 문자열로 변환한다.

- Date Format Elements

YYYY --> 1999 (년출력) , YEAR --> nineteen ninety-nine (년출력)

MM --> 12 (월출력) , MONTH --> DECEMBER (월출력), MON --> DEC

D --> 요일을 숫자로 리턴한다.(일요일은 1, 월요일은 2...)

DD --> 07 (달의 일출력)

DDD --> 200 (연의 일출력)그 해의 총 몇 일째인가를 리턴한다.

DAY --> MONDAY (요일출력) DY-->MON

CC --> 20 (몇 세기인지를 보여준다.)

WW --> 그 해의 몇 번째 주인가를 리턴한다.

W --> 그 달의 몇 번째 주인가를 리턴한다.



* Element들을 소문자로 쓰면 소문자로 나오고 대문자로 쓰면 대문자로 출력된다.

HH or HH12 or HH24 / MI(0-59분) / SS(0-59초)



* 문자열은 " " 묶어 추가한다 DD " of " MONTH --> 12 of DECEMBER

*숫자 접미어는 숫자를 문자로 표기. TH(4->4TH)/ SP(4->FOUR)/ SPTH or THSP(4->FOURTH)

ddspth : 14-> fothteenth

* / . , : 구두점은 결과에 그대로 출력한다. * 공백, 선행제로를 제거하는 fm요소가 있다.

TO_CHAR(number,'format',[nlsparams]) : number를 format에 맞게 문자열로 변환한다.

- Number Format Elements

9 : 999,999 --> 1,234 $: 부동 달러 기호 $99999 -> $1234

0 : 099999 --> 001234 99.999EEEE -> 1.234E+03 B: 0값을 공백으로

L : L99,999 --> FF1,234 (NLS_CURRENCY에 설정되어있는 값이 L로 보여진다.)



TO_NUMBER(char,['format'],[nlsparams]) : 숫자형태의 문자열을 숫자로 변한한다.

TO_DATE(char,['format'],[nlsparams]):날자형태의 문자열을 format에 맞게 날자형식으로 변환 한다.



★. NVL Funcion : 값이 null일 때 설정값을 보여준다.                                                                                        

NVL(number_column, 0) : null일 때 0을 보여준다.

NVL(date_column, '01-JAN-95') : null일 때 '01-JAN-95'를 보여준다.

NVL(character_column, 'Empty') : null일 때 'Empty'를 보여준다.

* column Type과 표현식의 type이 반드시 일치해야 한다.



★. DECODE Function : CASE or IF-THEN-ELSE 형식과 비슷하다.                                                       

*DECODE(col/expression, search1, result1 [,search2,result2,…] [,default])

F1 (F2 (F3 (col,arg1),arg2),arg3)

-------------------------------------------------------------------------------------------------------

예제

-------------------------------------------------------------------------------------------------------

/** 함수 - Number Function **/

--무조건 올림
SELECT CEIL(13.11) FROM DUAL;
SELECT CEIL(13.001) FROM DUAL;

--나머지 구함
SELECT MOD(23, 5) FROM DUAL;
SELECT MOD(57, 145) FROM DUAL;

-- 제곱승
SELECT POWER(3, 2), POWER(3, -2) FROM DUAL;
SELECT POWER(2, 10) FROM DUAL;

--자릿수 지정
SELECT ROUND(345.123, 0) FROM DUAL;
SELECT ROUND(345.123, 2), ROUND(345.123, -1) FROM DUAL;

--지정된 자리까지 잘라내기
SELECT TRUNC(345.123, 1), TRUNC(345.123, 0), TRUNC(345.123, -1) FROM DUAL;
SELECT TRUNC( 345.123 + 0.09, 1 ) FROM DUAL;
SELECT TRUNC( 345.123 + 0.9, 0 ) FROM DUAL;
SELECT TRUNC( 345.123 + 9, -1 ) FROM DUAL;

SELECT SIGN(5.989), SIGN(0), SIGN(-999.098) FROM DUAL;

/** 함수 - Character Function **/

--특정Character의 아스키값 구하기
SELECT CHR(65) "CHR", ASCII('A') "ASCII" FROM DUAL;

SELECT LOWER('My name is LKM') "LOWER", UPPER('My name is LKM') "UPPER" FROM DUAL;

SELECT LPAD('LKM', 10, '*') "LPAD", RPAD('LKM', 10, '*') "RPAD" FROM DUAL;

SELECT LPAD('1234567890', 20, '+') || RPAD('1234567890', 20, '^') "12345678901234567890"
  FROM DUAL;

SELECT LPAD('1,234,567', 30, ' ') "LPAD사용으로 30자리 맞춤",
       '1,234,567'                "단순문자 사용",
       1234567                    "단순숫자 사용"
  FROM DUAL;

SELECT LTRIM('     AAA     ') "LTRIM", RTRIM('     AAA     ') "RTRIM" FROM DUAL;

SELECT REPLACE('ORACLE', 'A', 'BBB') "REPLACE" FROM DUAL;

SELECT EMP_NAME, REPLACE(EMP_NAME, '이', '박') "이->박"
  FROM PERSONNEL
 WHERE EMP_NAME LIKE '이%';

SELECT SUBSTR('ORACLE PROJECT', 1, 3) SUBSTR1,
             SUBSTR('ORACLE PROJECT', 4, 5) SUBSTR2,
             SUBSTR('ORACLE PROJECT', 10)   SUBSTR3 FROM DUAL ;

SELECT LENGTH ('ORACLE PROJECT') "LENGTH",
             LENGTHB('ORACLE PROJECT') "LENGTHB",
  FROM DUAL;

SELECT INSTR ('ORACLE PROJECT', 'R', 1, 1)  INSTR1,
             INSTR ('ORACLE PROJECT', 'R', 1, 2)  INSTR2,
             INSTR ('ORACLE PROJECT', 'R', 1, 3)  INSTR3
  FROM DUAL;

SELECT HOBBY, 
       INSTR (HOBBY, ')', 1, 1) INSTR,
       INSTRB (HOBBY, ')', 1, 1) INSTRB
  FROM PERSONNEL
 WHERE EMPNO > '98102';

/** 함수 - Date Function **/

SELECT SYSDATE FROM DUAL;

SELECT LAST_DAY(SYSDATE) FROM DUAL;

SELECT MONTHS_BETWEEN( '2002/01/13', '2002/05/13' ) "MONTHS_BETWEEN (-)",
       MONTHS_BETWEEN( '2002/01/13', '2001/11/13' ) "MONTHS_BETWEEN (+)"
  FROM DUAL ;

SELECT ADD_MONTHS(SYSDATE,  1) "ADD_MONTHS (+)",
      ADD_MONTHS(SYSDATE, -1) "ADD_MONTHS (-)"
  FROM DUAL ;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT ADD_MONTHS( '2002/02/28', 12 ) "1년후",
             ADD_MONTHS( '2002/02/28', 24 ) "2년후",
             ADD_MONTHS( '2002/02/28', 36 ) "3년후"
  FROM DUAL ;

SELECT SYSDATE,
            NEXT_DAY(SYSDATE, '일요일') "NEXT_DAY 1",
            NEXT_DAY(SYSDATE, 1       ) "NEXT_DAY 2"
  FROM DUAL;

SELECT SYSDATE, 
       NEXT_DAY(SYSDATE, '수요일') "NEXT_DAY 1",
       NEXT_DAY(SYSDATE, 4       ) "NEXT_DAY 2"
  FROM DUAL;

/** 함수 - Conversion Function **/

SELECT TO_CHAR(1234567.891)                    "TO_CHAR1",
             TO_CHAR(1234567.891, '999')             "TO_CHAR2",
             TO_CHAR(1234567.891, '9,999,999')       "TO_CHAR3",
             TO_CHAR(1234567.891, '0.0000')          "TO_CHAR5",
             TO_CHAR(1234567.891, '9,999,999.0000')  "TO_CHAR6",
             TO_CHAR(123, '9,999.00')                "TO_CHAR7",
             TO_CHAR(123, '9,999.99')                "TO_CHAR8"
  FROM DUAL ;

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') "TO_CHAR1",
             TO_CHAR(SYSDATE, 'YYYY/MM')    "TO_CHAR2",
             TO_CHAR(SYSDATE, 'YYYY')       "TO_CHAR3",
             TO_CHAR(SYSDATE, 'DD')         "TO_CHAR4",
             TO_CHAR(SYSDATE, 'DAY')        "TO_CHAR5",
             TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')                  "TO_CHAR6",
             TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYY-MM-DD')      "TO_CHAR7",
             TO_CHAR(TO_DATE('20020101','YYYYMMDD'), 'YYYYMMDD HHMISS') "TO_CHAR8"
  FROM DUAL ;

SELECT TO_NUMBER('123456.9') "TO_NUMBER1",
             TO_NUMBER('1234567')  "TO_NUMBER2"
  FROM DUAL ;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TO_DATE('20020824')                 "TO_DATE1",
             TO_DATE('2002-08-24')              "TO_DATE2",
             TO_DATE('200208')                    "TO_DATE3"
  FROM DUAL ;

SELECT TO_DATE('2002/08/24 08:14:06', 'YYYY/MM/DD HH24:MI:SS')       "TO_DATE1",
             TO_DATE('2002/08/24 08:14:06 오후', 'YYYY/MM/DD HH:MI:SS AM') "TO_DATE2"
  FROM DUAL ;

/** 함수 - Group Function **/

SELECT AVG(HEIGHT), AVG(WEIGHT) FROM PERSONNEL;

SELECT MAX(EMPNO), MAX(EMP_NAME), MIN(EMPNO), MIN(EMP_NAME) FROM PERSONNEL;

SELECT SUM(WEIGHT) FROM PERSONNEL;

SELECT COUNT(*), COUNT(EMPNO), COUNT(JIKCH_CODE) FROM PERSONNEL;

/** 함수 - ETC **/

SELECT EMPNO, EMP_NAME, HOBBY 취미, WELL 특기
  FROM PERSONNEL
 WHERE EMPNO BETWEEN '98001' AND '98005';

SELECT DECODE( '나', '나', '맞다', '아니다' ) "나가 나면 맞다, 아니면 아니다"
  FROM DUAL ;

SELECT GREATEST (132, 33, 45, 90, 60.77) GREATEST,
              LEAST    (132, 33, 45, 90, 60.77) LEAST
  FROM DUAL;

SELECT USERENV('LANGUAGE')  "LANGUAGE",
             USERENV('TERMINAL')  "TERMINAL",
             USERENV('SESSIONID') "SESSIONID"
  FROM DUAL;
 
SELECT UID, USER FROM DUAL;