[DataBase] 내장 함수

내장함수

• 함수 안에 함수도 작성 가능

숫자 관련

• ABS : 절대값을 구하는 함수
• round(값, 자리수)
  • 반올림한 값을 구하는 함수
  • 자리 수가 0이거나 생략하는 경우 정수 부분(일의 자리)만 표시(즉, 소수 첫째 자리에서 반올림)
  • 자리 수가 1이면 소수점 첫째 자리까지 표시(즉, 소수 둘째 자리에서 반올림)
  • 자리 수가 -1이면 십의 자리까지 표시(일의 자리에서 반올림)
• ceil : 무조건 올림 수를 반환하는 함수
• floor : 무조건 버림 수를 반환하는 함수
• power(숫자, n) : 숫자를 n 제곱승하여 반환하는 함수
• sqrt : 숫자를 2제곱승하여 반환하는 함수

문자 관련

• 문자열은 ' ' 안에 작성
• chr(정수) : 정수 아스키 코드 값에 해당하는 문자를 반환 ex) chr(65) --> A
• concat(문자열1, 문자열2) : 두 개의 문자열 연결하여 반환
• initcap(문자열) : 문자열의 첫 번째 알파벳을 대문자로 변환
• lower(문자열): 대상 문자열을 모두 소문자로 변환
• upper(문자열): 대상 문자열을 모두 대문자로 변환
• lpad(문자열, 자리수, 지정문자)
  • 대상 문자열의 왼 쪽부터 지정한 자리 수까지 칸을 잡아 오른 쪽 정렬하여 출력하고 왼 쪽 빈칸을 지정한 문자로 채움
• rpad(문자열, 자리수, 지정문자)
  • 대상 문자열의 오른 쪽부터 지정한 자리 수까지 칸을 잡아 왼 쪽 정렬하여 출력하고 오른 쪽 빈칸을 지정한 문자로 채움
• replace(문자열,문자열a, 문자열b) : 대상 문자열의 문자열a를 문자열b로 변경
• tirm(문자열) : 좌우 공백을 제거하는 함수, trim(문자열 from 문자) : 해당 문자열의 양쪽에서 지정 문자 삭제
• ltrim(문자열) : 왼 쪽 공백을 제거하는 함수
• rtirm(문자열) : 오른 쪽 공백을 제거하는 함수
• length(문자열) : 문자열의 길이를 반환하는 함수, 빈칸도 하나의 글자로 처리 됨
• ascii(문자) : 문자에 해당하는 아스키 코드값 반환 ex) ascii('A') --> 65
• instr(문자열1, 문자열2) ex) instr('hello', 'e') --> 2
  • 문자열1로부터 문자열2가 나오는 위치를 반환
  • 자바의 index of method와 동일한 기능
  • 오라클에서 인덱스는 1번째부터 시작
  • instr(문자열1, 문자열2, 숫자1) : 문자열1로부터 숫자1 인덱스 이후에 나오는 문자열2의 위치를 반환
• substr
  • 문자열의 일부분을 잘라서 반환
  • substr(문자열, 시작위치, 길이) : 문자열의 시작위치에서 길이만큼 잘라서 반환

날짜 ·시간 관련

• to_date(문자열,형식날짜) : 문자열 데이터를 날짜형으로 반환

• to_char(날짜자료형, 형식문자)

  • 날짜자료형을 형식문자대로 문자로 반환
  • 연과 월 그리고 일을 각각 따로 반환 할 수도 있다.
  • 형식문자
    • yyyy yy : 연도
    • mm: 월
    • dd: 일
    • d dy day : 요일, d는 숫자로 반환됨(일요일(1)을 기준으로 순서 시작)
    • hh : 시
    • mi : 분
    • ss : 초

• add_month(날짜자료형,개월수): 날짜 자료형에 지정한 개월 수 만큼 더해진 날짜를 반환

• months_between(날짜1, 날짜2) : 두 날짜 사이의 개월 수 반환(날짜 1이 더 최근 날짜여야 한다.)

• last_day(날짜자료형) : 날짜 자료형의 그 달의 마지막 날을 반환

• sysdate : dbms 시스템 상의 오늘 날짜 반환, 인자(매개변수)가 없는 함수이다.

  • 년,월,일 뿐아니라 시,분,초에 대한 정보도 함께 갖고 있음

• 날짜 자료형에 연산 가능

  • + 연산자로 더하는 경우
    • 해당하는 숫자만큼 며칠 더해 줌
    • sysdate의 경우 sysdate -1 은 dbms시스템 날짜의 어제, sysdate + 1은 dbms시스템 날짜의 내일

그 외 함수

• decode(컬럼,값1,결과1,값2,결과2,[결과3])
  • 컬럼의 값이 값1이면 결과1을 반환, 칼럼의 값이 값2이면 결과2를 반환, 둘 다 아니면 결과3 반환(생략가능)
  • 컬럼 자리에 내장 함수 작성 가능
  • 값과 결과는 쌍을 이루어 얼마든지 나열 가능

---

null 처리

• null 정의
  • 아직 지정되지 않은 값
  • 0, 빈문자, 공백 등과 다른 특별한 값
  • ''은 null로 처리됨 : 문자의 null만 해당, 하지만 애초에 만드지 않도록 하는 것이 좋다.
  • is null로 null 값 찾을 수 있음
• 연산
  • 비교 연산자로 비교 불가능
  • 연산 수행시(ex)null+숫자) 결과 역시 null 값으로 반환
  • null인 항목에는 연산이 되지 않음, 만약 null인 항목도 연산을 시키려면 nvl함수를 이용하여 다른 값으로 대체한 후에 연산할 수 있음
• 집계함수
  • 집계 함수 계산 시 null 포함 행은 집계에서 빠짐
  • 해당하는 행이 하나도 없을 경우 sum, avg 함수의 결과는 null이 되며, count 함수의 결과는 0이된다.
• NVL 함수
  • 특정 칼럼의 값이 null인 경우 다른 값으로 대체하는 함수
  • nvl(컬럼이름, 대체할 값)

---

rownum

• 내장함수는 아니지만 자주 사용되는 문법
• 오라클에서 내부적으로 생성되는 가상칼럼으로 sql 조회 결과 순번 나타냄
• 자료를 일부분만 확인하여 처리할 때 유용
• select 결과에 행번호를 매겨주는 속성
• 별칭 만들기 가능
• 조건식에 활용 가능
• order by와 rownum을 같이 쓸 경우 rownum에도 order by 결과가 적용된다면 order by 실행 결과를 먼저 동작하도록 하기 위해 서브쿼리 사용해야 한다.
• 행번호를 출력하려면 컬럼이름들을 나열해야 한다.(rownum과 *는 같이 쓸 수 없다.)
  • 행번호를 출력할 필요가 없다면 와일드문자(*)로 표현할 수 있다.
• 행번호를 붙혀 뽑은 다음 컬럼순서로 정렬하는 거랑 컬럼순서를 정렬한 다음 행번호를 붙이는 건 다르다.

Tips

• dual : 가상의 테이블로 내장 함수 사용 시 그값을 알고 싶을 때 사용 ex) select instr('hello', 'e') from dual --> 2
• cmd line size 조정 : set linesize 라인크기; (지금은 250 정도로 해놓음)
• 컬럼 포맷이 맞지 않으면 #으로 표현됨