1.Oracle SQL 함수

2022.07.20.화

---

가.단일행 함수

 =>모든 행에 대해서 각각 적용되어 행의 개수와 동일한 개수를 반환하는 함수를 의미

 

1)문자 함수

 -INITCAP 함수 : 단어의 첫 문자를 대문자로 바꾸고 나머지 문자는 소문자로 변경하여 반환

SELECT INITCAP('ORACLE SQL')
FROM dual;

SELECT email, INITCAP(email)
FROM employees;

 -UPPER 함수 : 모든 문자를 대문자로 변경

SELECT UPPER('Oracle Sql')
FROM dual;

SELECT last_name, salary
FROM employees
WHERE UPPER(last_name)='KING';

 -LOWER 함수 : 모든 문자를 소문자로 변경

SELECT LOWER('Oracle Sql')
FROM dual;

SELECT last_name, LOWER(last_name)
FROM employees;

 -CONCAT 함수 : 두 개의 문자열을 연결하여 하나의 문자열로 반환

SELECT CONCAT('Oracle','Sql')
FROM dual;

SELECT CONCAT( last_name, salary)
FROM employees;

 -LENGTH 함수 : 문자열의 길이를 반환

SELECT LENGTH('Oracle')
FROM dual; 

SELECT last_name, LENGTH(last_name)
FROM employees;

 -INSTR 함수 : 문자열에서 특정 문자가 나타나는 위치

SELECT INSTR('MILLER' , 'L', 1 , 2 ), INSTR('MILLER' , 'X', 1 , 2 )
FROM dual;

 -SUBSTR 함수 : 문자열에서 일부분의 문자열을 추출

SELECT SUBSTR('900303-1234567' , 8 , 1 )
FROM dual;

SELECT hire_date 입사일, SUBSTR(hire_date,1,2) 입사년도
FROM employees;

--주민번호 뒷자리 추출
SELECT SUBSTR('900303-1234567' , 8 )
FROM dual;

 -REPLACE 함수 : REPLACE 함수는 특정 문자열을 치환

SELECT REPLACE('JACK and JUE' , 'J' , 'BL' )
FROM dual;

 -LPAD 함수 : 문자열을 오른쪽 정렬 후에 특정 문자를 왼쪽부터 지정한 문자로 채움

SELECT LPAD('MILLER' , 10 , '*' )
FROM dual;

 -RPAD 함수 : 문자열을 왼쪽 정렬 후에 특정 문자를 오른쪽부터 지정한 문자로 채움

SELECT RPAD('MILLER' , 10 , '*' )
FROM dual;

 -LTRIM 함수 : 문자열에서 일치하지 않는 문자가 나올때까지 특정 문자를 삭제(왼쪽) 

SELECT LTRIM('MILLER', 'M')
FROM dual;

 -RTRIM 함수 : 문자열에서 일치하지 않는 문자가 나올때까지 특정 문자를 삭제(오른쪽)

SELECT RTRIM('MILLER', 'R')
FROM dual;

 -TRIM 함수 : 문자열에서 일치하지 않는 문자가 나올때까지 특정 문자를 삭제(양쪽)

  => LEADING(왼쪽), TRAILING(오른쪽), BOTH(양쪽)

SELECT TRIM( '0' FROM '0001234567000' )
FROM dual;

SELECT TRIM( LEADING '0' FROM '0001234567000' )
FROM dual;

SELECT TRIM( TRAILING '0' FROM '0001234567000' )
FROM dual;

 

2)숫자 함수

 -ROUND 함수 : 지정한 자리 수 이하에서 반올림하여 반환

SELECT ROUND( 456.789, 2 )
FROM dual;

SELECT ROUND( 456.789, -1 )
FROM dual;

 -TRUNC함수 :  지정한 자리 수 이하에서 절삭한 결과를 반환

SELECT TRUNC( 456.789, 2 )
FROM dual;

SELECT TRUNC( 456.789, -1 )
FROM dual;

 -MOD함수 :  나누기 연산을 한 후에 나머지 값을 반환

SELECT MOD( 10 , 3 ) , MOD( 10 , 0 )
FROM dual;

 -CEIL함수 :  주어진 숫자값보다 크거나 같은 최소 정수값을 반환

SELECT CEIL(10.6), CEIL(-10.6)
FROM dual;

 -FLOOR함수 :  주어진 숫자값보다 작거나 같은 최대 정수값을 반환

SELECT FLOOR(10.6), FLOOR(-10.6)
FROM dual;

 -SIGN함수 :  주어진 값이 양수(1)인지 음수(-1)인지 0(0)인지 식별할 수 있는 값을 반환(값)

SELECT SIGN( 100 ) , SIGN(-20) , SIGN(0)
FROM dual;

 

3)날짜 함수

 -SYSDATE 함수 :  DB서버에 설정된 날짜를 반환(날짜)

--오라클 날짜 형식 RR/MM/DD

SELECT SYSDATE
FROM dual;

SELECT SYSDATE 오늘, SYSDATE+1 내일, SYSDATE-1 어제
FROM dual;

 -MONTH_BETWEEN 함수 :  두 날짜 사이의 월수를 계산하여 반환(숫자, 개월수)

SELECT last_name, hire_date, MONTHS_BETWEEN(sysdate, hire_date) "근무 월수" FROM employees
ORDER BY 3 desc;

 -ADD_MONTHS 함수 :  특정 개월수를 더한 날짜를 계산하여 반환, 만약 음수값을 지정하면 뺀 날짜를 반환(날짜)

SELECT sysdate 현재, ADD_MONTHS(sysdate,1) 다음달,
 ADD_MONTHS(sysdate,-1) 이전달
FROM dual;

 -NEXT_DAY 함수 :  지정된 날짜를 기준으로 돌아오는 가장 가까운 요일에 해당하는 날짜 를 반환(날짜)

SELECT last_name, hire_date, NEXT_DAY(hire_date, '금'),NEXT_DAY(hire_date, 6)
FROM employees
ORDER BY 3 desc;

 -LAST_DAY 함수 :  지정된 월의 마지막 날짜를 반환, 윤년 및 평년 모두 자동으로 계산됨(날짜)

SELECT last_name, hire_date, LAST_DAY(hire_date)
FROM employees
ORDER BY 2 desc;

 -ROUND 함수 :  날짜를 가장 가까운 년도 또는 월로 반올림하여 반환(날짜)

SELECT last_name, hire_date,
 ROUND(hire_date,'YEAR'),
 ROUND(hire_date,'MONTH')
FROM employees;

 -TRUNC 함수 :  날짜를 가장 가까운 년도 또는 월로 절삭하여 반환 (날짜)

SELECT last_name, hire_date,
 TRUNC(hire_date,'YEAR'),
 TRUNC(hire_date,'MONTH')
FROM employees;

 

4)변환 함수(숫자, 문자, 날짜)

Oracle SQL 형변환

-TO_NUMBER 함수 :   문자 데이터를 숫자 데이터로 변환

SELECT TO_NUMBER('123') + 100
FROM dual;

-TO_DATE 함수 :   문자 데이터를 날짜 데이터로 변환

SELECT TO_DATE( '20170802181030' , 'YYYYMMDDHH24MISS' )
FROM dual;
--2017/08/02 18:10:30

--현재 날짜에서 2017/01/01d을 뺀 결과 출력 SQL
SELECT SYSDATE, SYSDATE - TO_DATE( '20170801' , 'YYYYMMDD' )
FROM dual;

-TO_CHAR 함수 :   숫자 데이터를 문자 데이터로 변환하거나 날짜 데이터를 문자 데이터로 변환

SELECT last_name,hire_date, salary
FROM employees
WHERE TO_CHAR(hire_date, 'MM')='09';

SELECT TO_CHAR(SYSDATE, ' YYYY "년" MM "월" DD "일" ') 날짜
FROM dual;

SELECT last_name, salary,
 TO_CHAR(salary, '$999,999') 달러,
 TO_CHAR(salary, 'L999,999') 원화
FROM employees;

 

5)조건 함수

 -DECODE 함수 : 조건이 반드시 일치하는 경우에 사용

SELECT last_name,salary,
 DECODE(salary, 24000, salary*0.3, -- salary == 24000
 		17000, salary*0.2, -- salary == 17000 
 		salary) 보너스     -- default
FROM employees
ORDER BY 2 desc;

 -CASE 함수 : 조건이 반드시 일치하지 않아도 범위 및 비교가 가능한 경우

SELECT last_name,salary,
--CASE column WHEN 값 TEHN
 	CASE salary WHEN 24000 THEN salary * 0.3
 		    WHEN 17000 THEN salary * 0.2
 		    ELSE salary
 	END 보너스 
FROM employees ORDER BY 2 desc;

SELECT last_name,salary,
--CASE WHEN 조건절 THEN
	CASE WHEN salary >= 20000 THEN 1000
	     WHEN salary >= 15000 THEN 2000
	     WHEN salary >= 10000 THEN 3000
	                          ELSE 4000
	END 보너스 
FROM employees ORDER BY 2 desc;

---

나.그룹 함수

 

1)그룹 함수

 =>단일함수와는 달리 그룹 함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 반환하는 함수

 

 -SUM 함수 : 해당 열의 총합계

SELECT SUM(DISTINCT salary),SUM(ALL salary), SUM(salary)
FROM employees;

 -AVG 함수 : 해당 열의 평균

SELECT SUM(salary), AVG(salary)
FROM employees;

 -MAX 함수 : 당 열의 총 행중에 최대값

 -MIN 함수 : 해당 열의 총 행중에 최소값

SELECT MAX(salary), MIN(salary)
FROM employees;

 -COUNT 함수 : 행의 개수를 카운트

SELECT COUNT(last_name), COUNT(commission_pct)
FROM employees;

--중복제거
SELECT COUNT(job_id), COUNT(DISTINCT job_id)
FROM employees;

--전체 행 개수(NULL 포함)
SELECT COUNT(*)
FROM employees;

 

2)GROUP BY

 =>GROUP BY는 특정 컬럼값을 기준으로 그룹으로 묶을 때 사용하는 방법

 =>단순 컬럼을 GROUP BY를 사용 하여 그룹핑(Grouping)하면 SELECT 절에서 그룹함수와 같이 사용 가능

 예)부서별,성별,직급별 또는 학년별로 묶는 경우

 

 -GROUP BY 절 작성 지침

■ SELECT절 뒤에 사용할 수 있는 컬럼은 GROUP BY뒤에

     기술된 컬럼이거나 그룹함수가 적용된 컬럼만 사용 가능하다.

■ WHERE 절을 사용하여 행을 그룹으로 그룹핑(Grouping)하기 전에 제외 시킬 수 있다.

■ 그룹으로 묶은 후 행을 제외 시키려면 HAVING절을 사용한다.

■ GROUP BY 절 뒤에는 컬럼 별칭(alias) 및 컬럼 순서 위치값을 사용할 수 없다.

■ WHERE 절에는 그룹함수를 사용할 수 없다.

 

SELECT department_id 부서번호, AVG(salary) 평균월급
FROM employees
GROUP BY department_id
ORDER BY 1;

SELECT TO_CHAR( hire_date , 'YYYY' ) 년
 ,TO_CHAR( hire_date , 'MM') 월 , SUM(salary)
FROM employees
GROUP BY TO_CHAR( hire_date , 'YYYY'),
 TO_CHAR( hire_date , 'MM')
ORDER BY 년 ASC;

 

3)HAVING

=>HAVING 절은 GROUP BY절에 의해서 생성된 결과 중에서 조건과 일치하는 데이터를 추출할 때 사용

HAVING절 문법

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) >= 90000
ORDER BY 1;

---

다.SQL Docu

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Introduction-to-Oracle-SQL.html#GUID-049B7AE8-11E1-4110-B3E4-D117907D77AC