[Oracle] 오라클 함수에 대해 알아보자.

 

1. 내장 함수

- UPPER(문자열)

: 모든 문자열을 대문자로 변환

 

- LOWER(문자열)

: 모든 문자열을 소문자로 변환

 

- INITCAP(문자열)

: 첫 글자는 대문자, 그 외 글자는 소문자로 변환

 

SELECT UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
	FROM EMP

 

* 사원 이름에 YEONY 단어를 포함한 데이터 찾기

SELECT *
	FROM EMP
  WHERE UPPER(ENAME) LIKE UPPER('%yeony%')

 

- LENGTH(문자열)

: 문자열 길이 반환

SELECT LENGTH('한글')
	FROM DUAL

 

이때 DUAL이란?

: 최고 권한 관리자 계정인 SYS 소유의 테이블로

SCOTT 계정도 사용할 수 있는 더미테이블이다. 

 

더미테이블을 사용하는 이유는

몇 개의 데이터 행으로 작은 테이블을 생성함으로써

데이터베이스 관리자는 기본 테이블의 데이터에 영향을 주지 않고 SQL 쿼리를 테스트할 수 있기 떄문이다.

 

 

- SUBSTR(문자열,시작위치,추출길이)

: 문자열 일부를 추출하는 SUBSTR 함수

SELECT SUBSTR(JOB,1,2)
	FROM EMP

 

만약 JOB 값이

DEVELOPER이라면

SUBSTR(JOB,1,2)는

첫 번째 글자부터 2글자를 출력하는 것이기 떄문에

DE를 출력하게 된다. 

 

만약 두 번째 글자부터 끝까지 출력하려면

SUBSTR(JOB,2)로 하면 된다. 

 

 

- INSTR 함수

: 특정 문자나 문자열이 어디에 포함되어 있는지 알기 위해 사용하는 함수

INSTR(문자열 데이터, 위치를 찾으려는 문자)

 

 

- REPLACE 함수

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

REPLACE(문자열 데이터, 찾는 문자, 대체할 문자(선택))

 

만약 대체할 문자의 값이 안 적혀있다면

찾는 문자로 지정한 문자는 데이터에서 삭제된다.

 

 

SELECT '010-1234-5678' AS REPLACE_PHONENUMBER1,
	REPLACE('010-1234-5678', '-',' ') AS REPLACE_PHONENUMBER2,
    REPLACE('010-1234-5678','-') AS REPLACE_PHONENUMBER3
FROM DUAL


010-1234-5678
010 1234 5678
01012345678

 

 

- LPAD, RPAD 함수

Left Padding, Right Padding을 뜻한다. 

데이터의 빈 공간을 특정 문자로 채우는 함수이다. 

LPAD(문자열 데이터, 데이터의 총 자릿수, 빈 공간에 채울 문자)

RPAD(문자열 데이터, 데이터의 총 자릿수, 빈 공간에 채울 문자)

 

 

 

- CONCAT 함수

: 두 문자열 데이터를 합치는 함수

SELECT CONCAT(EMPNO,ENAME)
	FROM EMP
  WHERE ENAME='SCOTT'

혹은 || 연산자로도 문자열을 연결할 수 있다.

SELECT EMPNO || ENAME,
	EMPNO || ' : ' || ENAME
  FROM ...

 

 

- TRIM , LTRIM, RTRIM 함수

: 데이터 내에서 특정 문자를 지우기 위해 사용한다. 

왼쪽 공백 지우는 건 LTRIM, 

오른쪽 공백 지우는 건 RTRIM

 

TRIM(문자열데이터)

 

---

1.2 숫자 함수

- ROUND

: 특정 위치에서 반올림하는 함수.

반올림할 위치를 지정하지 않으면 첫째 자리에서 반올림한 결과가 반환된다.

ROUND(숫자, 반올림 위치)

 

- TRUNC 함수

: 지정된 자리에서 숫자를 버림 처리하는 함수.

TRUNC(숫자, 버림 위치)

 

- CEIL, FLOOR 함수

: 각각 입력된 숫자와 가까운 큰 정수, 작은 정수를 반환하는 함수

 

 

- MOD 함수

: 숫자를 나눈 나머지 값을 구하는 함수

 

 

 

- DECODE 함수

: 기준이 되는 데이터를 먼저 지정한 후

해당 데이터 값에 따라 다른 결과 값을 내보내는 함수.

SELECT EMPNO, ENAME, JOB, SAL
	DECODE(JOB,	
    		'MANAGER', SAL*1.1,
            'SALESMAN',SAL*1.05,
            'ANALYST', SAL,
            SAL*1.03) AS UPSAL
 FROM EMP;

 

 

- CASE문

: DECODE와 마찬가지로 특정 조건에 따라 반환할 데이터를 설정할 때 사용한다. 

DECODE 함수는 기준 데이터를 반드시 명시하고 그 값에 따라 반환 데이터를 정하지만

CASE문은 각 조건에 사용하는 데이터가 서로 상관없어도 된다. 

CASE[검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]
	WHEN [조건1] THEN [조건 1의 결과 값이 true일 때, 반환할 결과]
	WHEN [조건2] THEN [조건 2의 결과 값이 true일 때, 반환할 결과]
	ELSE [위 조건1~조건N과 일치하는 경우가 없을 떄 반환할 결과]
END

SELECT EMPNO, ENAME, JOB, SAL
	CASE JOB	
    	WHEN 'MANAGER' THEN SAL*1.1,
        WHEN 'SALESMAN' THEN SAL*1.05
        WHEN 'ANALYST' THEN SAL
        ELSE SAL*1.03
    END AS UPSAL
 FROM EMP;

 

기준 데이터 없이 조건식만으로 CASE문 사용하기

SELECT EMPNO, ENAME, COMM,
	CASE
    	WHEN COMM IS NULL THEN '해당사항 없음'
        WHEN COMM=0 THEN '수당없음'
        WHEN COMM>0 THEN '수당 : ' || COMM
        END AS COMM_TEST
 FROM EMP;

 

---

2. 다중행 함수와 데이터 그룹화

- GROUP BY절

: 결과 값을 원하는 열로 묶어 출력한다. 

즉 데이터를 그룹화한다.

SELECT AVG(SAL), DEPTNO
	FROM EMP
GROUP BY DEPTNO;

-- DEPTNO가 같은 데이터를 그룹화해서 1줄로 출력

 

다중행 함수를 사용하지 않은 일반 열은

GROUP BY절에 명시하지 않으면

SELECT절에서 사용할 수 없다는 것이다. 

 

 

- GROUP BY절에 조건을 줄 때 사용하는 HAVING절

: GROUP BY절을 통해 그룹화된 결과 값의 범위를 제한하는 데 사용한다. 

 

WHERE절은 출력 대상 행을 제한하고

HAVING절은 그룹화된 대상을 출력에서 제한하므로

쓰임새는 전혀 다르다.

 

SELECT DEPTNO, JOB, AVG(SAL)
	FROM EMP
GROUP BY DEPTNO, JOB
	HAVING AVG(SAL)>=2000

 

WHERE절에서는 그룹화된 데이터 AVG(SAL)를 제한하는 조건식을 지정할 수 없다.

 

 

SELECT DEPTNO, JOB, AVG(SAL)
	FROM EMP
GROUP BY DEPTNO, JOB
	HAVING AVG(SAL)>=2000
ORDER BY DEPTNO, JOB;

WHERE절이 GROUP BY절, HAVING절보다 먼저 실행된다.

 

 

- ROLLUP함수

: 명시한 열을 소그룹부터 대그룹의 순서로 각 그룹별 결과를 출력하고

마지막에 총 데이터의 결과를 출력함

 

---

참고문헌 : Do it 오라클로 배우는 데이터베이스 입문