오라클 DB 용어, 명령문 정리02 - 함수(문자, 숫자, 날짜, 단일, 그룹)

2019. 05. 20 필기

 

 

※ 가독성을 위해 명령어는 대문자, 인자값은 소문자로 표기하였으나 실제로는 상관 없음.(오라클은 문자상수가 아닌 경우 대, 소문자를 구분하지 않는다.)

 

<오라클 함수 개요>

• 함수(FUNCTION)란? : 인자로 값을 전달받아 원하는 형태로 가공하여 결과값을 반환하는 기능
• 단일함수 : 인자로 값을 한 개만 전달받아 결과 반환. 단일행함수라고 부르기도 함
                -- 단일함수에는 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수로 구분
• 그룹함수 : 인자로 값을 여러 개 전달받아 결과 반환.

 

<문자함수>

• 문자함수란? : 문자형 상수를 전달받아 가공하여 결과를 반환하는 함수
• UPPER('text') : 전달받은 문자형 상수를 모두 대문자로 변환
• LOWER('text') : 전달받은 문자형 상수를 모두 소문자로 변환
• INITCAP('text') : 전달받은 문자형 상수의 첫문자를 대문자로, 나머지는 소문자로 변환
• CONCAT('text1', 'text2') : text1과 text2를 결합 --- || 연산자와 동일
• SUBSTR('text', index, n) : 전달받은 문자형 상수에서 INDEX 위치부터 갯수만큼의 문자들을 분리
• LENGTH('text') : 전달받은 문자형 상수의 문자개수 반환
• INSTR('text', 'search_txt', index, start_n, occur_n) : 전달받은 문자형 상수에서 검색 문자형 상수를 INDEX 위치부터 검색하여 n번째 일치하는 값의 INDEX값 반환 --- 검색결과가 없으면 0을 반환
• LPAD('text', n, 'padding_txt') / RPAD('text', n, 'padding_txt')
   : 전달받은 문자형 상수를 n자릿수 만큼의 길이로 오른쪽 정렬하여 출력하고, 왼쪽(오른쪽) 남은 자릿수를 원하는 문자로 채워 반환
  --- ex) LPAD(EMPNO, 8, '*') ==> ****1438, ***21754 (8자리 출력, 비는 공간은 * 출력)
• TRIM({LEADING|TRAILING} 'delete_txt' FROM 'text') : 전달받은 문자형 상수의 맨 앞(LEADING) 또는 맨 뒤(TRAILING)부터 시작하여, 원하는 문자를 없애고 반환 --- 만약 같은 문자가 연속되어 붙어있을 경우 모두 지워짐
• REPLACE('text', 'search_txt', 'replace_txt') : 전달받은 문자형 상수에서 검색문자를 찾아 변환문자로 변환

 

 

 

<숫자함수>

• 숫자함수란? : 숫자형 상수를 전달받아 가공하여 결과를 반환하는 함수
• ROUND('text', n) : 전달받은 숫자형 함수를 소수점 n자릿수까지 반올림하여 반환
  --- 소숫점자릿수가 1 이상의 숫자면 소숫점 n자리수까지, 0이면 1의자리 수까지, -1이면 십의자리까지 반환한다는 뜻
• TRUNC('text', n) : 전달받은 숫자형 상수를 소수점 n자릿수까지 버림하여 반환
• CEIL('text') : 전달받은 숫자형 상수에서 소수점 이하 값이 존재할 경우 올림하여 반환
• FLOOR('text') : 전달받은 숫자형 상수에서 소수점 이하 값이 존재할 경우 내림하여 반환
  --- ※주의 : 음수의 경우, -15.3의 올림은 -15, 내림은 -16이다.
• MOD(n, divisor_n) : 전달받은 숫자형 상수를 나누어 나머지 값을 반환
• POWER(n1, n2) : 전달받은 숫자형 상수의 제곱값을 반환

 

<날짜함수>

• 날짜함수란? : 날짜형 상수를 전달받아 가공하여 결과를 반환하는 함수. 오라클에서는 자료형이 날짜형인 경우 내부적으로 날짜와 시간정보가 저장되지만, 표면적으로는 'RR/MM/DD'라고 표현
• ※오라클에 접속된 사용자 환경(세션:SESSION)에 따라 날짜와 시간정보를 다른 언어로 표현 가능
  ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; --- 환경설정>데이터베이스>NLS에서 변경도 가능
• SYSDATE : 오라클에서 시스템의 현재 날짜 및 시간을 제공하기 위한 키워드
• ADD_MONTHS('date', n) : 전달된 날짜형 상수에 n개월 더한 결과를 반환
• NEXT_DAY('date', 'day') : 전달된 날짜형 상수를 기준으로 다가오는 특정 요일의 날짜형 상수 반환
  --- 한국어 버전을 깔면 기본적으로 한국어 세팅이므로, '화' 이런 식으로 표기 가능
• TRUNC('date', 'date_unit') : 전달받은 날짜형 상수를 원하는 단위까지만(day/month/year 등) 표현하며, 나머지는 초기값으로(01) 표현되도록 하여 반환
• 'date' + - n : 날짜 증가 또는 감소
• 'date' + - n/24 : 시간 증가 또는 감소
• 'date1' - 'date2' : date1으로부터 경과한 일수를 계산
• ※ 날짜형의 연산은 '문자형'-날짜형은 불가능! (ex)'2000-01-01'-SYSDATE)

 

<변환함수>

• 변환함수란? : 전달받은 상수를 원하는 자료형의 값으로 변경하여 반환하는 함수
• TO_NUMBER('number') : 전달받은 문자형 상수를 숫자형 상수로 변환하여 반환
  --- 전달값이 숫자 형태가 아닌 경우 에러 발생
  --- 비교 컬럼의 자료형이 숫자형인 경우, 비교 대상을 자동으로 숫자형 상수로 변환하여 비교 : 자동형변환
• TO_DATE('text', [pattern]) : 전달받은 문자형 상수를 날짜형 상수로 변환하여 반환
  --- 패턴은 'text'의 형식을 알려주는 것! 출력패턴이 아님! (생략 가능)
  --- 비교 컬럼의 자료형이 날짜형인 경우 비교 대상이 날짜형으로 자동형변환 되어 비교
• TO_CHAR({n | 'date'}, chage_pattern) : 숫자형 상수 또는 날짜형 상수를 전달받아 패턴기호를 변환하여 반환
  --- 날짜패턴기호: YYYY(년), RR(년), MM(월), DD(일), HH24(시간-24시간제), HH12(시간-12시간제), AM(오전), PM(오후), MI(분), SS(초)
  --- 숫자패턴기호 : 9(숫자 또는 공백), 0(숫자), L(화폐 단위), $(달러)
  --- 날짜형 상수를 원하는 날짜패턴기호로 변환하여 문자를 비교하여 검색

 

 

<일반함수>

• 일반함수란? : 전달값이 특정 조건일 경우 처리하여 반환하는 함수
• NVL(culumn, change) : 전달받은 컬럼값이 null인 경우, 변경값으로 변환하여 반환
  --- 컬럼의 자료형과 일치하지 않을 경우 에러 발생
  --- 예를 들어 null=/0 이므로, 100+null 은 100이 아닌 (null)과 같이 이상값으로 나옴. nvl(culumn, 0) 으로 치환해야 함
• NVL2(culumn, change1, change2) : 전달받은 컬럼값이 null이 아닌 경우 변경값1로 변환하고, null인 경우 변경값2로 변환하여 반환. 변환값에는 연산식도 들어갈 수 있다.
• DECODE(culumn, compare1, change1, compare1, change1,...,[, change_basic]} : 전달받은 컬럼값을 비교값과 비교하여 같은 경우 변경값으로 변환
  --- 동일한 비교값이 존재하지 않을 경우 기본변경값으로 변환(기본변경값을 생략한 경우 null 반환) 단, 자료형이 일치해야 한다.

 

<그룹함수>

• 그룹함수란? : 인자로 값을 여러개 전달받아 결과를 반환하는 함수
• ※ 그룹함수에서는 null값을 값으로 인식하지 않는다
• COUNT(culumn) : 컬럼값을 전달받아 행의 개수 반환
  --- null값은 값으로 인식하지 않으므로 카운트 되지 않음
  --- 다른 검색대상과 함께 사용할 수 없다!
  --- 컬럼값에 *을 넣어 COUNT(*)형태로 사용 가능 : 테이블에 저장된 행의 개수 검색 시 많이 사용
• MIN(culumn), MAX(culumn) : 전달된 컬럼값 중 최소값 반환
• SUM(culumn) : 전달받은 컬럼값들의 합계를 계산하여 반환 - 컬럼값은 반드시 숫자형
• AVG(culumn) : 전달받은 컬럼값들의 평균을 계산하여 반환 - 컬럼값:숫자형, 결과값:실수형
  --- 보기좋게 만들려면 ROUND, TRUNC, CEIL, FLOOR 등 사용해서 표현
• GROUP BY : 그룹함수를 사용할 경우, 컬럼값으로 그룹을 세분화하여 검색하기 위한 키워드
  --- 컬럼값이 동일한 경우 같은 그룹으로 처리
• SELECT 그룹함수 [, 검색대상] [ALIAS]
  FROM 테이블명
  WHERE 조건식 
  GROUP BY {컬럼명|연산식|함수} -- 여기엔 ALIAS 올 수 없음
  ORDER BY {컬럼명|연산식|함수|INDEX|ALIAS} -- ORDER BY는 항상 마지막!
  --- *순서 중요* SELECT-FROM-WHERE-GROUP BY-ORDER BY 순 
  --- GROUP BY 로 지정된 그룹의 경우, 검색대상으로 사용하길 권장
• HAVING : GROUP BY 명령으로 세분화 된 그룹에서, 그룹에 대한 조건을 부여하여 검색하기 위한 키워드. 즉, 행이 아닌 그룹에 대한 조건식
  
• SELECT 그룹함수 [, 검색대상]
  FROM 테이블명
  WHERE 조건식
  GROUP BY {컬럼명|연산식|함수}
  HAVING 그룹조건식
  ORDER BY {컬럼명|연산식|함수|INDEX}
  --- 그룹조건식은 그룹함수를 이용하여 표현 가능
  
  
  

** 예시 ** 결과는 같으나, 쓰임새가 좀 다름

SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP WHERE JOB<>'PRESIDENT' GROUP BY JOB; -- 데이터가 많을수록 좋음
SELECT JOB, CEIL(AVG(SAL)) AVG_SAL FROM EMP GROUP BY JOB HAVING JOB<>'PRESIDENT'; -- 데이터가 적을수록 좋음

 

<기타>

• USER 키워드 : 현재 접속 사용자의 이름을(사용자 스키마) 표현하기 위한 키워드