[Oracle] SQL문법 2.함수(Function)

함수(Function)

하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해놓은 작은 서브프로그램
자바의 메서드와 같은 존재라고 볼 수 있다
호출하며 값을 전달하면 결과를 리턴하는 방식으로 사용한다
크게 아래 두가지로 나뉜다

• 단일행 함수 : n개의 값을 읽어서 n개의 결과를 리턴 (매 행마다 함수 실행 후 결과 반환)
• 그룹 함수 : n개의 값을 읽어서 1개의 결과를 리턴(하나의 그룹별로 함수 실행 후 결과 반환)

단일행 함수와 그룹함수는 결과 행의 갯수가 다르기 때문에 SELECT절에는 이 둘을 함께 사용할 수 없다

단일 행 함수

각 행마다 반복적으로 적용되어 입력받은 행의 개수만큼 결과를 반환하는 함수이다

[ 문자 처리 함수 ]

구분	입력 값	리턴 값	설명
LENGTH(CHAR | STRING)	CHARACTER	NUMBER	문자열 길이 반환
LENGTHB			문자열의 바이트 크기 반환
INSTR(STRING, STR, [POSITION,[OCCURRENCE]])			특정 문자의 위치 반환
INSTRB			특정 문자의 위치 바이트 크기 반환
LPAD / RPAD(STRING, N, [STR])		CHARACTER	지정 문자열을 입력한 크기만큼 본 문자열의 왼쪽 / 오른쪽부터 채워서 생성된 문자열 리턴
LTRIM / RTRIM(STRING, STR)			왼쪽 / 오른쪽부터 지정한 문자를 잘라내고 남은 문자 리턴
TRIM( LEADING | TRAILING | BOTH [CHAR] FROM STRING)			왼쪽 / 오른쪽 / 양쪽부터 지정한 문자를 잘라내고 남은 문자 리턴
SUBSTR( STRING, POSITION, [LENGTH] )			지정한 위치에서 지정한 길이만큼 문자 잘라내어 리턴
SUBSTRB			지정한 위치에서 지정한 바이트만큼 문자를 잘라내어 리턴
LOWER(STRING)			전달받은 문자 / 문자열을 소문자로 변환하여 리턴
UPPER(STRING)			전달받은 문자 / 문자열을 대문자로 변환하여 리턴
INITCAP(STRING)			전달받은 문자 / 문자열의 첫 글자만 대문자로, 나머지는 소문자로 변환하여 리턴
CONCAT(STRING, STRING)			인자로 전달받은 두 개의 문자 / 문자열을 합쳐서 리턴
REPLACE(STRING, STR1, STR2)			전달받은 문자열 중에서 지정한 문자를 인자로 전달받은 문자로 변환하여 리턴

LENGTH : NUMBER

SELECT
    LENGTH('오라클 쉽네'), -- 6
    LENGTHB('오라클 야호') -- 11
FROM
    DUAL;
-- 가상 테이블(DUMMY TABLE) : 산술 연산이나 가상칼럼값등 한번만 출력하고 싶을 때 사용하는 테이블

• LENGTH() : 해당 전달된 문자열의 글자 수 반환
• LENGTHB () : 매개변수로 전달된 문자열의 바이트 수 반환
  • 한글 : 3byte
  • 영문, 숫자, 특수문자 : 1byte
  • 인코딩 설정에따라 모두 동일하게 2byte로 취급될수도 있다
• 표현법 : LENGTH (CHAR | STRING)

  • CHAR	STRING : 문자 타입 컬럼명 또는 문자열

INSTR : NUMBER

SELECT
  INSTR('AABAACAABBAA', 'B'), -- 3
  INSTR('AABAACAABBAA', 'B', - 1),  -- 10
  INSTR('AABAACAABBAA', 'B', - 1, 2),  -- 9

• 지정한 위치부터 지정한 숫자 번째로 나타나는 문자의 시작위치를 반환한다
• SQL은 숫자의 처음을 0 부터가 아닌 1부터 세는것을 감안하여 결과를 생각해야 한다
• POSITION이 -1 이라고 해서 맨 끝의 글자부터 1로 세는 것이 아님을 주의
  => 찾을 문자를 끝에서 시작방향으로 하여 찾는것은 맞으나 반환하는 것은 첫글자부터 1로 차례대로 센 위치의 숫자 값이다
• 표현법 : INSTR(STRING, STR, [POSITION,[OCCURRENCE]])
  • = INSTR(문자열, 특정 문자, 찾을 위치의 시작값, 순번)
  • STRING : 문자 타입 컬럼 또는 문자열
  • STR : 찾으려는 문자열
  • POSITION : 찾을 위치 시작 값(기본 값 1 , -1)
    => 1 : STRING의 시작부터 끝 방향으로 찾음 => -1 : STRING의 끝부터 시작 방향으로 찾음
  • OCCURRENCE : 문자열 안에서 STR이 반복될 때 지정하는 위치(기본 값 1), 0 이하 사용 불가
    => POSITION으로 정해진 찾는 방향을 기준으로 몇 번째 문자의 위치를 반환할 것인지를 나타낸다

LPAD / RPAD : CHARACTER

SELECT
  LPAD(EMAIL, 16),
  RPAD(EMAIL, 16)

• 주어진 컬럼/문자열에 임의의 문자열을 왼쪽/오른쪽에 덧붙여 최종 길이 N의 문자열을 반환한다
• 덧붙이고자 하는 문자를 따로 명시하지 않았다면 기본으로 공백이 덧붙여진다
• 중요한 정보들은 애초에 암호화되어 db에 들어오기 때문에 이러한 처리를 할 일이 거의 없다
  => 사소한 정보들 가공에 조금씩 쓰이고 그다지 많이 사용되는 함수는 아님
• 표현법 : LPAD(STRING, N, [STR]) / RPAD(STRING, N, [STR])
  • = LPAD /RPAD (문자열, 최종적으로 반환할 문자의 길이 (BYTE), 덧붙이고자 하는 문자)
  • STRING : 문자 타입 컬럼 또는 문자열
  • N : 반환할 문자(열)의 길이(바이트), 원래 STRING의 길이보다 작다면 N만큼 잘라서 표시
  • STR : 덧붙이려는 문자(열), 생략 시 공백문자

LTRIM / RTRIM : CHARACTER

SELECT
    LTRIM('    D   D   D     '),        -- 'D   D   D  '
    RTRIM('0001230456000855000', '0'),  -- '0001230456000855'
    LTRIM('11332212312KH123123', '123') -- 'KH123123'

• 주어진 컬럼/문자열의 왼쪽/오른쪽에서 지정한 STR에 포함된 모든 문자를 제거한 나머지를 반환한다
• 제거하고자 하는 문자를 명시하지 않으면 기본으로 공백을 제거한다
• 제거하고자 하는 문자열을 명시 했을 때 그 문자열을 통으로 지워주는 것이 아니다
  => 문자 하나하나 검사를 하면서 현재 문자가 지우고자하는 문자열 안에 포함되어있다면 지워준다
• 표현법 : LTRIM(STRING, STR) / RTRIM(STRING, STR)
  • = LTRIM / RTRIM (문자열, 제거시키고자 하는 문자)
  • STRING : 문자 타입 컬럼 또는 문자열
  • STR : 제거하려는 문자(열), 생략 시 공백문자

TRIM : CHARACTER

SELECT
    TRIM('       F     F         '),         -- 'F     F'
    TRIM('Z' FROM 'ZZZZZZFFZZZZZZ'),         -- 'FF'
    TRIM(LEADING 'Z' FROM 'ZZZZZZFFZZZZZZ'), -- 'FFZZZZZZ'

• 주어진 컬럼/문자열의 앞/뒤/양쪽에 있는 지정한 문자를 제거한 나머지를 반환한다
• 지정 문자는 한 하나의 문자만 가능하다
• 표현법 : TRIM( LEADING | TRAILING | BOTH [CHAR] FROM STRING)
  • = TRIM(BOTH | LEADING | TRAILING '제거하고자 하는 문자' FROM '문자열')
  • STRING : 문자 타입 컬럼 또는 문자열
  • CHAR : 제거하려는 문자(열), 생략 시 공백문자
  • LEADING : TRIM할 CHAR의 위치 지정, 앞(LEADING) / 뒤(TRAILING) / 양쪽(BOTH) 지정 가능(기본 값 양쪽)

SUBSTR : CHARACTER

SELECT
    SUBSTR('ORACLEDATABASE', 7),     -- 'DATABASE'
    SUBSTR('ORACLEDATABASE', 7, 4),  -- 'DATA'
    SUBSTR('ORACLEDATABASE', - 8, 3) -- 'DAT'

• 컬럼/문자열에서 지정한 위치부터 지정한 개수의 문자열을 잘라내어 반환한다
• 추출할 문자 갯수를 명시하지 않으면 해당 문자열 끝까지 출력된다
• 처음위치는 음수로도 제시 가능하다
  => 뒤에서부터 N번째 위치로부터 문자를 추출함
• 표현법 : SUBSTR( STRING, POSITION, [LENGTH] )
  • = SUBSTR (문자열, 처음위치, 추출할 문자갯수)
  • STRING : 문자 타입 컬럼 또는 문자열
  • POSITION : 문자열을 잘라낼 위치로 양수면 시작방향에서 지정한 수만큼, 음수면 끝 방향에서 지정한 수만큼의 위치 의미
  • LENGTH : 반환할 문자 개수(생략 시 문자열의 끝까지 의미, 음수면 NULL 리턴)

LOWER / UPPER / INITCAP : CHARACTER

SELECT
    LOWER('WELCOME TO C CLASS'), -- 'welcome to c class'
    UPPER('wellcom to c class'), -- 'WELLCOM TO C CLASS'
    INITCAP('welcom to c class') -- 'Welcom To C Class'

• 컬럼/문자열을 소문자/대문자/첫 글자만 대문자로 변환하여 반환한다
• LOWER(문자열) : 소문자로 변경
• UPPER(문자열) : 대문자로 변경
• INITCAP(문자열) : 각 단어의 첫글자만 대문자로 변환
• 표현법 : LOWER(STRING) / UPPER(STRING) / INITCAP(STRING)
  • STRING : 문자 타입 컬럼 또는 문자열

CONCAT : CHARACTER

SELECT
    CONCAT('가나다', '라마바사') -- '가나다라마바사'

• 컬럼/문자열을 두개 전달받아 하나로 합친 후 반환한다
• 매개 문자열은 두개까지밖에 입력하지 못한다
• || 연결 연산자와 동일한 역할이라고 할 수 있다
• 표현법 : CONCAT(STRING, STRING)

REPLACE : CHARACTER

SELECT REPLACE('서울시 강남구 역삼동 56번길 강남빌딩 3층', '3층', '2층 C 클래스')
    -- 서울시 강남구 역삼동 56번길 강남빌딩 2층 C 클래스

• 컬럼/문자열에서 특정문자(열)을 지정한 문자(열)로 바꾼 후 반환한다
• 표현법 : REPLACE(STRING, STR1, STR2)
  • = REPLACE (문자열, 찾을문자, 바꿀문자)
  • STRING : 문자 타입 컬럼 또는 문자열
  • STR1 : 변경하려고 하는 문자 혹은 문자열
  • STR2 : 변경하고자 하는 문자 혹은 문자열

[ 숫자 처리 함수 ]

구분	입력 값	리턴 값	설명
ABS(NUMBER)	NUMBER	NUMBER	절대 값 리턴
MOD(NUMBER, DIVISION)			입력 받은 수를 나눈 나머지 값 반환
ROUND(NUMBER, [POSITION])			특정 자릿수에서 반올림
FLOOR(NUMBER)			버림(소수점 아래를 잘라냄)
TRUNC(NUMBER, [POSITION])			특정 자릿수에서 잘라냄
CEIL(NUMBER)			올림(소수점 아래에서 올림)

ABS : NUMBER

SELECT ABS(- 10.9)  --  10.9

• 인자로 전달받은 숫자의 절대값을 반환한다
• 표현법 : ABS(NUMBER)
  • NUMBER : 숫자 혹은 숫자 데이터 컬럼

MOD : NUMBER

SELECT
    MOD(10, 3)  --  1
    MOD(- 10, 3) -- -1
    MOD(10.9, 3) -- 1.9

• 인자로 전달받은 숫자를 나누어 나머지값을 반환한다
• 자바에서의 % 연산자와 비슷한 기능이라고 할 수 있다
• 표현법 : MOD(NUMBER, DIVISION)
  • = MOD(숫자, 나눔값)
  • NUMBER : 숫자 혹은 숫자 데이터 컬럼
  • DIVISION : 나눌 수 혹은 나눌 숫자 데이터 컬럼

ROUND : NUMBER

SELECT
    ROUND(123.556)      -- 124
    ROUND(123.556, 1)   -- 123.6
    ROUND(125.556, - 1) -- 130

• 소수점 기준 지정한 위치부터 반올림하여 값을 반환한다
• 반올림 위치 : 소수점 기준으로 아래 N번째 수에서 반올림 한다
  => 반올림할 위치 생략시 기본값인 0으로 자동 진행된다 (=소숫점을 없앤 양수를 반환)
• 표현법 : ROUND(NUMBER, [POSITION])
  • = ROUND(반올림 하고자하는 수 , 반올림할 위치)
  • NUMBER : 숫자 혹은 숫자 데이터 컬럼
  • POSITION : 반올림할 위치(생략 시 기본 값 0)

FLOOR : NUMBER

SELECT  FLOOR(207.9999) -- 207

• 소숫점 아래의 수를 버림하여 값을 반환한다
• 표현법 : FLOOR(NUMBER)
  • = FLOOR (버림처리고자 하는 숫자)

TRUNC : NUMBER

SELECT
    TRUNC(123.789, - 1) -- 120
    TRUNC(123.789, 2)   -- 123.78

• 지정한 위치부터 버림처리하여 값을 반환한다
• 위치는 생략가능하며 생략시 기본값은 0으로 되어 FLOOR함수와 동일하게 동작한다
• 표현법 : TRUNC(NUMBER, [POSITION])
  • = TRUNC(버림처리할 숫자, 위치)
  • POSITION : 버릴 위치(생략 시 기본 값 0)

CEIL : NUMBER

SELECT  CEIL(123.00001) -- 124

• 소숫점 아래의 수를 올림하여 값을 반환한다
• 표현법 : CEIL(NUMBER)
  • = CEIL (올림처리할 숫자)

[ 날짜 처리 함수 ]

구분	입력 값	리턴 값	설명
SYSDATE	DATE	DATE	시스템에 저장된 현재 날짜 반환
MONTHS_BETWEEN		NUMBER	두 날짜를 전달받아 몇 개월 차이인지 계산하여 반환
ADD_MONTHS	DATE	DATE	특정 날짜에 개월 수를 더하여 반환
NEXT_DAY			특정 날짜에서 인자로 받은 요일이 최초로 다가오는 날짜 반환
LAST_DAY			해당 달의 마지막 날짜 반환
EXTRACT		NUMBER	년, 월, 일 정보를 추출하여 반환

구분 입력 값 리턴 값 설명 SYSDATE DATE DATE 시스템에 저장된 현재 날짜 반환 MONTHS_BETWEEN(DATE1, DATE2) NUMBER 두 날짜를 전달받아 몇 개월 차이인지 계산하여 반환 ADD_MONTHS(DATE, NUMBER) DATE DATE 특정 날짜에 개월 수를 더하여 반환 NEXT_DAY(DATE, STRING [OR NUMBER]) 특정 날짜에서 인자로 받은 요일이 최초로 다가오는 날짜 반환 LAST_DAY(DATE) 해당 달의 마지막 날짜 반환 EXTRACT(YEAR | MONTH | DAY FROM DATE) 년, 월, 일 정보를 추출하여 반환

</table>

SYSDATE : DATE

SELECT SYSDATE -- 00/00/00 형태로 현재 시간 반환

• 시스템에 저장되어있는 현재 날짜를 반환한다
• DATE 자료형으로 년도, 월, 일, 시, 분 ,초 를 다 포함하고 있다

MONTHS_BETWEEN : NUMBER

SELECT
    MONTHS_BETWEEN('22/01/22' , '22/11/07') -- -9.51612903225806451612903225806451612903
    MONTHS_BETWEEN('22/11/07' , '22/01/22') -- 9.51612903225806451612903225806451612903

• 인자로 날짜 두 개를 전달받아 개월 수 차이를 NUMBER 타입으로 반환한다
• 두번째 날짜인자가 첫번째 날짜보다 미래일 경우 음수를 반환한다
• 표현법 : MONTHS_BETWEEN(DATE1, DATE2)
  • DATE1 : 기준이 되는 날짜
  • DATE2 : 개월 수를 구하려는 날짜

ADD_MONTHS : DATE

SELECT ADD_MONTHS('22/12/18', 6)  -- 23/06/18

• 특정 날짜에 인자로 받은 숫자만큼 개월수를 더한 날짜를 반환한다
• 표현법 : ADD_MONTHS(DATE, NUMBER)
  • DATE : 기준이 되는 날짜
  • NUMBER : 더하려는 개월 수

NEXT_DAY : DATE

SELECT NEXT_DAY('22/12/18', '토')  -- 22/12/24

• 특정 날짜에서 가장 가까운 요일을 찾아 해당 날짜를 반환한다
• 표현법 : NEXT_DAY (DATE, STRING [OR NUMBER])
  • = NEXT_DAY(DATE, 요일(문자/숫자))
  • DATE : 기준이 되는 날짜
  • STRING[OR NUMBER] : 구하려는 요일
    => 숫자의 경우 1 = 일요일, 2 = 월요일, …., 7 = 토요일
    => 문자의 경우 한국어로 설정된 상태이기 때문에 ‘월’, ‘월요일’ 모두 가능하나 ‘MONDAY’, ‘MON’은 인식하지 않는다
    => ALTER SESSION SET NLS_LANGUAGE = AMERICAN;로 언어설정을 변경후엔 ‘MONDAY’, ‘MON’ 인식이 가능하다

LAST_DAY : DATE

SELECT LAST_DAY('22/12/18')  -- 22/12/31

• 인자로 전달받은 날짜가 속한 달의 마지막 날짜를 반환한다
• 표현법 : LAST_DAY (DATE)

EXTRACT : NUMBER

SELECT
    EXTRACT(YEAR FROM '22/12/18'),  -- 2022
    EXTRACT(MONTH FROM '22/12/18'), -- 12
    EXTRACT(DAY FROM '22/12/18')    -- 18

• 년, 월, 일 정보를 추출해서 반환한다
• 표현법 : EXTRACT(YEAR | MONTH | DAY FROM DATE)
  • DATE : 기준이 되는 날짜

[ 형 변환 함수 ]

구분	입력 값	리턴 값	설명
TO_CHAR	DATE / NUMBER	VARCHAR2	날짜형 혹은 숫자형을 문자형으로 변환
TO_DATE	CHARACTER / NUMBER	DATE	문자형 혹은 숫자형을 날짜형으로 변환
TO_NUMBER	CHARACTER	NUMBER	문자형을 숫자형으로 변환

FORMAT 형식

구분	형식	의미
숫자형	0 => '0000'	앞의 빈자리를 0으로 채움
	9 => '9999'	일반적인 숫자
	$ => '$9999'	‘$’(dollar) 표시를 나타냄
	L => 'L9999'	￦등 지역 통화 단위를 표시
	. => '99.99'	소수점을 표시함
	, => '9,999'	천단위를 표시함
날짜형	SCC	세기를 표시 S는 기원전(BC)
	YEAR	연도를 알파벳(영어)로 표시
	RRRR	50년 기준 작으면 2000년대로 크면 1900년대로 4자리 표시
	RR	끝 2자리 연도 표시(50년을 기준으로 앞 2자리 년대가 바뀌는 것은 동일)
	YYYY	4자리 연도 표시(기본 2000년대)
	YY	끝 2자리 연도 표시(기본 2000년대)
	MONTH	월을 표시
	MON	월의 약자로 표시(한글일 경우 MONTH와 다르지 않음)
	MM	월을 2자리 숫자로 표시
	DAY	날짜에 해당하는 요일 표시
	DY	날짜에 해당하는 요일을 약자로 표시
	DDD	연도 기준으로 몇 번째 날 인지 표시(1-366)
	DD	월 기준으로 몇 번째 날 인지 표시(1-31)
	D	주 기준으로 몇 번째 날 인지 표시(1-7), 요일을 나타냄(일요일부터 1로 시작)
	HH, HH24	12시간 기준, 24시간 기준으로 시간을 표시
	MI	분을 표시
	SS	초를 표시
	AM, PM	오전이나 오후를 표시

• 날짜형의 FORMAT들은 문자를 날짜로 변활할때도 같은 형식을 사용한다
• 날짜형 FORMAT 중 R이 뜻하는 단어 : ROUND(반올림)
  => 50년을 기준으로 작으면 2000년대 , 크면 1900년대로 표시된다

TO_CHAR : VARCHAR2

SELECT
    TO_CHAR(123456),         -- '123456'
    TO_CHAR(123, '00000')    -- '00123'
    TO_CHAR(123, '99999')    -- '123'
    TO_CHAR(1234, 'L00000')  -- '￦01234'
    TO_CHAR(1234, 'L99,999') -- '￦1,234'
    TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" (DY)') -- '2022년 01월 01일 (토)' (2022/01/01 기준)
    TO_CHAR(SYSDATE, 'FMYYYY"년" MM"월" DD"일" (DY)') -- '2022년 1월 1일 (토)' (2022/01/01 기준)

• 날짜/숫자형 데이터를 문자형 데이터로 변환하여 반환한다
• 데이터 변환시 원하는 FORMAT을 지정할 수 있다
• FORMAT 지정 중 FORMAT 형식에는 없는 의미없는 문자를 넣어주고 싶다면 “” 로 감싸서 넣어주면 된다
• 날짜형 데이터 변환시 날짜에 붙는 앞자리 0을 없애고 싶다면 FM키워드를 앞에 붙여주면 된다
• 날짜형/숫자형 FORMAT형식 사용 가능
• 표현식 : TO_CHAR( NUMBER / DATE [, FORMAT] )

TO_DATE : DATE

SELECT
    TO_DATE('20221104') -- 22/11/04 (2000년대)

    TO_DATE('000101')
    -- 0으로 시작하는 년도를 다룰때는 반드시 홀따옴표를 붙여서 문자열처럼 다뤄줘야한다
    -- TO_DATE(000101)은 정수값중에 0으로 시작하는 숫자는 없기 때문에 literal does not match format string 에러발생 => 101로 인식하기 때문

    TO_DATE('20201104', 'YYYYMMDD') -- 20/11/04 (2000년대)
    -- YYYYMMDD 로 형식을 지정했음에도 20/11/04로 출력되는 이유는
    -- 옆의 FORMAT이 해당 데이터가 저런 형식으로 되어있다고 알려주기 위함이지 저렇게 만들어달라는 뜻이 아니기 때문이다

    TO_DATE('091129 143050', 'YYMMDD HH24:MI:SS') -- 09/11/29 (2000년대)

• 숫자/문자형 데이터를 날짜형 데이터로 변환하여 반환한다
• 날짜형 FORMAT형식 사용가능
• 표현법 : TO_DATE(CHARACTER/NUMBER [, FORMAT]

TO_NUMBER : NUMBER

SELECT
    TO_NUMBER('0123') -- 123
    '123' + '123',  -- 246  (자동 형변환)
    '10,000,000' + '550,000'  -- 오류발생
    -- ',' 문자를 포함하고 있기때문에 숫자로 자동형변환이 되지 않는다
    TO_NUMBER('10,000,000') + TO_NUMBER('550,000')
    -- 형식을 지정해주지 않았기 때문에 위와 똑같은 오류가 발생한다
    TO_NUMBER('10,000,000', '99,999,999') + TO_NUMBER('550,000', '999,999') -- 10550000

• 날짜/문자형 데이터를 숫자형 데이터로 변환하여 반환한다
• 첫글자에 0이 있다면 다 날려버리고 0이 아닌 숫자부터 시작된다
• 숫자가 아닌 다른 문자를 포함하고 있는 데이터가 들어온다면 해당 데이터를 어떤식으로 읽어야 할지 FORMAT을 꼭 지정해주어야 한다
• 오라클 SQL에선 문자열 -> 숫자 로의 자동형변환이 많이 이루어진다
• 숫자형 FORMAT형식 사용가능
• 표현법 : TO_NUMBER(CHARACTER [, FORMAT])

[ NULL 처리 함수 ]

NVL(exp1, exp2)

SELECT NVL(BONUS, 0) -- BONUS가 NULL일 경우 0을 출력

• NULL로 되어있는 컬럼의 값을 인자로 지정한 숫자/문자로 변경하여 반환한다
• 인자로 지정한 값에 따라 NUMBER/CHARACTER 타입을 반환하게 된다
• 표현식 : NVL(exp1, exp2)
  • = NVL (컬럼명, 해당 컬럼값이 NULL일 경우 반환할 반환 값)
  • exp1 : NULL데이터를 처리할 컬럼명 혹은 값
  • exp2 : NULL값을 대체하고자 하는 값

NVL2(exp1, exp2, exp3)

SELECT NVL2(BONUS, '보너스 있음', '보너스 없음')
-- BONUS가 NULL일 경우 '보너스 없음', 값이 있을 경우 '보너스 있음' 출력

• 주어진 컬럼의 값이 NULL일경우와 아닐경우에 반환할 값을 모두 인자로 받아 상황에 따라 반환해준다
• 표현식 : NVL2(exp1, exp2, exp3)
  • = NVL2(컬럼명, 결과값1, 결과값2)
  • exp1 : NULL데이터를 처리할 컬럼명 혹은 값
  • exp2 : NULL이 아닐 경우 반환할 값
  • exp3 : NULL값을 대체하고자 하는 값

NULLIF(exp1, exp2)

SELECT NULLIF('456', '123')  -- 456

• 동등비교를 수행하며 두 값이 동일할 경우 NULL, 동일하지 않을 경우 첫번째 인자를 반환한다
• 표현식 : NULLIF(exp1, exp2)
  • = NULLIF(비교대상1, 비교대상2)
  • exp1 : 두 인자 값이 동일하지 않을 경우 반환될 값

[ 선택 함수 ]

DECODE

SELECT DECODE(SUBSTR('041208-1XXXXXX', 8,1), 1, '남', 2, '여', '성별을 알 수 없습니다')  -- 남

• 비교하고자 하는 값/컬럼이 조건식과 같으면(동등비교) 결과 값을 반환한다
• 비교 대상에는 컬럼명, 산술연산, 함수가 들어갈 수 있다
• 자바에서의 switch 문과 작동방식이 유사하다
• 표현법 : DECODE(표현식, 조건1, 결과1, 조건2, 결과2, …, DEFAULT)
  • 표현식 : 값에 따라 선택을 다르게 할 컬럼 혹은 값
  • 조건 : 해당 값이 참인지 거짓인지 여부 판단
  • 결과 : 해당 조건과 일치하는 경우 반환할 값
  • DEFAULT : 모든 조건이 불일치 시 반환할 값

CASE WHEN THEN

SELECT
    CASE WHEN SALARY > 5000000 THEN '고급'
        WHEN SALARY > 3500000 THEN '중급'
        ELSE '초급' END

• 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값을 반환(조건은 범위 값이 가능하다)
• DECODE와는 다르게 조건을 자유롭게 제시할 수 있다 (DECODE는 하나의 조건으로 동등비교만을 수행)
• 자바에서의 if else if 문과 작동방식이 유사하다
• 표현법 : CASE WHEN 조건1 THEN 결과1 WHEN 조건2 THEN 결과2… ELSE 결과(DEFAULT) END
  • 조건 : 해당 값이 참인지 거짓인지 여부 판단
  • 결과 : 해당 조건과 일치하는 경우 반환할 값
  • DEFAULT : 모든 조건이 불일치 시 반환할 값

그룹 함수

구분	설명
SUM(NUMBER)	그룹의 누적 합계 반환
AVG(NUMBER)	그룹의 평균 반환
COUNT()	그룹의 총 개수 반환
MAX(ANY)	그룹의 최대 값 반환
MIN(ANY)	그룹의 최소 값 반환

• 하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등을 하나의 컬럼으로 반환하는 함수이다
• N개의 값을 읽어서 1개의 결과를 반환한다
• 하나의 그룹별로 함수를 실행하고 그 결과를 반환한다
• WHERE절에서는 사용하지 못한다

SUM

SELECT SUM(SALARY), -- SALARY 컬럼의 총 합
       SUM(SALARY + (SALARY*NVL(BONUS, 0))*12) -- SALARY컬럼의 보너스 포함 연봉의 총 합

• 해당 컬럼 값들의 총합을 반환한다
• 인자로 연산식과 함수를 넣을 수 있다
• null 값을 가진 행은 연산의 대상에서 제외된다
  => 따라서 동일 컬럼 내의 연산 진행중, null값이 있다고 전체 결과가 null이되는 것을 방지하기 위해 SUM(NVL(COL1,0)) 이런식의 처리는 하지 않아도 된다(오히려 불필요한 연산이 늘어남) => 최종 결과가 null일경우 공백으로 화면이 출력되어보이므로 이런 결과를 방지하고 싶다면 NVL(SUM(COL1),0)과 같이 해주는 것도 좋다
• 다만 여러 컬럼들을 조합하여 연산을 수행할 때는 의도와 다른 결과가 나올 수 있으므로 꼭 null 처리를 해주자
• 표현법 : SUM (숫자타입의 컬럼)

AVG

SELECT AVG(SALARY), -- SALARY 컬럼의 평균
       AVG(SALARY + (SALARY*NVL(BONUS, 0))*12) -- SALARY컬럼의 보너스 포함 연봉의 평균

• 해당 컬럼 값들의 평균을 구해서 반환한다
• NVL처리를 하지 않을 시 null 가진 행은 평균 계산에서 제외되어 계산된다
• 표현법 : AVG(숫자타입컬럼)

MAX / MIN

SELECT MAX(SALARY), MIN(SALARY), -- SALARY 컬럼의 최대값, 최소값
       MAX(HIRE_DATE), MIN(HIRE_DATE) -- HIRE_DATE컬럼의 가장 오래된 날짜, 가장 최근 날짜

• 해당 컬럼 내에서 가장 큰값(MAX)/작은 값(MIN)을 반환한다
• 표현법 : MAX(ANY타입) / MIN(ANY타입)

COUNT

SELECT COUNT(*), -- 조회된 행의 전체 갯수
       COUNT(SALARY), -- 제시한 컴럼의 값이 null이 아닌것의 갯수
       COUNT(DISTINCT SALARY) -- 제시한 해당 컬럼값에 중복값이 있을 경우 하나로만 세서 반환

• 조회된 행의 갯수를 반환한다
• 표현법 : COUNT( * | 컬럼이름 | DISTINCT 컬럼이름 )
  • COUNT(*) : 조회 결과에 해당하는 모든 행의 개수를 반환
  • COUNT(컬럼이름) : 제시한 컬럼 값이 null이 아닌 것의 개수를 반환
  • COUNT(DISTINCT 컬럼이름) : 제시한 컬럼에 중복값이 있을 경우 하나로만 카운트 하여 총 개수 반환 (null은 개수에 포함하지 않는다)