모방은 창조의 어머니

오라클(Oracle) 함수 정리

오라클 데이터베이스에서 사용되는 함수를 한곳에서 보고 싶어 보아 보았습니다.
오라클 함수는 크게 문자열 함수, 숫자 함수, 날짜 함수, 변환 함수, 집계 함수, 분석 함수로 구분 지어 정리.

1. 문자열 함수(String Functions)

• UPPER(문자열) : 문자열을 대문자로 변환하여 반환 합니다.
• LOWER(문자열) : 문자열을 소문자로 변환하여 반환 합니다.
• INITCAP(문자열) : 각 단어의 첫 글자를 대문자로 변환하여 반환 합니다.
• LENGTH(문자열) : 문자열의 길이를 반환합니다.
• LENGTHB(문자열) : 문자열의 길이를 바이트 단위로 구해서 반환합니다.
• SUBSTR(문자열, 시작 위치, 길이) : 지정한 위치에서 부분 문자열을 추출합니다.
• INSTR(문자열, 검색 문자열, 시작 위치, 발생 횟수) : 특정 문자열이 나타나는 위치를 반환합니다.
• REPLACE(문자열, 검색 문자열, 대체 문자열) : 특정 문자열을 다른 문자열로 대체합니다.
• LPAD(문자열, 검색 문자열, 길이, 대체 문자열) : 문자열에서 길이만큼 대체 문자열로 문자열 왼쪽을 대체 합니다.
• RPAD(문자열, 검색 문자열, 길이, 대체 문자열) : 문자열에서 길이만큼 대체 문자열로 문자열 오쪽을 대체 합니다.
• CONCAT(문자열1, 문자열2 ) : 두 개의 문자열을 연결합니다.
• TRIM(문자열) : 문자열의 앞뒤 공백을 제거합니다.

2. 숫자 함수(Numeric Functions)

• ROUND(숫자, 소수점 자리) : 지정한 자리에서 반올림합니다.
• TRUNC(숫자, 소수점 자리) : 지정한 자리에서 절삭합니다.
• MOD(숫자1, 숫자2) : 나머지 연산을 수행합니다.
• CEIL(숫자) : 올림 값을 반환합니다.
• FLOOR(숫자) : 내림 값을 반환합니다.
• ABS(숫자) : 절댓값을 반환합니다.

3. 날짜 함수(Date Functions)

• SYSDATE : 현재 날짜 및 시간을 반환합니다.
• CURRENT_DATE : 세션의 현재 날짜를 반환합니다.
• ADD_MONTHS(날짜, 개월 수) : 날짜에 지정한 개월 수를 추가합니다.
• MONTHS_BETWEEN(날짜1, 날짜2) : 두 날짜 간 개월 수를 반환합니다.
• NEXT_DAY(날짜, 요일) : 지정한 날짜 이후의 가장 가까운 해당 요일을 반환합니다.
• LAST_DAY(날짜) : 해당 월의 마지막 날을 반환합니다.
• EXTRACT(YEAR | MONTH | DAY FROM 날짜) : 날짜에서 연, 월, 일을 추출합니다.

4. 변환 함수(Conversion Functions)

• TO_CHAR(날짜 또는 숫자, 형식) : 날짜 또는 숫자를 문자열로 변환합니다.
• TO_DATE(문자열, 형식) : 문자열을 날짜로 변환합니다.
• TO_NUMBER(문자열, 형식) : 문자열을 숫자로 변환합니다.

5. 집계 함수(Aggregate Functions)

• COUNT(컬럼) : 행 개수를 반환합니다.
• SUM(컬럼) : 합계를 반환합니다.
  AVG(컬럼) : 평균 값을 반환합니다.
• MAX(컬럼) : 최대값을 반환합니다.
• MIN(컬럼) : 최소값을 반환합니다.
• GROUP BY 컬럼 : 그룹별 집계를 수행합니다.

6. 분석 함수(Analytic Functions)

• RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) : 순위를 반환하며, 동일 값에 동일 순위를 부여합니다.
• DENSE_RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) : 중복 없이 연속된 순위를 반환합니다.
• ROW_NUMBER() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) : 행 번호를 반환합니다.
• LEAD(컬럼, 이동 행 수, 기본값) OVER (ORDER BY 컬럼) : 다음 행의 값을 반환합니다.
• LAG(컬럼, 이동 행 수, 기본값) OVER (ORDER BY 컬럼) : 이전 행의 값을 반환합니다.
• NTILE(N) OVER (ORDER BY 컬럼) : 전체 데이터를 N개의 그룹으로 나눕니다.

7. 기타 함수

• NVL(컬럼, 기본값) : NULL 값을 기본값으로 대체합니다.
• NVL2(컬럼, 값1, 값2) : 컬럼이 NULL이 아닐 경우 값1, NULL이면 값2를 반환합니다.
• DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, ...) : IF-ELSE와 유사한 조건 처리를 수행합니다.
• CASE WHEN 조건 THEN 결과 ELSE 결과 END : 조건문을 처리합니다.

NULL에 관하여

Null 또는 NULL은 구조적 질의언어(SQL)에서 데이터베이스 내의 데이터 값이 존재하지 않는다는 것을 지시하는 데 사용되는 특별한 표시어(special marker)이다' _ 위키 

데이터베이스를 처음 공부하시는 분들이 의외로 많이 혼동스러워 하시는 개념 입니다. '값이 존재하지 않는다'라는 말은 NULL은 비어 있는 공백이나, 숫자에서 없음을 뜻하는 0이 아닙니다. 말 그대로 값이 없는 상태입니다. 

비어 있기 때문에 연산을 수행 할 수도 없습니다.

• NULL + 숫자 = NULL
• NULL > 숫자 = NULL
•  

아직 부여되지 않은 값을 의미 합니다. 예를들어 아직 태어나지 않은 아이의 생일,

직원 중 수당(SAL)이 없는(NULL) 직원을 조회하고 싶은 경우 아래와 같이 조회 하시면 됩니다. 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

SELECT *
FROM EMP
WHERE COMM IS NULL;

예제에서 NULL인 컬럼을 조회하기 위해서 'IS NULL' 연산자를 사용하였습니다. 혹시 'COMM = NULL'이라고 하면 NULL인 컬럼을 검색할 수 있지 않을까 생각하시는 분도 계실 것 같아 직접 쿼리를 수행해 보았습니다.

보시는 바와 같이 아무런 결과 값을 출력하지 않습니다. 쿼리를 통한 연산에서 값이 'TRUE'인 경우에 결과를 출력하는데, NULL과의 연산 결과는 NULL로 결과 값이 'TRUE'도 'FALSE'도 아니기 때문에 결과값으로 출력되지 않습니다

논리 연산자와 NULL의 관계

AND 연산자

OR 연산자

테이블을 삭제하는 DROP

더 이상 사용하지 않는 테이블을 삭제할 때 DROP 명령어를 사용하시면 됩니다.

DROP 기본형식

DROP TABLE 테이블 명;

DROP 사용 예

실습을 위해 만들어 놓았던 DEPT_BAK 테이블을 삭제해 봅시다.

DROP TABLE DEPT_BAK04;

문자에 포함된 %, _ 찾기

데이터에서 '%'나 '_'가 포함되어 있는 경우 어떻게 찾으면 될까요? LIKE문에서 '%'와 '_'는  와일드 카드로 사용되어 일반적인 방법으로는 해당 문자열의 포함여부를 찾으실수 없습니다. 이때에는 ESCAPE절을 사용하시면 됩니다. 문자열에서 '0_0'로 시작하는 데이터를 찾으려면 아래와 같이 SQL문을 작성하시면 됩니다.

SELECT *
FROM SAMPLE_TABLE
WHERE SAMPLE_COLUMN LIKE '0\_0%' ESCAPE '\';

'\'는 '\'문자 바로 뒤에 있는 '_'를 와일드 카드가 아닌 문자로 인식하라는 의미입니다. 이스케이프 문자 '\'는 ESCAPE절에서 지정할 수 있습니다. '\'이외에 다른 문자도 지정하여 사용 할 수 있습니다.

[Oracle] 형변환 함수 - 날짜/숫자 → 문자열 TO_CHAR 함수 

TO_CHAR 함수는 날짜 데이터를 문자열 데이터로 바꾸거나, 숫자 데이터를 문자열 데이터로 변환합니다.

TO_CHAR( 날짜 데이터, 출력하길 원하는 문자 형태 )

기준 포멧