Oracle 기초 : 실전(3) Built-in, Single-Row Functions, Conditional Expressions

Built-in Fuction?

• Oracle 내장 함수이며, 반드시 "하나"의 결과값이 존재한다.
• Oracle DB API 적극 참조 추천.

Character Functions: length

• syntax: length(ColumnName)
• 해당 Column내 데이터의 길이를 반환하나, Single-Row Function(단행함수)인 만큼 매 데이터의 길이를 일일히 반환한다. 비교절에 활용할 때는 다음과 같이 작성할 수 있다:

where length(ColumnName) > length;

Character Functions: instr

• syntax: instr(Col/val, TargetString, PositionIndex)
• 해당 Column(이하 Col)/Value(이하 Val)에서 targetString을 가진 index를 리턴하나, 1-based index를 제공한다. (Oracle은 모든 수를 0이 아닌 1부터 센다.) 쉽게 말해 해당 문자열에서 검색 대상인 문자열이 몇 번째 자리에 있는지를 조회한 후 1부터 세어 그 수를 리턴한다는 것이다. 여기서 조회 시작 position을 선언할 수 있고, 이는 음수로 선언하는 것도 가능하며 음수로 선언하는 경우 뒤에서부터 목표값을 조회한다. 자바에서의 indexof에 대응하는 함수라고 보면 되나, instr은 조회 결과가 없는 경우 0을 리턴한다. (자바의 indexof는 조회 결과가 없는 경우 -1 리턴)

Character Functions: lpad/rpad

• syntax: lpad/rpad(Col/Val, length, paddingCharacter)
• padding character를 문자의 좌(lpad)/우측(rpad)에 채우며, padding character 선언을 생략할시 이는 공백문자로 대체된다. 또한 선언한 Col/Value의 크기보다 선언한 length의 크기가 더 작다면 padding characters는 삽입되지 않고 Col/Value가 선언된 length길이만큼 리턴된다.

Character Functions: ltrim/rtrim

• syntax: ltrim/rtrim(Col/Val, targetString)
• 해당 Col/Val에서 targetString을 "!!문자 단위로!!" 좌(ltrim)/우(rtrim)측부터 제거한다. 즉 Col/Val에서 targetString을 한 번에 제거하는 것이 아니라 targetString을 char 단위로 쪼개어 그에 포함된 문자 하나하나를 Col/Val에서 제거한다.

select ltrim('13456797834567890test','1234567890')
--상기의 코드를 실행시키면 결과값은 test가 리턴된다.

Character Functions: substr

• syntax: substr(Col/Val, startIndex, length)
• 자바의 substring과 비슷한 함수. startIndex만 선언하면 해당 인덱스부터 모든 String을 리턴하며, length까지 지정해주는 경우 startIndex부터 해당 길이만큼 Col/Val의 문자열을 리턴한다.

Numeric Functions: mod

• syntax: mod(dividend(num), divisor(num)), return remainder(num)
• 자바의 %에 대응하는 함수. number타입의 dividend(피젯수)와 divisor(젯수)를 선언한 후 remainder(나머지)에 해당되는 값을 리턴받는다.

Numeric Functions: ceil/floor & round/trunc

• syntax: ceil(number), floor(number), round(number, scale), trunc(number, scale)
• round(반올림), trunc(버림)의 경우는 두 번째 파라미터로 scale을 받으며, 해당 소숫점 자릿수까지 표현된다. scale은 생략 또한 가능하다. ceil/floor는 오직 1개의 파라미터를 받는다. (대상 number) 소숫점 자리 기준으로 올림/내림되며 특정 소숫점 자릿수까지 표현하고 싶다면 다음과 같이 수식을 통해 직접 소숫점을 옮겨줄 수 있다.

ceil(123.45678 * 100)/100 --return 123.457
floor(123.45678 * 100)/100 --return 123.455

Date Functions: add_months, months_between, extract

• 1. add_months: syntax: add_months(targetDate, +/-Months(number)), targetDate에 개월수(Months)를 더하거나 뺀 날짜를 리턴한다.
• 2. months_between: syntax: months_between(date1, date_older_than_date1), 두 날짜간 개월 수의 차이를 계산하며 1달을 숫자 1.0으로 취급한다. 리턴되는 결과값은 소수일 수 있다.
• 3. extract: syntax: extract(year/month/days from date) 년/월/일을 추출하여 숫자로리턴한다.

Conversion Functions: to_number, to_char, to_Date

• syntax:
  to_char(date, dateFormat), to_char(number, numberFormat),
  to_number(string, numberFormat), (automatic type conversion),
  to_date(number or char, dateFormat)
• dateFormat: 'yyyy-MM-dd(dy) am/pm hh24:mi:ss', 반드시 이러한 형태로 변환해야 하는 것은 아니지만 가장 일반적이다. yyyy는 년도, mm은 개월, dd는 일자, dy는 요일, am/pm은 12시간제의 오전/오후 표기(둘 중 하나만 입력해도 시간대에 맞게 표시된다.), hh는 시간(hh24는 24시간제 표기), mi는 분, ss는 초를 의미한다. 각 요소를 제대로 입력한 상태에서 형태는 얼마든지 바꿀 수 있으나, 한국어 포맷을 사용하고 싶은 경우는 반드시 ""으로 감싸주어야 제대로 출력된다.

--ex
to_char(hire_Date,  'yyyy"년"mm"월"dd"일"')
--한국어는 다음과 같은 포맷 표기법을 따른다.

• number/charFormat: 'fm0,000,000', 'fmL9,999,999', 포맷 앞에 'fm'을 붙여주면 공백문자 제거, L을 붙여주면 해당 값을 local currency로 변환하여 준다. 또한 값인 수의 크기보다 NumberFormat이 작은 경우 빈 포맷의 자리는 #으로 표시된다. 주의! 포맷 작성시에 자체적으로 공백문자를 포함하고 fm을 작성하지 않는 경우 에러가 발생한다.

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 
|| ' trunc 적용 전',
to_char(trunc(sysdate), 'yyyy-mm-dd hh24:mi:ss') 
|| ' trunc 적용 후'
    from dual;

select to_char(sysdate, 'yyyy-mm-dd (dy) am hh24:mi:ss')
from dual;
    
select to_char(1234567890, 'fmL999,999,999,999,999'),
to_char(13243546721, 'fm000,000,000,000,000,000,000')
from dual;
    
select '100' +100 --자동 형변환
from dual;
    
select to_number('$1,234,567,890', 'L9,999,999,999') + 100 
--숫자포맷테스트1,
to_number('$1,234,567,890', 'fmL9,999,999,999') + 100 
--숫자포맷테스트2
from dual;
    
select to_Date('01-Jan-24')
from dual;

--2018/02/08 12시 23분 50초에서 3시간 30분이 지난 날짜 정보를 조회하라.
    
select --to_char(add_months(to_Date('20180208 12:23:50',
'yyyy/mm/dd hh24:mi:ss'), 0.0045833334), 
'yyyy/mm/dd hh24:mi:ss'),
to_char(to_Date('20180208 12:23:50', 'yyyy/mm/dd hh24:mi:ss')
+3/24 +30/24/60, 'yyyy/mm/dd hh24:mi:ss') 테스트
from dual;

Single-Row Conditional Function: Decode

• syntax: decode(Col/Val, 'val1', 'result1', 'val2', 'result2', ... defaultVal)
• Column 단위의 조건문 단행 함수. Col/Val의 값이 val1과 같으면 result1로 변경, val2와 같으면 result2로 변경... 과 같은 형태로 조건/리턴값을 다수 제시할 수 있다. 또한 그 외의 경우에 부여할 default 값을 마지막 인자로 전달할 수 있으며 이는 생략 가능하다. (자바의 if-else 문과 유사하다.)

--ex)decode
select  emp_name,
            substr(emp_no, 8, 1) "주민번호 앞자리",
            decode(substr(emp_no, 8, 1), '1', '남', '2',
            '여', '3', '남', '4', '여') 성별판별1,
            decode(substr(emp_no, 8, 1), '1', '남', '3',
            '남', '여' ) 성별판별2
from employee;

Single-Row Conditional Expression: Case

• syntax of the case(1):
  case Col/Val
  when 'val1' then 'result1'
  when 'val2' then 'result2'
  ...
  else defaultVal
  end Alias(nullable)

• syntax of the case(2):
  case
  when Condition1 then 'result1'
  when Condition2 then 'result2'
  ...
  else defaultVal
  end Alias(nullable)

• java의 if/else절과 유사한 기능을 하는 조건문으로 defaultVal은 자바의 else 절과 같은 기능을 하며, case는 2가지 문법으로 작성이 가능하다.

--ex)case
select emp_name, job_code,
	--syntax of the case(1)
            case
            when job_code = 'J1' then '대표'
            when job_code in ('J2', 'J3') then '임원'
       --or when job_code = 'J2' or job_code = 'J3' then '임원' 
          --when job_code = 'J3' then '임원'
            else '평사원'
            end 직급판별1,
            
	--syntax of the case(2)
            case job_code
            when 'J1' then '대표'
            when 'J2'then '임원'
            when 'J3' then '임원'
            else '평사원'
            end 직급판별2  
from employee
order by job_code asc;