1장_6절. 함수(Fonction)

1. 내장함수(built-in function)개요

-함수는 벤더에서 제공하는 내장함수(built-in function)와 사용자가 정의하는 유저함수(user-defiend function)가 있음

-단일행 함수(single-row function)와 다중행함수(multi-row function)으로 나눌수 있음

-다중행 함수는 집계함수(aggregate function)와 그룹함수(group function) 윈도우 함수(window function)으로 나눌수 있음

-6절에서는 단일행 함수만 설명

-함수는 입력인수가 많아도 하나의 출력만 된다(M:1관계 특성)

-함수명 (column이나 표현식 [인자1,인자2,...]);

1)문자형 함수 (문자입력 ->문자나 숫자 return)

lower, upper, substr/substring, length/len, ltrim, rtrim, trim, ascii

2)숫자형 함수(숫자입력 -> 숫자 return)

abs, mod, round, trunc, sign, chr/char, ceil/ceiling, floor, exp, log, ln, power, sin, cos, tan

3)날짜형 함수(date 타입 연산)

sysdate/getdate, extract/datepart, to_number(to_char(d,'YYYY|'MM'|'DD'))

4)변환형 함수(문자, 숫자, 날짜형값의 타입을 변환)

to_number, to_char, to_date/cast, convert

5)null 함수(null 처리 연산)

nvl/isnull, nullif, coalesce

# /있는것은 왼쪽이 oracle 오른쪽이 sql server 함수

#단일행 함수의 특징

1)select, where, order by 절에서 사용가능

2)행 개별적으로 연산

3)return값은 1개

4)함수의 중첩가능

2. 문자형함수

1)lower(문자열),upper(문자열) : 문자열을 소문자로/문자열을 대문자로

ex) lower('SQL') ->결과값 : sql

ex)upper('sql') -> SQL

2)ascii(문자),chr(ascii번호) : 문자나 숫자를 아스키 코드번호로/아스키코드번호를 문자나 숫자로

ex)ascii('A') -> 65

ex)chr(65) -> A

3)concat(문자열1,문자열2) : 문자열1과 2를 연결

ex)concat('hi','hello') -> hihello

#||연산자와 동일

4)substr(문자열, index, n) : 문자열의 index위치에서부터 n개의 문자열을 출력

ex)substr('abcde', 1, 3) -> abc

substr(문자열, n) : n번째 인덱스부터 끝까지 문자열 출력

ex)substr('abced', 2) -> bcde

5)length(문자열) : 문자열 길이return

ex) length('abcde') -> 5

6)ltrim(문자열, 지정문자) / rtrim(문자열, 지정문자) / trim(지정문자 from 문자열) : 왼쪽, 오른쪽, 양쪽에서 다른문자가 나올때까지 지정문자 모두 제거

#trim뜻 : 불필요한 부분을 제거하다

ex)ltrim('xxxEExxx', 'x') -> EExxx / ltrim('  xxxEExxx', 'x') -> '   xxxEExxx'

ex)rtrim('xxxEExxx', 'x') -> xxxEE

ex)trim('x' from 'xxxEExxx') -> EE / trim('x' from ' xxxEExxx') -> ' xxxEE'

#공백을 고려함

3.숫자형 함수

1)abs(숫자) : 절대값 return

ex) abs(-15) -> 15

2)sign(숫자) : 양수면 1, 음수면 -1, 0이면 0을 return

ex)sign(100), sign(-123) -> 1 , 0

3)mod(숫자1, 숫자2) : 숫자1 / 숫자2의 나머지값을 return

ex)mod(-15, 1) -> -3

4)ceil(숫자)/floor(숫자) : 크거나 같은 최소정수 return / 가우스 값 return

ex) ceil(-3.5), floor(-3.5) -> -3, -4 

5)round(숫자, m) : 소수점 m+1번째 자리에서 반올림하여 return/trunc(숫자,m) : 소수점 m+1자리부터 버림

ex)round(2.366, 2), trunc(2.366, 2) -> 2.37, 2.36

#truncate뜻 : (아래나 위를 잘라서)길이를 줄이다.

6)sin(숫자) / cos(숫자) / tan(숫자) ->삼각함수값 return

ex)sin(90) ->0.89399664

모르겠군..

7)exp(숫자),ln(숫자) : 자연지수값,자연로그값 return

ex)exp(1) -> e의 1제곱 return : 2.71827183

ex)ln(2.71828183) -> e 로그 e : 1

8)power(n,m), sqrt(n), log(n,m) : n의 m제곱값, n의 제곱근값, n 로그 m값 return

ex)power(2,3) ->8 // power(3,2)->9

ex)sqrt(4) -> 2 //sqrt(100) ->10

ex)log(2,16) -> 4 //log(10,100) ->2

4.날짜형 함수

1)sysdate : 현재날짜와 시각 출력

2)extract(year from 날짜데이터) / extract(month from 날짜데이터) / extract(day from 날짜데이터) : 날짜데이터에서 년, 월, 일을 출력

3)to_char(날짜데이터,'yyyy') : 날짜데이터의 년 데이터를 문자로 변경

to_number(to_char(날짜데이터,'yyyy')) : 날짜데이터->문자데이터->숫자데이터로 변경

5.변환형함수

1)명시적변환 vs 암시적변환

-사용자가 함수를 사용하여 데이터형태를 변환 vs 벤더에서 데이터형태를 자동변환

ex)number형태로 명시적 변환하여 입사년도끼리 더하기

char형태로만 변환하였지만 oracle에서 암시적으로 numeric형으로 변환하여 입사년도끼리 더해짐

2)to_number(문자열) : 문자열 -> 숫자형으로 변환

3)to_char(날짜, 형식), to_char(숫자,형식) : 숫자 -> 문자형, 날짜 -> 문자형로 변환

ex)to_char(sysdate, 'yyyy mm dd') ->2017 01 28

ex)to_char(sysdate, 'yyyymmdd') -> 20170128

ex)to_char(sysdate) , to_char(sysdate, 'yy/mm/dd') ->17/01/28

ex)to_char(123456789/1200, '$999,999,999.99) || ' dollors' "달러표시" -> $102,880,66dollors

ex)to_char(123456789/1200, '$000,000,000.00) || ' 달러' as"달러표시"-> $000,102,880.66달러

ex)to_char(123456789, 'L999,999,999.99) || ' won' as "로컬화폐표시" -> \123,456,789won

ex)to_cjar(123456789, 'L000,000,000.00) || ' 원'-> \000,123,456,789원

3)to_date(문자) : 문자열 -> 날짜형으로 변환

6.Case표현

if -then-else논리와 유사한 방식으로 표현식을 작성

-select 

case 조건문1 then 표현절1

case 조건문2 then 표현절2

case 조건문3 then 표현절3

...

end colunm의 별명

from 테이블명;

ex)

SQL> select ename,

  2  case when sal>=3000 then 'high'

  3  when sal>=1000 then 'mid'

  4  else 'low'

  5  end as "salary grade"

  6  from emp;

7.NULL 관련 함수

1)nvl(표현식1, 표현식2) : 표현식1의 결과값이 null이면 표현식2를 출력, null이 아니면 표현식1을 출력

-nvl(null판단대상, 'null일때 대체값')

ex)select nvl(null, null) from dual; -> N // 표현식1이 null이므로 표현식2의 널값출력

ex)select nvl(null, 'null') from dual; -> 'null' // 표현식1이 null이므로 표현식2의 'null'값 출력

ex)select nvl('null', null) from dual; ->'null' // 표현식1이 null이 아니므로 표현식1의 'null'값 출력

ex)select nvl('null', 'null') from dual; -> 'null' // 표현식1이 null이 아니므로 표현식1의 'null'값 출력

2)nullif(문자열1, 문자열2) : 문자열1이 문자열2와 같으면 null을 출력, 같지않으면 문자열1을 출력

ex)select nullif(null, null) from dual; -> 오류 // 문자열만 표현식에 들어갈수 있음

ex)select nullif(null, 'null') from dual; -> 오류 // 문자열만 표현식에 들어갈수 있음

ex)select nullif('null', null) from dual; -> 오류 // 문자열만 표현식에 들어갈수 있음

ex)select nullif('null', 'null') from dual; ->N // 표현식1과 표현식2가 같으므로 널값출력

ex)select nullif('hi','hi') from dual; ->N //표현식1과 표현식2가 같으므로 널값 출력

ex)select nullif('null', 'not null') from dual; ->'null' // 표현식1과 표현식2가 같지않으므로 표현식1의 'null'값 출력

ex)select nullif('not null', 'null') from dual; -> 'not null' // 표현식1과 표현식2가 같지 않으므로 표현식1의 'not null'값 출력

3)coalesce(표현식1, 표현식2, ...) : 임의의 표현식들에서 null이 아닌 최초의 표현식을 출력, 모든표현식이 null이면 null을 출력

ex)select coalesce(null, 'hi') from dual; -> 'hi'

ex)select coalesce('null',null) from dual; ->'null'

ex)select coalesce(null, null) from dual; -> N //널값출력

#coalesce뜻 : 덩어리로 합치다.

<case문2중사용 + null관련 함수 사용>...개어렵..

<emp 테이블에서 사원명, comm, sal, coal을 보여주되

만약 comm이 null아 아니면 sal를 보여주고

만약 comm이 null이면 

만약 sal이 null이 아니면 sal를 보여주고

만약 sal도 null이면 null을 보여줘라

case2의 종료

case1을 종료하고 계산된 값을 coal로 표현하라>

SQL> select ename, comm, sal,

  2     case when comm is not null

  3          then sal

  4          else (case when sal is not null

  5                     then sal

  6                     else null

  7                     end)

  8          end coal

  9  from emp;