데이터 처리 및 가공 함수

조회에 대한 부분은 크게 어렵지 않아서 책으로만 정독해보고 넘어갔다. 함수에 대해서는 몰랐던 부분을 좀 정리해두려고 한다.

 

LENGTH, SUBSTR

이름에서 추론할 수 있듯 LENGTH는 해당 문자열의 길이를 출력해준다. 어렵지 않기에 따로 적어두진 않는다. SUBSTR도 프로그래밍 언어에서는 슬라이싱의 개념으로 많이 사용하는데 제대로 된 정의를 남겨두려고 한다.

 

SELECT ENAME, SUBSTR(ENAME, 1, 3) FROM EMP

위와 같이 입력해보자.

 

SUBSTR(STRING, 1, 3)을 하게되면 해당 컬럼의 데이터를 1번째 문자부터 3번째 문자까지 출력해준다는 의미로 생각하면 된다.

 

SELECT ENAME, SUBSTR(ENAME, -LENGTH(ENAME)), SUBSTR(ENAME, -LENGTH(ENAME), 2) FROM EMP

어떻게 값이 나올지 예상해보자.

 

왜 이렇게 되는지 알아보면 다음과 같다.

 

CLERK

이 데이터를 예시로 보면 C는 -5의 인덱스, L은 -4의 인덱스를 가지고 있다. -LENGTH(CLERK)는 -5를 의미할 것이고 -5번째부터 그 뒤에 오는 숫자만큼 출력한다는 의미라고 생각하면 된다. 즉, 위 이미지에서의 첫번째는 -5번째부터 끝까지 출력을 의미하고 두번째는 -5번째부터 2글자를 출력한다는 의미이다.

INSTR

INSTR 함수는 주어진 문자열에 문자가 몇 번째 인덱스에 있는지 찾아주는 함수이다.

 

SELECT INSTR('Hello', 'l') FROM DUAL

Hello라는 스트링에서 l의 위치를 출력해보자. 무엇이 나올지 생각해보자. 아마도 3이 나올 것이다.

 

SELECT INSTR('Hello', 'l', 2, 2) FROM DUAL

이렇게 입력하면 뭐가 나올까? 이건 4가 나올 것이다. 맨 위에서 적은 쿼리문의 경우에는 INSTR 함수를 사용하기 위한 필수 파라미터들이고 숫자들은 그 속에 정하는 것이다. 2, 2의 경우에는 2번째 인덱스부터 2번째 l을 찾는다고 생각하면 된다. 그렇기에 3이 아닌 4가 나온 것이다.

 

여기서 DUAL이라는 테이블이 뭔지 의심했다면 잘 눈여겨본 것이다. 단순히 오라클에서 제공해주는 테이블로써 테이블 속에서 데이터를 얻어서 계산식을 만드는 것이 아닌 단순한 계산식을 사용하기 위해 쓰는 테이블이라고 생각하면 된다.

 

SELECT 1 AS result

mysql에서는 이런 식으로 사용한다.

 

LPAD, RPAD

이 함수들은 빈 공간을 특수문자로 채워주는 함수라고 생각하면 된다.

 

SELECT 'oracle', LPAD('oracle', 10, '#'), RPAD('oracle', 10) FROM DUAL

이렇게 입력해서 결과물을 확인해보자.

 

이렇게 얻을 수 있다. 참고로 RPAD에 해당하는 쿼리문에는 ''의 데이터가 4개 들어가있다. 즉, 입력하지 않는다면 디폴트로는 빈 문자를 제공해준다는 것을 알 수 있다.

 

CONCAT

CONCAT은 두 문자열을 합쳐주는 함수이다.

 

SELECT CONCAT('Choi', 'Devv') FROM DUAL;

이렇게 하면 ChoiDevv라는 문자열을 얻을 수 있다.

 

TRIM, LTRIM, RTRIM

문자열 데이터 내에서 특정 문자를 지우기 위해 사용되는 함수이다. TRIM은 옵션에 따라 달라지긴 하지만 옵션을 외우기 번거롭다면 LTRIM, RTRIM이 있다는 것을 기억해둔다. LTRIM은 왼쪽의 문자를, RTRIM은 오른쪽의 문자를 제거하는데 사용된다.

 

숫자 함수

ROUND, TRUNC, CEIL, FLOOR 함수가 대표적으로 숫자와 관련된 함수들이다.

 

SELECT ROUND(3.141592, 1), ROUND(3.141592, 3), TRUNC(3.141592, 3), CEIL(3.14), FLOOR(3.14) , MOD(15, 6) FROM DUAL;

ROUND는 반올림 함수로 뒤에 위치한 숫자로 해당 위치에서 반올림을 한 경우를 말한다. 즉, 3.141592 중 3.14를 반올림해 3.1이 나오게 된다. TRUNC는 버림 함수로 뒤의 숫자까지 남기고 나머지를 다 버린다. CEIL은 입력된 숫자에서 가까운 숫자 중 가장 큰 걸 반환한다. 3.14에 가까운 정수는 3과 4로 CEIL은 4를 반환해준다. FLOOR는 그와 반대로 가장 작은 수를 반환해주므로 3이 나온다.

 

날짜 함수

오라클에서는 SYSDATE를 통해 사용하는 OS 체제의 현재 시간을 가져온다. SYSDATE, ADD_MONTHS, MONTHS_BETWEEN 등등이 있다.

 

SELECT SYSDATE AS NOW, SYSDATE -1 AS YESTERDAY, SYSDATE + 1 AS TOMORROW FROM DUAL

 

위와 같이 쿼리를 사용할 수 있고 +1, -1을 하면 내일, 어제의 시간이 출력된다.

 

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM DUAL

ADD_MONTHS를 사용하면 날짜에 개월 수를 더해서 구할 수 있다. SYSDATE를 통해 오늘 날짜를 구하고 ADD_MONTHS를 사용해 현재 시간에 3개월을 더한 값을 조회할 수 있다.

 

SELECT EMPNO, ENAME, HIREDATE, SYSDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) AS MONTH1 FROM EMP

MONTHS_BETWEEN 함수를 사용해 개월 수 차이를 구할 수도 있다. 참고로 내 오라클 설치부터 따라왔다면 HIREDATE가 최신으로 되어 있어 대부분 0.XXX의 차이가 나오는데 이 소수점을 TRUNC로 버림 처리하면 개월 수 차이를 알 수 있는 것 정도만 알고 넘어가자.

 

형 변환 함수

형 변환이라는 건 문자열인데 정수처럼 바꿔주는 걸 의미한다. 프로그래밍을 조금 했으면 알 것이라 생각한다. EMP 테이블에서 사원번호를 조회하고 그 번호에 500이라는 문자열을 더해보자.

 

SELECT EMPNO, EMPNO + '500' FROM EMP

 

EMPNO의 프로퍼티를 보면 정수형인데 문자열 500이 더해진 값을 조회했다. 이것을 명시적 형 변환이라고도 하는데 데이터베이스가 알아서 이를 형 변환해주어 더해주고 이 값을 조회해준 것이다.

 

SELECT '1,500' - '1,700' FROM DUAL

하지만 이런 경우는 어떻게 될까? 이 경우는 명시적 형 변환이 발생되지 않아 에러를 발생하게 된다. 

 

SELECT TO_NUMBER('1,300', '999,999') - TO_NUMBER('1,500', '999,999') FROM DUAL

그럴 경우에는 TO_NUMBER 함수를 사용해 사용하려고 하는 문자가 어떤 형태로 사용하는지 주입해주도록 하여 결과를 도출해낼 수 있다.

 

NULL 처리 함수

NULL이라는 건 프로그래밍적으로도 굉장히 문제가 될 수 있는 요소로 많이 작용하는 것이다. 실무에서도 NULL 처리를 하고 연산하는 경우가 많기에 이 부분은 알아두면 좋을 것이라 생각한다. 데이터베이스 내에서도 함수를 통해 NULL을 처리할 수 있다.

 

SELECT EMPNO, NVL(COMM, 0), SAL + NVL(COMM, 0) FROM EMP

EMP 테이블 내부의 COMM 컬럼에서 NULL 값이 존재한다. 위의 쿼리를 실행하기 전에 NVL이라는 함수를 제외하고 추출하면 다음과 같이 값을 얻을 수 있다.

 

이렇게 NULL이라고 나와버리기에 NULL 처리를 해주어야 한다. 잠시 나왔지만 그 함수는 NVL로 NULL이라면 어떻게 처리해줄지 변환해주고 값을 더해준다.

 

하다보면 NVL2라는 함수도 있는데 NVL과 비슷하지만 살짝 다르다. NVL은 NULL일 경우에는 값을 어떻게 처리해줄지 결정해준다면 NVL2는 NULL일 경우에는 어떻게, 아닐 경우에는 어떻게 할지 결정해준다.

 

SELECT EMPNO, COMM, NVL2(COMM, 'O', 'X') FROM EMP

이렇게 쿼리를 조회하면 COMM이 NULL일 경우에는 O라는 문자열을, 아니면 X라는 문자열을 출력해준다. 이걸 토대로 계산식을 넣어 다르게 계산해준다거나 하면 된다.

 

DECODE

DECODE의 형태를 보면 switch-case문이 생각났는데 그와 비슷한 것이 맞다.

 

SELECT EMPNO, ENAME, JOB, 
    DECODE(JOB,
        'MANAGER', SAL * 2,
        'SALESMAN', SAL * 1.5,
        'ANALYST', SAL * 1.1,
         SAL) AS NEW_SAL 
FROM EMP

swtich-case처럼 각 케이스별로 나누어 계산식을 정해주고 그 외에 아무것도 조건에 합당하지 않으면 가장 밑에 있는 로직이 수행되도록 한다. 참고로 마지막의 반환 케이스를 정해주지 않으면 NULL이 저장된다.

 

CASE

CASE 또한 DECODE 함수와 비슷하다. 조건절이라는 명목은 똑같으나 DECODE는 switch-case에, CASE는 if-else문에 가깝다. DECODE 자체를 CASE로 리팩토링하는 것은 가능하지만 CASE를 DECODE로 리팩토링하기에는 어렵다. 즉, CASE문이 범용성이 더 가깝다고 보면 된다.

 

SELECT EMPNO, ENAME, JOB,
        CASE JOB
        WHEN 'MANAGER' THEN SAL * 2
        WHEN 'SALESMAN' THEN SAL * 1.5
        WHEN 'ANALYST' THEN SAL * 1.1
        ELSE SAL
        END
        AS NEW_SAL
FROM EMP

결과는 위의 DECODE와 같다.