Function

함수

데이터 형식

오라클은 표준 SQL + 종속 SQL을 함께 사용, 표현 방법이 조금씩 다르다. 기본 oracle 데이터 형식은 31개 정도가 있다. 그 중 주로 사용하는 데이터 형식은 다음과 같다.

SQL 기호 정리

DUAL 테이블

가상 임시 테이블. 특정 함수의 결과를 조회할 때 사용하는 테이블. 1행만 존재한다.

함수

함수정리표

함수 중에 변수가 없는 몇 개의 함수들이 있다.

함수의 사용과 설명

T0_CHAR TO_NUMBER **TO_DATE

SELECT 100+200 FROM DUAL;

SELECT '100'+'200' FROM DUAL; > 0-9구성, 자동 숫자변환 300 출력

• ‘100’ 은 ‘ ‘ 안에 있어도 자동으로 숫자 변환이 가능하다. 그러나 ‘123, 456’의 식은 숫자 변환 X

TO_NUMBER

문자열 타입을 숫자 타입으로 변환할 때

사용 예시

​ 예1) ‘123, 456’ (문자)를 숫자로 변환

TO_NUMBER('123,456', '999,999');
: 9는 숫자 한 자리, 구분자(,)를 빼고 숫자로 인식
SELECT TO_NUMBER('123,456', '999,999')+'200' FROM DUAL;

TO_NUMBER('$100', '$999'); > 정수 100으로 변환 

SELECT TO_NUMBER('$100', '$999')+ TO_NUMBER('$200', '$999') FROM DUAL;
: 	9라는 것은 숫자로 인식, 나머지는 '기호'로 인식, 버린다. 

TO_CHAR

숫자타입을 문자열타입으로 변환할 때

예1)

​ SELECT 123456 FROM DUAL; -> $123,456으로 출력하겠다.

TO_CHAR(123456, '$999,999')
SELECT TO_CHAR(123456, '$999,999') FROM DUAL;

SELECT TO_CHAR(123456.789, '$999,999') FROM DUAL;
:소수점부분이 잘렸을 경우, 표현 가능(반올림). 그러나 정수부분이 초과되면 오류 

SELECT TO_CHAR(123456.789, '$999,999.99') FROM DUAL;
:소수점까지 표현

• 소수점이 잘렸을 때 반올림으로 표현가능, 그러나 정수부분이 초과되면 오류!

​ 예2)

SELECT TO_CHAR(123456, ‘L999,999’) FROM DUAL; > L은 원화로 표시

TO_DATE

• 현재 오라클에서 설정된 날짜의 형태 조회하기

SELECT * FROM DICT; : 딕셔너리 테이블 이름들이 나옴
SELECT table_name FROM dict WHERE table_name LIKE '%NLS%': 이 안에 포멧이 있음  

SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';
: 날짜 포멧 조회의 최종문

• 날짜 데이터를 문자열 데이터로

  TO_CHAR(SYSDATE, ‘YYYY/MM/DD DAY HH:MI:SS’)

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD DAY HH24:MI:SS') FROM DUAL;

​ 분리기준이 특수기호가 아닐 때: “ “ 이중 따옴표 안에 분리자를 넣어준다.

SELECT TO_CHAR(SYSDATE, 'YYYY" YEAR" MM" MONTH" DD"DAY" HH"HOUR"MI" MIN"SS" SEC"') FROM DUAL;

​ 불필요한 것을 생략할 때: fm

SELECT TO_CHAR(SYSDATE, fm'YYYY" YEAR" MM" MONTH" DD"DAY" HH"HOUR"MI" MIN"SS" SEC"') FROM DUAL;
:불필요한 부분 앞에 fm을 넣어주면 그 부분에서 필요한 값만 조회된다. 

​ 연산자 사용

select sysdate + 1 from dual; : 다음 날짜가 조회
select '25-MAR-21' + 1 from dual; : 오류! '25-MAR-21'값은 sysdate값을 드러내지 않는다.

• 문자데이터를 날짜데이터로

select to_date('25-MAR-21', 'yy/mm/dd') + 1 from dual; 

​ 1년 뒤의 날짜 조회하기

select sysdate + 365 from dual;
select to_char(sysdate, 'yyyy') + 1 from dual;
:문자열 타입으로 원하는 날짜부분(연도)를 변환 뒤 연산자를 사용하면 된다. 

예시) 05년도 입사자 조회하기

select hire_date from employees where to_char(hire_date, 'yy') = '05';

INSTR

INSTAR(‘This is JAVA’, ‘JAVA’) : ‘This is JAVA’에 ‘JAVA’가 포함되어 있는가

select first_name from employees where INSTR(first_name, 'er')= 0;
: 포함하고 있지 않다. 

select first_name from employees where INSTR(first_name, 'er') > 0;
: 포함하고 있다. 0이면 없다는 의미  

select hire_date from employees where instr( hire_date, 'MAR') = 4;
:hire_date의 4번째 자리에 MAR이 있는가 

• 연산자 = > 를 사용하여 존재여부를 따진다.

대소문자변환 LOWER UPPER INITCAP

​ 가령 어떤 키워드를 조회할 때, 그 문자가 어떤 식의 형태를 갖고 있는지 알 수 없을 경우 사용

where lower(first_name) = 'jennifer'

where upper(first_name) = 'JENNIFER'

where initcap(first_name) = initcap('JENNIFER')
:initcap은 문자의 원래 값을 조회한다. 

• = 연산자로 조회시 각 변환타입이 대조 데이터와 맞는지(둘 다 소문자인지, 대문자인지)확인.

REPLACE

REPLACE/TRANSLATE('THIS IS JAVA', 'JAVA', 'ORACLE')

SUBSTR

문자열 함수, 숫자나 날짜 - to_char - 문자열 / 숫자나 날짜는 문자열로 자동형변환 가능

​ SUBSTR(‘this is Java’, 1, 2) ==>th

​ : 위치를 주면 데이터를 리턴(1자리부터 2까지 리턴)

INSTAR

​ INSTR(‘this is Java’, ‘this’) ==> 1

​ : 데이터를 주면 위치를 리턴

LTRIM/RTRIM

select ltrim('     aaa     ') from dual; : 'aaa      '

select rtrim('     aaa     ') from dual; : '      aaa'

의미 없는 공백을 없앨 때: 
select rtrim('##aa###', '#') from dual;  : 'aa###'

ROUND / TRUNC

ROUND: 반올림 함수 TRUNC: 반올림 하지 않고 버림

ROUND(10/3, 0) : 3
ROUND(3.6789, 1) : 3.7(소수점 첫자리, 반올림)
ROUND(336.6789, -1) : 340, 10자리까지 -2는 100자리까지

TRUNC(3.6789, 1) : 3.6 

입사연도별 급여 평균 조회하되 평균이 정수로 보이도록:
select substr(hire_date, 4, 3), trunc(avg(salary), 0) from employees group by substr(hire_date, 4, 3); 

입사 경과년수 조회:
 select round((sysdate-hire_date)/365) from employees;

MOD

:나누고 나머지를 출력하는 함수

employees table에서 홀수 사번 조회:
select employee_id from employees where mod(employee_id, 2) = 1;

순위함수 ROW RANK() DENSE_RANK()

​ ROW / RANK() / DENSE_RANK() OVER (PARTITION BY ORDER BY ) FROM__;

• ROW_NUMBER

  같은 순위를 동일한 순위로 처리하지 않음

  SELECT first_name as NAME, salary as SALARY, ROW_NUMBER() OVER (ORDER BY salary DESC) employees; 
    
  NAME			 SALARY       RANK
  -------------------- ---------- ----------
  Steven			  24000 	 1
  Neena			  17000 	 2
  Lex			  17000 	 3
  John			  14000 	 4
  Karen			  13500 	 5
  Michael 		  13000 	 6
  Nancy			  12008 	 7
  Shelley 		  12008 	 8
  Alberto 		  12000 	 9
  Lisa			  11500 	10
  Den			  11000 	11
    
  

  
  

• RANK() OVER

: 동일 값을 동일 순위로 매겨주는데 중복된 순서는 + 해서 다음 순위에 매겨줌

  SELECT first_name as NAME, salary as SALARY, RANK() OVER (ORDER BY salary DESC) AS RANK FROM employees;
  
  NAME			 SALARY       RANK
  -------------------- ---------- ----------
  Steven			  24000 	 1
  Neena			  17000 	 2
  Lex			  17000 	 2
  John			  14000 	 4
  Karen			  13500 	 5
  Michael 		  13000 	 6
  Nancy			  12008 	 7
  Shelley 		  12008 	 7
  Alberto 		  12000 	 9
  Lisa			  11500 	10
  Den			  11000 	11

• DENSE_RANK()

  동일 값을 동일 순위로 매겨주고 중복된 순위를 +해서 다음 순위에 매기지 않음.

  SELECT first_name as NAME, salary as SALARY, DENSE_RANK() OVER (ORDER BY salary DESC) AS RANK FROM employees;
    
  NAME			 SALARY       RANK
  -------------------- ---------- ----------
  Steven			  24000 	 1
  Neena			  17000 	 2
  Lex			  17000 	 2
  John			  14000 	 3
  Karen			  13500 	 4
  Michael 		  13000 	 5
  Nancy			  12008 	 6
  Shelley 		  12008 	 6
  Alberto 		  12000 	 7
  Lisa			  11500 	 8
  Den			  11000 	 9
    
  

  
  

• • PARTITION BY (파트별로 조회할 때)

  SELECT first_name as NAME, salary as SALARY, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS RANK FROM employees;
    
  NAME			 SALARY       RANK
  -------------------- ---------- ----------
  Jennifer		   4400 	 1
  Michael 		  13000 	 1
  Pat			   6000 	 2
  Den			  11000 	 1
  Alexander		   3100 	 2
  Shelli			   2900 	 3
  Sigal			   2800 	 4
  Guy			   2600 	 5
  Karen			   2500 	 6
  Susan			   6500 	 1
  Adam			   8200 	 1
    
  NAME			 SALARY       RANK
  -------------------- ---------- ----------
  Matthew 		   8000 	 2
  Payam			   7900 	 3
  Shanta			   6500 	 4
  Kevin			   5800 	 5
  Nandita 		   4200 	 6
  Alexis			   4100 	 7
  Sarah			   4000 	 8
  Britney 		   3900 	 9
  Kelly			   3800 	10
  Jennifer		   3600 	11
  Renske			   3600 	11
  .
  .
  

NULL

NULL값을 공백 대신 다른 값으로 변환. (그 값이랑 타입이 같아야 한다)

커미션을 받지 못 하는 사원들 null 을 0으로 조회:
select first_name, NVL(commission_pct, 0) from employees;

select first_name, NVL(to_char(commission_pct), 'NO BONUS') from employees;
: null값과 타입이 같지 않을 경우, 대신한 값으로 타입변환을 해준다