SQL_Additional_SELECT_단일 행 함수

함수 유형: 반환 결과에 따라 단일 행 함수와 그룹 함수로 구분

단일 행 함수: Input N -> Output N / 그룹 함수: Input N -> Output 1

# 문자열 함수

# 문자열 함수 LENGTH(string) > NUMBER 반환 

-- LENGTH
SELECT	LENGTH(CHARTYPE),
	LENGTH(VARCHARTYPE)
FROM	COLUMN_LENGTH;

(CHARTYPE은 픽스되어있는 길이의 CHAR값을 활용할 때 사용한다!)

 

# 문자열 함수 INSTR(문자열, 찾고싶은 문자열, [POSITION, [OCCURENCE]])

-- INSTR . 바로 앞의 'c' 찾기
SELECT	EMAIL, 
	INSTR(EMAIL, 'c', -1, 2) 위치
FROM 	EMPLOYEE;

-- INSTR . 바로 앞의 'c' 찾기(함수중첩을 통해)
SELECT 	EMAIL,
	INSTR(EMAIL, 'c', INSTR(EMAIL, '.')-1) 위치
FROM	EMPLOYEE;

POSITION은 찾고 싶은 위치 

OCCURENCE 반복횟수(몇 번째 것을 찾을 것인가)

 

# 문자열 함수 LPAD/RPAD 정렬의 효과(채워넣기)

 

LPAD(string, N, [str]) / RPAD(string, N, [str])

N은 전체 길이, [str]은 채워넣을 값

-- LPAD
SELECT	EMAIL AS 원본데이터,
	LENGTH(EMAIL) AS 원본길이,
	LPAD(EMAIL, 20, '.') AS 적용결과,
	LENGTH(LPAD(EMAIL, 20, '.')) AS 결과길이
FROM	EMPLOYEE;

 

# 문자열 함수 LTRIM, RTRIM, TRIM (자르기)

 

LTRIM(string, str) / RTRIM(string, str) /

TRIM(string, str) 옵션으로 LEADING | TRAILING | BOTH 로 다 처리가능!

TRIM(LEADING '0' FROM '00TECH000') > 지우고 싶은 문자열 FROM 문자열

str은 패턴이 아닌 열거의 형식을 뜻한다! EX) xyz는 x 또는 y 또는 z 이다

-- LTRIM
SELECT	LTRIM('   TECH') FROM DUAL;
SELECT	LTRIM('   TECH', ' ') FROM DUAL;
SELECT	LTRIM('123123TECH', '123') FROM DUAL;
SELECT	LTRIM('xyzxkaxaxkTECH', 'xyzka') FROM DUAL;
SELECT 	LTRIM('6156TECH442221', '0123456789') FROM DUAL; -- 왼쪽만 제거

-- TRIM
SELECT 	TRIM(LEADING '0' FROM '00TECH000') FROM DUAL;
SELECT 	TRIM(TRAILING '0' FROM '00TECH000') FROM DUAL;
SELECT 	TRIM(BOTH '0' FROM '00TECH000') FROM DUAL;

CF. 더미테이블은 FROM DUAL

 

# 문자열 함수 SUBSTR(string, position, [length])

length는 얼마나 가져올 것인가(default는 끝까지)

-- SUBSTR
SELECT 	SUBSTR('This is a test', 6, 2) FROM DUAL;
SELECT 	SUBSTR('TechOnTheNet', -3, 3) FROM DUAL; 
SELECT	SUBSTR('이것은 연습입니다', 3, 4) FROM DUAL;
SELECT 	SUBSTR(TO_DATE('20200120','YYYY/MM/DD'), 0, 2) FROM DUAL;

# 숫자 함수

# 숫자 함수 ROUND( number, [decimal_places]) 반올림 

decimal_places 0 보다 크면 소수점 이하, 0 보다 작으면 숫자 자리 반올림

-- ROUND
SELECT 	ROUND(125.315) FROM DUAL;
SELECT 	ROUND(125.315, -1) FROM DUAL;
SELECT	ROUND(125.315, 3) FROM DUAL;
SELECT	ROUND(-125.315, 2) FROM DUAL;

# 숫자 함수 TRUNC( number, [decimal_places]) 내림

-- TRUNC
SELECT 	TRUNC(125.315) FROM DUAL;
SELECT 	TRUNC(125.315, -1) FROM DUAL;
SELECT	TRUNC(125.315, 3) FROM DUAL;
SELECT	TRUNC(-125.315, 2) FROM DUAL;

# 날짜 함수

# 날짜 함수 SYSDATE > 현재 시각 반환

# 날짜 함수 ADD_MONTHS(date, N) > 지정한 만큼 달 수 더한 DATE 반환

-- ADD_MONTHS
SELECT	EMP_NAME,
	HIRE_DATE,
	ADD_MONTHS(HIRE_DATE, 240)
FROM	EMPLOYEE;

# 날짜 함수 MONTHS_BETWEEN(date1, date2) > 두 날짜 사이의 월 수 반환

date1 > date2 양수 반환, date1 < date2 음수 반환

-- MONTHS_BETWEEN
-- 근속년수가 20년 이상인 직원들 정보
SELECT	EMP_NAME AS 이름, 
	HIRE_DATE AS 입사일,
	ROUND(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12) AS 근속년수,
	JOB_ID	AS 직급,
	DEPT_ID AS 부서
FROM	EMPLOYEE
WHERE	MONTHS_BETWEEN(SYSDATE, HIRE_DATE) >= 240;

# 데이터 타입 변환

묵시적 변환 / 명시적 변환

묵시적 변환에서 COLUMN이 변환, 명시적 변환에서 VALUE의 변환 

이 때, 묵시적 형 변환은 성능적으로 떨어짐

-- 묵시적 형 변환
-- CHAR를 NUMBER로 비교
SELECT	EMP_NAME,
		SALARY
FROM	EMPLOYEE
WHERE 	EMP_ID = 100; 

-- 명시적 형 변환
-- CHAR를 NUMBER로 비교
SELECT	EMP_NAME,
		SALARY
FROM	EMPLOYEE
WHERE 	EMP_ID = TO_CHAR(100); 

-- 양식
-- 9 : 자리수 지정
SELECT	TO_CHAR(123, '99999') FROM DUAL;

-- 0 : 남는 자리 0으로 표시
SELECT	TO_CHAR(123, '09999') FROM DUAL;

-- . , : 지정한 위치에 . , 표시
SELECT 	TO_CHAR(1234, '99,999') FROM DUAL;

-- EEEE : 과학적 지수 표시
SELECT 	TO_CHAR(1000, '9.9EEEE') FROM DUAL;

-- $ l 통화기호 표시
SELECT 	TO_CHAR(1234, 'L999999') FROM DUAL;

SELECT	TO_CHAR(SYSDATE, 'PM HH24:MI:SS') FROM DUAL;
SELECT	TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL;
SELECT 	TO_CHAR(SYSDATE, 'MON DY, YYYY') FROM DUAL;
SELECT	TO_CHAR(SYSDATE, 'YYYY-fmMM-DD DAY') FROM DUAL; -- fm 이후 01 > 1
SELECT	TO_CHAR(SYSDATE, 'Year Q') FROM DUAL; -- Twenty Twenty 1

-- 원하는 양식 주기
SELECT	EMP_NAME AS 이름,
	TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') AS 입사일
FROM	EMPLOYEE
WHERE	JOB_ID = 'J7';

-- 원하는 양식 주기
SELECT	EMP_NAME AS 이름,
	SUBSTR(HIRE_DATE, 1, 2)||'년 '||
	SUBSTR(HIRE_DATE, 4, 2)||'월 '||
	SUBSTR(HIRE_DATE, 7, 2)||'일' AS 입사일
FROM	EMPLOYEE
WHERE 	JOB_ID = 'J7';

-- DATE와 CHAR 비교
SELECT	EMP_NAME
FROM	EMPLOYEE
WHERE	HIRE_DATE = '04/04/30';

-- DATE와 CHAR 비교(시분초까지 있기 때문에)
SELECT	EMP_NAME
FROM	EMPLOYEE
WHERE	HIRE_DATE = '90/04/01';

-- CHAR TO DATE
SELECT	TO_DATE('20100101', 'YYYYMMDD') FROM DUAL;
SELECT	TO_CHAR(TO_DATE('20100101', 'YYYYMMDD'), 'YYYY, MON') FROM DUAL;
SELECT	TO_DATE('041030 143000', 'YYMMDD HH24MISS') FROM DUAL;
SELECT 	TO_DATE('980630', 'YYMMDD') FROM DUAL;
SELECT	TO_CHAR(TO_DATE('980630', 'RRMMDD'),
		'YYYY.MM.DD') FROM DUAL;

RR 써야 1998로 나옴(YY 쓰면 2098)

-- TO_NUMBER
SELECT	TO_NUMBER(SUBSTR(EMP_NO, 1, 6)) + TO_NUMBER(SUBSTR(EMP_NO, 8)) AS 결과
FROM 	EMPLOYEE
WHERE	EMP_ID = '101';

 

# NVL(exp1, exp2) 함수 > exp1이 NULL이면 exp2 반환

-- NVL(exp1, exp2) exp1이 NULL일 경우 exp2 반환
SELECT	EMP_NAME, SALARY, NVL(BONUS_PCT, 0)
FROM 	EMPLOYEE
WHERE	SALARY > 3500000;

-- 보너스가 NULL이면 0으로 처리하기
SELECT	EMP_NAME, 
		(SALARY * 12) +((SALARY * 12) * NVL(BONUS_PCT, 0))
FROM	EMPLOYEE
WHERE	SALARY > 3500000;

# DECODE 함수 <중요!> ANSI 표준 다른 함수도 알아야함

DECODE(expr, search1, result1 [ , searchN, resultN, ... ] [, default])

-- DECODE 통한 성별 구분
SELECT	EMP_NAME 
      , EMP_NO
      , SALARY
      , DECODE(SUBSTR(EMP_NO, 8, 1), '1', 'M', '2', 'W', '3', 'M', '여') AS 성별
FROM	EMPLOYEE
WHERE	JOB_ID = 'J7';

-- WHERE + DECODE
SELECT	*
FROM	EMPLOYEE
WHERE	DECODE(SUBSTR(EMP_NO, 8, 1), '2', 'W', '4', 'W') IS NOT NULL;

-- DECODE 활용하기
SELECT		EMP_ID
	      , EMP_NAME
	      , MGR_ID
              , DECODE(MGR_ID, NULL, '관리자', '직원') AS 직원구분
FROM		EMPLOYEE
WHERE		JOB_ID = 'J4';

-- NVL2 활용하기
SELECT		EMP_ID
	      , EMP_NAME
	      , MGR_ID
	      , NVL2(MGR_ID, '직원', '관리자') AS 직원구분
FROM		EMPLOYEE
WHERE		JOB_ID = 'J4';

-- 직급별 인상급여 확인하기
-- J7 20% 인상, J6 15% 인상, J5 5% 인상, 나머지 직급은 해당 급여
-- 이름, 직급, 급여 , 인상급여를 조회
SELECT		EMP_NAME 
	      , JOB_ID
	      , SALARY
	      , SALARY * DECODE(JOB_ID, 'J7', 1.2, 'J6', 1.15 , 'J5', 1.05 , 1.0) AS 인상급여
FROM 		EMPLOYEE;

# CASE 함수

CASE expr WHEN search1 THEN result1 [WHEN... THEN...] [ELSE default] END

CASE WHEN condition1 THEN result1 [WHEN... THEN...] [ELSE default] END

-- 직급별 인상급여 확인하기
-- J7 20% 인상, J6 15% 인상, J5 5% 인상, 나머지 직급은 해당 급여
-- 이름, 직급, 급여 , 인상급여를 조회
SELECT		EMP_NAME
	      , JOB_ID
	      , SALARY
	      , CASE JOB_ID WHEN 'J7' THEN SALARY * 1.2 
		  		WHEN 'J6' THEN SALARY * 1.15
		  		WHEN 'J5' THEN SALARY * 1.05
		  		ELSE SALARY
	        END AS 인사급여
FROM 		EMPLOYEE;

-- CONDITION 활용
SELECT		EMP_NAME
	      , JOB_ID
	      , SALARY
	      , CASE WHEN JOB_ID = 'J7' THEN SALARY * 1.2 
		     WHEN JOB_ID = 'J6' THEN SALARY * 1.15
		     WHEN JOB_ID = 'J5' THEN SALARY * 1.05
		     ELSE SALARY
	        END AS 인사급여
FROM 		EMPLOYEE;

-- 직원들의 급여등급을 확인학하고 싶다.
-- 급여가 300 이하면 초급 400 이하면 중급 400 이상이면 고급으로 간주한다.
-- 직원들의 이름, 급여, 급여등급을 출력하라
SELECT		EMP_NAME
              , SALARY
              , CASE WHEN SALARY <= 3000000 THEN '초급'
                     WHEN SALARY <= 4000000 THEN '중급'
                     ELSE '고급'
             	END AS 급여등급
FROM		EMPLOYEE;

CF. 문자열 다루기

-- 사원 테이블에서 사원의 이름, 메일아이디 조회
SELECT		EMP_NAME
              , EMAIL
              , SUBSTR(EMAIL, 0, INSTR(EMAIL, '@')-1) AS 메일아이디
FROM 		EMPLOYEE;