[ORACLE DATABASE]함수(Function) 이용하기(1)

ORACLE DATABASE

함수(Function)?

개발자를 준비하거나 공부하는 프로그래머라면 함수에 대해 전반적으로 이해하고 있을것이다. 

보통은 반복적으로 사용되는 코드 부분을 분리하여 함수로 작성한다.

이 작성해 놓은 함수를 호출하면 원하는 결과값을 리턴받는 방식으로 사용한다.

 

단일행 함수

테이블의 모든 행에 결과를 반환하는 함수를말한다.

LENGTH(컬럼명||문자열 리터럴)

-- 이메일 글자수 확인하기
SELECT EMP_NAME, EMAIL, LENGTH(EMAIL) AS EMAIL_LENGTH
FROM EMPLOYEE;

컬럼명 결과

SELECT LENGTH('ORACLE DATABASE 열심히 공부해봅시다!!!')
FROM DUAL;

리터럴 결과

LENGTHB()

SELECT LENGTHB('ABCD'), LENGTHB('월요일')
FROM DUAL;

LENGTHB() 함수를 이용하면 문자열의 바이트수를 출력한다.

현재 학습용으로 사용하고 있는 EXPRESS버전에서는 영문자 1BYTE, 한글은 3BYTE를 차지하기 때문에 아래 결과를 얻을 수 있다.

INSTR(문자열||컬럼 , '찾을문자' [,시작위치,찾을횟수])

JAVA를 공부했기에 JAVA의 indexOf() 메소드와 유사하다고 생각하면 좋다. INSTR() 함수를 이용하면 원하는 문자의 인덱스 번호를 반환받아 '찾을문자'에 해당하는 인덱스번호를 결과로 얻을 수 있다.

-- 사원의 이메일이 j위치 찾기
SELECT EMP_NAME, EMAIL, INSTR(EMAIL,'j')
FROM EMPLOYEE;

LPAD/RPAD(문자열||컬럼, 지정된 최대길이, 공백을 채울 문자값)

문자열의 길이가 지정된 길이만큼 확보되지 않았을 때 특정값으로 빈  값을 채우는 함수

LPAD는 최대길이의 문자열의 왼쪽을 공백으로 두고 해당 문자열의 길이를 채우지 못하면 대체 문자로 공백을 채운다.

RPAD는 LPAD와 반대로 지정된 최대길이 문자열의 오른쪽 공백을 대체 문자로 채운다.

➡️ 대체문자를 입력하지 않고 최대길이만 지정했을 경우 해당 공백은 공백 그대로 출력한다.

SELECT LPAD('ORACLE',10,'*'),RPAD('ORACLE',10,'#'),LPAD('ORACLE',10)
FROM DUAL;

TRIM(문자열||컬럼)

TRIM(LEADING||TRAILING||BOTH 찾을문자열 FROM 문자열||컬럼)

TRIM은 대상 문자열의 앞뒤의 공백을 제거해주는 함수이다.

'LEADING '찾을 문자열'' 을 이용하면 앞 문자열에서 찾을 문자와 공백을 제거한다.

'TRAILING '찾을 문자열'' 을 이용하면 뒤 문자열에서 찾을 문자와 공백을 제거한다.

'BOTH '찾을 문자열'' 을 이용하면 앞,뒤 문자열에서 찾을 문자와 공백을 제거한다.

SELECT '        월요일    ',TRIM('        월요일    '),'ZZZZ ORACLE ZZZZ',TRIM(LEADING 'Z' FROM 'ZZZZ ORACLE ZZZZ'),
    TRIM(TRAILING 'Z' FROM 'ZZZZ ORACLE ZZZZ'), TRIM(BOTH 'Z' FROM 'ZZZZ ORACLE ZZZZ')
FROM DUAL;

 

SUBSTR(문자열||컬럼명,시작인덱스[,길이])

해당 문자열에서 원하는 문자열을 잘라내는 기능, JAVA의 substring()과 유사하다.

➡️ 사원의 이메일에서 아이디를 도출해내는 SQL문을 작성해보았다.

-- 이메일 아이디가 7글자 이상인 사원 조회하기
SELECT EMAIL, SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-1) AS EMAIL_ID
FROM EMPLOYEE
WHERE LENGTH(SUBSTR(EMAIL,1,INSTR(EMAIL,'@')-1))>=7;

문자열에 컬럼명 'EMAIL'을 적었고 시작인덱스는 아이디가 시작하는 인덱스번호 '1'을 기입하였다.

길이는 각 사원마다 아이디의 길이가 다르기때문에 INSTR() 함수를 이용하여 이메일에 공통적으로 적혀있는 '@'문자를 찾았고 해당 문자의 이전까지가 아이디가 되기 때문에 -1을 하여 이메일에 기입된 아이디 값문 추출될 수 있도록 작성하였다.

SUBSTR()을 이용한 SQL 결과

➡️ SUBSTR() 함수를 유용하게 사용할 수 있을거 같아 예제 하나를 더 보겠다.

-- 사원중 여사원만 출력하기, 사원명, 주민번호, 월급
SELECT EMP_NAME, EMP_NO, SALARY
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1)='2';

EMPLOYEE 테이블의 컬럼에는 직원의 주민번호인 'EMP_NO' 컬럼이 존재한다. 

보통 주민번호는 뒷자리의 첫 번호를 기준으로 '남','여' 구분을 할 수 있다. SUBSTR() 함수를 이용하여  '여'사원만 출력하는 RESULTSET(결과)을 얻어낼 수 있다. (2000년대생부터 뒷자리가 '3','4'로 시작하는 구분은 하지 않았음을 참고한다.)

EMPLOYEE 테이블에서 '여'사원만 출력한 결과