[SQL] 단일 행 함수 (Single-Row Funtion)

단일행 함수(Single-Row Function) 란 모든 행에 대해서 각각 적용되어 행의 개수와 동일한 개수를 반환하는 함수를 의미한다. SELECT WHERE ORDER BY절에 사용 가능하다.

문자처리 함수

함수	설명
INITCAP()	첫 글자만 대문자로 변환
UPPER()	모든 글자를 대문자로 변환
LOWER()	모든 글자를 소문자로 변환
CONCAT()	두 개의 문자열을 연결
LENGTH()	문자열의 길이 반환
INSTR()	특정 문자의 위치 반환
SUBSTR()	문자의 일부분 반환
REPLACE()	특정 문자열을 치환
LPAD()	오른쪽 정렬 후 왼쪽에 생긴 빈 공백에 특정 문자 채우기
RPAD()	왼쪽 정렬 후 오른쪽에 생긴 빈 공백에 특정 문자 채우기
LTRIM()	왼쪽에서 특정 문자 삭제
RTRIM()	오른쪽에서 특정 문자, 공백 삭제
TRIM()	양쪽에 있는 특정문자, 공백 삭제

💡 dual : 임시테이블 (dummy) . FROM 절에 적용할 마땅할 테이블이 없는 경우 사용

💻 문자처리 함수 예시코드 💻

-- 💡 INITCAP(): 첫 글자만 대문자
SELECT INITCAP('ORACLE SQL')
FROM dual;

-- 💡 UPPER(): 모두 대문자
SELECT last_name, UPPER(last_name)
FROM employees;

-- 💡 LOWER(): 모두 소문자
SELECT LOWER('Oracle Sql')
FROM dual;

-- 대소문자 상관없이 특정 리터럴 문자를 찾을 때 유용 
SELECT last_name, salary
FROM employees
WHERE UPPER(last_name)='KING';

-- 💡 CONCAT(): 두 개의 문자열 연결
SELECT CONCAT('Oracle','Sql')
FROM dual;

-- 💡 LENGTH() : 문자열의 길이
SELECT last_name, LENGTH(last_name)
FROM employees;

-- 💡 INSTR(): 특정 문자의 위치 반환
-- SELECT INSTR ( 컬럼, 검색값, [m, n] )
-- m : 문자를 검색하기 위한 시작 위치값, n : 몇 번째로 나오는 문자를 검색할 지 
SELECT INSTR('MILLER' , 'L', 1 , 2 ), INSTR('MILLER' , 'X', 1 , 2 )
FROM dual;

-- 💡 SUBSTR(): 문자열에서 일부분 추출
SELECT hire_date 입사일, SUBSTR(hire_date,1,2) 입사년도
FROM employees;

- 💡 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(): 양쪽에서 공백이나 특정문자 제거
SELECT TRIM( '0' FROM '0001234567000' )
FROM dual;

---

숫자처리 함수

함수	설명
ROUND()	지정한 자리 수 이하에서 반올림
TRUNC()	지정한 자리 수 이하에서 절삭
MOD()	나누기 연산 후 나머지 값
CEIL()	주어진 숫자값보다 크거나 같은 최소 정수값 (실수 👉 정수)
FLOOR()	주어진 숫자값보다 작거나 같은 최대 정수값 (실수 👉 정수)
SIGN()	주어진 값이 양수인지 음수인지 0인지 식별

💻 숫자처리 함수 예시코드 💻

-- 💡 ROUND(): 반올림
SELECT ROUND( 456.789, 2 ), ROUND( 456.789, -1 ), ROUND( 456.789 )
FROM dual;

-- 💡 TRUNC(): 절삭
SELECT TRUNC( 456.789, 2 ), TRUNC( 456.789, -1 ), TRUNC( 456.789 )
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;

---

날짜 함수

함수	설명	반환값
SYSDATE()	DB서버에 설정된 날짜	날짜
MONTH_BETWEEN	두 날짜 사이의 월 수	숫자 (개월 수)
ADD_MONTHS	특정 개월 를 더한 날짜 (만약 음수값이면 뺀 날짜)	날짜
NEXT_DAY	명시된 날짜로부터 다음 요일에 대한 날짜	날짜
LAST_DAY	지정된 월의 마지막 날짜	날짜
ROUND	날짜를 가장 가까운 년도 또는 월로 반올림	날짜
TRUNC	날짜를 가장 가까운 년도 또는 월로 절삭	날짜

✨ RR과 YY 타입 비교

Oracle에서 년도를 표기하는RR방식은 과 YY형식 두 가지가 있다.

 

이 두 가지 형식의 차이를 예를 들어보면 입사년도가 1997년인 사원을 찾기 위해서 두 자리 년도 값으로 '97'를 입력 했다면 Oracle은 년도를 2097로 인식하여 처리한다. 이것은 Oracle 서버의 현재 날짜 세기가 2000년이기 때문에 동일하게 처리하는 것이 YY이다. 하지만 RR 형식은 Oracle이 자동으로 반환년도를 결정하며 계산 공식은 다음과 같다.

현재가 2023년이기 때문에 현재 년도의 뒤의 두자리는 00~49 사이에 있다. 이 때, '97'을 입력한다면 입력하는 년도의 두 자리는 50~99에 해당하여 이전 세기 즉, 1900년대로 반환년도를 결정해 1997년으로 인식하여 처리할 수 있다.

💻 날짜처리 함수 예시코드 💻

-- 💡 SYSDATE(): 현재 날짜 조회
SELECT SYSDATE
FROM dual;

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

-- 💡 TRUNC(): 날짜를 가장 가까운 년도 또는 월로 절삭
-- 사원들의 근무일수가 몇 년인지 출력
SELECT last_name, hire_date, TRUNC((sysdate-hire_date)/365) "년" 
FROM employees
ORDER BY 3 desc;

-- 💡 ADD_MONTHS(): 특정 개월 추가 및 빼기
SELECT sysdate 현재, ADD_MONTHS(sysdate,1) 다음달, ADD_MONTHS(sysdate,-1) 이전달
FROM dual;

-- 💡 NEXT_DAY(): 지정된 날짜를 기준으로 돌아오는 가장 가까운 요일에 해당하는 날짜 
SELECT sysdate "현재 날짜", NEXT_DAY(sysdate, '일') "일요일" ,NEXT_DAY(sysdate, 7) "토요일"
FROM dual;

-- 💡 LAST_DAY(): 지정된 날짜의 월의 마지막 날짜
SELECT sysdate, LAST_DAY(sysdate) 이번달, LAST_DAY(sysdate+1) 다음달
FROM dual;

-- 💡 ROUND(): 년도 및 월로 반올림
SELECT  hire_date, 
 ROUND(hire_date,'YEAR') "년도(ROUND)",
 ROUND(hire_date,'MONTH') "월(ROUND)"
FROM employees;

-- 💡 TRUNC(): 년도 및 월 절삭
SELECT  hire_date, 
 TRUNC(hire_date,'YEAR') "년도(TRUNC)",
 TRUNC(hire_date,'MONTH') "월(TRUNC)"
FROM employees;

---

변환함수

SQL문의 데이터 타입을 변환 시키는 방법은 2가지가 있다. 하나는 묵시적 방법이고 다른 하나는 명시적 방법이다. 묵시정 방법은 Oracle이 자동으로 데이터 타입을 변환시키는 방법이다. 명시적 방법은 TO_NUMBERTO_CHARTO_DATE 함수를 사용하여 데이터를 직접 변환시키는 방법이다.

함수	설명	반환값
TO_CHAR	숫자 데이터를 문자로 변환 날짜 데이터를 문자로 변환	문자
TO_DATE	문자 데이터를 날짜로 변환	날짜
TO_NUMBER	문자 데이터를 숫자로 변환	숫자

TO_CHAR() 함수

TO_CHAR 함수는 숫자 및 날짜를 문자로 변환하기 위해서 사용되며 출력 형식은 반드시 단일 따옴표''내부에 기술하며 출력 형식이 제공된다.

날짜를 문자로 변환하였을 때, 출력 형식

💻 변환 함수 예시코드 💻

-- 자동 형변환

-- 월급(salary)이 17000인 사원 정보를 출력
SELECT last_name, salary
FROM employees
WHERE salary = '17000'; -- 문자가 숫자로 자동 변환

-- 💡 TO_CHAR : 날짜데이터를 문자로 
SELECT sysdate, to_char(sysdate, 'YYYY')
FROM dual;

SELECT sysdate, to_char(sysdate, 'MM DD MON DAY DY')
FROM dual;

SELECT sysdate, to_char(sysdate, 'YYYY"년"MM"월"DD"일"')
FROM dual;

SELECT sysdate, to_char(sysdate, 'AM HH"시" HH24:MI:SS')
FROM dual;

-- 💡 EXTRACT
-- 특정 년도만 추출, 월만 추출, 일만 추출
SELECT sysdate, EXTRACT( year FROM sysdate), EXTRACT(month FROM sysdate)
FROM dual;

SELECT  last_name, hire_date
FROM employees
WHERE EXTRACT(month from hire_date) = '09'; 

-- 💡 TO_CHAR : 숫자데이터를 문자로 
SELECT last_name, salary, 
 TO_CHAR(salary, '$999,999') 달러,
 TO_CHAR(salary, 'L999,999') 원화
FROM employees;

-- 자릿수에 따라서 포맷을 늘려줘야한다 
SELECT  TO_CHAR(987654321, '$999,999,999') 달러
FROM dual;

-- 💡 TO_NUMBER : 문자데이터를 숫자로 
SELECT TO_NUMBER('123') + 100 
FROM dual;

-- 입력 값의 포맷 지정
SELECT TO_NUMBER('123,456', '999,999') + 100 
FROM dual;

-- 입력 값의 포맷 지정
SELECT TO_NUMBER('$123,456', '$999,999') + 100 
FROM dual;

-- 💡 TO_DATE : 문자데이터를 날짜로

-- Oracle의 날짜 형식이 RR/MM/DD 이기 때문에 다른 형식으로 변경하기 위해서는 
-- NLS_DATE_FORMAT 파라미터 값을 변경해주면 된다.
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

SELECT TO_DATE( '20230727181030' , 'YYYYMMDDHH24MISS' )
FROM dual;

-- 입력 값의 포맷지정
SELECT TO_DATE( '2023년09월27일' , 'YYYY"년"MM"월"DD"일"' )
FROM dual;

💡 DB에 날짜데이터를 저장한다면? 20230723 (권장)

---

조건 함수

Oracle은 if문이나 case문과 같이 조건에 따라서 SQL 문장을 다르게 처리할 수 있는 DECODE 함수와 CASE 함수를 제공한다.

함수	설명
DECODE()	조건이 반드시 일치하는 경우에 사용
CASE	조건이 범위 및 비교가 가능한 경우에 사용

DECODE 함수

DECODE 함수는 조건이 반드시 일치해야 되는 경우에 사용되는 함수이다. 즉 동등 연산자 = 에 대해서만 사용

	DECODE(컬럼, 비교값1, 결과값1,
                 비교값2, 결과값2,
                  ...
                 비교값n, 결과값n,
                 기본결과값 )

CASE 함수

CASE 함수는 DECODE 함수와 마찬가지로 여러 조건에 대해서 선택적으로 SQL문을 실행시킬 수 있는 함수이다. 차이점은 DECODE 함수는 반드시 조건이 일치하는 경우에 사용이 가능하지만 CASE 함수는 다양한 비교 연산자를 이용하여 조건을 설정할 수 있다.

CASE 컬럼 WHEN 비교값1 THEN 결과값1
          WHEN 비교값2 THEN 결과값2
            ...
          ELSE 결과값n
END

💻 조건 함수 예시코드 💻

-- 💡 DECODE() 
-- 월급(salary)에 따라서 보너스를 차등 지급
SELECT last_name,salary,
DECODE(salary,24000, salary*0.3, 
              17000, salary*0.2,
              salary) 보너스
FROM employees
ORDER BY 2 desc;

-- 💡 DECODE() 
-- 사원들의 입사년도 별로 사원들의 인원수
SELECT COUNT(*) "총인원수",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2001, 1, 0)) "2001",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2002, 1, 0)) "2002",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2003, 1, 0)) "2003",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2004, 1, 0)) "2004",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2005, 1, 0)) "2005",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2006, 1, 0)) "2006",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2007, 1, 0)) "2007",
 SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 2008, 1, 0)) "2008"
FROM employees;

-- 💡 CASE() 
-- 월급(salary)에 따라서 보너스를 차등 지급
SELECT last_name,salary,
CASE salary WHEN 24000 THEN salary*0.3
 		    WHEN 17000 THEN salary*0.2
 		    ELSE salary 
 		    END 보너스 
FROM employees
ORDER BY 2 desc;

-- 💡 CASE() : 비교 연산자 
SELECT last_name,salary,
 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;

-- 💡 CASE() : BETWEEN a AND b 연산자
SELECT last_name,salary,
 CASE WHEN salary BETWEEN 20000 AND 25000 THEN '상'
	  WHEN salary BETWEEN 10000 AND 20001 THEN '중'
 	  ELSE '하'
 END 등급 
 FROM employees
ORDER BY 2 desc;

-- 💡 CASE() : IN 연산자
SELECT last_name,salary,
 CASE WHEN salary IN ( 24000, 17000 , 14000) THEN '상'
	  WHEN salary IN ( 13500, 13000) THEN '중'
	  ELSE '하'
 END 등급 
 FROM employees
ORDER BY 2 desc;