오라클 각종함수사용법

1. SQL관련 예약어 조회
○ ORACLE DB에는 예약어가 존재하여 사용을 금지하거나, 사용을 권장하지
않는 단어가 있다. SYSDATE, DESC 등 이와 관련된 내용을 조회하는 view는
다음과 같다
- SELECT * FROM V$RESERVED_WORDS



2. 오라클 비교함수
○ 오라클 비교함수 관련 하여 많이 쓰고 있는 함수를 정리했습니다.


NVL(col명,값) : col의 값이 null를 가질때 값을 가진다.
ex> 100+NULL은 NULL이 되기 때문에 이러한 문제점을 해결할 때 사용한다.
select ename,sal,comm,sal+nvl(comm,0) from emp;
-->sal값과 comm값을 더하는데 comm값이 null값이면 0값을 취한다...





산술 함수


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

ex> select abs(-200.1),abs(200.2) from dual; ----> 200.1 200.2


sin() cos() tan() sinH() cosH() tanH() : radian값을 이용

cos는 주워진 값의 cosine값을 cosh는 주워진 Hyperbolic cosine 값을 반환한다

tan은 Tangent 값을 Tanh는 Hyperbolic Tangent 값을 반환한다
ex> select cos(180*3.141592/180) from dual; ----> -1
ex> 30 일 경우 sin(30* (3.14.../180));


exp(n) : 지수값 e를 밑으로 하는 e의 n승 값;

ex> select exp(1), exp(2) from dual; ----> 2.71828183 7.3890561


LN(n) : 자연 log값 밑수 e 지수 n

ex> select ln(1.0), ln(2.3) from dual; ----> 0 .832909123



log(m,n) m을 기수로 이용하여 n의 대수를 복귀시킨다
ex> select log(10,1), log(10,2) from dual; ----> 0 .301029996


SQRT(n) : 루트n

ex> select sqrt(36) from dual; ----> 6


power(m,n) : m의 n승

ex> select power(10,2), power(10,3) from dual; ----> 100 1000


MOD(m.n) : m을 n으로 나눈 나머지를 retirn 한다 C언어의 m%n 동일하다

ex> select mod(5,2) from dual; ----> 1


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

ex> select floor(20.3),floor(-30.2) from dual; ----> 20 -31


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

주워진 값보다 근접한 큰수중 제일 작은 값을 구한다. 음수인경우는 양수의 경우 와 다르게 return(주의)
ex> floor(3.5) --> 3 , ceil (3.5) --> 4
floor(-3.2) --> -4 , ceil (-3.5) --> -3

ex> select ceil(-15.1), ceil(+10.3) from dual; ----> -15 11


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


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


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


sign 만약 값이 0보다 크면 1, 만약 값이 0 같으면 0, 만약 값이 작으면 -1
ex> select sign(-15), sign(0),sign(+10) from dual; ----> -1 0 1






ADD_MONTHS ( d , n ) : returns the date d plus n months.
SELECT TO_CHAR(ADD_MONTHS(hiredate,1),’DD-MON-YYYY’) Next month FROM emp WHERE ename = ’SMITH’; ----> 17-JAN-1981


round(n,[m]) n값의 반올림을 하는 함수로 m은 소숫점 아래 자리수 만큼 구한다.
ex> select round(20.2354,2),round(20.2354,3),round(20.2354,4) from dual; ----> 20.24 20.235 20.2354



trunc(n,[m]) n값의 반내림을 하는 함수로 m은 소숫점 아래 자릿수만큼 구한다
ex> select trunc(20.2344,2),trunc(20.2354,3),trunc(20.2354,4) from dual; ----> 20.23 20.235 20.2354

ex> Round(2.4678, 2) --> 2.47 , trunc(2.4687, 2) --> 2.46
Round(3654.26, -2) --> 3700 , trunc (3654.26, -2 ) --> 3600






문자열 함수

SUBSTR(p_MENU_ID, LENGTH(p_MENU_ID)-1,2) : 오른쪽 끝 문자 구하기(RIGHT함수구현)

lower() : 소문자로 변환시켜주는 함수
upper() : 대문자로 변환시켜주는 함수
Initcap() : 단어의 첫자만 대문자로 나머지는 소문자로

ex> Initcap(i LOVE you) ----> I Love You


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

SUBSTR( char , m ,[n] ) m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환합니다.

ex> SELECT SUBSTR(JUNG-SICK, 3, 3) NAME FROM dual ; ----> NG-

ex> SELECT SUBSTR(JUNG-SICK, -3, 3) NAME FROM dual ; ----> ICK
ex> substr (KOREA, 3, 2) ----> RE //3번째인 R부터 2글자 출력


instr(문자열,찾을 문자열[,위치,찾을위치]) : 문자열에서 이 문자를 찾아라. return값은 찾은 위치
ex> Instr(abcdabkdoerabjdlfjdg,ab) -----> 1 //1번째에서 시작해서 처음 ab가 나타나는 1출력
ex> Instr(abcdabkdoerabjdlfjdg,ab,4,2) ----> 12 //4번째부터 시작해서 두번째 나타나는 ab 출력


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


ex> decode(deptno,10,총무부,20,영업부,30,전산부,관리부) : deptno가 10이면 총무부, 20이면 영업부, 30이면 전산부

나머지 는 관리부 출력

ex> select decode(name,홍길동,의적,홍수환,참피언,차두리,축구선수,사람);



CHR(N) CHR 함수는 이진 N 값에 해당하는 문자 즉 ascii N 값을 반환한다
ex> select chr(66) from dual; ----> B



concat(char1,char2) 주워진 두 문자열을 합친다 || 연산자와 같다
ex> select concat(name,은 성실하다) name from wowbbs; ----> 홍길동은 성실하다



length 문자열 길이 구하기
ex> SELECT length(123456789) from dual ; ----> 9



substr(문자열,시작위치,문자수) 문자열 구하기
ex> SELECT substr(123456789,2,3) from dual ; ----> 234



initcap(char) 문자의 첫번째 를 대문자로 변환시킨다
ex> SELECT init(wowschool) name FROM DUAL; ----> Wow school



lower(char) 모든 문자열을 소문자로 변환시킨다
ex> SELECT lower(WOWSCHOOL) name FROM DUAL; ----> wow school



UPPER(CHAR) 모든 문자열을 대문자로 변환시킨다
ex> SELECT lower(wowschoool) name FROM DUAL; ----> WOWSCHOOL



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:초
AM & PM & A.M & P.M:12시간제 일때 오전 오후를 표시


접미사로 사용하는 포맷
TH:서수로 표시 ex> 4 -> DD ->4
4-> DDTH -> 4TH
SP:철자로 표시 ex> 4 ->DDSP -> FOUR
SPTH & THSP ex> 4 -> DDSPTH -> FOURTH
근무한 년월 수 ex> column 근무년수 Format a 14;




그룹 함수



group함수 :
NULL값은 계산되지 않는다.
여러개의 데이타에 대한 결과 이므로 그룹함수라 한다
단일 필드와 함께 사용할 수 없다.
만약 단일 필드와 사용되면 group by절에 반드시 나와야 한다.
sum() : 합
avg() : 평균
count() : 갯수 count(*): 리턴되는 레코드의 수를 계산
ex> select deptno,sum(sapay) from sawon; 에러
-->단위 필드이므로 따라서 마지막에 group by deptno;라고 지정해주면 사용가능


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