오라클 함수

1. 단일행 함수

 

1-1. 문자 함수

* LOWER  : 소문자로
* UPPER  : 대문자로
* SUBSTR : 문자열 일부 반환
* LENGTH : 문자열 길이 반환

* INSTR  : 문자 위치 반환

 

1-2. 숫자 함수

* ROUND : 반올림
* TRUNC : 버림
* MOD   : 나머지

 

1-3. 날짜 함수

* MONTHS_BETWEEN  : 두 날짜 간의 달 수 (정수가 아닐 수 있음) 
   ex) SELECT MONTHS_BETWEEN('20140102','20140202') FROM DUAL;
* ADD_MONTHS : 날짜에 달 수 더하기 (N값은 정수여야하며 음수일 수 있음)
   ex) SELECT ADD_MONTHS('20140101',2) FROM DUAL;
* NEXT_DAY : 지정한 날짜의 다음 날 (지정한 요일에 해당하는 날짜를 찾음)
   ex) SELECT NEXT_DAY('20140916','금') FROM DUAL; (윈도우가 한글이면 한글 요일, 영문이면 영문요일 넣어야됨)
* LAST_DAY : 해당 달의 마지막 날
   ex) SELECT LAST_DAY('20140901') FROM DUAL;

 

1-4. 데이터 타입 변환 함수

     (데이터 타입 변환을 암시적 변환으로 오라클에게 맡길 경우, SQL 성능 저하의 원인이 되므로 위와 같은 명시적 변환을 해줘야 함)

* TO_CHAR  :  문자타입 변환 함수

사용 1 > TO_CHAR(number , 'fmt')

    ex) SELECT TO_CHAR(28000000,'\99,999') FROM DUAL;

fmt 요소	설명	예제	결과
9	숫자 위치입니다. (9의 개수가 표시 폭(width)을 결정합니다.)	99999	1234
0	선행 제로를 표시합니다.	099999	001234
$	부동 달러 기호입니다.	$999999	$1234
.	지정된 위치의 소수점 입니다.	999999.99	1234.00
,	지정된 위치의 쉼표 입니다.	999,999	1,234
l	지역 통화 기호를 사용합니다.	L999999	FF1234

( fmt자리에 최대 자리수를 고려해야함. fmt 갯수가 더 적을 경우 ##### 으로 출력됨 )

 

사용 2> TO_CHAR(date, 'fmt')

    ex) SELECT TO_CHAR(SYSDATE,'YY/Mon/dd') FROM DUAL;

         SELECT TO_CHAR(SYSDATE,'fmYY/Mon/dd') FROM DUAL;  --선행 0을 제거

 

fmt 요소	설명
YYYY	네 자리 연도(숫자)
YEAR	년도(문자)
MM	두 자리로 나타낸 달 (숫자)
MON	3자리 달의 약어(문자)
DY	3자리 약어의 요일 이름(문자)
DD	달의 일(숫자)
AM  / PM	오전/오후 표시자
AM. / PM.	마침표가 포함된 오전/오후 표시자
HH / HH12 / HH24	하루 중의 시 또는 1-12 또는 0-23으로 표시되는 시
MI	분(0-59)
SS	초(0-59)

 

* TO_NUMBER (char, 'fmt') : 숫자 변환 함수.  fmt 와 char 자리수가 동일해야함.

* TO_DATE(char,'fmt') : 날짜 변환 함수. (char 와 fmt가 공백 , 대소문자 , 자리수 모두 일치해야함)

   ex) SELECT TO_DATE('JUN 24,1999','MON DD, YYYY') FROM DUAL;

 

1-5. 함수의 중첩

* 단일행 함수는 여러 번 중첩될 수 있음(nested)

* 계산 순서는 안에서 바깥쪽으로 계산됨.

 

1-6. 기타 함수

          * NVL : null 값을 실제 값으로 반환함.

                     ex) NVL(expr1, expr2)

                     사용되는 데이터 타입은 날짜, 문자, 숫자로 서로 일치해야함.

* NVL2 : 첫번째 표현식이 null이 아닌경우, 두번째 표현식을, null인경우 세번째 표현식을 반환 함

           ex)NVL2(expr1, expr2, expr3)

* NULLIF : 두 표현식을 비교하여 동일한 경우 null 을 반환하고, 동일하지 않은 경우 첫번째 표현식을 반환 함

           첫번째 표현식에 null 리터럴을 지정할 수 없음

           ex) NULLIF(expr1, expr2)

* COALESCE : 목록에서 null이 아닌 첫번째 표현식을 반환 함

           ex) COALESCE(expr1, expr2, .... exprn)

* DECODE : IF-THEN-ELSE 의 논리를 사용하여 조건부 조회를 쉽게 수행할 수 있음 - 오라클만 가능

           ex) DECODE(expr, search1, result1, search2, result2, deault)

* CASE : DECODE 와 같은 논리. ANSI 표준으로 오라클 외의 DB에서도 사용 가능함

           ex) CASE expr WHEN comparison_expr1 THEN return_expr1

                   ELSE else_expr END;

 

2. 그룹 함수

          1-1. 그룹 함수의 종류

    그룹 함수는 행 집합에 작용하여 그룹 당 하나의 결과를 생성함

   

함수	설명
AVG([DISTINCT|ALL]n)	n의 평균 값이며 널 값은 무시합니다.
COUNT({*|DISTINCT|ALL]expr})	expr이 널이 아닌 행의 수입니다. *를 사용하면 널이 있는 행을 포함하여 선택한 모든 행을 셉니다.
MAX([DISTINCT|ALL]expr)	expr의 최대값이며 널 값은 무시합니다.
MIN([DISTINCT|ALL]expr)	expr의 최소값이며 널 값은 무시합니다.
SUM([DISTINCT|ALL]n)	n의 합계이며 널 값은 무시합니다.

   * AVG, SUM 은 number 타입의 column에만 사용 나머진 모두 사용 가능.

         * COUNT

            ex) COUNT(*) :  모든 행 개수를 반환

                 COUNT(column) : 널이 아닌 값의 개수를 반환

                 COUNT(DISTINCT column) : 널이 아닌 값중에서 유일한 값의 개수를 반환

         * GROUP BY : 행 그룹화 기준을 결정하는 값을 가진 컬럼을 지정

            ex) SELECT department_id, AVG(salary)

                     FROM employees

                    GROUP BY department_id

                    ORDER BY AVG(salary);

     

         1-2. 주의점

              * SELECT 목록의 컬럼 또는 표현식 중 그룹 함수가 아닌 것은 GROUP BY 절에 포함시켜야 함

              * WHERE 절을 사용하여 그룸을 제한할 수 없음

                틀린예) SELECT department_id, AVG(salary)

                              FROM employees

                            WHERE AVG(salary) > 8000

                            GROUP BY department_id;

                맞는예) SELECT department_id, AVG(salary)

                              FROM employees 

                            HAVING AVG(salary) > 8000

                             GROUP BY department_id;

 

         1-3. 그룹 함수의 중첩(nesting)

             ex) SELECT MAX(COUNT(employee_id))

                      FROM employees

                    GROUP BY department_id;