02. 함수

JAVA 웹 개발/2. Oracle

02. 함수

코딩 펭귄 2022. 6. 8. 10:50

📅 2021.09.09 ~ 2021.09.10

함수

하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브 프로그램으로 호출하여 값을 전달하면 결과를 리턴하는 방식으로 사용된다.
단일행 함수 : 여러 개의 값을 전달하면 결과가 행마다 적용되어 여러 개가 나오는 함수로 각 행마다 반복적으로 적용되어 입력 받은 행의 개수만큼 결과를 반환한다.
그룹 함수 : 여러 개의 값을 전달해도 결과가 하나만 나오는 함수로 특정 행들의 집합으로 그룹이 형성되어 적용되고, 한 그룹 당 1개의 결과를 반환한다.
단일행함수와 그룹함수는 결과 행의 개수가 다르기 때문에 함께 사용할 수 없다.
DUAL은 가상 테이블을 말하며, 테이블의 데이터를 사용하지 않는 경우 사용할 수 있다.
```
SELECT LENGTH('오라클'), LENGTHB('오라클')
FROM DUAL;
```

단일행 함수

문자 관련 함수
- LPAD/RPAD
  - LPAD: 제시한 숫자만큼의 공간을 만들어 놓고 남는 공간은 임의의 문자로 왼쪽에 채워 넣는다.
  - RPAD: 제시한 숫자만큼의 공간을 만들어 놓고 남는 공간은 임의의 문자로 오른쪽에 채워 넣는다.
  만약 채워 넣을 문자를 지정하지 않는 경우에는 공백이 채워진다.
- LTRIM/RTRIM/TRIM
  - LTRIM : 왼쪽 부분의 공백 또는 지정한 문자를 제거한다
  - RTRIM : 오른쪽 부분의 공백 또는 지정한 문자를 제거한다.
  - TRIM: 양쪽의 공백 또는 지정한 문자를 제거한다.
  LTRIM/RTRIM의 경우 제거할 문자열을 입력할 수 있으나, 해당 문자열을 통째로 인식하는 것이 아니라, 각각 개별의 문자로 인식하여 해당 문자열에 존재하는 문자가 없을 때까지 제거하기 때문에 주의하여야 한다.
  
  TRIM의 경우 LTRIM/RTRIM처럼 여러 개의 문자(문자열)를 제거 문자로 설정할 수 없고, 하나의 문자만 지정이 가능하다. 또한 다음 명령어들과 함께 사용할 수 있다.
  LEADING : 앞에서부터 제거 (LTRIM의 의미)
  TRAILING : 뒤에서부터 제거 (RTRIM의 의미)
  BOTH : 양쪽 제거 (TRIM의 의미)
- SUBSTR : JAVA의 String.subString()과 같은 역할을 하는 함수로, 내가 원하는 위치에 있는 문자열 일부를 반환한다.
```
SELECT SUBSTR('SHOWMETHEMONEY', 7) FROM DUAL;
-- 7번째 문자부터 끝까지 반환
SELECT SUBSTR('SHOWMETHEMONEY', 5, 2) FROM DUAL;
-- 5번째부터 2개를 반환
```
  만약 파리마터에 0을 넣는 경우에는 null이 반환되며, 몇 번째부터 반환할 것인가에 대한 숫자에 음수가 들어가는 경우 끝에서부터 세어 해당 번째의 문자열을 반환한다. 이때 반환하는 문자의 개수가 존재하는 경우에는 해당 문자부터 정방향으로 카운트한다.
- LOWER/UPPER/INITCAP
  - LOWER : 문자열 전체를 소문자로 바꿔 반환한다.
  - UPPER : 문자열 전체를 대문자로 바꿔 반환한다.
  - INITCAP : 단어의 첫 글자만 대문자로 바꿔 반환한다.
- CONCAT : 두 개의 문자열을 붙여 준다. || 연산자를 이용하여도 결과는 같다.
- REPLACE : 지정한 문자를 대체할 문자로 변경해 준다.
숫자 관련 함수
- ABS : 절댓값을 계산하여 반환한다.
- MOD : 나머지를 계산하여 반환한다.
- ROUND : 자릿수를 지정하지 않은 경우 소수 첫 번째 자리에서 반올림하며, 숫자를 지정하는 경우 해당 자릿수에서 반올림한다.
  1 : 소수 두 번째 자리에서 반올림
  0 : 소수 첫 번째 자리에서 반올림
  -1 : 일의 자리에서 반올림
- FLOOR : 숫자를 내림하여 반환하며, 자릿수 지정이 불가능하다. 수학적으로 숫자를 내리는 것을 의미한다.
- TRUNC : 수학적 의미의 내림이 아닌 소수점 이하의 숫자를 절삭하여 반환한다.
- CEIL : 숫자를 올려 반환한다.
날짜 관련 함수
- SYSDATE : 현재 시간을 가지고 있는 함수
- MONTHS_BETWEEN : 개월 수의 차이를 숫자로 리턴하며, 매개변수의 순서가 과거, 미래인 경우 음수로 출력된다. 개월차를 구하는 경우 올림 처리를 진행한다.
- ADD_MONTHS : 날짜에 숫자만큼 개월 수를 더하여 날짜를 반환한다. 만약 12월이 넘어가는 경우 년도도 더해진다.
- NEXT_DAY : 기준 날짜에서 구하려는 요일에 가장 가까운 날짜를 반환한다. 문자 제일 앞의 글자를 인식하여 해당 요일의 날짜를 반환한다. (ex. 월, 월요일, 월남쌈 → 월요일 날짜 반환)
  숫자로도 요일 인식이 가능하다. (1=일, 2=월, 3=화, 4=수, 5=목, 6=금, 7=토)
  영어는 인식하지 못하며, 영어를 사용하려는 경우 아래와 같은 방법으로 세션을 바꿔 주어야 한다.
```
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
```
- LAST_DAY : 해당 월의 마지막 날짜를 구해 반환한다.
- EXTRACT : 년, 월, 일 정보를 추출하여 반환한다.

형변환 함수

TO_CHAR : 날짜형/숫자형 데이터를 문자형 데이터로 변경한다. 문자형 데이터는 왼쪽 정렬되며, 숫자형 데이터는 오른쪽 정렬 된다.
데이터의 형식을 지정하여 반환 가능하다.

-- 5자리, 숫자가 없는 부분은 공백으로 채워짐
SELECT TO_CHAR(1234, '99999') A FROM DUAL; 

-- 5자리, 숫자가 없는 부분은 0으로 채워짐
SELECT TO_CHAR(1234, '00000') A FROM DUAL; 

-- 원화 표시
SELECT TO_CHAR(1234, 'L99999') A FROM DUAL;

-- 원화 표시, 앞 부분 공백 삭제
SELECT TO_CHAR(1234, 'FML99999') A FROM DUAL; 

-- 달러 표시
SELECT TO_CHAR(1234, '$99999') A FROM DUAL;

-- ,를 이용해 자리수 구분
SELECT TO_CHAR(1234, '99,999') A FROM DUAL;

-- 자리수보다 표시할 숫자가 많은 경우 출력할 수 없다는 뜻으로 #### 출력됨
SELECT TO_CHAR(1234, '999') A FROM DUAL;

날짜형으로도 변롼 가능하며, 포멧 설정이 가능하다.

-- 오전, 오후는 현재 시각에 맞춰 설정되므로 AM/PM으로 변경한다고 해서 바뀌지 않는다
SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL; 

-- 24시간 기준으로 출력
SELECT TO_CHAR(SYSDATE, 'AM HH24:MI:SS') FROM DUAL;

-- 2021-09-09 형태로 출력
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') FROM DUAL;

-- 앞에 FM을 붙이는 경우 앞에 붙는 0을 제거할 수 있으며, MM에 FM을 붙이는 경우 DD까지 같이 제거된다
-- 만약 DD에 FM을 붙이는 경우에는 DD에만 0이 제거된다.
-- 따라서 MM에서만 0을 제거할 수는 없다.
SELECT TO_CHAR(SYSDATE, 'YYYY-FMMM-DD DAY') FROM DUAL;  

-- 년, 월, 일은 포멧이 아닌 데이터이기 때문에 ""로 묶어 줘야 한다.
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" DAY') FROM DUAL; 

-- 년도의 출력 방식
SELECT TO_CHAR(SYSDATE, 'YYYY'), 
        TO_CHAR(SYSDATE, 'YY'), 
        TO_CHAR(SYSDATE, 'YEAR')
FROM DUAL;

-- 달의 출력 방식
SELECT TO_CHAR(SYSDATE, 'MM'), 
        TO_CHAR(SYSDATE, 'MONTH'), 
        TO_CHAR(SYSDATE, 'MON'),
        TO_CHAR(SYSDATE, 'RM') -- 로마 숫자로 출력
FROM DUAL;

-- 일의 출력 방식
-- 해당 기준으로무터 며칠재인지 출력한다
SELECT TO_CHAR(SYSDATE, 'DDD') "1년 기준", 
        TO_CHAR(SYSDATE, 'DD') "달 기준", 
        TO_CHAR(SYSDATE, 'D') "주 기준" 
FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'Q'), -- 분기
        TO_CHAR(SYSDATE, 'DAY'), -- 요일
        TO_CHAR(SYSDATE, 'DY') -- 요일
FROM DUAL;

TO_DATE : 숫자형/문자형 데이터를 날짜형 데이터로 변경해준다.
TO_DATE에서 두 자리 연도에 YY를 적용시키는 경우 무조건 현재 세기(21세기, 2000년대)가 적용된다.
RR을 적용시키는 경우에는 두 자리 연도 수가 50 이상일 때는 이전 세기(20세기, 1900년대)를 적용하며, 50 미만일 때는 현재 세기를 적용한다. 따라서 두 자리 연도를 변경하는 경우에는 RR을 사용하는 것이 조금 더 좋다.
TO_NUMBER : 문자형 데이터를 숫자형 데이터로 변경해 준다. 날짜의 경우 변경 불가능하다.문자의 경우에도 아래와 같이 연산하는 경우 알아서 숫자형으로 바꾸어 연산해 주지만, 중간에 문자가 들어가는 경우에는 연산이 불가능하다. 이런 경우에 TO_NUMBER를 사용하여 형변환을 진행한다.
```
-- 문자도 숫자로 계산 가능
SELECT '1111' + '2222' FROM DUAL;

-- 중간에 , 문자가 들어가기 때문에 계산 불가능
SELECT '1,111' + '2,222' FROM DUAL;

-- 다음과 같이 숫자형으로 변경하여 계산
SELECT TO_NUMBER('1,111', '999,999') + TO_NUMBER('2,222', '9,999')
FROM DUAL;
```

NULL 처리 함수
- NYL : 해당 컬럼값이 NULL인 경우에 지정한 문자 또는 숫자를 삽입해 준다.
- NYL2 : NYL2(컬럼, 값1, 값2) 형태로 사용하여 해당 컬럼 값이 NULL이 아닌 경우에는 값1을, 해당 컬럼 값이 NULL인 경우에는 값2를 반환한다.
- NULLIF : 두 개의 값을 비교하여 값이 같은 경우 NULL은 반환하고, 값이 다른 경우 앞에 있는 값을 반환한다.
선택 함수 : 여러 개의 경우가 있고, 선택해야 하는 경우에 사용된다.
- DECODE : DECODE(계산식|컬럼명, 조건값1, 선택값1, 조건값2, 선택값2, ...) 형태로 사용되며, 비교하고자 하는 값/컬럼이 조건식과 같은 경우 바로 뒤에 있는 선택값을 반환한다. 특정 조건 제외 나머지가 같은 조건인 경우에는 마지막 조건값을 생략하고 선택값을 넣어 주면 된다.
- CASE WHEN: 아래와 같은 형태로 사용되며, 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과값을 반환한다. DECODE와는 다르게 범위에 대한 설정이 가능하다.
  CASE WHEN 조건식 THEN 결과값
  WHEN 조건식 THEN 결과값
  ELSE 결과값
  END

그룹 함수

SUM : 해당 컬럼의 총합을 반환하며, 결과 행 하나만 출력된다.
AVG : 해당 컬럼의 평균을 반환한다. 이때 NULL을 가진 행은 평균 산출에서 제외된다. 따라서 정확한 평균을 구하기 위해서는 NVL 함수를 이용하여 NULL 처리를 해 주어야 한다.
MAX/MIN : 가장 큰/작은 값을 반환하는 함수로 숫자, 문자, 날짜 모두에 적용이 가능하다.
COUNT : 숫자를 세어 반환한다.
- COUNT(컬럼명) : NULL을 제외한 해당 컬럼의 개수 리턴
- COUNT(DISTINCT 컬럼명) : 중복이 제거된 행 개수 리턴
- COUNT(*) : NULL을 포함한 전체 행 개수 리턴

저작자표시 비영리 동일조건 (새창열림)