단일행 함수

DB  ||  DAY8  ||  단일행 함수

---

*** 함수를 사용하는 경우, alias지정하는 것이 보기좋다.

 

 1. 문자함수 

1) 대소문자 변환함수

-> 해당 컬럼의 데이터를 대문자 / 소문자 / 카멜타입으로 변환하여 출력한다

• LOWER(컬럼명)
• UPPER(컬럼명)
• INITCAP(컬럼명) -> 카멜타입으로 변환함.

 

2) 문자조작 함수

• CONCAT( 컬럼명1 , 컬럼명2 )    : 컬럼1과 컬럼2의 데이터를 합친다.
• INSTR( 컬럼명 , '문자' )    : 해당컬럼의 데이터에서 '문자'의 첫번째 위치를 출력함.
• SUBSTR( 컬럼명 , n1 )    : 컬럼데이터의 n1번째 글자부터 끝까지 추출함.
• SUBSTR( 컬럼명 , n1 , n2 )    : 컬럼데이터의 n1번째 글자부터 n2개의 문자를 추출함.
• LENGTH(컬럼명)    : 컬럼의 데이터의 길이를 출력함.
• LPAD( 컬럼명, n , 문자 ) | RPAD( 컬럼명, n , 문자 )   
  : 해당컬럼의 데이터가 n개가 되도록 빈 공간을 문자로 채운다.
  : L -> 왼쪽부터 / R -> 오른쪽부터 문자를 채움.
  : 결과적으로 LPAD == 오른쪽 정렬 , RPAD == 왼쪽 정렬의 효과를 가진다.
• TRIM( '문자' FROM 컬럼명 )    : '문자'가 컬럼테이터의 맨앞/맨뒤에 있는 경우 삭제해준다.
• REPLACE( 컬럼명, '문자1' , '문자2' )    : 컬럼의 데이터 중 '문자1'을 '문자2'로 변경하여 출력한다.

---

 2. 숫자함수 

• ROUND( 숫자, 결과값의 자릿수 )    : 지정된 소숫점 자릿수로 값을 반올림한다.
• TRUNC( 숫자, 결과값의 자릿수 )    : 지정된 소숫점자리로 값을 버림
• MOD( 숫자, 나눌숫자 )    : 나눈 나머지를 반환함.

# sqlplus에서 숫자함수 이용하기

SELECT round(44.842, 2)
FROM dual ; //  결과 44.84

SELECT round( 88.564 , -2)
FROM  dual  ;  // 결과:100

---

 3. 날짜함수 

1) 날짜를 이용한 산술연산

select ( sysdate- hire_date ) / 7 as WEEKS
from employees;

 

2) 날짜 조작함수

• MONTHS_BETWEEN( '날짜1', '날짜2' )    : 날짜1과 날짜2 사이의 개월차이를 반환함
• ADD_MONTHS( '날짜', n )   : 해당 '날짜'의 n개월 후의 날짜를 반환함
• NEXT_DAY( '날짜' , '요일' )    : 해당 '날짜' 다음에 처음으로 오늘 '요일'의 날짜를 반환함
• LAST_DAY( '날짜' )    : 해당 날짜가 속해있는 달의 마지막 날을 반환함
• ROUND( '날짜', 'MONTH' / 'YEAR' )    : 해당날짜를 반올림함.
                        MONTH -> 日에서 반올림함
                        YEAR ->月에서 반올림함
• TRUNC( '날짜', 'MONTH' / 'YEAR' )    : 해당 날짜가 포함되어있는 날짜에서 버림을 함
                       MONTH -> 日을 버림하고, 1일로 출력
                       YEAR -> 해당 날짜 연도의 1월 1일을 출력함.

 

---

 4. 변환함수 

 

1) TO_CHAR ( date or number , 'format model' ) 

• DATE -> CHAR  ( date, 'format model' )

 # format model 

요소	결과
YYYY	숫자로 된 전체 연도
YEAR	영어 철자로 표기된 연도
MM	월의 2자리값
MONTH	전체 월 이름
MON	월의 3자리 약어
DD	숫자 형식의 월간 일
DAY	요일의 전체 이름
DY	3문자로 된 요일 약어
HH	12시제
HH24	24시제
MI	분
SS	초

 # 숫자를 영문으로 출력하고싶다면, format뒤에 sp를 붙인다. (spelling의 약어.)

 # format 안에 문자열을 넣고싶다면, 큰따옴표로 결합시킨다. 

SELECT to_char( sysdate, 'yyyy/mm/dd/day "time: " hh24:mi' )
FROM dual ;

 

• NUMBER -> CHAR  ( number, 'format model' ) 

SELECT to_char( salalry , '$999,999' ) as salary
FROM employess;

 

2) TO_NUMBER ( 컬럼명 , 'format model ' )

• CHAR -> NUMBER 

연산을 해야하는 컬럼이 문자열로 지정되어있는경우, to_char함수를 사용하면 format model에 명시된 형식에서 숫자만 추출할 수 있다.

SELECT to_number( salary, '$999,999' ) * 12 as annual_salary
FROM employees;

 

3) TO_DATE ( 'nn-nn-nn' , 'format model' )

• CHAR -> DATE

날짜가 표현된 문자열의 년/월/일이 구분되지않는 경우에 사용한다.

어떤 항목이 년/월/일에 해당하는지 지정하면, DD-MON-RR형식으로 출력한다.

SELECT employee_id, last_name, salary,hire_date
FROM employees
WHERE hire_date < to_date( '2004/08/09' , 'yyyy/mm/dd' ) ;

---

 5. 일반함수 

1) NVL( 인수1 , 인수2 )

• 첫번째 인수를 검사함 - NULL이 아니면 첫번째인수를 반환함
                               - NULL이면 두번째 인수를 반환함
• NULL값을 실제 값으로 변환한다. 모든 데이터 유형을 사용할 수 있다.
• 두 인수의 데이터타입은 일치해야한다.

NVL ( commission_pct , 0 )  -> commission_pct가 null인 경우 0을 출력함
NVL (job_id , 'No Job Yet' )  -> job_id가 null인 경우 'No Job Yet'을 출력함

SELECT  NVL( commission_pct , 0 ) , (salary*12) + NVL( (salary*12*commission_pct) , 0 ) as ANNUAL_SALARY
FROM   employees ;
// commission_pct컬럼은 NULL을 포함하는 데이터를 가지고 있으므로 연산을 할 때 오류가 발생한다.
따라서 NVL()함수를 사용하여 NULL인 데이터를 0으로 변환하면 연산오류를 없앨 수 있다.

 

2) NVL2( 인수1, 인수2, 인수3 )

• 첫번째 인수를 검사함 - NULL이 아니면 첫번째 인수를 반환함
                               - NULL이면 두번째 인수를 검사함 - NULL이 아니면 두번째인수를 반환함
                                                                           - NULL이면 세번째 인수를 반환함

SELECT NVL2(commission_pct , 'SAL+COMM' , 'SAL' );
FROM employees ;

3) NULLIF( 인수1, 인수2 )

• 인수1 == 인수2 이면 NULL을 반환함
• 인수1 != 인수2 이면 인수1을 반환함

SELECT NULLIF( length(first_name) , length(last_name) )
FROM employees ;

 

4) COALESCE( 인수1, 인수2, . . . 인수N )

• NULL이 아닌 인수를 찾을때까지 인수를 검사하고, 처음으로 나온 실제값을 반환함.
• NVL함수보다 여러 대체값을 가질 수 있다.
• 모든 인수가 null이라면 마지막 인수가 출력될것임.

SELECT commission_pct, manager_id,
           COALESCE( to_char(commission_pct) , to_char(manager_id), 'No Commission No Manager' ) 
FROM employees;