SLQ 활용 2. 단일 행 함수

주요 단일 행 함수

구분	입력 값 타입	종류	리턴 값 타입
문자(열) 함수	CHARACTER	LPAD/RPAD , LTRIM/RTRIM/TRIM , SUBSTR	CHARACTER
		INSTR , LENGTH/LENGTHB	NUMBER
숫자 함수	NUMBER	ROUND , TRUNC	NUMBER
날짜 함수	DATE	ADD_MONTHS , SYSDATE	DATE
		MONTHS_BETWEEN	NUMBER
타입 변환 함수	ANY	TO_CHAR , TO_DATE , TO_NUMBER	ANY
기타 함수	ANY	MVL, DECODE	ANY

 

표는 많이 사용되는 주요 단일 행 함수를 나타낸 것입니다. 

 

이제부터 각 함수에 대한 개념과 간단한 사용예시를 살펴보도록 하겠습니다.

 

사용 예시에서는 Oracle의 기본 계정인 scott 계정을 이용합니다.

---

▶ 목차

1. 문자(열) 함수
   • LPAD / RPAD (컬럼명, 길이, 값)
   • LTRIM / RTRIM ('문자열','제거할 문자(열)') - 왼쪽/오른쪽 에서 부터 제거 - 문자열 제거. 패턴으로 제거가 아님!
   • TRIM('제거할 문자 하나' FROM '문자열') - 양쪽 모두 제거
   • SUBSTR(컬럼 or 문자열, 시작위치[, 반환할 갯수])
   • INSTR(컬럼 or 문자열, 찾으려는 문자(열)[, 시작위치[, 횟수]])
   • LENGTH / LENGTHB
2. 숫자 함수
   • ROUND / TRUNC(컬럼 or 숫자[, 소수점 자리지정])
   • CEIL / FLOOR ()
3. 날짜 함수
   • ADD_MONTHS(날짜, 더하려는 개월 수)
   • MONTHS_BETWEEN(날짜 1, 날짜 2)
   • SYSDATE
4. 타입 변환함수
   • TO_CHAR(입력 타입[, 형식])
   • 날짜 표현 형식
   • TO_DATE(입력 타입[, 형식])
   • TO_NUMBER(입력 타입[, 형식])
5. 기타 함수
   • NVL(컬럼, 해당 컬럼의 NULL 값을 변환할 값)
   • DECODE
   • CASE

---

<1. 문자열 함수>

LPAD / RPAD (컬럼명, 길이, 값)

• 해당 컬럼을 길이만큼 오른쪽/왼쪽 정렬한다. (기본적으로 Oracle은 글자 = 왼쪽정렬, 숫자 = 오른쪽정렬 입니다.)
• 빈 공간은 값을 채운다.

Q1) 빈 자리에는 *을 넣어서 7자리만큼 오른쪽 정렬하자.

SELECT LPAD(ENAME, 7, '*') FROM EMP;

Q2) 빈 자리에는 * 을 넣어서 10자리만큼 왼쪽 정렬하자.

SELECT RPAD(ENAME, 10, '*') FROM EMP;

 

 

LTRIM / RTRIM ('문자열','제거할 문자(열)')

• 문자열 제거. (패턴으로 제거하는 것이 아닙니다!!)

Q1) ‘xyxzyyTech6 327’ 의 왼쪽에서 xyz를 제거하자.

SELECT LTRIM(‘xyxzyyTech6 327’, ‘xyz’) FROM DUAL;

Q2) ‘xyxzyyTech6 327’ 의 오른쪽에서 숫자를 제거하자.

SELECT RTRIM(‘xyxzyyTech6 327’, ‘0123456789’) FROM DUAL;

Q3) ‘xyxzyyTech6 327’ 의 오른쪽에서 공백 및 숫자를 제거하자. (제거하려는 문자열에 공백이 포함)

SELECT RTRIM(‘xyxzyyTech6 327’, ‘ 0123456789’) FROM DUAL;

 

 

TRIM('제거할 문자 하나' FROM '문자열')

• 양쪽 모두 제거

Q1) ‘xyxzyyTech6 327xx’ 에서 양쪽의 x를 제거하자.

SELECT TRIM('x' FROM 'xyxzyyTech6 327xx') FROM DUAL;

Q2) ‘xyxzyyTech6 327xx’ 에서 양쪽의 xy를 제거하자.

SELECT TRIM('xy' FROM 'xyxzyyTech6 327xx') FROM DUAL;

error -> TRIM 은 제거할 문자를 '하나' 만 선택할 수 있습니다.

 

 

SUBSTR(컬럼 or 문자열, 시작위치[, 반환할 갯수])

• 시작 위치부터 [반환할 갯수 만큼] 문자열을 잘라서 반환
• 시작위치
  • = 0 or 1 : 처음
  • > 0 : 끝 방향으로 지정한 수 만큼
  • < 0 : 시작 방향으로 지정한 수 만큼
• 반환할 갯수
  • < 0 : NULL 반환

Q1)

SELECT ENAME, SUBSTR(ENAME, 1, 2) FROM EMP;

Q2)

SELECT ENAME, SUBSTR(ENAME, -1) FROM EMP;

 

INSTR(컬럼 or 문자열, 찾으려는 문자(열)[, 시작위치[, 횟수]])

• 찾는 문자(열)이 [시작 위치부터 [횟수만큼]] 나타난 시작 위치 반환
• 시작 위치
  • > 0 : 시작부터 끝 방향으로
  • < 0 : 끝부터 시작 방향으로

Q1)

SELECT ENAME, INSTR(ENAME, 'S', 1, 1) FROM EMP;

Q2)

SELECT ENAME, INSTR(ENAME, 'L', -1, 2) FROM EMP;

 

 

LENGTH / LENGTHB

• 주어진 컬럼의 문자열(값)의 길이를 반환 (NUMBER/BYTE) (**Oracle 에서 한글은 3BYTE 입니다.)
• 컬럼이 CHAR 인 경우 데이터의 길이와 상관없이 컬럼 전체의 길이 반환

Q1)

SELECT ENAME, LENGTH(ENAME) FROM EMP;

Q2)

SELECT ENMAE, LENGTHB(ENAME) FROM EMP;

 

---

<2. 숫자 함수>

ROUND / TRUNC(컬럼 or 숫자[, 소수점 자리지정])

• 지정한 자리수 에서 반올림 / 버림
• 자리 지정하는 값은 반드시 정수 값 사용 (생략하면 0)
  • > 0 : 소수점 이하 자리
  • < 0 : 소수점 이상 자리

Q1)

SELECT ROUND(125.335) FROM DUAL;

Q2)

SELECT ROUND(125.335, 1) FROM DUAL;

Q3)

SELECT TRUNC(125.335, 1) FROM DUAL;

Q4) -1 로 1의 자리 숫자를 버린 모습입니다.

SELECT TRUNC(125.335, -1) FROM DUAL;

 

 

CEIL / FLOOR ()

• 올림 / 버림

Q1)

SELECT CEIL(125.355) FROM DUAL;

Q2)

SELECT FLOOR(125.335) FROM DUAL;

Q3)

SELECT SAL, CEIL(SAL/1000) FROM EMP;

Q4)

SELECT SAL, FLOOR(SAL/1000) FROM EMP;

 

---

<3. 날짜 함수>

ADD_MONTHS(날짜, 더하려는 개월 수)

• 지정한 날짜부터 개월 수를 더한 날짜 반환

Q1) 입사한지 20년이 되는 달을 구하자.

SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 240) FROM EMP;

 

 

MONTHS_BETWEEN(날짜 1, 날짜 2)

• 지정한 두 날짜 사이의 월 수를 반환
  • 날짜 1 > 날짜 2 : 양수 반환
  • 날짜 1 < 날짜 2 : 음수 반환

Q1)

SELECT SYSDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;

 

 

SYSDATE

• 오늘 날짜

Q1)

SELECT SYSDATE FROM DUAL;

Q2) 00년 1월 1일을 기준으로, 10년 이상 근무한 사람의 이름, 직업, 입사일, 근무년수 를 구하자.

SELECT ENAME, JOB, HIREDATE,
	TRUNC(MONTHS_BETWEEN('2000/01/01', HIREDATE)/12)
FROM EMP
WHERE MONTHS_BETWEEN('2000/01/01', HIREDATE) > 120;

 

---

<4. 타입 변환 함수>

NUMBER 와 DATE는 직접 형변환이 안됩니다!!

TO_CHAR(입력 타입[, 형식])

• 숫자 표현 형식

형식	설명
9	자리 수 지정
0	남는 자리를 0 으로 표시
$ 또는 L	통화기호 표시
. 또는 ,	지정한 위치에 . 또는 , 표시
EEEE	과학 지수 표기법

Q1) 자리수 지정

SELECT TO_CHAR(1234,'99999') FROM DUAL;

Q2) 남는 자리를 0으로 표시

SELECT TO_CHAR(1234,'09999') FROM DUAL;

Q3) 통화기호 표시

SELECT TO_CHAR(1234,'L9999') FROM DUAL;

Q4) 지정한 위치에 . 또는 , 표시

SELECT TO_CHAR(1234,'99,999') FROM DUAL;

Q5) 과학 지수 표기법

SELECT TO_CHAR(1234,'9.9EEEE') FROM DUAL;

 

날짜 표현 형식

형식	설명
YYYY / YY / YEAR	년도 (4자리 숫자 / 뒤 2자리 숫자 / 문자)
MONTH / MON / MM / RM	달 (이름 / 약어 / 숫자 / 로마기호)
DDD / DD / D	일 (1년 기준 / 1달 기준 / 1주 기준)
Q	분기 (1, 2, 3, 4)
DAY / DY	요일(이름 / 약어 이름)
HH(12) / HH24	12시간 / 24시간
AM / PM	오전 / 오후
MI	분 (0 ~ 59)
SS	초 (0 ~ 59)

Q1)

SELECT TO_CHAR(SYSDATE, 'PM HH24:MI:SS') FROM DUAL;

Q2)

SELECT TO_CHAR(SYSDATE, 'MON DY, YYYY') FROM DUAL;

Q3)

SELECT TO_CHAR(SYSDATE, 'YYYY-FMMM-DD DAY') FROM DUAL;

Q4)

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

Q5)

SELECT TO_CHAR(SYSDATE, 'YEAR, Q') FROM DUAL;

 

TO_DATE(입력 타입[, 형식])

• 날짜 형식

Q1)

SELECT TO_DATE('20100101','YYYYMMDD') FROM DUAL;

Q2)

SELECT TO_CHAR(TO_DATE('20100101','YYYYMMDD'),'YYYY,MON') FROM DUAL;

Q3)

SELECT TO_CHAR(TO_DATE('041030 143000','YYMMDD HH24MISS'),'YY-MM-DD PM FMHH:MI:SS')
FROM DUAL;

Q4)

SELECT TO_CHAR(SYSDATE, 'YY-MM-DD PMFM HH:MI:SS') FROM DUAL;

 

 

TO_NUMBER(입력 타입[, 형식])

• 변환 되었을 때 숫자인 문자열만 가능!
• EX) '100' -> 100 (문자열 100을 숫자 100으로)

Q1)

SELECT ENAME,
	TO_NUMBER(SUBSTR(HIREDATE, 1, 2)) AS 년,
	TO_NUMBER(SUBSTR(HIREDATE, 4, 2)) AS 월
FROM EMP;

Q2) ERROR 인 경우!

SELECT TO_NUMBER('ABC') FROM DUAL; --'ABC' 는 숫자로 변환될 수 없는 문자입니다!

 

---

<5. 기타 함수>

NVL(컬럼, 해당 컬럼의 NULL 값을 변환할 값)

• NULL이 없는 경우 해당 COLUMN 값 변환
• NULL을 변환한 값은 해당 COLUMN의 데이터 타입과 동일한 타입

Q1)

SELECT ENAME, COMM, NVL(COMM, 0) FROM EMP;

Q2) 연봉 계산

SELECT ENAME, SAL * 12 + NVL(COMM, 0) FROM EMP;

 

 

DECODE(컬럼 or 문자열, 비교값, 같을 때 반환값[, 비교값, 반환값...][, 다를 때 기본값])

• 프로그래밍 언어의 switch문 처럼 사용
• switch(식 or 값)

Q1)

SELECT ENAME, JOB, DECODE(JOB,'MANAGER','0') FROM EMP;

Q2)

SELECT ENAME, JOB, DECODE(JOB,'MANAGER','IS MANAGER','NOT MANAGER') FROM EMP;

Q3)

SELECT ENAME, JOB, DECODE(JOB,'MANAGER''2','PRESIDENT','1') FROM EMP;

Q4)

SELECT ENAME, JOB, DECODE(JOB,'MANAGER','2','PRESIDENT','1') FROM EMP;

Q5)

SELECT ENAME, JOB, DECODE(JOB,'MANAGER','2','PRESIDENT','1','ANALYST','3','4') FROM EMP;

 

 

CASE 컬럼 or 문자열,

        WHEN 조건 THEN 같을 때 반환 값

       [WHEN ....    THEN ....]

       [ELSE 다를 때 기본값]

END

• IF 처럼 사용
• ELSE 기본값을 지정하지 않으면, 일치하지 않거나 조건을 만족시키지 않을 때 NULL 값 반환

Q1) 

SELECT ENAME, SAL,
	CASE
		WHEN SAL <= 1000 THEN '초급'
		WHEN SAL <= 2000 THEN '중급'
		ELSE '고급'
	END
FROM EMP;