[Oracle DB(오라클)/#1 SQL 함수]

SQL - 함수( function )

오라클 DBM 엔진 내부에 구현되어 있는 내장 함수

SQL 실행순서
FROM -> WHERE -> GROUP -> SELECT문 -> ROWNUM -> ORDER BY

• 단일행 함수
  • 내장 함수 중 입력값에 대해서 단 하나의 출력값을 리턴하는 함수
  • 행렬로 함수를 적용하여 하나의 결과를 반환
• 다중행 함수
  • 여러 간의 데이터를 동시에 입력 받아 1건으로 만들어주는 함수
  • 문자열 함수, 숫자형 함수, 날짜형 함수 변환형 함수(명시적/묵시적), NULL

단일행 함수의 종류

• 문자열 함수 : F(문자) = 문자/숫자
  • UPPER (대문자), LOWER (소문자), INITCAP (첫자만 대문자)
• 숫자형 함수 : F(숫자) = 숫자
  • ABS, MOD, ROUND
• 날짜형 함수 : DATE 타입의 값을 연산
  • SYSDATE
• 변환형 함수(명시적/묵시적)
  • TO_NUMBER : F(숫자/날짜) = 문자
  • TO_CHAR : F(문자) = 날짜
  • TO_DATE : F(문자) = 숫자
• NULL 관련 함수 : NULL값 처리
  • NVL, NULLIF

다중행 함수 - 집계함수

종류	설명	비고
COUNT(*)	전체 행의 수를 출력	NULL포함
COUNT(expr)	expr을 만족하는 행의 수를 출력	NULL 제외
SUM(expr)	expr을 만족하는 행의 합계를 출력	//
AVG(expr)	expr을 만족하는 행의 평균을 출력	//
MAX(expr)	expr을 만족하는 행의 최대값을 출력	//
MIN(expr)	expr을 만족하는 행의 최소값을 출력	//
SRDDEV(expr)	expr을 만족하는 행의 표준편차를 출력	//
VARIAN(expr)	expr을 만족하는 행의 분산을 출력	//

단일행 함수 - CASE 표현

case 표현

각각의 조건이 맞는다면 각 조건에 then 값을 반환,
맞는 조건이 없다면 ELSE절을 수행한다
END로 CASE표현의 마무리
case when 조건1 then 값/SQL
	 when 조건2 then 값/SQL
     else 값/SQL
END

DECODE 표현

조건1 = TRUE 이면 값1,
조건1 = FALSE & 조건2=TRUE이면 값2,
둘 다 아니라면 디폴트 값
DECODE(조건1.값1, 조건2.값2, 디폴트값)

실습

<대문자로 이름을 표기하고 부서번호가 90인 월급을 출력>
SELECT last_name, UPPER(last_name) AS UPPER_NAME, salary 
FROM employees 
where department_id = 90;

<부서번호가 90인 모든 직원의 월급 합계>
SELECT  SUM(salary) AS sum_salary
FROM employees
where department_id = 90;

<DUAL> : 오라클내에 임의 테이블
SELECT 'mango' FROM DUAL;

<문자열을 나타낼때 '', 제목은 "">
SELECT UPPER('mango pineapple kiwi') AS UPPER,
LOWER('mango pineapple kiwi') AS LOWER,
INITCAP('mango pineapple kiwi') AS "INITCAP"
from DUAL;

<문자열 붙여쓰기>
SELECT 'The job ID for ' || UPPER(Last_name) || ' is ' || LOWER(job_ID) AS  EMP_DETAILS
FROM employees
where department_id = 20;

<문자열의 중요성>
SELECT employee_id, last_name, department_id 
FROM employees
where last_name = 'Higgins';

SELECT employee_id, last_name, department_id 
FROM employees
where last_name = 'higgins';

SELECT employee_id, last_name, department_id 
FROM employees
where last_name = INITCAP('higgins');
: 성능의 문제가 발생할 수 있다.

<문자열 붙여 쓰기 함수>
SELECT CONCAT('GRA', 'TEFUL') as "문자열 붙이기"
FROM dual;

<SUBSTR() 함수> : SUBSTR('문자열' 또는 컬럼명 , 1,4) //1번째에서 4개 문자 검색 출력

SELECT substr('SQLDeveloper', 1,3) AS "첫번째부터 3번째까지"
FROM dual;

<문자열 길이>
SELECT LENGTH('오라클')
,LENGTH('오라클 SQL')
FROM dual

<문자열 치환>
replace(문자열, 바꿀문자열, 바뀔문자열)
SELECT replace('JACK and JUE', 'J','BL')
FROM dual;

<문자열 한글자씩 치환>
TRANSLATE(STR, FROM_STR, TO_STR

<문자열 제거>
SELECT TRIM(BOTH 'S' from 'SSMISTHSS') AS "양쪽 제거",
TRIM(LEADING 'S' from 'SSMISTHSS') AS "왼쪽 제거",
TRIM(TRAILING 'S' from 'SSMISTHSS') AS "왼쪽 제거"
FROM dual;

SELECT TRIM(BOTH 'S' from 'SSMISTHSS') AS "양쪽 제거",
LTRIM('SSMISTHSS', 'S') AS "왼쪽 제거",
RTRIM('SSMISTHSS', 'S') AS "오른쪽 제거"
FROM dual;

<문자열 공백 채우기>
R/LPAD("값", "총 문자길이", "채움문자")

SELECT LPAD('SMITH', 10,'*') AS "앞쪽 빈공간 채우기",
RPAD('SAM', 10,'?') AS "뒤쪽 공간 채우기"
FROM dual;

<문자열 찾기>
INSTR('문자열', '찾는문자열',시작번째, 몇번등장(default: 1))
SELECT instr('HELLO ORACLE', 'L', 1, 1) as "첫번째 부터 시작해서 첫번째로 등장 L",
instr('HELLO ORACLE', 'L', 4, 2) as "4번째 2번째로 등장 L ",
instr('HELLO ORACLE', 'L', -7, 2) as "뒤에서7번째 부터 2번째로 등장 L",
instr('HELLO ORACLE', 'ORACLE', 1, 1) as "첫번째 부터 1번째로 등장"
FROM dual;

<나누기 몫, 나머지>
SELECT TRUNC(1600/300) AS "나누기 값"
, MOD(1600,300) AS "나누기 나머지"
FROM dual;

<정수/소수점 반올림>
ROUND("값", "자리수")

SELECT ROUND(1745.9260, 4) as "소수점 4번째 자리까지"
, ROUND(1745.9260, 2) as "소수점 2번째 자리까지"
, ROUND(1745.9260, 0) as "소수점 0번째 자리까지"
FROM dual;

SELECT ROUND(1745.9260, -1) as "정수부분 일의 자리까지"
, ROUND(1745.9260, -2) as "정수부분 십의 자리까지"
, ROUND(1745.9260, -3) as "정수부분 백의 자리까지"
, ROUND(1745.9260, -4) as "정수부분 천의 자리까지"
FROM dual;

<반올림>
SELECT ROUND(1745.9260, 4) as "소수점 4번째 자리까지"
, ROUND(1745.9260, 2) as "소수점 2번째 자리까지"
, ROUND(1745.9260, 0) as "소수점 0번째 자리까지"
FROM dual;

SELECT ROUND(1745.9260, -1) as "정수부분 일의 자리까지"
, ROUND(1745.9260, -2) as "정수부분 십의 자리까지"
, ROUND(1745.9260, -3) as "정수부분 백의 자리까지"
, ROUND(1745.9260, -4) as "정수부분 천의 자리까지"
FROM dual;

<반버림>
SELECT TRUNC(1745.9260 ,1) AS "소수 1번째 자리까지 절삭"
, TRUNC(1745.9260 ,2) AS "소수 2번째 자리까지 절삭"
, TRUNC(1745.9260 ,3) AS "소수 3번째 자리까지 절삭"
, TRUNC(1745.9260 ,4) AS "소수 4번째 자리까지 절삭"
FROM dual;

SELECT TRUNC(1745.9260 ,-1) AS "정수 일의 자리까지 절삭"
, TRUNC(1745.9260 ,-2) AS "정수 십의 자리까지 절삭"
, TRUNC(1745.9260 ,-3) AS "정수 백의 자리까지 절삭"
, TRUNC(1745.9260 ,-4) AS "정수 천의 자리까지 절삭"
FROM dual;

<날짜형식>
SELECT last_name, to_date(hire_date, 'yyyy/mm/dd') AS "입사일"
FROM employees
WHERE hire_date >= '2002/06/15';

SELECT last_name, hire_date
FROM employees
WHERE hire_date >= to_date('2002-06-15', 'yyyy/mm/dd');

SELECT last_name, hire_date
FROM employees
where hire_date >= to_date('2002-06-15', 'yyyy/mm/dd');

<날짜 설정>
SELECT last_name, 
TO_DATE(sysdate,'yyyy/mm/dd') - TO_DATE(hire_date,'yyyy/mm/dd') AS "입사일 부터 지금까지"
FROM employees;

SELECT last_name, 
trunc((TO_DATE(sysdate,'yyyy/mm/dd') - TO_DATE(hire_date,'yyyy/mm/dd'))/7,0) AS "입사일 부터 지금까지 몇 주 일했나"
FROM employees;

SELECT last_name,
trunc((sysdate-hire_date)/7, 0) AS "입사일 현재 까지 몇주"
FROM employees
Where department_id = 90;

SELECT to_char(sysdate, 'yyyy/mon/dd HH24:MI:SS AM DAY') AS "TO_CHAR"
FROM dual;

SELECT to_char(sysdate, 'YYYY"년" MM"월" DD"일" HH24"시" MI"분" SS"초" ') AS  "한글표기"
FROM dual;

SELECT to_char(sysdate, 'DL') AS  "한글표기"
FROM dual;

SELECT Concat(to_char(salary, '$999,999,999,999'), '$') AS "월급 표시"
from employees
where employee_id= 100;

SELECT to_char(sysdate, 'yyyy') as "YEAR"
,to_char(sysdate, 'mm') as "MONTH"
,to_char(sysdate, 'dd') as "DAY"
,to_char(sysdate, 'day') as "WEEKDAY"
,to_char(sysdate, 'HH24:MI:SS') as "TIME"
FROM dual;

SELECT to_number('$12,000', '$999,999')*0.3
from dual;

<다중행함수> : 여러 연산을 하지만 하나의 값으로 나온다
SELECT last_name
, job_id, salary
, case when job_id = 'IT_PROG' then salary+(salary*0.1)
       when job_id = 'ST_CLERK' then salary+(salary*0.15)
       when job_id = 'SA_REP' then salary+(salary*0.2)
       else salary
       end as "BONUS+SALARY"
from employees