[Lecture] DataBase - Day 3

🖥 DataBase - Day 3

👉🏻 [Chapter 06] 데이터 처리와 가공을 위한 오라클 함수

[01] 오라클 함수

• function : x -> y
  • y값은 x값에 종속됨
• 오라클 함수 : 특정 결과 데이터를 얻기 위해 데이터를 입력할 수 있는 특수 명령어
  • built-in function : 기본 제공 내장 함수
    • single-row function : 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수
    • multiple-row function : 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수
  • user-defined function : 사용자에 의해 정의된 사용자 정의 함수

[02] 문자 데이터를 가공하는 문자 함수

• 대,소문자 변환 함수
  • upper(문자열) : 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환
  • lower(문자열) : 괄호 안 문자 데이터를 모두 소문자로 변환하여 반환
  • initcap(문자열) : 괄호 안 문자 데이터 중 첫 글자는 대문자로, 나머지 문자를 소문자로 변환 후 반환
  • select upper(CustomerName), lower(ContactName), initcap(City) from Customers;
  • select * from EMP where upper(ENAME) = upper('%scott%');
  • select * from EMP where upper(ENAME) like upper('%scott%');
• 문자열 길이 함수
  • length : 문자열 길이 값 출력
  • lengthb : 문자열 길이의 바이트 값 출력
  • select CustomerName, length(CustomerName) from Customers;
• 문자열 추출 함수
  • substr(문자열 데이터, 시작 위치, 추출 길이) : 문자열 데이터의 시작 위치부터 추출 길이만큼 추출
    • 시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 시작
  • substr(문자열 데이터, 시작 위치) : 문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출
    • 시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출
  • select CustomerName, substr(CustomerName, 1, 2), substr(CustomerName, 3, 2), substr(CustomerName, 5) from Customers;
• 문자열에서 특정 문자 위치를 찾는 함수
  • instr : 특정 문자의 위치 추출
  • instrb : 특정 문자의 위치 바이트 값 추출
  • instr([대상 문자열 데이터(필수)], [위치를 찾으려는 부분 문자(필수)], [위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값 1)], [시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값 1)])
  • select instr('hello, oracle!', 'l') as instr_1, instr('hello, oracle!', 'l', 5) as instr_2, instr('hello, oracle!', 'l', 2, 2) as instr_3 from DUAL;
  • select * from EMP where instr(ENAME, 'S') > 0;
  • select * from EMP where ENAME like '%S%'
• 특정 문자를 다른 문자로 바꾸는 함수
  • replace : 특정 문자를 다른 문자로 대체
  • replace([문자열 데이터 또는 열 이름(필수)], [찾는 문자(필수)], [대체할 문자(선택)])
• 데이터의 빈 공간을 특정 문자로 채우는 함수
  • lpad : 왼쪽으로 특정 문자 채움
  • rpad : 오른쪽으로 특정 문자 채움
  • lpad/rpad([문자열 데이터 또는 열 이름(필수)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)])
• 두 문자열 데이터를 합치는 함수
  • concat : 두 문자열 결합(|| 연산자와 동일)
• 특정 문자를 지우는 함수
  • trim : 양쪽의 특정 문자 삭제
  • ltrim : 왼쪽의 특정 문자 삭제
  • rtrim : 오른쪽의 특정 문자 삭제
  • trim([삭제 옵션(선택)] [삭제할 문자(선택)] from [원본 문자열 데이터(필수)])
  • ltrim([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])
  • rtrim([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])

[03] 숫자 데이터를 연산하고 수치를 조정하는 숫자 함수

• round : 지정된 숫자의 특정 위치에서 반올림한 값을 반환
  • round(12.345, 2) -> 12.35
• trunc : 지정된 숫자의 특정 위치에서 버림한 값을 반환
  • trunc(12.345, 2) -> 12.34
• ceil : 지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환
  • ceil(12.345) -> 13
• floor : 지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환
  • floor(12.345) -> 12
• mod : 지정된 숫자를 나눈 나머지 값을 반환
  • mod(12, 10) -> 2
• power : 지정된 숫자의 지정된 숫자 제곱 출력
  • power(3, 2) -> 9

[04] 날짜 데이터를 다루는 날짜 함수

• 날짜 연산
  • 날짜 데이터 + 숫자 : 날짜 데이터보다 숫자만큼 일수 이후의 날짜
  • 날짜 데이터 - 숫자 : 날짜 데이터보다 숫자만큼 일수 이전의 날짜
  • 날짜 데이터 - 날짜 데이터 : 두 날짜 데이터 간의 일수 차이
  • 날짜 데이터 + 날짜 데이터 : 연산 불가, 지원하지 않음
• 현재 날짜와 시간 함수
  • sysdate : 오라클 데이터베이스 서버가 놓인 os의 현재 날짜와 시간 출력
• 수 개월 이후 날짜를 구하는 함수
  • add_months : 특정 날짜 데이터에 입력한 개월 수만큼의 이후 날짜를 출력
  • add_months([날짜 데이터(필수)], [더할 개월 수(정수)(필수)])
• 두 날짜 간의 개월 수 차이를 구하는 함수
  • months_between : 두 날짜 데이터 간의 날짜 차이를 개월 수로 계산하여 출력
  • months_between([날짜 데이터1(필수)], [날짜 데이터2(필수)])
• 돌아오는 요일, 달의 마지막 날짜를 구하는 함수
  • next_day : 특정 날짜를 기준으로 돌아오는 요일의 날짜를 출력
  • last_day : 특정 날짜가 속한 달의 마지막 날짜를 출력
  • next_day([날짜 데이터(필수)], [요일 문자(필수)])
  • last_day([날짜 데이터(필수)])
• 날짜의 반올림, 버림을 하는 함수
  • 숫자 데이터
    • round : round([숫자(필수)], [반올림 위치])
    • trunc : trunc([숫자(필수)], [버림 위치])
  • 날짜 데이터
    • round : round([날짜데이터(필수)], [반올림 기준 포멧])
    • trunc : trunc([날짜데이터(필수)], [버림 기준 포멧])
    • 포멧 모델
      • CC, SCC : 네 자리 연도의 끝 두 자리를 기준으로 사용
        • 2016년이면 2050 이하이므로, 반올림할 경우 2001년으로 처리
      • SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y : 날짜 데이터의 해당 연, 월, 일의 7월 1일을 기준
        • 2016년 7월 1일일 경우, 2017년으로 처리
      • IYYY, IYY, IY, I : iso 8601에서 제정한 날짜 기준년도 포멧을 기준
      • Q : 각 분기의 두 번째 달의 16일 기준
      • MONTH, MON, MM, RM : 각 달의 16일 기준
      • WW : 해당 연도의 몇 주(1~53번째 주)를 기준
      • IW : iso 8601에서 제정한 날짜 기준 해당 연도의 주를 기준
      • W : 해당 월의 주(1~5번째 주)를 기준
      • DDD, DD, J : 해당 일의 정오를 기준
      • DAY, DY, D : 한 주가 시작되는 날짜를 기준
      • HH, HH12, HH24 : 해당 일의 시간을 기준
      • MI : 해당 일 시간의 분을 기준

[05] 자료형을 변환하는 형 변환 함수

• 명시적 형 변환(explicit type conversion) : 자료형을 직접 지정해 주는 방식
  • to_char : 숫자 또는 날짜 데이터를 문자 데이터로 변환
    • 날짜 표현 형식
      • CC : 세기
      • YYYY, RRRR : 연(4자리 숫자)
      • YY, RR : 연(2자리 숫자)
      • MM : 월(2자리 숫자)
      • MON : 월(언어별 월 이름 약자)
      • MONTH : 월(언어별 월 이름 전체)
      • DD : 일(2자리 숫자)
      • DDD : 1년 중 며칠(1~366)
      • DY : 요일(언어별 요일 이름 약자)
      • DAY : 요일(언어별 요일 이름 전체)
      • W : 1년 중 몇 번째 주(1~53)
    • 시간 형식 지정
      • HH24 : 24시간 표현식
      • HH, HH12 : 12시간 표현식
      • MI : 분
      • SS : 초
      • AM, PM, A.M., P.M. : 오전 오후 표시
    • 숫자 형식 지정
      • 9 : 숫자의 한 자리를 의미(빈 자리 채우지 않음)
      • 0 : 빈 자리를 0으로 채움을 의미
      • $ : 달러 표시를 붙여서 출력
      • L : 지역 화폐 단위 기호를 붙여서 출력
      • . : 소수점 표시
      • , : 천 단위의 구분 기호 표시
  • to_number : 문자 데이터를 숫자 데이터로 변환
  • to_date : 문자 데이터를 날짜 데이터로 변환
  • to_char([날짜데이터(필수)], '[출력되길 원하는 문자 형태(필수)]')
    • 'nls_date_language = language' : 특정 언어에 맞춰서 날짜 출력
  • to_number('[문자열 데이터(필수)]', '[인식될 숫자형태(필수)]')
  • to_date('[문자열 데이터(필수)]', '[인식될 날짜형태(필수)]')

[06] NULL 처리 함수

• nvl : 첫 번째 입력 데이터가 null이 아니면 그 데이터를 그대로 반환하고 null이라면 두 번째 입력 데이터에 지정한 값 반환
  • nvl([null인지 여부를 검사할 데이터 또는 열(필수)], [앞 데이터가 null일 경우 반환할 데이터(필수)])
• nvl2 : 데이터가 null이 아닐 때 반환할 데이터를 추가로 지정 가능
  • nvl2([null인지 여부를 검사할 데이터 또는 열(필수)], [앞 데이터가 null이 아닐 경우 반환할 데이터 또는 계산식(필수)], [앞 데이터가 null일 경우 반환할 데이터 또는 계산식(필수)])

[07] 상황에 따라 다른 데이터를 반환하는 decode 함수와 case문

• decode : 기준이 되는 데이터 지정 후 해당 데이터 값에 따라 다른 결과 값을 내보내는 함수
  • decode([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과], [조건n], [데이터가 조건n과 일치할 때 반환할 결과], [위 조건1~조건n과 일치한 경우가 없을 때 반환할 결과])
• case : 특정 조건에 따라 반환할 데이터를 설정할 때 사용
  • case [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택) when [조건n] then [조건n의 결과 값이 true일 때, 반환할 결과] else [위 조건1~조건n과 일치하는 경우가 없을 때 반환할 결과] end]