함수 2가지

1. 단일 행 함수(Single Row Function)

문자형 함수 - UPPER, LOWER, INITCAP, CONCAT, SUBSTR, LENGTH, LPAD, RPAD, LTRIM, RTRIM, TRANSLATE, REPLACE

• UPPER(문자값)

UPPER( 'ORACLE Server') ----> ORACLE SERVER

• LOWER(문자값)

LOWER( 'ORACLE Server') ----> oracle server

• INITCAP(문자값)

INITAP( 'ORACLE Server') ----> Oracle Server

• CONCAT(문자값1, 문자값2)  - 문자값을 붙인다.

CONCAT( 'ORACLE' , 'Server') -----> ORACLEServer

• SUBSTR( 문자값, a, b ) - 문자열 자르기
  a : 선택할 문자열의 시작위치, 음수면 끝에서 부터 시작한다.
  b : 선택할 문자열의 갯수, 생략가능하며 생략시 문자열의 끝까지 선택한다.

SUBSTR( '강남구 대치동', 5, 2) ----> 대치

 

select substr('1234567890',5,2) from dual;
--(x,a,b) a는 시작위치를 의미하며 b는 갯수를 의미, 시작위치는 음수도 가능하고 끝에서부터 위치계산한다. 

 

• LENGTH(문자값)

LENGTH( '홍길동') ----> 3

• LPAD( 문자값1, a, 문자값2), RPAD( 문자값1, a, 문자값2 )
  a : 전체 채울 자리수
  문자값 : 채울문자, 생략가능하며 생략시 공백값이다.

LPAD( '홍길동' , 10 '*') ----> ****

• LTRIM( 문자값1, 문자값2 ), RTRIM( 문자값1, 문자값2 ) - 문자값1에서 왼쪽(오른쪽)에서부터 더이상 문자값2를 만나지 않을때 까지 채운다. 공백뿐 아니라 숫자, 문자도 가능하다.

LTRIM( 'XXAXBA', 'X') ----> AXBA

• TRANSLATE( 문자값, a, b) - 문자를 대체한다.[잘 사용하지 않는다.]
  a : 대체하고 싶은 문자(from)
  b : 대체할 결과의 문자(to)

TRANSLATE( 'AABBAA', 'B', 'C') ----> AACCA

• REPLACE( 문자값, a, b ) - 문자의 값 a를 b로 바꾼다.
  a : 바꾸고 싶은 문자(from)
  b : 바꿀 결과의 문자(to)

REPLACE( 'JACK and JUE', 'J' , 'BL' ) ----> BLACK and BLUE

 

 

숫자형 함수

• ROUND(숫자값, a) - 숫자를 반올림
   a : 숫자값을 반올림여 a자리까지 돌려준다. a가 양수이면 소수이하 자리를, 음수이면 정수 부분 자리이다. 생략 가능하며, 생략하면 0이다.

select round(35.734,2) from dual--소수점 두번째까지 구하지
; 

• TRUNC(숫자값, a) - 숫자를 절사
   a : 숫자값을 버림하여 a자리까지 돌려준다. a가 양수이면 소수이하 자리를, 음수이면 정수 부분 자리이다. 생략 가능하며, 생략하면 0이다.

MOD(숫자값, a) - 나누기 연산에서 나머지 수함

MOD(7, 2) ----> 1

 

• POWER( 숫자값1, 숫자값2 ) - 거듭제곱

POWER(3,2) ----> 9

• SQRT( 숫자값 ) - 제곱근

SQRT(25) ----> 5

• SIGN( 숫자값 ) - 양수인지 음수인지 0인지를 구별한다.(부호 구분)

SIGN(-15) ----> -1

• CHR( 숫자값) - ASCII값에 해당하는 문자를 구한다.(문자의 코드를 넣는경우 문자가 나온다.)

CHAR(65) ----> A

 

날짜형 함수

• SYSDATE - 현재 시스템의 날짜 및 시간을 구한다.

SYSDATE ----> 19/06/05

• LAST_DAY(날짜값) - 지정 날짜의 해당월의 마지막 날짜 구한다.

LAST_DAY('19/06/05') ----> '19/06/30'

select last_day(sysdate) from dual; 

 

MONTHS_BETWEEN (날짜값1, 날짜값2) - 두 날짜 사이의 개월수를 구한다.(음수도 나올 수 있다.)

MONTHS_BETWEEN( '17/01/01', '17/03/01') ----> -2

 

select round(sysdate, 'year') from dual; 

TRUNC(날짜값, 자리수) - 날짜에 대한 버림구한다.

현재 날짜가 1995년 5월 10일 이라면,
TRUNC(SYSDATE, 'YEAR') ----> 17/01/01

예제)

--Q4. 다음달의 마지막날을 구하시오 (last_day()사용하지마시오)
select trunc(add_months(sysdate,2), 'month')-1 from dual;

※ 날짜간의 간단한 계산은 산술연산을 이용하여 가능하다.
날짜 + 숫자 : 특정한 날로부터 몇일 후의 날짜 계산
날짜 - 숫자 : 특정할 날로부터 몇일 전의 날짜 계산
날짜 - 날짜 : 두 날짜 사이의 차이를 숫자로 계산

select (sysdate+2)-sysdate from dual--날짜에서도 덧셈 뺄셈가능
;

 

select name, salary, start_date from s_emp
where start_date > '14/12/31'--일부 날짜 형식중에는 date와 비교할수 있는 것들이 있다.
order by start_date asc
;
 

 

select 'x' from dual    --dual은 가상테이블을 의미한다.--x가 찍힌다면 조건식이 맞는 다는 것을 뜻한다.
where '16/03/01' < '16/04/03'
;
 
 
 

 

select name, start_date, title
from s_emp
where start_date like '15%'--15년도에 입사한 사람이 출력될것   %는 0~n개의 글자를 갖는 와일드카드이고 _는 하나당 한글자를 갖는 와일드카드이다. (_는 0개를 포함하지 않는다)
; 

변환형 함수

• TO_CHAR(문자값, '형식') : 숫자나 날짜를 문자열로 변환

숫자를 문자로 변환

TO_CHAR(350000, '$999,999') ----> $350,000

날짜를 문자로 변환

TO_CHAR(SYSDATE, 'YYYY/MM/DD' ) ----> 2019/06/05

※ 숫자 문자 변환시 형식에 사용되는 요소

9 - 일반적인 숫자
0 - 앞의 빈자리 0으로 채움
$ - 달러를 표시
L - 지역통화단위(ex. \)
. - 소수점 표시
, - 천단위 표시

 
select length('이름은')from dual;--한글의 경우 인코딩에따라 바이트가 다름
 
 

※ 날짜 문자 변환시 형식에 사용되는 요소

SCC :  세기를 표시 S는 기원전(BC)
YEAR : 연도를 알파벳으로 spelling
YYYY : 4자리 연도로 표시
YY : 끝의 2라지 연도로 표시
MONTH : 월을 풀네임 영문으로 표시 
MON : 월을 영문으로 줄여 표시
MM : 월을 2자리 숫자로 표시
DAY : 일에 해당하는 요일
DY : 일에 해당하는 요일의 약서
DDD,DD,D : 연도, 월, 일 중의 날짜를 숫자로 표시
HH, HH24 : (1~12), (0~23)중의 시간을 표시
MI : 분을 표시
SS : 초를 표시
AM(A.M.), PM(P.M.) : 오전인지 오후인지를 표시

• TO_NUMBER : 문자를 숫자로 변환

TO_DATE( '10 9월 1992' , 'DD MONTH YYYY') ----> 17/09/10

 

• TO_DATE : 문자를 날짜로 변환

TO_NUMBER( '1234' ) ----> 1234

※ 함수 여러개 겹쳐 사용하기

함수3(함수2(함수1(컬럼값, 형식1), 형식2), 형식3)

 

2. 다중행 함수 (Multi Row Funtion)

그룹함수

COUNT(a) : a의 행의 개수를 구한다.

 select count(zip_code)from s_customer--zip_code컬럼의 nul이 아닌 값의 갯수 , 값이 몇개 있는지 세는것
 ;

 

 select count (*) from  s_customer--s_customer의 행의 갯수를 세기에 null값을 포함한다
 ;

 

AVG(a) : a의 평균을 구한다.
SUM(a) : a의 합계를 구한다.
MIN(a) : a의 최소값을 구한다.
MAX(a) : a의 최대값을 구한다.
STDEV(a) : a의 표준편차를 구한다.
VARIANCE(a) : a의 분산을 구한다.
※ COUNT(*)를 제외한 모든 그룹함수는 NULL값을 고려하지 않는다.