04_Function_1 : 오라클 내장 함수들 소개
#1 문자열 함수
1. 문자 함수
[사용법]
- 대문자 변환 : UPPER('문자열') 또는 UPPER(컬럼명) => SELECT UPPER(컬럼명) FROM 테이블명
- 소문자 변환 : LOWER('문자열') 또는 LOWER(컬럼명) => SELECT LOWER(컬럼명) FROM 테이블명
- 첫글자대문자 변환 : INITCAP('문자열') 또는 INITCAP(컬럼명) => SELECT INITCAP(컬럼명) FROM 테이블명
* 함수 뒤에는 항상 괄호
* 테스트 테이블 : DUAL
SELECT 'Oracle mania'
,UPPER('Oracle mania') AS 대문자
,LOWER('Oracle mania') AS 소문자
,INITCAP('Oracle mania') AS 첫글자대문자
FROM DUAL;
연습 1) 사원테이블(EMPLOYEE)에서 사원명(ENAME) 은 소문자, 직위(JOB) 은 첫글자만 대문자로 출력
SELECT LOWER(ENAME), INITCAP(JOB) FROM EMPLOYEE;
연습 2) SCOTT 사원을 조회해서 사원번호(ENO), 사원명(ENAME), 부서번호(DNO) 출력하기
단, 사원명이 대소문자 구분없이 조건절에 사용된다고 가정하고 작성하세요
SELECT ENO, ENAME, DNO FROM EMPLOYEE
WHERE ENAME = UPPER('SCOTT'); -- 만약 소문자 scott으로 들어오면 찾을수없어서 UPPER 사용
2. 문자의 길이를 출력하는 함수
[사용법]
LENGTH('문자열') 또는 LENGTH(컬럼명)
SELECT LENGTH('Oracle mania')
,LENGTH('오라클 매니아')
FROM DUAL;
3. 문자 조작 함수 : 문자열 붙이기 함수 또는 예약어
[사용법]
함수 : CONCAT('문자열1', '문자열2') 또는 CONCAT(컬럼명1, 컬럼명2)
예약어 : '문자열1' || '문자열2' 또는 컬럼명1 || 컬럼명2
SELECT 'Oracle'
,'mania'
, CONCAT('Oracle','mania') AS 함수
, 'Oracle' || 'mania'
FROM DUAL;
4. 문자 조작 함수 : 문자열 자르기 함수
SQL : 인덱스번호는 1부터 시작함
[사용법]
SUBSTR('문자열', 시작위치, 자를개수)
단, 시작위치가 음수이면 뒤에서 셈
SELECT SUBSTR('Oracle mania', 4, 3)
, SUBSTR('Oracle mania', -1, 1)
FROM DUAL;
5. 문자열에서 대상 문자를 찾아서 인덱스번호를 출력하는 함수
[사용법]
INSTR('문자열', '특정문자') 또는 INSTR(컬럼명, '특정문자')
SELECT INSTR('Oracle mania', 'a')
,INSTR('오라클매니아', '라')
FROM DUAL;
6. 컬럼에 어떤 문자를 왼쪽/오른쪽 붙이기 함수
[사용법]
왼쪽 채움 : LPAD(컬럼명, 자리수, '채울문자')
오른쪽 채움 : RPAD(컬럼명, 자리수, '채울문자')
SELECT LPAD(SALARY, 10, '*')
FROM EMPLOYEE;
SELECT RPAD(SALARY, 10, '*')
FROM EMPLOYEE;
7. 문자열의 양옆에 공백 제거하기 함수
[사용법]
LTRIM(컬럼명) : 왼쪽 공백제거
RTRIM(컬럼명) : 오른쪽 공백제거
TRIM(컬럼명) : 양쪽 공백제거 ★
SELECT 'Oracle mania'
, LTRIM(' Oracle mania ')
, RTRIM(' Oracle mania ')
, TRIM(' Oracle mania ')
FROM DUAL;
연습 3) SUBSTR 함수를 사용하여 사원들의 입사한 년도와(HIREDATE) 입사한 달만(HIREDATE) 출력하세요.
SELECT SUBSTR(HIREDATE, 1, 2), SUBSTR(HIREDATE, 4, 2)
FROM EMPLOYEE;
04_Function_2 : 오라클 내장 함수들 소개
#2 숫자 함수
1. 반올림
[사용법]
ROUND(숫자, 자리수) 또는 ROUND(컬럼, 자리수)
- 자리수가 음수일 경우 정수 첫번째 자리부터 반올림이 됨
- 소수 첫째자리가 0이라고 생각하면 쉬움
SELECT 98.7654
, ROUND(98.7654) -- 소수점 1st 자리에서 반올림
, ROUND(98.7654, 2) -- 소수점 3rd 자리에서 반올림
, ROUND(98.7654, 1) -- 소수점 2nd 자리에서 반올림
, ROUND(98.7654, -1) -- 정수 1st 자리에서 반올림
FROM DUAL;
2. 버림
[사용법]
TRUNC(숫자, 자리수) 또는 TRUNC(컬럼, 자리수)
SELECT 98.7654
, TRUNC(98.7654) -- 소수점 1st 자리부터 버림
, TRUNC(98.7654, 2) -- 소수점 3rd 자리부터 버림
, TRUNC(98.7654, -1) -- 정수 1st 자리부터 버림
FROM DUAL;
3. 내림 (참고)
[사용법]
FLOOR(숫자)
SELECT 98.7654
, FLOOR(98.7654)
FROM DUAL;
4. 올림
[사용법]
CEIL(숫자) 또는 CEIL(컬럼)
SELECT 98.7654
,CEIL(98.7654)
FROM DUAL;
5. 나머지 연산 함수
[사용법]
MOD(숫자, 나눌값) 또는 MOD(컬럼, 나눌값)
SELECT MOD(31,2)
FROM DUAL;
연습 1) 모든 사원의 급여를(SALARY) 각각 500으로 나눈 나머지를 계산해서 출력하세요
SELECT MOD(SALARY, 500) FROM EMPLOYEE;
04_Function_2 : 오라클 내장 함수들 소개
#3 날짜 함수
1. 현재 날짜보기 함수 : ★ ★ ★ ★ ★ SYSDATE ★ ★ ★ ★ ★
SELECT SYSDATE FROM DUAL;
예제 1) 오늘, 어제, 내일 날짜를 출력해보세요
★ 날짜형도 산술연산이 가능 ★
SELECT SYSDATE AS 오늘
, SYSDATE -1 AS 어제
, SYSDATE +1 AS 내일
FROM DUAL;
예제 2) 사원테이블에서 근무일수 계산해서 조회하기 : 현재시간 - 입사일
날짜 : 시간이 들어가있어서 소수점이 나옴
★ 버림 또는 반올림 해야함 ★
SELECT ROUND(SYSDATE - HIREDATE) FROM EMPLOYEE;
2. 두 날짜 사이의 개월수를 출력하는 함수 : TRUNC ( MONTHS_BETWEEN (현재날짜, 과거날짜) )
예제 3) 각 사원들이 근무한 개월 수 구하기
SELECT ENAME, SYSDATE, HIREDATE,
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS 개월수
FROM EMPLOYEE;
3. 개월수 더하기 함수 : ADD_MONTHS (날짜컬럼, 더할개월수)
예제 4) 입사일에서 6개월이 지난 시점에 날짜 구하기
SELECT ENAME, HIREDATE,
ADD_MONTHS(HIREDATE, 6) AS 월더하기
FROM EMPLOYEE;
4. 날짜 컬럼에서 데이터가 최초로 도래하는 요일의 날짜 출력 : NEXT_DAY (날짜컬럼, '찾는요일')
예제 5) 오늘을 기준으로 최초로 도래하는 토요일의 날짜 구하기
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '토요일')
FROM DUAL;
5. 날짜컬럼의 그 달(월)의 마지막 날의 날짜를 구해주는 함수 : LAST_DAY(날짜컬럼)
예제 6) 입사한(HIREDATE)날 달의 마지막 날짜 구하기
SELECT ENAME, HIREDATE,
LAST_DAY(HIREDATE)
FROM EMPLOYEE;
04_Function_3 : 오라클 내장 함수들 소개
#4 자료형 변환 함수
<DB 자료형>
1. 문자열 : VARCHAR2(가변문자열) ★, CHAR(고정문자열)
예) 10자리 문자열 , 고정일때 : 가나다 , 가나다...카 => 남은 자리수는 공간낭비
예) 10자리 문자열 , 가변일때 : 가나다 , 가나다...카 => 남은 자리수는 공간낭비 안함(자동축소)
2. 숫자 : NUMBER (실수, 정수) ★
3. 날짜 : DATE ★
4. 기타 : CLOB, BLOB 등
1. 날짜형 -> 문자열로 바꾸는 함수 : TO_CHAR(날짜컬럼명, '날짜포맷')
<날짜포맷>
ex) 'YYYY-MM-DD HH24:MI:SS'
YYYY (년도(4자리)), YY(년도(2자리))
MM (월(2자리))
DD (일(2자리))
HH (시(1~12까지 시간, 2자리))
HH24 (시(1~24까지 시간, 2자리)) ★
MI (분(2자리))
SS (초(2자리))
포맷을 제외한 / , -, : , ... 등등은 자유롭게 써도 됨
SELECT ENAME, HIREDATE,
TO_CHAR(HIREDATE, 'YY-MM') AS 단축날짜,
TO_CHAR(HIREDATE, 'YYYY-MM-DD HH24:MI:SS') AS 날짜
FROM EMPLOYEE;
연습 1) 현재 날짜와 시간을 표현하세요 (날짜 -> 문자열로 변환)
단, 출력날짜포맷 예) 2011/05/01, 15:07:43 으로 출력하세요
SELECT SYSDATE,
TO_CHAR(SYSDATE,'YYYY/MM/DD, HH24:MI:SS')
FROM DUAL;
2. 숫자형 -> 문자형로 변환하는 함수 : TO_CHAR(숫자컬럼명, '숫자포맷')
예제 2) 급여를 출력하면서 통화기호를 (W, $ 등) 포함해서 출력하세요
<숫자포맷>
ex) 'L999,999', 'L000,000'
L : 각 나라별 통화 기호를(W, $ 등) 자동으로 붙여줌 (S/W 설치시 통화지정)
9 : 3자리를 잡고 자리수가 모자라면 0을 채워주지는 않음 ★
0 : 3자리를 잡고 자리수가 모자라면 0을 채움
, : 각 통화의 중간에 쉼표(,)를 자동으로 추가해줌
SELECT ENAME, SALARY,
TO_CHAR(SALARY, 'L999,999'),
TO_CHAR(SALARY, 'L000,000')
FROM EMPLOYEE;
3. 문자형 -> 날짜형으로 변환하는 함수 : TO_DATE('날짜', '날짜포맷') => 날짜포맷은 생략가능
얘는 WHERE절 써서 적어야함!
화면출력 : SELECT 위치에 작성
함수 사용 위치 : SELECT 다음, 또는 WHERE (조건절은 화면에 나타나는게 아님)
포맷이 YYYYMMDD 라도 데이터 자체가 이미 슬래쉬가 붙어있어서 슬래쉬가 붙어서 나옴
SELECT ENAME, HIREDATE
FROM EMPLOYEE
-- WHERE HIREDATE = '19810220';
WHERE HIREDATE = TO_DATE('19810220', 'YYYYMMDD');
4. 문자형 -> 숫자형으로 변환하는 함수 : TO_NUMBER('문자', '숫자포맷' = '999,999')
예제 4) 숫자형태의 통화(돈) 빼기
SELECT TO_NUMBER('100,000','999,999') - TO_NUMBER('50,000','999,999')
FROM DUAL;
04_Function_3 : 오라클 내장 함수들 소개
#5 일반 함수
1. NULL -> 숫자(문자)로 변경하는 함수 NVL (NULL 포함된 컬럼, 특정숫자(문자))
SELECT ENAME, SALARY, COMMISSION
, SALARY*12 + NVL(COMMISSION, 0) AS 연봉_보너스
FROM EMPLOYEE;
2. SQL에서 사용하는 조건문 함수 : DECODE
사용법)
DECODE(컬럼, 값1, '결과1'
, 값2, '결과2'
...
, '기본결과') AS 부서명
: 컬럼이 값1이면 결과1이 나오고, 값2이면 결과2가 나오고, ... 모두 아니면 기본결과가 화면에 출력됨
예제 5) DECODE 를 이용해서 부서명을 출력하세요
10 '회계부', 20 '연구소', 30 '판매부', 40 '운영부'
SELECT ENAME, DNO
,DECODE(DNO, 10, '회계부'
, 20, '연구소'
, 30, '판매부'
, 40, '운영부'
, '디폴트') AS 부서명
FROM EMPLOYEE;
3. SQL에서 사용하는 조건문 함수 : CASE WHEN
장점) 조건식에 부등호 등 사용가능(DECODE 함수는 안됨)
사용법)
CASE WHEN 컬럼=값1 THEN '결과1'
WHEN 컬럼=값2 THEN '결과2'
...
ELSE '기본결과'
END
: 컬럼이 값1이면 결과1이 나오고, 값2이면 결과2가 나오고, ... 모두 아니면 기본결과가 화면에 출력됨
예제 6) CASE WHEN 을 이용해서 부서명을 출력하세요
10 '회계부', 20 '연구소', 30 '판매부', 40 '운영부'
SELECT ENAME, DNO
,CASE WHEN DNO=10 THEN '회계부'
WHEN DNO=20 THEN '연구소'
WHEN DNO=30 THEN '판매부'
WHEN DNO=40 THEN '운영부'
ELSE '디폴트'
END AS 부서명
FROM EMPLOYEE;
<문자열 함수>
1. 대소문자 변경
- 대문자 변환 : UPPER('문자열')
- 소문자 변환 : LOWER('문자열')
- 첫글자대문자 변환 : INITCAP('문자열')
2. 문자 길이 : LENGTH('문자열')
3. 문자 붙이기
함수 : CONCAT('문자열1', '문자열2')
예약어 : '문자열1' || '문자열2'
4. 문자 자르기 : SUBSTR('문자열', 시작위치, 자를개수) (단, 시작위치가 음수이면 뒤에서 셈)
5. 인덱스번호 찾기 : INSTR('문자열', '특정문자')
6. 문자 채우기
왼쪽 채움 : LPAD(컬럼명, 자리수, '채울문자')
오른쪽 채움 : RPAD(컬럼명, 자리수, '채울문자')
7. 공백 제거
LTRIM(컬럼명) : 왼쪽 공백제거
RTRIM(컬럼명) : 오른쪽 공백제거
TRIM(컬럼명) : 양쪽 공백제거 ★
<숫자 함수>
1. 반올림 : ROUND(숫자, 자리수)
2. 버림 : TRUNC(숫자, 자리수)
3. 내림 : FLOOR(숫자)
4. 올림 : CEIL(숫자)
5. 나머지 연산 : MOD(수, 나눌값)
<날짜 함수>
1. 현재 날짜보기 함수 : ★ SYSDATE ★
2. 두 날짜 사이의 개월수를 출력하는 함수 : TRUNC ( MONTHS_BETWEEN (현재날짜, 과거날짜) )
3. 개월수 더하기 함수 : ADD_MONTHS (날짜컬럼, 더할개월수)
4. 날짜 컬럼에서 데이터가 최초로 도래하는 요일의 날짜 출력 : NEXT_DAY (날짜컬럼, '찾는요일')
5. 날짜컬럼의 그 달(월)의 마지막 날의 날짜를 구해주는 함수 : LAST_DAY(날짜컬럼)
<자료형 변환 함수>
1. 날짜형 -> 문자열로 바꾸는 함수 : TO_CHAR(날짜컬럼명, '날짜포맷') ★
2. 숫자형 -> 문자형로 변환하는 함수 : TO_CHAR(숫자컬럼명, '숫자포맷') ★
3. 문자형 -> 날짜형으로 변환하는 함수 : TO_DATE('날짜', '날짜포맷') => 날짜포맷은 생략가능
4. 문자형 -> 숫자형으로 변환하는 함수 : TO_NUMBER('문자열', '숫자')
<일반 함수>
1. NULL -> 숫자(문자)로 변경하는 함수 NVL (NULL 포함된 컬럼, 특정숫자(문자))
2. SQL에서 사용하는 조건문 함수
DECODE(컬럼, 값1, '결과1'
, 값2, '결과2'
...
, '기본결과') AS 부서명
CASE WHEN 컬럼=값1 THEN '결과1'
WHEN 컬럼=값2 THEN '결과2'
...
ELSE '기본결과'
END
'SQL' 카테고리의 다른 글
| #45 [SQL] 그룹함수(그룹 함수 5개, 부분 집계 ORDER BY, 조건절 HAVING 예약어) (0) | 2024.02.29 |
|---|---|
| #45 [SQL] 함수 연습문제 (0) | 2024.02.28 |
| #44 [SQL] SELECT 조건절 연습문제 (0) | 2024.02.28 |
| #43 [SQL] 조건절, 예약어 (BETWEEN/NOT BETWEEN, IN/NOT IN, LIKE(%, _)/NOT LIKE, NULL 조회, ORDER BY) (0) | 2024.02.27 |
| #43 [SQL] 계정 접속, ★ 조회 (SELECT) ★ (1) | 2024.02.27 |
