2022-11-03 오라클(Oracle) 단일행함수, 다중행함수

Oracle

---

- 내장함수의 종류

 

1. 단일행 함수

: 데이터가 한 행씩 입력되고 입력된한 행당 결과가 하나씩 나오는 함수

 

2. 다중행 함수

: 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수

 

 

 

단일행함수

[ 문자 함수 ]

<대소문자를 바꿔주는 함수>

- select upper(문자열)

: 모두 대문자로 변환

- select lower(문자열)

: 모두 소문자로 변환

- select inicap(문자열)

:첫 글자는 대문자, 나머지는 소문자로 변환

 

 

<문자열의 길이 구하기>

- where length(문자열)

 

 

<문자열의 일부 추출>

- select substr(문자열 데이터, 시작 위치, 추출 길이)

: 시작 위치부터 길이만큼

- select substr(문자열 데이터, 시작 위치)

: 시작위치부터 끝까지

** 오라클의 인덱스 번호는 1번부터이다.

 

 

<특정 문자 위치 찾기>

** 기본값 - 1

- select instr('oracleoracleoracle','a') 

: 제일 처음 만나는 a찾기
- select instr('oracleoracleoracle','a',5) 

: 5번부터 시작해서 a찾기
- select instr('oracleoracleoracle','a',-1) 

: 뒤에서부터 찾음
- select instr('oracleoracleoracle','a',1,3)

: 1번위치부터 3번째만나는 a를찾음

 

 

<replace>
- select replace(문자열 데이터/열 이름, 찾는 문자, 대체할 문자(선택)) 

: 특정 문자열 데이터에 포함된 문자를 다른 문자로 대체한다

 

 

<데이터의 빈 공간을 특정 문자로 채우기>

- select lpad(문자열 데이터/열 이름, 데이터의 자릿수, 빈공간에 채울 문자(선택))

: 왼쪽채움
- select rpad(문자열 데이터/열 이름, 데이터의 자릿수, 빈공간에 채울 문자(선택))

: 오른쪽채움

 

 

<두 문자열 데이터 합치기>

- select concat('oracle','재밌다')  => oracle재밌다

- select concat(empno, concat(' : ', ename)) => 7788 : scott

- select ename || ' : ' || job

** 문자열만 바꾸고 싶으면 || 사용

 

 

<특정 문자 지우기>

- trim(삭제 옵션(선택), 삭제할 문자(선택)) from 원본 문자열 데이터(필수)

- select ltrim('aaaaaaaaoracle','a') from dual;

: 왼쪽의 모든 a를자름
- select rtrim('aaaaaaaaoracleaaa','a') from dual; 

: 오른쪽의 모든 a를 자름

- select trim(both  'a'  from 'aaaaaoracleaaaa') from dual;

: 양쪽의 'a' 다 지워줌
- select trim(both '  ' from '          oracle        ') from dual;

: 양쪽 공백지워줌
(ㄴ공백지우는 용도로 사용)

 

 

 

 

[ 숫자 함수 ]

 

반올림- select round(167.678,0),round(167.678,2),round(167.678,-1)from dual;
버림처리- select trunc(167.678,0),trunc(167.678,1),trunc(167.678,-1)from dual;
=> -1 : 10의자리에서 반올림하고 버림
가장 가까운 큰정수반환-select ceil(198.555), 작은정수반환-floor(197.1234) from dual;
나머지- select mod(15, 6) from dual;
절댓값-select abs(-12) from dual;

 

 

 

[ 날짜 함수 ]

 

오늘날짜- select sysdate,sysdate+5, sysdate-5 from dual;
몇 개월 이후 날짜 구하기 -  select add_months(sysdate,3) from dual; // 3개월 후
두 날짜 간의 개월 수 차이 -  select months_between(sysdate,'2022/6/25') from dual; // 1.오늘날짜, 2. 비교할날짜
돌아오는 요일구해줌- select next_day(sysdate,2) from dual;
달의 마지막 날짜 구해줌-select last_day(sysdate) from dual;

select to_char(sysdate,'YYYY') from dual;  // 2022
select to_char('1290','9,999') from dual;  //  1,290
문자를 숫자로 - select to_number('1,290','9,999') from dual;  // 1290
                        - select '12345', to_number('12345') from dual
형식바꾸기 - select '2022-11-1', to_date('2022-11-1','yyyy/mm/dd') from dual;  // 22/11/01

 

 

 

[ NULL 처리 함수 ]

null인 경우 지정한 데이터 반환 - select sal,comm,nvl(comm,0) from emp;
- select sal+comm, sal+nvl(comm,0)  // comm이 null이면 0을 더해라
- select ename,sal, nvl(to_char(comm),'신입사원')  // comm이 숫자이기 때문에 문자로 변환 후
- select ename,sal, nvl2(to_char(comm),'경력','신입사원')  // null이 아니면 경력, null이면 신입사원

 

 

 

[ decode함수(정확하게 일치하는 것만 비교함), case문(=,>,<, and, or 등 사용 가능) ] 

<decode함수>

1-1) select deptno, decode(deptno,   // 평가할컬럼
                       10, '전산실',       // 조건, 조건과 일치할 때 반환할 결과
                       20, '인사과',
                       30, '영업팀') from emp;
1-2) select deptno, decode(deptno,
                      10, '전산실',
                      20, '인사과',
                      '기타') from emp;   // else
1-3)  select ename,deptno,hiredate,job,decode(substr(hiredate,1,2),
  81, '이사',
  82, '부장',
  83, '과장',
   '상무')
from emp;

<case문>

2-1)select deptno, case deptno
                    when 10 then '전산실'
                    when 20 then '인사과'
                    when 30 then '영업팀'
                    else '기타'
                    end
                    from emp;
2-2)select ename,deptno,hiredate,job,case to_number(substr(hiredate,1,2))
                                      when 81 then '이사'   // to_number하지 않고 '81'도 ok
                                      when 82 then '부장'
                                      when 83 then '과장'
                                      else '상무'
                                      end
                                      from emp;
2-3) select deptno, sal, case when sal>=5000 then sal*0.1
                           when sal>=3000 then sal*0.7
                           else sal*0.5
                           end
                           from emp;
2-4) select ename,hiredate,job,case
  2  when to_char(hiredate,'mm') in (1, 2, 12) then '겨울'
  3  when to_char(hiredate,'mm') in (3,4,5) then '봄'
  4  when to_char(hiredate,'mm') in (6,7,8) then '여름'
  5  else '가을'
  6  end as 계절
  7  from emp;

 

 

 

 

 

 

다중행함수

[ GROUP BY ]

*작성순서 : select->from->where->groub by->having->order by
select 컬럼(groub by에 정의한 컬럼만), 그룹함수
from 테이블명
where 조건(group by 하기 전에 적용할 조건) - 그룹화 대상에 포함할 건지 안 할 건지
group by 컬럼(그룹화하고 싶은 컬럼명이나 값)
having 조건(group by 결과에 적용할 조건)
order by 컬럼

- select job, count(empno),avg(sal)
  from emp
  group by job;

***주의사항
   - groub by를 하는 경우 groub by에 명시한 컬럼과 그룹함수만 select절에 추가할 수 있다.

- select job, count(empno),avg(sal)
  from emp
  group by job
  order by job;
- select deptno,job, count(empno)
  from emp
  group by deptno, job
  order by deptno;

 

[ having ]

1-1)
 select job, count(empno)  // count는 기본키가 있는 걸로 주기
 from emp
 where sal>1000
 group by job
 having count(empno)<4;

1-2) 입사월별 인원수 출력.
 select to_char(hiredate,'mm') 월, count(empno) 인원수
 from emp
 where sal<5000
 group by to_char(hiredate,'mm')
 having count(empno)>1
 order by to_char(hiredate,'mm');
*********==**********
 select to_char(hiredate,'mm') 월, count(empno) 인원수
 from emp
 where sal<5000
 group by to_char(hiredate,'mm')
 having count(empno)>1
 order by 월;    // select보다 뒤에 실행하기 때문에 가능함

1-3) 부서별로 최대급여, 최소급여 출력. job이 'PRESIDENT'인 데이터는 제외하고 최대급여가 3000이상인 부서만,
       부서별 오름차순 정렬
select deptno, max(sal), min(sal)
from emp
where job <> 'PRESIDENT'  // job not IN ('PRESIDENT')
group by deptno
having  max(sal)>=3000
order by deptno;

 

 

 

p. 174 - 4번 문제

SQL> select empno, ename, mgr, case substr(mgr,1,2)
  2  when '75' then '5555'
  3  when '76' then '6666'
  4  when '77' then '7777'
  5  when '78' then '8888'
  6  else to_char(mgr)
  7   end as CHG_MGR
  8   from emp;

     EMPNO ENAME                       MGR CHG_MGR
---------- -------------------- ---------- --------------------
      7369 SMITH                      7902 7902
      7499 ALLEN                      7698 6666
      7521 WARD                       7698 6666
      7566 JONES                      7839 8888
      7654 MARTIN                     7698 6666
      7698 BLAKE                      7839 8888
      7782 CLARK                      7839 8888
      7788 SCOTT                      7566 5555
      7839 KING
      7844 TURNER                     7698 6666
      7876 ADAMS                      7788 7777
      7900 JAMES                      7698 6666
      7902 FORD                       7566 5555
      7934 MILLER                     7782 7777

14 rows selected.

*풀이

풀다보면 "ORA-00932: inconsistent datatypes: expected CHAR got NUMBER" 라는 에러를 만날 수 있다. 이 에러는 타입이 일치하지 않아 발생하는 에러인데 타입을 일치시켜 주면 해결되는 에러이다.

우선 desc emp; 입력하여 내가 출력하고자 하는 데이터(mgr)의 타입을 먼저 확인한다.

1번 방법, to_number(substr(mgr,1,2))를 쓰고  숫자를 그대로 적어준다.

2번 방법, substr(mgr,1,2)를 쓰고 '75' 이런식으로 숫자에 작은따옴표를 붙여준다.

6번의 else to_char(mgr)은 mgr이 숫자타입이기 때문에 앞에 문자로 바꿔주는  to_char()를 붙여준 것이다.