Day34 :) 내장함수

Nux·2021년 10월 25일
0

자바웹개발

목록 보기
35/107
post-thumbnail

내장함수

  • SQL 작성 시 유용한 기능을 제공하는 함수
  • RDBMS마다 조금씩 다름
공통점차이점
함수이름이 있는 코드블럭객체에 종속되어 있지 않음
메서드이름이 있는 코드블럭객체에 종속되어 있음

오라클 내장함수(빌트인 함수)

단일행 함수

  • 행마다 함수 실행

문자함수

대소문자 반환

  • LOWER(컬럼): 소문자를 반환함
  • UPPER(컬럼): 대문자를 반환함
SELECT LOWER(FIRST_NAME), UPPER(LAST_NAME)
FROM EMPLOYEES;

문자열 잘라내기

  • SUBSTR(컬럼, 시작위치, 길이)
  • ORACLE의 인덱스는 0이 아닌 1부터 시작하므로 참고 할 것
SELECT SUBSTR('오라클공부하기', 4)
FROM DUAL;
-- 공부하기
SELECT SUBSTR('오라클공부하기', -2)
FROM DUAL;
-- 하기
SELECT SUBSTR('오라클공부하기', 4, 2)
FROM DUAL;
-- 공부
SELECT SUBSTR('오라클공부하기', -5, 3)
FROM DUAL;
-- 클공부

문자열 길이 조회

  • LENGTH(컬럼): 문자열의 길이를 반환
  • LENGTHB(컬럼): 바이트 수 반환. 한글은 1자 당 3바이트
SELECT LENGTH('안녕하세요')	-- 글자수 반환(5)
	  ,LENGTHB('안녕하세요') -- 바이트 수 반환(15)
FROM DUAL;
ORDER BY LENGTH(FIRST_NAME) DESC;
-- 글자수 내림차순으로 정렬 

문자열 채우기

  • LPAD(컬럼, 글자 수, 패딩문자): 왼쪽부터 패딩문자로 자릿수를 채워줌
SELECT LPAD(10, 5)	    --    10(앞에 공백 3칸)
	  ,LPAD(10, 5, '0') -- 00010
	  ,LPAD(10, 5, 'A') -- AAA10
  • RPAD(컬럼, 글자 수, 패딩문자): 오른쪽부터 패딩문자로 자릿수를 채워줌
SELECT RPAD(10, 5)	    -- 10   (뒤에 공백 3칸)
	  ,RPAD(10, 5, '#') -- 10###

문자열 연결하기

  • CONCAT(컬럼명1, 2): 지정된 컬럼의 값을 이어붙임(2개가 최대)
SELECT FIRST_NAME, LAST_NAME, CONCAT(FIRST_NAME, LAST_NAME)
FROM EMPLOYEES;

  • 컬럼명1 || 컬럼명2 || 컬럼명3||...||컬럼명N : 계속 이어붙일 수 있음
SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYESS;

불필요한 공백 없애기

  • TRIM(컬럼명): 문자열 끝과 끝에 위치한 공백을 삭제함(문자 사이의 공백은 지우지 않음)
SELECT TRIM('              ABC      DEF             GHI             ')
FROM DUAL;

문자열 바꾸기

  • REPLACE(컬럼명, 찾는 문자열, 대체할 문자열)
SELECT FIRST_NAME, REPLACE(FIRST_NAME, 'a', '*')
FROM EMPLOYEES;
- FIRST_NAME의 A가 모두 *로 변경됨

문자열 찾기

  • INSTR(컬럼명, '찾을 문자열'): 지정한 컬럼에서 찾는 문자의 위치를 숫자로 반환
SELECT INSTR(FIRST_NAME, 'a')
FROM EMPLOYEES;
-- 이름에 'a'가 들어가는 값의 위차가 반환됨
-- Neena -> 5반환
-- Laura -> 2반환(나타나는 최초값만 반환)

숫자함수

반올림

  • round(반올림 할 숫자, 반올림 할 자리수)
    • 자리수가 양의 정수면 소수점 자리수
    • 자리수가 음의 정수면 일의 자리, 십의 자리, 백의자리 등으로 반올림
SELECT ROUND(123.45, 1), ROUND(123.45, 3)
      ,ROUND(123.45, -1), ROUND(123.45, -2)
FROM DUAL;      

  • 반올림할 자리수가 반올림할 숫자의 자리수를 초과하면 변동없이 숫자 그대로 출력됨

소수점 값 버리기

  • ROUND(컬럼): 반올림 숫자를 지정하지 않으면 소수점 첫번째 자리에서 반올림함
  • TRUNC(컬럼): 소수점 아래를 무조건 버림
SELECT ROUND(123.45), TURNC(123.45)
FROM DUAL;
-- ROUND는 124, TRUNC는 123이 출력됨

천장값과 바닥값

  • CEIL(컬럼): 자신보다 큰 정수 중 가장 작은 정수 반환
  • FLOOR(컬럼): 자신보다 작은 정수 중 가장 큰 정수 반환
SELECT CEIL(2.6), FLOOR(2.6)
FROM DUAL;
-- CEIL: 3, FLOOR: 2

나머지 구하기

  • MOD(숫자1, 2): 첫번째 숫자를 두번째 숫자로 나누고, 나머지 반환
SELECT MOD(10,3), MOD(5,3)
FROM DUAL;
-- 각각 1, 2 출력

날짜함수

현재날짜

  • SYSDATE: 시스템의 현재 날짜와 시간정보 반환
    • 도구-환경설정-데이터베이스:NLS에서 형식 변환 가능

날짜 반올림

  • ROUND(날짜): 해당 날짜의 시간이 정오를 넘었을 경우, 다음날 0시 0분 0초로 반환
SELECT ROUND(SYSDATE)
FROM DUAL;

시간정보 지우기

  • TRUNC(날짜): 해당 날짜의 모든 시간정보를 0으로 바꾼 후 반환
SELECT TRUNC(SYSDATE)
FROM DUAL;

개월수 산출

  • MONTHS_BETWEEN(날짜1, 날짜2): 두 날짜 사이의 개월수 표시
SELECT MONTHS_BETWEEN(SYSDATE, HIR_DATE)
FROM EMPLOYEES
-- SYSDATE와 HIRE_DATE 간의 개월수 반환

개월수 더하기

  • ADD_MONTHS(날짜, 개월수): 날짜에서 지정된 개월 수 만큼 변경 된 날짜 반환
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -3), ADD_MONTHS(SYSDATE, 3)
FROM DUAL;

날짜 연산

  • 날짜+숫자: 날짜에서 지정된 숫자만큼 일수가 경과된 날짜 반환
  • 날짜-숫자: 날짜에서 지정된 숫자만큼 일수가 감소 된 날짜 반환
  • 날짜-날짜: 두 날짜 사이의 일수 반환
  • 날짜+날짜: 오류 발생
  • 날짜+숫자/24: 지정된 숫자만큼 시간을 증가시킴 +1/24(1시간 증가)
  • 날짜-숫자/24: 지정된 숫자만큼 시간을 감소시킴 -1/24(1시간 감소)

기타함수

NVL

  • NVL(컬럼명, NULL일 때 대체값): 컬럼이 NULL일 때 대체값 반환.
    컬럼값과 대체값의 데이터 타입이 같아야 함.
  • NVL2(컬럼명, 값1, 값2): 컬럼 값이 NULL이 아니면 값1을 반환하고 NULL이면 값2를 반환
    컬럼값과 대체값의 데이터 타입이 같아야함.

CASE

CASE
	WHEN 조건식1 THEN 표현식1
	WHEN 조건식2 THEN 표현식2
	...
    ELSE 표현식3
END    
  • TURE인 조건식의 표현식을 반환
  • 모두 FALSE일 경우 ELSE 뒤의 표현식3을 반환
  • 자바의 IF ELSE구문과 비슷함
CASE 값
	WHEN 값1 THEN 표현식1
    	WHEN 값2 THEN 표현식2
        ...
        ELSE 표현식3
END        
  • CASE 뒤의 값이 WHEN 뒤의 값과 일치하면, 해당하는 표현식 반환
  • 모두 FALSE이면 ELSE 뒤의 표현식3 반환
  • 자바의 SWITCH구문과 비슷함

DECODE

DECODE(컬럼명 값1, 표현식1,
	     값2, 표현식2,
             ...
             표현식4)
  • 값과 일치하는 표현식을 반환함
  • Equal 비교만 가능
  • 값이 모두 false 일 경우 표현식4를 반환하고, 표현식4가 지정되어 있지 않으면 null반환

변환함수

  • 묵시적형변환: RDBMS가 자동으로 데이터 타입을 변환함
SELECT '1000' * 10, '1000'/10
FROM DUAL;
-- '1000'은 문자열이지만 자동으로 숫자로 변환됨
  • 명시적형변환: 변환함수를 이용해 데이터 타입을 변환함
    • 숫자👉문자: TO_CHAR(숫자, '패턴')
    SELECT TO_CHAR(123.456, '999.999')
    FROM DUAL;
    -- 출력값: 123.456
    SELECT TO_CHAR(123, '00000')
    FROM DUAL;
    -- 출력값: 00123
    • 문자👉숫자: TO_NUMBER('문자', '패턴')
    SELECT TO_NUMBER('100,000', '999,999')
    FROM DUAL;
    -- 출력값: 100000
    • 날짜👉문자: TO_CHAR(날짜, '패턴')
    SELECT TO_CHAR(sysdate, 'YYYY-MM-DD') AS 변환날짜 
    FROM DUAL;
    -- 출력값: 2021-10-25
    • 문자👉날짜: TO_DATE('날짜형식의문자', '날짜패턴')
    SELECT TO_DATE('20211025_123456', 'YYYYMMDD_HH24MISS')
    FROM DUAL;
    -- 출력값: 20211025_오후12:34:56
    • 패턴문자
      • 9: 딱 떨어지게 계산되며 주로 정수를 다룰때 사용
      • 0: 빈자리에 0이 채워지며 주로 소수를 다룰때 사용
      • , : 천단위 표시
      • . : 소수점 표시
      • FM: 문자열 공백 제거

다중행 함수

  • group by절로 묶어서 그룹 당 하나의 결과 계산
  • sum(컬럼명): 해당 컬럼의 합계 반환. null행 제외
  • avg(컬럼명): 해당 컬럼의 평균값 반환. null행 제외
  • max(컬럼명): 해당 컬럼의 최대값 반환. null행 제외
  • min(컬럼명): 해당 컬럼의 최소값 반환. null행 제외
  • count(* 혹은 컬럼명): 행의 갯수 반환. *인 경우 해당 컬럼 값이 null이어도 포함시킴. 컬럼명을 적으면 해당 컬럼이 null 일 시 제외됨

0개의 댓글