[DB] SQL함수

함수란?

- 함수가 작업을 수행하는것

SQL 함수의 두가지 유형

- 단일행 함수 : 행당 하나의 결과 반환

  ( 문자형, 숫자형, 날짜형, 변환형, NULL 관련 함수)

문자함수	문자입력을 받아 들이며 문자 및 숫자값을 모두반환
숫자함수	숫자입력을 받아 들이고 숫자값을 반환
날짜함수	DATE테이터 유형의 값에 대해 실행
변환함수	값의 데이터유형을변환
일반함수	모든데이터타입에 사용가능

1) 문자함수

 - 함수를 SELECT절에 적용한 경우 출력 결과에 함수가 적용

 - WHERE절에 함수를 적용한 경우 행을 선택할 때에만 적용되고 출력 형태에는 영향을 미치지 않음

 - 자리값이 음수인 경우 자리값을 오른쪽에서부터 센다.

   (자르는 방향은 무조건 오른쪽방향으로만 가능)

 

 (1)대소문자 변환함수

LOWER	대문자에서 소문자로 변환하는 함수	LOWER('SQL') = sql
UPPER	소문자에서 대문자로 변환하는 함수	UPPER('Sql') =SQL
INITCAP	첫번째 문자만 대문자로 변환하는 함수	INITCAP('sql') = Sql

(2)문자조작함수

 - CONCAT('문자', '문자') : 입력받은 문자연결 

CONCAT('Hello', 'World') = HelloWorld

 - SUBSTRING('전체문자', 추출할자리값 ,추출할 문자수) : 문자열의 일부분을 출력

SUBSTRING('Hello', 2,3) = ell
SUBSTRING('Hello', -4,3) = ell

 - LENGTH() : 길이값 출력(공백도 count함)

select length('Hello World') from d; //11

 - ISTR() : 전체문자열에서 특정문자의 자리값찾아주는 함수

select name, instr(name, 'i') "contain a =" from emp;
//  kim, 2| rain , 3


//중첩함수
select concat(first_name, last_name) name, instr(concat(first_name, last_name), 'i') "contain a =" 
from emp;
//  kimyan, 2| jungrain , 6

select last_name from employees where where instr(last_name,'a') >=1;
//a가 포함된거 출력
select last_name from employees where where instr(last_name,'a') < 0;
//a가 포함되지 않은것 출력

 - LPAD || RPAS : 여백추가(왼쪽|오른쪽)(쌍둥이함수)

select lpad(salary, 10, '*') FROM employees; //******2600
select rpad(salary, 10, '*') FROM employees; //2600******

 - TRIM() : 깍아낼때(제거)쓰는 함수

select trim('H' from 'hhhhhellohhhWorldhhhhh') from dual;
//ellohhhWorld // 양옆 h를 자른다

 - REPLACE() : 전체문자열에서 특정문자를 치환할때 사용

select replace('JACK','J','BL') from daul;// BLACK //J를 BL로 치환

 

2) 숫자함수

 - ROUND() : 지정된 소수점 자릿수로 값을 반올림

ROUND(45.26,2)  //45.93

select round(45.923,2), round(45.923,0), round(45.923,-1) from dual;
//45.92  , 46  , 50

 - TRUNC() : 지정된 소수점 자릿수로 값을 truncate함

TRUNC(45.26,2)  //45.92

select trunc(45.923,2), trunc(45.923,0), trunc(45.923,-1) from dual;
//45.92  , 45  , 40

 

 - MOD() : 나눈 나머지를 반환

MOD(1600,300)  //100

select name, salary, mod(salary,5000) from emp qhere id ='SA_REP';
//Abel 11000 1000
//Tay 8600 3600

 

3) 날짜함수

 - 현재디비서버날짜 및 시간을 반환하는 함수

select sysdate from dual;
// 05-AUG-20

연산	결과	설명
날짜+숫자	날짜	날짜에 일수를 더합니다.
날짜 - 숫자	날짜	날짜에서 일수를뺍니다
날짜 - 날짜	일 수	한 날짜를 다른 나날짜에서 뺍니다.
날짜 + 숫자/24	날짜	날짜에 시간 수를 더합니다.

 

4) 변환함수(명시적 형변환)

 1. TO_CHAR DATE → CHAR

TO_CHAR(date,'format_model)

select employee_id, TO_CHAR(hire_date,'MM/YY') Month_Hired 
from employees 
where last_name='Higgins';

//  508-05/21

  (1)날짜형식요소

요소	결과
YYYY	숫자로 된 전체연도
YEAR	영여 철자로 표기된 연도
MM	월의 2자리값
MONTH	전체 월 이름
MON	월의 3자 약어
DY	3문자로 된 요일 약어
DAY	요일의 전체 이름
DD	숫자형식의 월간 일

  (2)시간요소는 날짜에서 시간부분의 형식을 지정

HH24:MI:SS AM 15:45:32 pm   //24시로 표기된다.
HH12:MI:SS AM 15:45:32 pm   //12시로 표기된다.

  (3)문자열은 큰따옴표로 묶어 추가

DD "of" MONTH //12 of OCTOBER

select to_char(sysdate, 'DD "of" MONTH') from dual;
//07 of august

  (4) 숫자접미어는 숫자를 영어철자로 표기

     - sp: 숫자를 스펠링으로 출력

     - spth : 서수형식으로 출력

     - th : 서수형식으로 출력 (숫자+th)

ddspth // fourteenth

select to_char(sysdate, 'ddsp') from dual;
//seven

select to_char(sysdate, 'ddspth') from dual;
//seventh

select to_char(sysdate, 'ddth') from dual;
//07th

  (5) fm사용

  - 보통 출력시 자리채움으로  07,08,09... 0으로 자리수를 채우는데

     fm 사용시 0자리 없어짐

  - 대소문자 구분에 따라 대소문자 구분 출력이됨

select to_char(sysdate, 'fmDD MONTH YYYY') from dual;
//7 AUGUST 2020

select to_char(sysdate, 'fmDd Month YYYY') from dual;
//7 August 2020

 2. TO_CHAR(NUMBER → CHAR)

TO_CHAR(number, 'format_model')

  (1)숫자형식요소

요소	결과
9	숫자를 나타냄
0	0이 표시되도록 강제적용
$	부동 달러 기호 배치
L	부동 로컬 통화 기호 사용
.	소수점 출력
,	천단위 표시자로 쉼표 출력

//9로 fomat
select TO_CHAR(1234, '9999') from dual; //1234

//0로 fomat
select TO_CHAR(1234, '09999999') from dual; //00001234
select TO_CHAR(1234, '0000000') from dual; //0001234

//$로 fomat
select TO_CHAR(1234, '$999999') from dual; //$1234

//L로 fomat
select TO_CHAR(1234, 'L999999') from dual; //$1234  //미국기준(나라에따라 기호가 달라짐)

//.로 fomat
select TO_CHAR(1945.76, '9999.999') from dual; //1945.760
select TO_CHAR(1945.76, '999999.9') from dual; //1945.8

//,로 fomat
select TO_CHAR(3945.76, '9,999.9') from dual; //3,945.8
select TO_CHAR(3945.76, '9,999,999.9') from dual; //    3,945.8 //공백출력

 3. TO_NUMBER 함수(CHAR→NUMBER)

TO_NUMBER(char[,'format_model']);

select to_number('1234','9999')from dual; //1234

select to_number('$1,234.5', '$9,999.9');//1234.5 숫자만 뽑아줌

4. TO_DATE 함수(CHAR → DATE)

TO_DATE(char[,'format_model'])

select to_date('2020-08-07','YYY-MM-DD') from dual; //2020-08-07

 

3) 일반함수

- NVL(expr1, expr2)

- NVL2(expr1, expr2, expr3)

- NULLIF(expr1, expr2)

- COALESCE(expr1, expr2,........,exprn)

   → 데이터 타입이 일치(통일)해야함 (숫자-숫자/문자-문자)

 

1. NVL 함수

 - null 값을 싫제값으로 변환

   → 데이터 타입 날짜, 문자 및 숫자 사용가능

   → 데이터 타입이 일치(통일)해야함

select nvl(salary,0) from emp; 
//500
//600
//700
//0 → null 일때 0으로 출력

select nvl(hire_date,'01-jan-97') from dual  //null값에 01-jan-97
select nvl(commission_pxt,0) from emp; //null값에 0
select nvl(job_id,'No Job Yet') from emp; //null 값에 No Job Yet

2. NVL2 함수

 - 첫번째 표현식을 검사해서 null이 아니면 두번쨰 표현식을 반환하고 null이면 세번째 표현식 반환

   → 데이터 타입이 일치(통일)해야함

select last_name commission_pctn nvl2(commision_pct, 'sal+comm','sal')income 
from employees 
where department_id in(50,80)
//null이 아닐 때는 sal+comm 출력 null 이면 sal출력

3. NULLIF 함수

 - 선택한 두표현식을 비교해서 두표현식이 같으면 null을반환하고 두표현식이 다르면 앞의것 을반환

 

select nullif(length(first_name), length(last_name)) result form emp;
//- length(first_name)와 length(last_name)를 비교해서 
//두표현식이 같으면 null을반환하고 두표현식이 다르면 expr1을반환

4. COALESCE 함수

- NVL함수보다 여러 대체값을 가질 수 있음

- null이 아닌 첫번째 표현식 반환

   → 데이터 타입이 일치(통일)해야함

select emp_id, 
       COALESCE(to_char(commission_pct), 
       to_char(manager_id),'no commission and no manager')
  from emp;
  
  //commission_pct과 manager_id 가 없으면 no commission and no manager 출력
  //commission_pct이 없으면 manager_id
  // commission_pct이 있으면 commission_pct

 

- 여러행 함수 : 행 집합당 하나의 결과 반환 

여러행함수 추후게시...