단일 행 함수
- 인수(argument)를 받고 하나의 결과를 리턴
- 리턴될 각각의 행에 적용
- 행별로 하나의 결과를 리턴
- 데이터타입을 수정 가능
- 중첩(nested) 가능
- 문자 조작 함수
LOWER( ): 문자열을 소문자로 변환
INITCAP( ): 문자열의 첫 문자를 대문자로
UPPER( ): 문자열을 대문자로 변환
How? 어떻게 쓰는 거라고?
LOWER (last_name) : 모든 문자를 소문자로 출력
INITCAP (last_name) : 첫 문자만 대문자로, 나머지는 소문자로 출력
UPPER (last_name) : 모든 문자를 대문자로 출력
LENGTH ( ): 문자열 길이, INSTR ( ): 문자열에서 해당 문자의 위치
How? 어떻게 쓰는 거라고?
LENGTH (first_name) : first_name의 문자열 길이 출력
INSTR (first_name, 'a') : first_name에서 'a'문자의 위치를 출력
SUBSTR ( ) : 문자열 자르기, CONCAT ( ) : 문자열 붙이기
How? 어떻게 쓰는 거라고?
SUBSTR (first_name, 1, 3) : first_name을 첫 번째 문자를 시작으로 3자리까지 출력
CONCAT (first_name, last_name) : first_name과 last_name을 붙여서 출력
RPAD ( ): 오른쪽 남은 자리에 문자 채우기, LPAD( ) : 왼쪽 남은 자리에 문자 채우기
How? 어떻게 쓰는 거라고?
RPAD(first_name, 10, ' - ') : first_name열을 추출할 때 10자리로 왼쪽만 추출하고
first_name의 길이만큼을 제외하고 남은 자리는 ' -'로 채워서 출력
LPAD(salary, 10, ' * ') : salary열을 추출할 때 10자리로 오른쪽만 추출하고
salary의 길이만큼을 제외하고 남은 자리는 '*'로 채워서 출력
RTRIM ( ): 오른쪽 공백 제거 / 오른쪽에 있는 문자 제거
LTRIM( ) : 왼쪽 공백 제거 / 왼쪽에 있는 문자 제거
How? 어떻게 쓰는 거라고?
LTRIM( 'JavaSpecialist', 'Java' ) : 'JavaSpecialist'문자열에서 왼쪽에 있는 'Java' 문자 제거
LTRIM( ' JavaSpecialist' ) : 왼쪽에 있는 공백 제거
TRIM( ' JavaSpecialist ' ) : 양쪽에 있는 공백 제거
REPLACE ( ) : 특정 문자열에서 지정한 문자열을 새로운 문자열로 변경
How? 어떻게 쓰는 거라고?
REPLACE( 'JavaSpecialist', 'Java', 'BigData' ) : 'JavaSpecialist' 문자열에서 'Java'를 'BigData'로 변경
REPLACE( 'Java Specialist', ' ', ' ' ) : 'Java Specialist' 문자열 가운데에 존재하는 공백을 제거
TRANSLATE( 'javaspecialist', : 'javaspecialist' 문자열 중에 abc...z를 순서대로 def...c로 변경
'abcdefghjklmnopqrstuvwxyz',
'defghijklmnopqrstuvwxyzabc' )
숫자함수
ROUND ( column | expression, n ) : 열 표현식 또는 값을 소수점 n 자리까지 반올림한다
n이 생략(or 0)되면 소수점이 사라진다
n이 음수이면 소수점의 왼쪽 자리 수만큼 반올림 된다
How? 어떻게 쓰는 거라고?
ROUND(45.923, 2) : 45.923의 소숫점 2번째 자리로 반올림 (소숫점 3번째 자리가 3이기 때문에 올림 X)
ROUND(45.923, 0) : 45.923의 소숫점 0번째 자리(일의 자리)로 반올림
ROUND(45.923, -1) : 45.923의 소숫점 -1번째 자리(십의 자리)로 반올림
TRUNC ( column | expression, n ) : 열, 표현식 또는 값을 n자리 이후로 절삭
How? 어떻게 쓰는 거라고?
TRUNC(45.923, 2) : 45.923의 소숫점 2번째 자리 이후로 삭제(3 삭제)
TRUNC(45.923) : 45.923의 소숫점을 싹 다 삭제
TRUNC(45.923, -1) : 45.923의 소숫점을 기준으로 왼쪽으로 한 칸(일의 자리)을 삭제
그 외의 숫자함수
CEIL ( column | expression ) : 열, 표현식 또는 값과 같거나 큰 가장 작은 정수 리턴 ( = 올림 )
FLOOR ( column | expression ) : 열, 표현식 또는 값보다 작거나 같은 가장 큰 정수 리턴 ( = 내림 )
MOD ( m, n ) : m을 n으로 나눈 나머지를 리턴
가볍게만 보고 넘어가자
1) abs ( -24 ) : 절댓값을 리턴하는 함수로, 24가 출력
2) ceil ( 3.14 ) : 올림을 하는 함수이기 때문에 정수부분으로 올림이 되어서, 4로 출력
3) floor ( 3.14 ) : 내림을 하는 함수이기 때문에 정수부분이 내림이 되어서, 3으로 출력
mod (10, 3)은 10을 3으로 나눠서 나온 나머지 값을 리턴한다
(mod를 안 쓰고 그냥 나누기를 하게 되면 몫이 출력)
날짜 함수
SYSDATE : 현재의 날짜 반환
"DUAL" 이라는 더미 테이블로부터 SYSDATE 선택
SYSTIMESTAMP : 현재의 날짜 반환 ( + 시간 출력 )
DUAL이라는 데이터집합으로부터 SYSDATE, SYSTIMESTAMP을 선택해서 시간을 출력하는 것을 확인할 수 있다
그렇다면 날짜도 숫자형식인데 연산이 가능할까?
다음의 예시를 통해 보다 정확하게 확인해보자 ▼
1) 입사일부터 현재까지 일한 시간을 주(week)로 표현
SELECT first_name, ( SYSDATE - hire_date ) / 7 as "Weeks"
① first_name열과 (SYSDATE - hire_date ) / 7 열을 출력해줘
( 근데 이제 (SYSDATE - hire_date ) / 7열은 "Weeks"라고 칭할게 )
② (SYSDATE - hire_date ) / 7 = (현재 날짜 - hire_date열 = 총 일한 일수) 을 7로 나누면 몇 주수인지 출력
FROM employees employees 데이터집합에서
WHERE department_id = 60; 근데 이제 부서 아이디가 60인
2) 입사일부터 현재까지 일한 시간을 년(year)으로 표현
그럼 숫자형식이 쓰이는 날짜함수도 사용할 수 있겠네?
다음의 예시를 통해 보다 정확하게 확인해보자 ▼
1) SELECT TRUNC ( SYSDATE, 'Month' ) 현재 날짜 데이터에서 "월" 이후 절삭해줘 ▶ 1일로 초기화
FROM dual; DUAL 데이터집합에서
2) SELECT TRUNC ( SYSDATE, 'Year' ) 현재 날짜 데이터에서 "년" 이후 절삭해줘 ▶ 1월 1일로 초기화
FROM dual; DUAL 데이터집합에서
3) SELECT ROUND ( TO_DATE ( '17/03/16' ), 'Month' )
TO_DATE ( '17/03/16' )
날짜를 나타내는 문자열을 '날짜'형식으로 변경
ROUND ( TO_DATE ( '17/03/16' ), 'Month' )
날짜 중에 "월"을 반올림 ("일"이 15일(절반) 이상일 때 반올림 됨)
FROM dual; DUAL 데이터집합에서
4) SELECT TRUNC ( TO_DATE ( '17/03/16' ), 'Month' )
TRUNC ( TO_DATE ( '17/03/16' ), 'Month' )
날짜 중에 "월" 이후를 절삭 (1일로 초기화)
FROM dual; DUAL 데이터집합에서
연습문제
< 문제 1 >
employees 테이블에서 phone_number컬럼은 ###.###.#### 형태로 저장되어 있다
이 때 처음 세 자리 숫자 대신 서울 지역번호인 (02)를 붙여 전화번호이 출력되도록 작성
1) select replace ( phone_number, substr ( phone_number, 1, 3), '(02)') from employees;
① substr ( phone_number, 1, 3 ) : phone_number를 첫 번째 문자부터 세 번째 문자까지 자르기
② replace ( phone_number, substr ( phone_number, 1, 3), '(02)')
: phone_number열의 첫 번째부터 세 번째 문자까지 자른 것을 '(02)'로 바꾸기
2) select concat ( '(02)',
substr ( phone_number, 4, length ( phone_number ))) as phone_number
from employees;
① substr( phone_number, 4,length( phone_number)
: phone_number열의 4번째 문자부터 phone_number의 길이만큼을 자르기
② concat ( '(02)', substr ( phone_number, 4, length ( phone_number ))
: substr로 자른 문자열을 다시 '(02)'와 결합하기
< 문제 2 >
employees 테이블에서 job_id가 IT_PROG인 사원의 이름(first_name)과 급여(salary)를 출력하세요
조건 1) 비교하기 위한 값은 소문자로 입력
조건 2) 이름은 앞 3자리 문자까지 출력하고 나머지는 *로 출력 ( 열 별칭은 name )
조건 3) 급여는 전체 10자리로 출력하되 나머지 자리는 *로 출력 ( 열 별칭은 salary )
select RPAD ( SUBSTR ( first_name, 1, 3 ), length( first_name ), ' * ' ) as name,
① SUBSTR ( first_name, 1, 3 )
: first_name열을 첫 번째 문자부터 세 번째 문자까지 자르기
② RPAD ( SUBSTR ( first_name, 1, 3 ) , length( first_name ), ' * ' )
: 자른 부분을 제외하고 남은 (first_name)문자열의 길이만큼 ' * '문자 채워주기(오른쪽으로)
LPAD ( salary, 10, ' * ' ) as salary
: salary열을 10자리까지 출력하는데 이 때 남은 자리에는 ' * '로 채우기(왼쪽으로)
from employees
where lower ( job_id ) = 'it_prog';
: employees 데이터집합에서 데이터를 추출할 건데 이 때 job_id가 'IT_PROG'인 것만 뽑아줘
비교를 위해 열의 모든 데이터 값을 소문자로 바꿨음
변환 함수
암시적 형변환
묵시적 형변환
1. TO_CHAR ( date , 'fmt' ) : 날짜를 문자열로 변환
[ 날짜 형식 모델 요소 ]
MM : 두 자리 값의 월
DDD / DD / D : 년, 월 또는 주의 일
HH / HH12 / HH24 : 하루 중 시간 또는 시간(1-12) 또는 시간(0-23)
MI : 분(0-59)
SS : 초(0-59)
"of the" : 인용 부호 내의 스트링이 결과에 다시 나타남
다음의 예시를 통해 보다 정확하게 확인해보자 ▼
SELECT first_name, TO_CHAR ( hire_date, ' YYYY"년" MM"월" DD"일" ' ) as HIREDATE
first_name열과 hire_date열을 출력해줘
TO_CHAR ( hire_date, ' YYYY"년" MM"월" DD"일" ' ) as HIREDATE
hire_date는 숫자형식의 데이터이기 때문에
문자열(예시 - 2017년 12월 31일)로 변환하여 출력할 것이고 HIREDATE로 지칭할 거야!
FROM employees; employees 데이터집합에서
"of"를 사용한 hire_date(숫자)를 문자열로 변환
2. TO_CHAR ( number , 'fmt' ) : 숫자를 문자열로 변환
어떻게 활용되는지 다음의 예시들을 통해 확인해보자
TO_CHAR( salary, '$999,999' ) : salary는 숫자 형식의 데이터이기 때문에 이를 문자열로 변환하려고 to_char 사용(근데 이제 달러로 출력)
★ 다만 주의해야 할 점이 있다
숫자를 문자로 표현할 때 반드시 자릿수를 맞추어야 한다 (숫자를 모두 표시할 수 있게끔)
그렇지 않을 경우 잘못된 출력이 나온다
문자열로 변환될 때 소숫점은 어떻게 처리되는가?
① salary * 0.123456 as SALARY1 [ 원래 데이터 ]
② TO_CHAR ( salary*0.123456, '$999,999.99' ) as SALARY2 [ 문자열로 바꾼 데이터 ]
원래 데이터와 바뀐 데이터의 소숫점 자리를 비교해보자
원래 데이터는 소숫점 4자리까지 모두 나오지만,
바뀐 데이터는 소숫점 2자리까지만 나오는 것을 확인할 수 있다
이것은 TO_CHAR를 이용해 문자열로 바뀔 때 문자열을 소숫점 두 번째까지만
출력되게 했기 때문에 소숫점 세 번째자리에서 반올림 되어서 출력된다
결론) TO_CHAR를 이용해 숫자 형식의 데이터를 문자열로 바꿀 때에는 소숫점도 문자열 출력 형태를 따른다
예제 1)
to_char ( 20000.14, '999,999,999' )
: 숫자 20000.14를 문자열로 변환할 때 형태를 999,999,999로 하겠다는 것이다
(소숫점은 표시를 해주고 있지 않기 때문에 소숫점 자리는 절삭된다)
예제 2)
to_char ( salary, 'L999,999' )
:salary열을 원화로 바꾸어서 표현하고 싶을 때 이렇게 사용한다
3. TO_NUMBER ( char , 'fmt' ) : 문자열을 숫자형식으로 변환
잊지 말아야 할 것이 있다! (진지..)
지금까지 한 모든 형변환들은 명시적인 것으로 자동 형변환이 되지 않기 때문에 직접 형변환을 해준 것!
그렇기 때문에 위의 예시에서처럼 숫자로만 이루어진 문자 형식일 경우에는
자동으로 숫자 형식으로 형 변환이 일어나서 문제가 없지만
$또는 (,) 등이 포함된 경우에는 명시적으로(직접) 형 변환을 해주어야 한다
따라서 위의 예시처럼 TO_NUMBER ( )을 사용해서 명시적으로 변환해줘야 한다
4. TO_DATE ( char , 'fmt' ) : 문자열을 날짜 형식으로 변환
SELECT first_name, hire-date first_name열과 hire_date열을 출력해줘
FROM employees employees 데이터집합에서
WHERE hire_date = TO_DATE( '2003/06/17', 'YYYY/MM/DD' );
근데 이제 hire_date가 2003/06/17인 데이터만 추출해줘
문자열인 '2003/06/17'을 'YYYY/MM/DD'형식(날짜 형식)으로 변환
이거 풀 수 있어?
문자형식을 "####년 ##월 ##일"로 변환해보렴
① to_date ('20050102', 'YYYY/MM/DD')
: '20050102'는 날짜를 의미하나 문자 형식이기에 이를 날짜 형식으로 변환해준다
② to_char( to_date ('20050102', 'YYYY/MM/DD'), 'YYYY"년" MM"월" DD"일"')
: 날짜 형식이 된 데이터를 다시 문자열로 변환해준다
NVL 함수
그래서 NVL 함수가 뭐라고?
(단, expr1의 데이터 타입 = expr2의 데이터 타입)
어떻게 활용되는지 다음의 예시를 통해 확인해보자
SELECT first_name, first_name열과 salary열을 선택해줘
salary + salary*NVL ( commission_pct, 0 ) as ann_sal
salary*NVL ( commission_pct, 0 ) as ann_sal
commission_pct가 null이 아니면 commission_pct을 그대로 출력하고 null이면 0을 출력
FROM employees; employees 데이터집합에서
NVL2 함수 : expr1이 기준
그래서 NVL 함수랑 차이가 뭐라고?
(expr1의 데이터 타입은 expr2의 데이터 타입, expr3의 데이터 타입과 같지 않아도 무방)
어떻게 활용되는지 다음의 예시를 통해 확인해보자
NVL2 ( commission_pct, salary + (salary*commission_pct), salary) as ann_sal
commission_pct열의 데이터가 null이 아닌 경우 salary + (salary*commission_pct) 출력하고
null인 경우 salary를 출력
DECODE 함수
그래서 DECODE 함수가 뭐라고?
어떻게 활용되는지 다음의 예시를 통해 확인해보자
DECODE( job_id, 'IT_PROG', salary*1.10, job_id가 'IT_PROG'면 salary*1.10 출력
'FI_MGR', salary*1.15, job_id가 'FI_MGR'면 salary*1.15 출력
'FI_ACCOUNT', salary*1.20, job_id가 'FI_ACCOUNT'면 salary*1.20출력
salary) 나머지는 default값인 salary 출력
CASE ~ WHEN ~ THEN 함수
그래서 DECODE 함수랑 차이가 있어? (없어!)
어떻게 활용되는지 다음의 예시를 통해 확인해보자
1) 열 또는 표현식을 CASE 뒤에 작성한 경우
2) 열 또는 표현식을 WHEN절 안에 작성한 경우
집합연산자
1) UNION
union은 합집합을 의미하여 A라는 집합과 B라는 집합이 있다면 두 집합의 모든 원소를 추출한다
2) UNION ALL
union all은 합집합을 의미하여 A라는 집합과 B라는 집합이 있다면 두 집합의 모든 원소를 추출하는데
다만 겹치는 부분(교집합)의 원소를 중복으로 추출한다
예제를 확인해볼까?
union all의 경우 집합끼리 합쳤을 때 모든 집합 요소들이 출력되는데
이 때 중복되는 데이터가 두 번 출력이 된다는 특징이 있다
★ 집합 연산자를 사용할 때 주의할 점 - column 갯수를 똑같이 해야 한다
3) INTERSECT
intersect는 교집합을 의미하며 두 집합간의 겹치는 부분의 원소를 추출한다
4) MINUS
minus는 차집합을 의미해 하나의 집합에서 다른 집합 부분을 아예 빼버린 상태의 원소 추출한다
분석함수
RANK : 해당 값에 대한 순위를 결정 (중복순위를 계산 - 2등이 2명이면 다음 순위는 4등)
DENSE_RANK : 해당 값에 대한 순위를 결정 (중복순위를 계산 X - 2등이 2명이더라도 다음 순위 3등)
ROW_NUMBER : 조건을 만족하는 모든 행의 번호 제공(일렬번호 생성)
예제로 확인해보자!
※ 주의
row_number ( )와 rownum의 차이를 분명하게 알아야 한다
row_number의 경우 데이터집합이 만들어지면 그 순서대로 번호를 붙여서 만들어진 것을 출력한다
rownum의 경우 order by가 적용이 안 된 원래의 데이터를 기준으로 붙여져 있던 번호를 출력한다
(따라서 rownum을 사용하려면 반드시 order by가 적용된 데이터에 번호를 붙일 수 있도록 처리해줘야 한다)
데이터베이스 수업을
듣기 시작하면서
정말 만감이 교차했다
스스로가 너무 오만했던 거 같다
하지만
무언가를 하려고 하면
체력적으로 한계를 느꼈고
그런 스스로를 받아들이지 못 했다
'나는 왜 이럴까..'
'지금 이럴 때가 아닌데' 라고
생각하는 마음과는 달리
이미 눈은 감기고
몸은 지쳐서
아우성을 치고 있었다
이런 상황이 반복되자
마음에도 거센 바람이 불었다
'나의 능력은 여기까지인가?'
'뭐 얼마나 했다고 벌써 이렇게 지친 건가..'
'이렇게 살면서 무엇인가 하고 싶다고 하면
그건 허영심 아닌가'
아니다. 분명 더 잘 할 수 있는 능력이 있고
지친 게 아니라
잠시 에너지를 보충해줘야 할 시간인 것이다
그래서
난 나 자신을 너무 옥죄지 않기로 했다
그렇다고 놓아버리겠다는 것은 아니다
다만 몸이 신호를 보낼 때는 확인을 해야 한다는 것
그렇게 느리더라도 조금씩 앞으로 나아갈 것이다
힘이 들고 지치는 건 부끄러운 것도 한심한 것도 아니고
그저 "나"를 사랑하고 "내 인생"을 사랑해서 일어난 아름다운 결과일 뿐
이 세상을 살아가는 모든 사람들이
지치고 힘든 자기 자신을
채찍질 하지 않았으면 좋겠다
조금만 보듬어주면 사랑스러운 "나"는
금세 힘을 내서 일어날 것이다
이건 누구도 아닌 "나 자신"만이 할 수 있다
'Oracle 기본 개념' 카테고리의 다른 글
| Oracle - 트랜잭션이란 무엇인가 (0) | 2022.11.21 |
|---|---|
| Oracle - DML (0) | 2022.11.20 |
| Oracle - 서브쿼리 (0) | 2022.11.15 |
| Oracle 함수의 모든 것 - 그룹함수 (0) | 2022.11.13 |
| Oracle이 Miracle이 되는 그 날까지 - SQL문 (0) | 2022.11.08 |
