FUNCTION

하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성
해 놓은 작은 서브프로그램

단일행함수 : 각행마다 반복적으로 적용
그룹함수 : 묶어서 처리

문자 처리 함수

• LENGTH : 문자열 길이 반환
• INSTR : 찾는 문자열이 지정한 위치부터 지정한 횟수에 나타난 위치 반환

INSTR(STR, '찾는 문자', 몇 번째부터 찾을지, 몇 번째로 나타났는지)

SELECT INSTR('Helli World Hi High','H',1,3) FROM DUAL;
-- 1번째 글자부터 찾아서, 3번째로 나타난 H의 위치 반환 > 16
SELECT INSTR('Helli World Hi High','H',-1,1) FROM DUAL;
-- -1은 뒤에서부터 세어서 몇 번에 있는지 반환한다

• LPAD / RPAD : 주어진 컬럼 문자열에 임의의 문자열을 왼쪽/ 오른쪽에 덧붙인다

LPAD(컬럼, 문자열 길이, '공백을 채울 문자')

SELECT EMAIL LENGTH,(EMAIL),LPAD(EMAIL, 25,'#'), RPAD(EMAIL, 20, '#')
FROM EMPLOYEE;
-- ######mylatteis@gmail.com | mylatteis@gmail.com######

• LTRIM / RTRIM : 주어진 컬럼이나 문자열의 왼쪽 또는 오른쪽에서 지정 문자열에 포함된 문자를 제거하고 나머지를 반환한다

SELECT LTRIM ('21121231321KH','123') FROM DUAL;
-- 왼쪽 123을 제거
SELECT RTRIM ('123KH12312KH312321','123') FROM DUAL;
-- 오른쪽 123을 제거

• TRIM : 주어진 컬럼이나 문자열의 앞/ 뒤/ 양쪽에 있는 지정문자를 제거하고 나머지를 반환한다

SELECT '000KH000' FROM DUAL;
SELECT TRIM('0' FROM '000KH000') FROM DUAL;
-- KH
SELECT TRIM(BOTH '0' FROM '000KH000') FROM DUAL;
-- KH (생략과 동일)
SELECT TRIM(LEADING '0' FROM '000KH000') FROM DUAL;
-- KH000
SELECT TRIM(TRAILING '0' FROM '000KH000') FROM DUAL;
-- 000KH

• SUBSTR : 컬럼이나 문자열에 지정한 위치부터 지정한 개수의 문자열을 잘라내어 반환한다

SELECT SUBSTR('SHOW ME THE MONEY', 9, 3) FROM DUAL;
-- THE (9번째 글자부터 3개)
SELECT SUBSTR('SHOW ME THE MONEY', 9) FROM DUAL;
-- THE MONEY (9번째 글자부터 끝까지)
SELECT SUBSTR('SHOW ME THE MONEY', -9, 3) FROM DUAL;
-- THE (-9번째 글자부터 정방향으로 3개)
-- SUBSTR('문자열', 시작 글자, 가져올 글자 수) 

숫자 처리 함수

• ABS(NUM) : 인자로 받은 숫자의 절대값을 리턴
• MOD(NUM1, NUM2) : NUM1을 NUM2로 나누어 나머지 출력

SELECT 10/3 FROM DUAL;  -- 3.33333333
SELECT MOD(10,3) FROM DUAL; -- 1

• ROUND(NUM, (POSITION)) : 반올림하여 리턴 (위치지정가능)
• FLOOR(NUM) : 숫자 혹은 컬럼의 소수점 자리수를 버리고 리턴
• TRUNC(NUM, POSITION) : 지정한 위치부터 소수점 자리의 수를 버리고 리턴

SELECT TRUNC(123.567, 1) FROM DUAL; --123.5
SELECT TRUNC(123.567, -1) FROM DUAL; --120

• CEIL(NUM) : 숫자 혹은 컬럼의 소수점 자리의 수를 올리고 리턴

날짜 처리 함수

• 현재 시간을 다루는 함수 :
  SYSDATE, CURRENT_DATE, LOCALTIMESTAMP, CURRENT_TIMESTAMP
• MONTHS_BETWEEN(DATE1, DATE2) : 두 날짜의 개월 수 차이를 반환
• ADD_MONTH(DATE, NUM) : 매개변수로 받은 날짜에 숫자를 더해 반환
• NEXT_DAY(DATE, STRING(or NUM)) : 매개변수로 받은 날짜에서 가장 가까운 STRING을 리턴 (1일, 2월, 3화, 4수, 5목, 6금, 7토)
• LAST_DAY(DATE) : 매개변수로 받은 날짜가 속한 달의 마지막 날짜 리턴
• EXTRACT(추출정보 FROM DATE) : 매개변수로 받은 날짜에서 년 / 월 / 일을 추출

SELECT SYSDATE,
EXTRACT(YEAR FROM SYSDATE),
EXTRACT(MONTH FROM SYSDATE),
EXTRACT(DAY FROM SYSDATE)
FROM DUAL;
-- 년/월/일	년 | 월 | 일

형변환 함수

TO_CHAR : NUM, DATE 모두 변환 가능

• TO_CHAR(DATE,[FORMAT]) 날짜형 > 문자형

형식	설명	형식	설명
YYYY	년도 4자리	YY	년도 2자리
RR	년도 2자리	MONTH	월 표시
MM	월 숫자로 표현	MON	월을 알파벳으로 표현
DD	날짜	DAY	요일 표현
DY	요일을 약어로 표현	D	요일을 숫자로 표현
HH, HH12	시간(12시간)	HH24	시간(24시간)
MI	분	SS	초
AM, PM	오전, 오후	FM	앞자리 0 제거

• TO_CHAR(NUMBER,[FORMAT]) 숫자형 > 문자형
  0 or 9를 통해서 숫자의 최대 개수를 표현한다
  변환될 숫자길이보다 포맷길이가 길어야 한다

SELECT 10000, TO_CHAR(10000, '000,000,000') FROM DUAL;
-- 000,010,000 자리수만큼 나옴
SELECT 10000, TO_CHAR(10000, '999,999,999') FROM DUAL;
-- 10,000 사용하지 않은 나머지는 자르고 나옴
SELECT 10000, TO_CAHR(10000, '999,999,999,999') FROM DUAL;
SELECT 10000, TO_CHAR(10000, 'L999,999,999') FROM DUAL;
-- 숫자 앞 L은 로컬 화폐단위 표시

SELECT EMP_NAME, TO_CHAR(SALARY, 'L999,999,999') 월급 FROM EMPLOYEE;

TO_DATE : CHAR, NUM 모두 변환 가능

TO_DATE(CHARACTER,[FORMAT])

TO_DATE(NUMBER,[FORMAT])

SELECT TO_DATE(20220715, 'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('20220715','YYYYMMDD'),
'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

TO_NUMBER : CHAR 데이터 가능

TO_NUMBER(CHARACTER,[FORMAT])

TO_CHAR와 마찬가지로 값을 담을 수 있는 충분히 큰 포맷을 지정해야 한다

유효한 수를 입력하여야 한다 (123a 같은 숫자가 아닌 형은 에러 발생)

SELECT TO_NUMBER('10,000', '99,999') FROM DUAL;
-- '99,999'는 담을 수 있는 그릇

그 외 함수

• NVL(처리할 컬럼, 대체 값) : NULL처리 함수
  NVL(BONUS,0)

• DECODE : 자바의 SWITCH와 비슷한 함수
  DECODE (비교값, 일치값, 일치하면 출력, 불일치 값, 불일치하면 출력 (DEFAULT))

SELECT EMP_NAME, EMP_NO, DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여') 성별
FROM EMPLOYEE;
-- 주민번호 1번째 자리를 통해 성별 구분 컬럼

• CASE : 여러가지 경우를 선택할 수 있는 기능 < DECODE와 다르게 범위값 설정 가능
  WHEN THEN ELSE 로 구성

SELECT EMP_NAME, EMP_NO, CASE
WHEN SUBSTR(EMP_NO,8,1)=1 OR SUBSTR(EMP_NO,8,1)=3 THEN '남' 
WHEN SUBSTR(EMP_NO,8,1) IN (2,4) THEN '여'
ELSE '?'
END AS 성별
FROM EMPLOYEE;

그룹함수

그룹함수는 일반 컬럼과 함께 쓰일 수 없다 (ROW 수가 다르기 때문)

• SUM : 해당 컬럼 값들의 총 합

• AVG : 해당 컬럼 값들의 평균
  NULL값은 계산에서 빠지기 때문에 NVL을 통해 NULL값을 0처리 해주고 계산해야 한다

• COUNT : 조회한 ROW수를 반환
  NULL값은 반환하지 않는다

• MAX / NIN : 컬럼에서 최대 최소 값을 반환한다

활용예제

-- 1999 | 2000 | 2001 | 2002 | 2004
--	  3		1	   3	 0		1
-- 각 년도별 인원수를 출력하시오

SELECT
    COUNT(CASE WHEN TO_CHAR(HIRE_DATE,'YYYY')='1999' THEN HIRE_DATE END) AS "1999",
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2000,1,0)) AS "2000년",
    COUNT(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2001,1)) AS "2001년",
    COUNT(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2002,1)) AS "2002년",
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2003,1,0)) AS "2003년",
    SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2004,1,0)) AS "2004년"
FROM EMPLOYEE;