오라클에서 자주 쓰이는 함수 모음
오라클에서 많이 쓰이는 함수들을 모음~
문자형함수, 숫자형함수, 날짜형함수, 다중행함수 등등등...
1. 문자형 함수
UPPER : 모든 문자를 대문자로 전환
LOWER : 모든 문자를 소문자로 전환
INITCAP : 문자를 단어별로 앞머리는 대문자 나머지는 소문자로 전환
CONCAT : 두 문자열을 합성. ||연산자와 같은 용도로 사용
SUBSTR : 특정문자열의 부분을 선택
LENGTH : 문자열의 길이를 구함
LPAD : 왼쪽 문자 자리를 채움
RPAD : 오른쪽 문자 자리를 채움
LTRIM : 왼쪽 문자를 지움
RTRIM : 오른쪽 문자를 지움
TRANSLATE : 특정 문자열을 대체
REPLACE : 특정 문자열을 대신
Ex)
UPPER(문자값) : UPPER('Oracle Server') → ORACLE SERVER
LOWER(문자값) : LOWER('Oracle Server') → oracle server
INITCAP(문자값) : INITCAP('Oracle Server') → Oracle Server
CONCAT(문자값1, 문자값2) : CONCAT('Oracle',' Server') → Oracle Server
SUBSTR(문자값, a, b)
a 선택할 문자열의 시작위치. 음수면 끝에서부터 시작
b 선택할 문자열의 개수. 이 인자는 생략할 수 있으며, 생략할 경우 문자열의 끝까지 선택
: SUBSTR('강남구 역삼동‘,5,2) → 역삼
LENGTH(문자값1, a, 문자값2) : LENGTH(‘홍길동’) → 3
LPAD(문자값1, a, 문자값2)
RPAD(문자값1, a, 문자값2)
a : 전체 채울 자리수 , 문자값2 : 채울 문자 생략할 수 있으며, 생략되면 공백값임
: LPAD(‘홍길동’,10 ‘*’) → ****홍길동
LTRIM(문자값1, 문자값2)
RTRIM(문자값1, 문자값2)
문자값1에서 왼쪽(오른쪽)에서부터 더이상 문자값2를 만나지 않을 때까지 지움
: LTRIM('XXAXBA','X') → AXBA
TRANSLATE(문자값, a, b)
a 대체하고 싶은 문자(from) , b 대체할 결과의 문자(to)
: TRANSLATE('AABBA','B','C') → AACCA
REPLACE (문자값, a, b)
a 바꾸고 싶은 문자(from) , b 바꿀 결과의 문자(to)
: REPLACE ('JACK and JUE','J','BL') → BLACK and BLUE
2. 숫자형 함수
ROUND : 숫자를 반올림
TRUNC : 숫자를 반내림
MOD : 나누기 연산에서 나머지 구함
POWER : 거듭제곱
SQRT : 제곱근
SIGN : 양수인지 음수인지 0인지를 구별함
CHR : ASCII 값에 해당하는 문자를 구함
EX)
ROUND(숫자)
: ROUND(35.735,2) → 35.74
TRUNC(숫자값, a) a 숫자값을 반올림(버림)하여 a자리까지 돌려줌.
a가 양수이면 소수이하자리를, 음수이면 정수 부분 자리임 생략할 수 있으며, 생략하면 0
: TRUNC(7.5597, 2) -> 7.55
MOD(숫자값, a ) a 숫자값을 나누기 할 수 있음
: MOD(7,2) → 1
POWER(숫자값1, 숫자값2)
: POWER(3,2) → 9
SQRT (숫자값)
: SQRT(25) → 5
SIGN(숫자값)
: SIGN(-15) → -1
CHR(숫자값)
: CHR(65) → A
3. 날짜형 함수
SYSDATE : 현재 시스템의 날짜 및 시간을 구함
LAST_DAY : 지정한 날짜의 해당 월의 마지막 날짜를 구함
MONTHS_BETWEEN : 두 날짜 사이의 개월 수를 구함
ADD_MONTHS : 지정한 날짜로부터 몇 개월 후의 날짜를 구함
ROUND : 날짜에 대한 반올림
TRUNC : 날짜에 대한 버림
SYSDATE :
SYSDATE → 10-MAY-99
LAST_DAY(날짜값) :
LAST_DAY('17-FEB-98') → 28-FEB-98
MONTHS_BETWEEN(날짜값1, 날짜값2) :
MONTHS_BETWEEN('26-APR-97','22-JUL-95') → 21.1290323
ADD_MONTHS(날짜값, 숫자값) :
ADD_MONTHS('22-JUL-95',21) → 22-APR-97
ROUND(날짜값, 자리수) :
현재 날짜가 1999년 5월 10일이라고 가정하자.
ROUND(SYSDATE,'MONTH') → 01-MAY-99
TRUNC(날짜값, 자리수) :
현재 날짜가 1999년 5월 10일이라고 가정하자.
TRUNC(SYSDATE,'YEAR') → 01-JAN-99
4. 날짜에 대한 산술연산
날짜 + 숫자 : 날짜 특정한 날로부터 몇일 후의 날짜 계산
날짜 - 숫자 : 날짜 특정한 날로부터 몇일 전의 날짜 계산
날짜 - 날짜 : 숫자 두 날짜 사이의 차이를 숫자로 계산
5. 변환형 함수
TO_CHAR : 숫자나 날짜를 문자열로 변환
TO_NUMBER : 문자를 숫자로 변환
TO_DATE : 문자를 날짜로 변환
6. TO_CHAR에서 숫자를 문자로 변환시에 형식에 사용되는 요소
9 : 일반적인 숫자를 나타냄
0 : 앞의 빈자리를 0으로 채움
$ : dollar를 표시함
L : 지역 통화 단위(ex )
. : 소숫점을 표시함
, : 천단위를 표시함
7. TO_CHAR에서 날짜를 문자로 변환시에 형식에 사용되는 요소
SCC : 세기를 표시 S는 기원전(BC)
YEAR : 연도를 알파벳으로 spelling
YYYY : 4자리 연도로 표시
YY : 끝의 2자리 연도로 표시
MONTH : 월을 알파벳으로 spelling
MON : 월의 알파벳 약어
MM : 월을 2자리 숫자로 표시
DAY : 일에 해당하는 요일
DY : 일에 해당하는 요일의 약어
DDD,DD,D : 연도,월,일 중의 날짜를 숫자로 표시
HH , HH24 : (1-12) , (0-23)중의 시간을 표시
MI : 분을 표시
SS : 초를 표시
AM(A.M.),PM(P.M.) : 오전인지 오후인지를 표시
TO_CHAR(문자값,‘형식’)
숫자를 문자로 변환
: TO_CHAR(350000,'$999,999') → $350,000
숫자를 날짜로 변환
: TO_CHAR(SYSDATE,'YY/MM/DD') → 95/05/25
TO_DATE(문자값, ‘형식’)
: TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY') → 10-SEP-92
TO_NUMBER(문자값)
: TO_NUMBER('1234') → 1234
8. DECODE 함수
DECODE 함수는 값을 비교하여 해당하는 값을 돌려주는 함수
> DECODE (형식,비교값1,결과치1,비교값2,결과치2,...기본치 )
형식 : 컬럼이나 값
비교값1 : 형식이 비교값1에 맞는지를 비교
결과값1 : 형식이 비교값1에 맞을 때 갖는 값
기본치 : 형식이 비교값1,2,...에 맞지 않을 때 가지는 값
생략될 수 있으며, 생략되면 NULL이다.
EX)
SQL> SELECT name,title,
DECODE(SUBSTR(title,-2,2),
'부장',salary*1.1,
'과장',salary*1.07,
'사원',salary*1.05,
salary) 이번달급여
FROM s_emp
ORDER BY 3 DESC ;
9. 다중행 함수
COUNT( a ) : a의 행의 개수를 구함
AVG( a ) : a의 평균을 구함
SUM( a ) : a의 합계를 구함
MIN( a ) : a의 최소값을 구함
MAX( a ) : a의 최대값을 구함
STDDEV( a ) : a의 표준 편차를 구함
VARIANCE( a ) : a의 분산을 구함
COUNT(*)를 제외한 모든 그룹함수는 NULL값을 고려하지 않습니다.
중복값을 제거하고 싶은 경우는 a의 앞에 DISTINCT를 기술합니다.
MAX , MIN , COUNT를 제외한 그룹함수는 숫자타입의 데이터에만 가능합니다