[Oracle] function(함수)

 

 function(함수) 

- 자주 사용될 기능을 미리 구현해놓은 것

- Oracle에서 제공하는 내장함수(built-in finction)와 개발자가 정의하는 함수

- 조회컬럼, insert 값, where 절에서 사용할 수 있음

- 사용법) 함수명(값) -- 값에는 컬럼명, 값이 들어갈 수 있음

- oracle에서는 dual 테이블 제공

  > 모든 계정에서 사용할 수 있는 가상 테이블로 입력되는 값으로 테이블이 생성됨

  > 계정에서 dual이라는 이름의 테이블을 생성하면 사용할 수 없음

  > 입력되는 값으로 컬럼을 생성하고, 조회하는 일을 수행

  > alias 사용도 가능

 

 

 1) 수학함수 

• 절댓값 : abs / abs(값)
• 반올림 : round / round(값, 자릿수)
  > 소수부 : 설정한 자리 다음 자리를 반올림
     정수부 : 설정한 자리를 반올림
  > 자릿수를 쓰지 않으면 소수 첫째자리를 반올림해서 정수부만 보여줌
• 올림 : ceil / ceil(값)
  > 소수점 0 제외, 값은 다 올림. 정수만 보여줌
• 내림 : floor / floor(값)
  > 소수점 0 제외, 값 다 내림. 정수만 보여줌
• 절사 : trunc / trunc(값, 자릿수)
  > 소수부 : 설정한 자리 다음 자리를 절사
     정수부 : 설정한 자리를 절사
  
  

 2) 문자열함수 

• length : 길이 / legnth(값)
  > 영어/한글/공백 모두 1자로 나옴
• upper : 대문자 / upper(값)
• lower : 소문자 / lower(값)
• instr : 문자열 안에서 특정 문자열의 시작 인덱스를 얻을 때 / instr(값, 찾을 문자열)
     > Oracle은 시작 인덱스가 1 
     > 3 = instr(‘ABCDE’ , ‘C’), 3 = instr(‘ABCDE’ , ‘CD’)
     > 0 = instr(‘ABCDE’, ‘c’) -- 찾는 문자열이 없으면 0이 반환됨
• substr : 문자열 자르기 / substr (값, 시작인덱스, 자를글자수)
  > substr(‘ABCDE’,2,3) – BCD
  > 자를글자수 없이 시작인덱스만 넣으면 시작~끝문자열까지 자름
• trim : 문자열에 앞/뒤 공백 자를 때 / trim(값)
  > 문자열의 앞 뒤의 반복 문자열 자르기 가능 / trim(‘자를문자열’ from ‘문자열’)
     select trim( 'a' from 'aaaaaaaOracleaaaaaaa') -- a 다 없어지고 Oracle만 나옴!
• ltrim : 문자열의 앞 공백 자르기 / ltrim(값)
• rtrim : 문자열의 뒷 공백 자르기 / rtrim(값)
• replace : 치환 / replace (값, 찾을문자열, 바꿀문자열)
• concat : 문자열 합치기 / concat(값,값)
• initcap : 첫 글자를 대문자로 변환 / initcap(값)
  > 대소문자 섞여있으면 앞글자만 대문자, 뒤에 소문자 됨
• lpad : 문자열의 앞에 채움(문자열을 동일한 길이로 만들때) / lpad(값, 총글자수, 채울문자)
• rpad : 문자열의 뒤에 채움(문자열을 동일한 길이로 만들때) / rpad(값, 총글자수, 채울문자)

** lpad와 rpad는 채울문자가 한글이면 한 글자에 2자로 계산함!! (charset 상관안함) & to_char와 다르게 공백이 안들어감!

       Ex) lpad (‘ABC’,7,’#’) => ####ABC /

               lpad(‘ABC’,7,’가’) =>가가ABC / lpad(‘ABC’,8,’가’) =>가가ABC

 

 

 3) null 변환함수 

- 출력할 값은 컬럼의 데이터형과 동일한 테이터 형으로 기입해야 함!

• nvl : 컬럼의 값이 null일 때 다른 값으로 출력하는 일 / nvl (컬럼명, null일 때 출력할 값)
• nvl2 : 컬럼의 값이 null인 경우 사용할 값과 null이 아닌 경우 사용할 값을 설정하여 출력하는 일
  / nvl2(컬럼명, null이 아닐 때 출력할 값, null일 때 출력할 값,)

 

 4) 조건함수 

- 조건함수는 PL(Procedural Language)/SQL에서 사용할 수 없음

  > PL/SQL은 데이터베이스에서 일반 언어처럼 코딩할 수 있도록 지원하는 기능

• decode : 입력값에 대해 비교하는 일!
  decode(값, 비교값, 출력할값, 비교값, 출력할값 ,,,, 비교값 없을 때 출력할 값)

* case문

- decode처럼 컬럼에서 비교할때 사용하는 문장

- case는 비교하여 실행하는 문장이 길 때 사용하고

   decode는 비교하여 실행하는 문장이 짧을 때 사용!

- 문법) case 컬럼명 when 비교값 then 코드

                         when 비교값 then 코드 ,,,,,

                         else 비교값이 없을 때 실행될 코드

           end alias

 

 

 5) 변환함수 

- Oracle은 변환을 잘 해줘서 to_char를 제외한 나머지 함수들은 잘 안쓰이는 편

• to_char : 문자열이 아닌 데이터형을 문자열로 변환하는 일
  
  ① 날짜 => 문자열  /  to_char(날짜 or 숫자, ’format’)
   - 날짜형식을 문자열로 만드는 일 (원하는 날짜만 얻는 일)
   - 매개변수에 날짜형식을 넣으면 에러가 발생함 // to_char('2021-08-20', 'yyyy-mm-dd')
      > 해결하려면 to_date 사용 : to_char(to_date('2021-08-20'), 'yyyy-mm-dd')
   - 날짜 형식이 너무 길면 에러 발생
   - to_char(날짜, ’format’) -- letter가 pattern이 되고 pattern이 모여 foramt이 됨
     * letter 종류 (대,소문자 모두 가능)
      년 : Y  /  월 : MM  /  일 : DD
      시간 : H (12시간 – H, 24시간 – HH24)  /  분 :  MI  / 초 : SS
      요일 : D-요일의 숫자(일~토까지), DY – 월, DAY – 월요일
      분기 : Q
    * 정해진 letter나 특수문자(-,:)가 아닌 경우 “”로 묶어서 사용함
      Ex) select to_char(sysdate,'yyyy "년" mm " 월"')
  
  ② 숫자 => 문자열 / to_char(숫자,’format’)
   - 데이터가 format보다 길다면 데이터가 표현되지 않고 #으로 나옴 //(20212021,'9,999')
   - length를 확인하면 설정한 format보다 1 길게 나옴
   - to_char를 사용하면 공백이 포함되므로 lpad 사용하자!!
     * letter 종류
       0 : 데이터가 존재하지 않으면 0 채워서 출력 (동일한 길이로 표현할 때)
       9 : 데이터가 존재하는 것 까지만 출력
       Ex) to_char(2021,'0,000,000,000') => 0,000,002,021
       Ex) 소수점도 가능 / to_char(2021,'99,999.00') => 2,021.00
  
  
• to_date : 문자열을 DATE로 변환하는 일 / to_date(‘날짜형식의 문자열’, ‘pattern’)
  - pattern은 안 써줘도 되고 letter는 to_char의 letter와 같음
  * date형으로 된 컬럼에 현재날짜가 아닌 다른 날짜를 입력하는 법
   (단, 연/월/일/분기는 들어가지만 시간정보는 들어가지 않음 -00시00분00초가 들어감)
   ① 문자열로 다른 날짜를 입력
       insert into 테이블명(컬럼명,,,) values (,, ‘2021-08-31’ ,,)
       -- 각각의 정보가 년,월,일이라는 정보의 기준이 존재하지 않음
   ② to_date()를 사용
       insert into 테이블명(컬럼명,,,) values (,, to_date(,‘2021-08-31’, ’yyyy-mm-dd’ ) ,,)
       -- 문자열 데이터를 format의 기준으로 date를 생성하므로 년,월,일 기준이 생김
  
  
• to_number : 숫자형식의 문자열을 number로 변환하는 일 / to_number(‘문자열’)
   Ex) to_number(‘2021’) => 2021
   Ex) '1'+'2' = 3  //  to_number('1') + to_number('2') = 3 // 둘다 똑같음

 

 

 6) 집계함수(그룹함수) 

- 조회된 결과를 합쳐서 하나의 결과를 만들어내는 함수 (n개의 행을 하나의 행으로 구하는 일)

- where절에서 사용할 수 없음

- 여러 행이 조회되는 일반컬럼과 같이 사용되면 error 발생

- group by 절과 같이 사용되면 그룹별 집계를 얻을 수 있음 (having에 집계함수 사용 가능)

• count() : 조회되는 행을 세는 함수 / count(컬럼명)
   > count(*) – 사용자가 컬럼 값을 알지 못해도 모든 행을 조회할 수 있음
     (count(primary key인 컬럼) 기본키는 null을 허용하지 않으므로 모든 행 조회 가능)
   > null인 컬럼은 행에 포함하지 않음
• sum() : 컬럼의 값을 모두 합산하여 조회하는 함수 / sum(컬럼명)
   > 숫자 형식만 합산 가능, 문자열은 error
• max() : 컬럼의 값 중에 최고 값을 조회하는 함수 / max(컬럼명)
• min() : 컬럼의 값 중에 최저 값을 조회하는 함수 / min(컬럼명)
• avg() : 컬럼의 값 중 평균 값을 조회하는 함수 / avg(컬럼명)

** group by : rollup, cube 그룹에 대한 합계(총계)를 얻을 때

  > rollup - 소계, 총합계를 얻을 때 사용

                   - 컬럼 하나를 사용하면 그룹별 계가 먼저 출력, 총계가 나중에 출력

                   - 컬럼 여러 개를 사용하면 그룹별계->소계->총계 순으로

                   - 문법) group by rollup(그룹으로 묶을 컬럼명,,,)

 > cube - 전체합계를 먼저 출력하고 소계를 얻을 때 사용

                - 컬럼 하나를 사용하면 그룹별 계가 나중에 출력, 총계가 먼저 출력

                 - 컬럼 여러 개를 사용하면 총계->그룹별총계->소계->그룹별계 순으로

                 - 문법) group by cube(그룹으로 묶을 컬럼명,,,)

 

 

 7) 순위함수 

- 조회된 레코드에 순위를 부여하는 함수

- 별도의 order by 절을 사용하지 않아도 됨

- rank() over()보다는 순위가 다 매겨지는 row_number() over()가 더 많이 쓰임!

• rank() over() : 같은 값이 존재하면 동일 순위가 나옴 (1,1,3)
   > 사용법 : rank() over( order by 컬럼명 asc | desc ) // asc은 생략해도 됨
• row_number() over() : 같은 값이 존재하더라도 동일 순위가 나오지 않음
   > 사용법 : row_number() over( order by 컬럼명 asc | desc ) // asc은 생략해도 됨

- 순위함수에 partition by를 사용하면 “분류별 순위”를 얻을 수 있음

   > 분류할 컬럼명의 동일 값으로 분류한 다음 순위를 설정함

   > 사용법 : rank() over( partition by 분류할 컬럼명 order by 정렬할 컬럼명 )

   > 사용법 : row_number() over( partition by 분류할 컬럼명 order by 정렬할 컬럼명 )

 

 

 8) 날짜함수 

- 날짜는 연산이 됨

• 날짜 +(더하기)연산 : 일자 변경 (일자에 대한 연산)
• add_months(날짜, 더할 개월수)
• months_between(큰 날짜, 작은 날짜) : 개월 차를 연산 // Ex. 근속개월수, 휴면계정 등
    > 날짜 형식의 문자열도 연산 가능 Ex) select months_between(sysdate, '2021-05-30')
    > 두 개월간의 차는 소수로 결과가 나올 수 있음