[오라클함수] 오라클 함수 정리

1. 산술 함수

ABS(n) : 절대값 |n| , 대입 값의 절대 값을 복귀시킨다

예) select abs(-100),abs(101) from dual;

결과 : 100, 101

 

MOD(m.n) : m을 n으로 나눈 나머지를 retirn 한다

SELECT MOD(12,3), MOD(13,4), MOD(13,5) FROM DUAL

 

floor(n) : n보다 작은 정수값들중에서 최대값

SELECT FLOOR(12.4), FLOOR(12.7), FLOOR(-12.4) FROM DUAL;

 

ceil(n) : n보다 큰 정수값들중에서 최소값

SELECT CEIL(12.4), CEIL(12.7), CEIL(-12.4) FROM DUAL;

 

greatest(m,n) : 두 수중에서 큰것

SELECT greatest(12,15), greatest(13,17), greatest(20,5) FROM DUAL;

 

least(m,n) : 두 수중에서 작은 것

SELECT least(12,15), least(13,17), least(20,5) FROM DUAL;

  

1. RPAD : 값이 지정 자릿수 이하일때 남는 자리수 만큼 지정 문자를 채운다. 오른쪽부터 채운다. 2. LPAD : 값이 지정 자릿수 이하일때 남는 자리수 만큼 지정 문자를 채운다. 왼쪽부터 채운다. ​

WITH IP AS (     SELECT '12' AA FROM DUAL     UNION ALL     SELECT '123' AA FROM DUAL     UNION ALL     SELECT '1234' AA FROM DUAL ) SELECT RPAD(AA,7,'*'), LPAD(AA,7,'*') FROM IP ;

TO_NUMBER('숫자형 문자열') : 문자를 숫자로 변환

SELECT TO_NUMBER('12442.7'), TO_NUMBER('12.7'), TO_NUMBER('14.5') FROM DUAL;

round(n,[m]) n값의 반올림을 하는 함수로 m은 소숫점 아래 자리수 만큼 구한다.

SELECT ROUND(12.3456,0), ROUND(12.3456,1), ROUND(12.3456,2), ROUND(12.3456,3) FROM DUAL;

trunc(n,[m]) n값의 반내림을 하는 함수로 m은 소숫점 아래 자릿수만큼 구한다

SELECT trunc(12.3456,0), trunc(12.3456,1), trunc(12.3456,2), trunc(12.3456,3) FROM DUAL;

2. 문자열 함수

NVL(col명,대체값) : col의 값이 null일 때 대체값으로 치환

SELECT NAME, NVL(SALE,0) FROM DUAL;

 

 

lower() : 소문자로 변환시켜주는 함수

SELECT LOWER('abcd'), LOWER('aBCd') FROM DUAL;

upper() : 대문자로 변환시켜주는 함수

SELECT UPPER('abcd'), UPPER('aBCd') FROM DUAL;

Initcap() : 단어의 첫자만 대문자로 나머지는 소문자로

SELECT Initcap('abcd'), Initcap('aBCd') FROM DUAL;

ength() : 문자열의 길이

SELECT length('abcd'), length('aBCd55') FROM DUAL;

SUBSTR(문자열,위치,갯수) : 해당위치에서 갯수만큼의 문자열을 추출한다.

SELECT SUBSTR('a12345bcd',2,3), SUBSTR('a12345bcd',1,5) FROM DUAL;

instr(문자열,찾을 문자열[,위치,찾을위치]) : 문자열에서 이 문자를 찾아라. return값은 찾은 위치

SELECT Instr('a12345bcd','bcd'), Instr('a12345bcd','a') FROM DUAL;

decode(col명,비교값,취할값,비교값,취할값,....기본값) : col값이 비교값이면 취할값으로 대체.

SELECT decode(10,10,'총무부',20,'영업부',30,'전산부','관리부') FROM DUAL

UNION ALL

SELECT decode(20,10,'총무부',20,'영업부',30,'전산부','관리부') FROM DUAL

UNION ALL

SELECT decode(30,10,'총무부',20,'영업부',30,'전산부','관리부') FROM DUAL;

replace(문자열,교체대상 문자열, 교체할문자열) 문자열 중 일부 교체 하기

ex> SELECT REPLACE('ok wow school' ,'ok','www') " wowschool" FROM DUAL; ----> www wow school

rtrim 문자열 트리밍 하기 오른쪽 space 제거

ex> SELECT Rtrim(' ok wow school ' ) ,length(Rtrim(' ok wow school ')) FROM DUAL;

----> ok wow school 15

ltrim 문자열 트리밍 하기 왼쪽 space 제거

ex> SELECT ltrim(' ok wow school ' ) ,length(ltrim(' ok wow school ') ) FROM DUAL;

----> ok wow school 18

RPAD 자열 Rpad 하기

ex> SELECT RPAD('1234', 10, '0') NAME FROM dual ; ----> 1234000000

ex> SELECT RPAD('school', 12, '*') NAME FROM dual ; ----> school******

lpad 문자열 Lpad 하기

ex> SELECT LPAD('1234', 10, '0') NAME FROM dual ; ----> 0000001234

ex> SELECT LPAD('school', 12, '*') NAME FROM dual ; ----> ******school

NLS_INITCAP() initcap 동일 하고 다국어지원

ex> SELECT NLS_INITCAP ('ijsland') "InitCap" FROM DUAL; ----> Ijsland

ex> SELECT NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "InitCap" FROM DUAL; ----> IJsland

NLS_LOWER() LOWER 동일 하고 다국어지원

ex> SELECT NLS_LOWER ('CITTA''', 'NLS_SORT = XGerman') "Lowercase" FROM DUAL; ----> citta

NLS_UPPER() UPPER 동일 하고 다국어지원

ex> SELECT NLS_UPPER ('große', 'NLS_SORT = XGerman') "Uppercase" FROM DUAL; ----> GROSS

ASCII 문자열을 ASCII 수치값으로 변환 시킨다

ex> select ASCII('a') from dual; ----> 97

instr 문자열에서 패턴의 어커런스를 찾아낸다

ex> select instr('wowschool 죽인다','ch') from dual; ----> 5

concat 두 문자열을 결합한다 || 같은 기능을 한다

ex> select concat('wow','school') from dual; ----> wowschool

날짜 함수

ROUND(d[,m]) m에 지정된 단위로 반올림 한다, m가 연도면 연도 단위로 반올림 한다.

ex> SELECT ROUND(TO_DATE('2004/06/30'), 'YEAR') FROM dual ; ----> 04/01/01

ex> SELECT ROUND(TO_DATE('2004/06/30'), 'MONTH') FROM dual ; ----> 04/07/01

ex> SELECT ROUND(TO_DATE('2004/06/30'), 'DAY') FROM dual ; ----> 04/06/27

TO_CHAR(날짜,'포맷') : 날짜를 문자열로 바꾼다.

TO_DATE(문자열,'포맷') : 문자열을 날짜로 ex> TO_DATE('97-10-14',' -----')

ADD_MONTHS(날짜,n) : 현재 날짜에 n개월수를 더한다.

ex> SELECT TO_Char(ADD_MONTHS(SYSDATE,2),'RRRR:MM:DD') FROM dual ; ----> 2004:09:18

MONTHS_BETWEEN(날짜1,날짜2) : 두 날짜의 개월수의 차를 구한다.

ex> SELECT MONTHS_BETWEEN(TO_DATE('2004/06/25'), TO_DATE('2000/06/25')) "date" FROM dual ; ----> 48

LAST_DAY(날짜) : 그 달의 마지막 날짜를 구한다.

ex> SELECT LAST_DAY(SYSDATE) LASTDAY FROM dual ; ---> 04/07/31

NEXT_DAY(날짜,'요일') : 날짜로 부터 다음에 나오는 요일은 며칠인가

ex> next_day(sysdate,'금요일')

날짜 + n : 날짜에다 n 일을 더한다.

포맷:

형식	형식설명
yy	년도를 2자리로
yyyy	년도를 4자리로
year	영어 철자로 표시
mm	월을 표시(01-12)
mon	월을 표시(DEC....)
month	월을 표시(1월,2월,....혹은 ,DECEMBER)
d	일을 표시(주에 대한 일)
dd	일을 표시 (월에 대한 일)
ddd	일을 표시 (년에 대한 일)
Q	분기를 구한다.
DAY	요일 (월요일)
DY	요일(월)
HH & HH12	시간을 12시간제로
HH24	24시간제로
MI	분
SS	초

 

3. 그룹 함수

sum() : 합 , avg() : 평균 , count() : 갯수 count(*): 리턴되는 레코드의 수를 계산

SELECT SUM(판매수) ,SUM(판매단가) ,ROUND(avg(판매수),2) ,avg(판매단가) ,count(판매수) ,count(판매단가) FROM( SELECT 12 판매수, 1000 판매단가 FROM DUAL UNION ALL SELECT 15 판매수, 2000 판매단가 FROM DUAL UNION ALL SELECT 32 판매수, 3000 판매단가 FROM DUAL );

max() : 최대값, min() : 최소값 , stddev : 표준편차 , variance : 분산

SELECT max(판매수) ,max(판매단가) ,min(판매수) ,min(판매단가) ,ROUND(stddev(판매수),2) ,ROUND(stddev(판매단가),2) ,ROUND(variance(판매수) ,2) ,ROUND(variance(판매단가),2) FROM( SELECT 12 판매수, 1000 판매단가 FROM DUAL UNION ALL SELECT 15 판매수, 2000 판매단가 FROM DUAL UNION ALL SELECT 32 판매수, 3000 판매단가 FROM DUAL );