26일차: Oracle – 문자/날짜/형변환/그룹 함수 - 2021.07.20

목차

1.     문자 함수

2.     날짜 함수

3.     형변환 함수

4.     그룹 함수

 

1. 문자 함수                         → ★2021_07_20_01 참고

 

1) substr()

- 문자열 일부만 추출하는 함수

- 대상 문자열이나 칼럼의 자료에서 시작위치부터 선택 개수만큼의 문자를 추출

- 형식: substr (대상, 시작 위치, 추출할 개수)

- ex) select substr(‘Welcome to Oracle’, 4, 3) from dual;        → 출력 결과: com

- 시작 위치 인자 값이 음수면 문자열의 앞쪽이 아닌 뒤쪽에서부터 세어서 시작위치를 잡음

- ex) select substr('Welcome to Oracle', -4, 3) from dual;       → 출력 결과: acl

 

2) instr()

- 특정 문자의 위치를 구하는 함수

- 형식: instr(대상, 찾을 글자, 시작 위치, 몇_번째_발견)

- ex) Q. 가장 먼저 나오는 'o'의 위치

       select instr('Welcome to Oracle', 'o') from dual;         → 출력 결과: 5

 

       Q. 6번 이후에 2번째로 발견된 'o'의 위치

       select instr('Welcome to oracle', 'o', 6, 2) from dual;   → 출력 결과: 12

 

3) lpad() / rpad()

- 특정 기호로 채우는 함수

- lpad(left padding) : 함수는 칼럼이나 대상 문자열을 명시된 자릿수에서 오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채움

- rpad(right padding) : 칼럼이나 대상 문자열을 명시된 자릿수에서 오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채움

- 형식: lpad/rpad(‘문자열’, 자리수, ‘기호’)

- ex) select lpad('Oracle', 20, '#') from dual;            → ##############Oracle

       select rpad('Oracle', 20, '#') from dual;            → Oracle##############

 

4) ltrim() / rtrim()

- ltrim() : 왼쪽 공백을 삭제하는 함수

- rtrim() : 오른쪽 공백을 삭제하는 함수

- ex) select ltrim('   Oracle   ') from dual;             → ‘Oracle   ‘

       select rtrim('   Oracle   ') from dual;            → ‘   Oracle’

 

5) trim()

- 문자열 좌우의 공백을 삭제하는 함수

- 특정 문자를 잘라내는 함수

- ex) select trim('   Oracle   ') as "trim" from dual;  → ‘Oracle’

       select trim('a' from 'aaaaOracleaaaa') from dual; → ‘Oracle’

 

2. 날짜 함수

 

1) sysdate

- 시스템의 날짜를 구해주는 함수

- 날짜에 숫자를 더하거나 빼서 연산 가능

- ex) select sysdate from dual;                                                 

       select sysdate-1 어제, sysdate 오늘, sysdate+1 내일 from dual;

 

2) round()

- 지정한 날짜 포맷을 기준으로 반올림하는 함수

- 형식: round (date, format)

- ex) select hiredate, round(hiredate, 'month') from emp;

→ 일을 기준으로 16일보다 적으면 이번 달 1일 크면 다음 달 1일로 구해줌

 

3) trunc()

- 지정한 날짜 포맷을 기준으로 날짜를 잘라내는 함수

- 형식: trunc (date, format)

- ex) select hiredate, trunc(hiredate, 'month') from emp;

→ 일의 크기와 상관없이 일을 1일로 만들어줌

 

4) months_between()

- 날짜와 날짜 사이의 개월 수를 구하는 함수

- 형식: months_between (date1, date2)

- ex) Q. 사원 테이블에서 각 사원들의 근무한 개월 수를 구하는 SQL문 작성

       select ename, sysdate, hiredate, months_between(sysdate, hiredate) from emp;

       select ename, sysdate, hiredate, months_between(hiredate, sysdate) from emp;   → 음수값

 

5) add_months()

- 함수는 특정 개월 수를 더한 날짜를 구하는 함수

- 형식: add_months (date, number)

- ex) Q. 오늘 날짜에서 6개월이 경과된 일자를 구하는 SQL문 작성

       select sysdate, add_months(sysdate, 6) from dual;

      Q. 입과 날짜에서 6개월이 경과된 일자를 구하는 SQL문 작성

       select add_months('21/06/15', 6) as "입과 6개월" from dual;

 

6) next_day()

- 해당 날짜를 기준으로 최초로 도래하는 요일에 해당되는 날짜를 구하는 함수

- 형식: next_day (date, 요일)

- ex) 오늘을 기준으로 가까운 토요일이 언제인지 구하는 SQL문 작성

       select sysdate, next_day(sysdate, '토요일') from dual;

 

7) last_day()

- 해당 달의 마지막 날짜를 구해주는 함수

- ex) Q. 이번 달의 가장 마지막 날짜를 구하는 SQL문 작성

       select sysdate, last_day(sysdate) from dual;    → 21/07/20  21/07/31

       select last_day('21/02/01') from dual;            → 21/02/28

 

3. 형 변환 함수

- to_char / to_date / to_number

 

 

1) to_char()

- 날짜형, 숫자형 데이터를 문자형으로 변환시켜주는 함수

 

① 날짜형 데이터를 문자형으로 변환

- 형식: to_char (날짜 데이터, '출력 형식')

 

• 날짜 출력 형식                               • 시간 출력 형식

 

- ex) Q. 현재 시스템의 날짜를 년, 월, 일, 시, 분, 초, 요일로 출력

       select sysdate, to_char(sysdate, 'yyyy-mm-dd am hh:mi:ss DY') from dual;           → hh, hh12는 12시간제

       select sysdate, to_char(sysdate, 'yyyy-mm-dd am hh24:mi:ss DAY') from dual;      → hh24는 24시간제

 

② 숫자형 데이터를 문자형으로 변환

- 형식: to_char ( 숫자 데이터,  '구분 기호')

 

• 숫자 출력 형식

 

- ex) 0으로 자릿수를 지정할 때, 데이터의 길이가 9자리가 되지 않으면 0으로 채운다.

       select 1230000, to_char(1230000, '000,000,000') from dual;  → 001,230,000

 

       9로 자릿수를 지정하면, 데이터의 길이가 9자리가 되지 않아도 자릿수를 채우지 않는다.

       select 1230000, to_char(1230000, '999,999,999') from dual;  → 1,230,000

 

       Q. 사원 테이블에서 각 사원들의 급여를 3자리씩 콤마(,)로 구분해서 출력하는 SQL문 작성

       select ename, sal, to_char(sal, '9,999') from emp;           → 1,600

       select ename, sal, to_char(sal, 'L9,999') from emp;          → \1,600

       select ename, sal, to_char(sal, '9,999L') from emp;          → 1,600\

 

2) to_date()

- 문자를 날짜형으로 변환하는 함수

- 형식: to_date ( '문자', 'format' )

- ex) Q. 2021년 1월 1일부터 현재까지 경과된 일수를 구하는 SQL문 작성

       select sysdate - '2021/01/01' from dual;         → 오류 발생 (날짜형 - 문자형 이기 때문)

 

       select sysdate - to_date('2021/01/01', 'yyyy-mm-dd') from dual;             → 200.5348....

       select round(sysdate - to_date('2021/01/01', 'yyyy-mm-dd')) from dual;    → 201

       select trunc(sysdate - to_date('2021/01/01', 'yyyy-mm-dd')) from dual;     → 200

 

3) to_number()

- 문자형을 숫자형으로 변환해주는 함수

- 형식: to_number ( '문자', '구분 기호' )

- ex) select '20,000' - '10,000' from dual;  → 오류 발생 (문자와 문자 산술연산 불가)

       select to_number('20,000', '99,999') - to_number('10,000', '99,999') from dual;

 

4. 그 외 함수

 

1) NVL()

- null 값을 다른 값으로 변환해주는 함수

- null 값은 정해지지 않은 값을 의미

- null 값은 산술연산(+,-,*,/)이 되지 않는다.

- nvl(comm, 0) : comm이 null 값인 데이터를 0으로 변환

- ex) Q. 사원 테이블에 있는 각 사원들의 연봉을 계산하는 SQL문 작성  (연봉 = 급여(sal) * 12 + comm)

       select sal*12+nvl(comm,0) as "연봉" from emp;

 

2) DECODE()

- switch ~ case 구문과 유사

- 형식: decode( 컬럼명, 값1, 결과1,

                               값2, 결과2,

                               값3, 결과3,

                                ………..........)

 

- ex) Q. 사원 테이블에서 부서번호(deptno)를 부서명으로 바꿔서 출력하는 SQL문 작성

       (10 : ACCOUNTING, 20 : RESEARCH, 30 : SALES, 40 : OPERATIONS)

       select ename, deptno, decode( deptno, 10, 'ACCOUNTING',

                                                           20, 'RESEARCH',

                                                           30, 'SALES',

                                                           40, 'OPERATIONS') as dname

                                                           from emp;

 

3) CASE 함수

- if ~ else if문과 유사

- 형식: case  when 조건1 then 결과1

                 when 조건2 then 결과2

                 else 결과3                     → else는 없어도 됨

                 end

- ex) Q. 사원 테이블에서 부서번호(deptno)를 부서명으로 바꿔서 출력하는 SQL문 작성

       select ename, deptno, case when deptno=10 then 'ACCOUNTING'

                                           when deptno=20 then 'RESEARCH'

                                           when deptno=30 then 'SALES'

                                           when deptno=40 then 'OPERATIOS'

                                           end as dname

                                           from emp;

 

5. 그룹 함수 <6장>

 

- 하나 이상의 데이터를 그룹으로 묶어서 연산을 수행하고 하나의 결과로 처리해주는 함수

- 그룹 함수와 일반 컬럼은 원칙적으로 같이 사용할 수 없음

   select sum(sal), sum(comm) from emp;

   → 그룹 함수끼리 사용 가능

   select sal, sum(sal) from emp;

   → 오류 발생 (그룹 함수와 일반 컬럼은 같이 사용할 수 없음)

 

1) sum()

- 합을 구해주는 함수

- ex) select sum(sal) from emp;           → 급여의 합

       select sum(comm) from emp;      → comm의 합, null 값은 제외

       select sum(ename) from emp;     → 오류 발생

 

2) avg()

- 평균값을 구해주는 함수

- ex) select avg(sal) from emp;

 

3) max()

- 최대값을 구해주는 함수

- ex) select max(sal) from emp;          → 5000

 

       Q. 사원 테이블에서 가장 최근 입사일을 출력하는 SQL문 작성

       select max(hiredate) from emp;     → 87/07/13

 

4) min()

- 최소값을 구해주는 함수

- ex) select min(sal) from emp;

 

       Q. 사원 테이블에서 가장 먼저 입사한 사원의 입사일을 출력하는 SQL문 작성

       select min(hiredate) from emp;     → 80/12/17

 

       select min(ename) from emp;           → ADAMS