오라클 SQL FUNCTION 정리
-작성: 오수영-
http://blog.naver.com/neptuneosy
1. TRANSLATE('스트링','비교문자','바꿀문자')
=> 문자열을 치환하여줌 .. 잘못 들어간 데이터 들어갔을때 찾으면 유용
예) SELECT TRANSLATE('OSY12', '123OSYKZ', '123XXXXX') FROM DUAL
결과 =>XXX12
2. CONCAT ('문자열','문자열2)
=> 문자열을 합쳐주는 함수 || 연산자와 같은 기능을 한다.
예) SELECT CONCAT ('오','수영') "이름" FROM DUAL
결과 =>오수영
3. INITCAP ('문자열')
=> 문자열의 첫문자를 대문자로 나머지는 소문자로 변환 해주는 함수
예) INITCAP ('oracle JABA')
결과 => Oracle Java
3. INSTR ('문자열','비교문자열')
=> 문자열에서 비교문자열이 위치하는 첫번째 자리수의 위치를 반환하는 함수
예1) INSTR ('안녕ABCDERFD','ABC')
=> 결과 => 3
예2)instr('안녕TATATA','T',4,1)
해설: 4번째 문자부터 검색하여 ‘T’가 첫번째 나타나는 위치
=> 결과 5
예3)instr('안녕TATATA','T',4,2)
해설: 4번째 문자부터 검색하여 ‘T’가 두번째 나타나는 위치
=> 결과 7
4. INSTRB ('문자열','비교문자열')
=> 문자열에서 비교문자열이 위치하는 첫번째 자리수의 위치를 반환하는 함수
INSTR가 틀린점은 바이트를 단위로 계산하기때문에 한글은 2바이트로 계산
예) INSTRB('안녕ABCDERFD','ABC')
=> 결과 => 5
5. LENGTH ('문자열')
=> 문자열의 총 길이를 구해준다.
예) length('osy79')
=> 결과 => 5
LENGTHB 는 역시 바이트 단위 이기 때문에 한글은 2바이트 처리 한다는것 빼곤 같다
6. LOWER('문자열)
=> 문자열을 소문자로 바꿔준다.
예) lower('COmpOsiTion')
=> 결과 => composition
7. LPAD(c1,n,c2)
=>전체길이가 n이며 c1의 왼쪽을 c2로 채운다. c2가 생략되면 공백으로 채운다.
예) lpad(905,5,'*')
=>결과 => **905
8. LTRIM('문자열','지울문자')
=> 문자열의 왼쪽에서 부터 지울문자가 안닐때까지 제거를 한다. 만약 지울문자 인수가
없다면 공백을 제거한다.
예) ltrim('AAAAOracleJava','A')
=> 결과 => OracleJava
9. REPLACE(c,a,b)
=> 인수 c의 문자중 a를 b로 바꿈
예) replace('myJava','my','Oracle')
=> 결과 => OracleJava
10. RPAD(c1,n,c2)
=> 전체의 길이가 n이며 c1의 오른쪽을 c2로 채운다. c2가 생략되었다면 공백으로 체운다.
예) rpad('905',5,'*')
=> 결과 => 905**
11. RTRIM(c,[set])
=> 인수의 c 오른쪽부터 set에 지정된 문자가 아닐때까지 문자를 제거 한다. set이 지정되어
있지 않으면 공백을 제거한다.
예) rtrim('JCLEEAAA','A')
=> 결과 => JCLEE
12. SUBSTR(c, m, [n])
=> 인수 C의 m번째부터 n개의 문자를 잘라낸다. n이 생략되면 c의 끝까지 잘라낸다.
예) substr('I LOVE KOREA',3,4)
=> 결과 => LOVE
예) substr('I LOVE KOREA',3)
=> 결과 => LOVE KOREA
13. TRANSLATE(c, from, to) : 인수 c의 문자열에서 form에 해당하는 것 들을 to에 해당하는것
으로 바꾼다.
예) translate('ABCDE','ABCDEFG','0123456')
=> 결과 => 01234
14. UPPER(c)
=> 인수 c의 문자열을 대문자로 바꾼다.
예)upper('abcde') => ABCDE
집합함수
15. AVG (컬럼명) 널을 제외한 평균값을 반환한다.
널까지 포함 시키려면
select avg(nvl(sal,0)) form sawon 이렇게 해주면 된다.
16. count (컬럼명) 널은 제외하고 추출된 행수를 반환한다.
count (distinct 컬럼명 ) 널 제외 중복 제외한 추출된 행수를 반환한다.
count (*) 널까지 포함하고 중복도 포함한 모든 행수를 반환한다.
17. MAX(컬럼명) : 최대값을 반환한다.
18 MIN(컬럼명) : 최소값을 반환한다.
* MAX, MIN함수는 숫자, 문자, 날짜 등 어떠한 자료형이라도 사용이 가능하다.
19. SUM(컬럼명) : 합계를 반환한다.
20. ROLLUP(컬럼명) :group by절과 같이 사용되며 주어진 구룹핑 조건에 따라 각 구룹핑
항목이 있으면 우측부터 하나씩 제외 하면서 그 결과를 반환한다.
예) select deptno, sum(sal)
from emp
group by rollup(dept_no);
21. CUBE(컬럼명) : rollup 연산자가 수행한 결과에 부가적으로 그룹핑 조건이 가능한
모든 조합에 대한 결과를 출력한다.
예) select deptno, sum(sal)
from emp
group by cube(dept_no);
22. GROUPING(컬럼명) : 이 함수는 기술된 컬럼이 ROLLUP이나 CUBE 연산시 사용되었는지를
확인하게 해주는 함수로서 별다른 기능은 없으며 ROLLUP이나 CUBE 사용시
사용자의 이해를 돕기 위한 함수이다. 예를 여 출력된 값 중 NULL이 포함되어
있다면 이 값이 원래 DB에서 NULL인지 CUBE, ROLLUP을 통해 NULL인 된 것인지를
판단 할 수 있다. ROLLUP/CUBE를 통해 NULL이 생기면 ‘1’이라고 표시 한다.
예) select deptno, job, sum(sal), grouping(deptno), grouping(job)
from emp
group by cube(deptno, job);
23. GROUPING SETS((구룹핑1 컬럼들), (구룹핑2컬럼들))
예) SELECT deptno, job, NULL, sum(sal)
FROM emp
GROUP BY deptno, job
UNION ALL
SELECT NULL, job, mgr, sum(sal)
FROM emp
GROUP BY job, mgr;
를 이렇게 GROUPING SETS 구문으로 바꿀수 있음
SELECT deptno, job, mgr, sum(sal)
FROM emp
GROUP BY GROUPING SETS ((deptno, job), (job, mgr));
24. ABS(N) : 인수 N의 절대값을 반환
예) select abs(-12) from dual => 12 //-12의 절대값
25. CEIL(N) : 인수 N보다 크거나 같은 최소 정수를 반환
예) select ceil(12.7) from dual => 13
26. EXP(N) : e(2.718,,,)의 N승을 반환
27. FLOOR(N) : 인수 N보다 작거나 같은 최대 정수를 반환
예) select floor(12.7) from dual => 12
28. MOD(M,N) : 인수 M을 N으로 나눈 나머지를 반환
예) SELECT MOD(10,3) from dual =>1 //10/3의 나머지 값
29. POWER(M,N) : 인수 M의 N 지수승을 반환
예)select power (3,2) from dual => 9 //3의 제곱
30. ROUND(M,N) : 인수 M을 소수이하 N자리까지 표시하는데 반올림한다.
예) select round(12.567,1) from dual => 12.6
31. SIGN(M) : 인수 M의 부호를 리턴, 양수이면 1, 음수이면 -1, 0이면 0을 리턴한다.
예) select sign(-100), sign(0), sign(100) from dual => -1, 0, 1
32. TRUNC(M,N) : 인수 M을 소수이하 N 자리까지 표시 하는데 절삭 한다.
예) select trunc(12.567,1) from dual => 12.5
**************************************************************************************
DATE FUNCTION
**************************************************************************************
34. ADD_MONTHS(DATE, N) : DATE에 N달을 추가한다 . DATE값을 반환하며 N은 정수이다.
예) SELECT ADD_MONTHS(TO_DATE('20051211','YYYYMMDD'), 3) FROM DUAL
=> 2006-03-11
35. LAST_DAY(DATE) : DATE를 포함하는 달의 마지막 날을 반환, DATE값을 반환한다.
예)SELECT LAST_DAY(TO_DATE('20031001', 'YYYYMMDD')) FROM DUAL
=>2003-10-31
36. MONTHS_BETWEEN(DATE1, DATE2) : DATE1과 DATE2의 차이를 달 수로 표현, 정수를 반환하며
차이가 1개월 미만인 경우는 1보다 자근 소수를 반환
한다. 즉 (DATE1-DATE2)를 나타낸 것이라 생각하면 된다.
예)SELECT MONTHS_BETWEEN(TO_DATE('20040101','YYYYMMDD'),
TO_DATE('20050101','YYYYMMDD')) FROM DUAL
=> -12
37. NEXT_DAY(DATE, C) : 날짜 DATE를 포함해서 이후 나타나는 첫번째 C요일을 반환한다.
예)SELECT NEXT_DAY(TO_DATE('20031001','YYYYMMDD'), 'FRIDAY') FROM DUAL
=> 2003-10-03
38. ROUND(DATE, FMT) : DATE를 지정한 포맷 형식에 맞춰 표시하는데 반올림 한다.
예) select round(sysdate, 'MONTH'), round(sysdate, 'DAY') from dual;
=> 현재 날짜를 2004년2월17일 오후15시25분 정도라면 가정 하고 다음 예문을 이해 하자.
MONTH는 월의 첫날을 출력하는데 17일 이므로 월은 반올림 하면 다음 월인 3월1일이
출력된다. 또한 DAY의 경우 주의 첫요일을 출력하는데 2월17일은 화요일 이므로
반올림 대상이 아니다. 즉 주의 첫요일인 2월15일이 출력 되는 것이다.
39. SYSDATE : 해당 시스템의 현재 날짜 및 시간을 반환.
예) SELECT SYSDATE FROM DUAL => 현재 날짜와 시간 구함
40. SYSTIMESTAMP : 오라클 9I에서 추가 되었으며 SYSDATE와 마찬가지로 해당 시스템의 현재
날짜 및 시간을 반환한다. TIMESTAMP는 DATETIME의 확장이며 보다 정교한
시간을 나타낼수 있다.
예) SELECT SYSTIMESTAMP FROM DUAL => 현재 시간과 날짜.. 더 자세히...
41. TRUNC(DATE, FMT) : DATE를 지정한 포멧 형식에 맞춰 표시하는데 절삭 한다.
예) select trunc(sysdate, 'MONTH'), trunc(sysdate, 'DAY') from dual;
=> 아래는 trunc를 사용하는 예문이다. 위의 예문에서는 현재 날짜가 17일 이므로 반올림이
되었지만 절삭을 하면 월의 첫날은 2월1일이 된다.
[ROUND, TRUNC함수의 fmt 문자열]
YYYY , YYY , YY , Y , YEAR : 년도의 첫날(7월1일부터 반올림)
Q : 분기의 첫날(분기의 두번째 달 16일부터 반올림)
MONTH , MON, MM : 월의 첫날(16일부터 반올림)
W : 월의 첫날과 같은 요일
DDD, DD : 일(정오를 지나면 반올림)
DAY, DY, D : 주의 첫 요일(수요일 정오가 지나면 반올림)
HH, HH12, HH24 : 시단위