SQL 함수 / 단일행, 숫자처리, 날짜, 변환, 조건 함수

edu day 3

SQL 함수

단일행 함수

LPAD && RPAD : 문자열을 오른쪽(왼쪽) 정렬 후에 특정 문자를 왼쪽(오른쪽)부터 지정한 문자로 채우는 함수이다. n은 전체 자릿수를 의미하고 str은 삽입할 문자를 의미한다. 삽일할 문자가 없을 경우 공백으로 채워진다.

L(or R)PAD( column | expression, n, 'str')

SELECT LPAD('MILLER', 10, '*') FROM DUAL;   --> ****MILLER
SELECT RPAD('MILLER', 10, '*') FROM DUAL;   --> MILLER****

SELECT RPAD(SUBSTR('900303-1234567', 1, 8), 14, '*') FROM DUAL; --> 900303-1******

시험!! REPLACE나 RPAD를 사용하여 주민번호 처리하는 문제.

.

TRIM : 앞/뒤에 있는 공백 또는 지정한 문자를 제거해줄 수 있다.

TRIM( LEADING'str' FROM col | exp )

SELECT TRIM( '0' FROM '0001234567000') FROM DUAL;	
--> 1234567. 지정한 문자 '0' 삭제

SELECT LENGTH('  hello  ') from dual;			
--> 9. 공백까지 센다.

SELECT LENGTH(trim ('  hello  ')) from dual;	
--> 5. 앞뒤 공백을 제거했다.

숫자처리 함수

ROUND : 지정한 자리 수 이하에서 반올림한 결과를 반환하는 함수이다. n은 반올림하여 출력하기 위한 자리수를 의미한다.

ROUND ( col | exp , [n] );

SELECT ROUND( 456.789, 2) FROM DUAL;	
--> 456.79	n+1의 자리에서 반올림하여 n의자리까지만 나타낸다.

SELECT ROUND( 456.789 ) FROM DUAL;
--> 457		n의 값을 입력하지 않으면 n이 0이고 정수로 반올림하여 출력한다. 
			일의 자리가 0을 나타낸다.
            
SELECT ROUND( 456.789, -1) FROM DUAL;
--> 460		따라서 -1부터 십의 자리수가 된다. 일의 자리에서 반올림

TRUNC : 지정한 자리 수 이하에서 절삭한 결과를 반환하는 함수. n은 절삭을 위한 자리수를 의미한다. 마찬가지로 n을 생략하면 기본값은 0이기 때문에 소수점에서 절삭이 된다.

TRUNC( col | exp, [n] )

SELECT TRUNC(456.789, 2) FROM DUAL;		-->	456.78

SELECT TRUNC(456.789) FROM DUAL;	-->	456

SELECT TRUNC(456.789, -1) FROM DUAL;	-->	450

중요!!
MOD : n은 나눌 값을 의미한다. n이 0이면 값 자체를 반환한다. 값을 0이라도 넣지 않으면 에러. JAVA에서는 '%' 기호로 사용한다.

MOD( col | exp, n )

SELECT MOD(10, 3) FROM DUAL;	--> 10 % 3  = 1
SELECT MOD(10, 0) FROM DUAL;	--> 0

-- 사원번호가 홀수/짝수인 사원 출력

--홀수
SELECT empno 사번, ENAME 이름 , SAL 월급 
FROM EMP WHERE MOD(EMPNO, 2) = 1; 
--짝수
SELECT empno 사번, ENAME 이름 , SAL 월급 
FROM EMP WHERE MOD(EMPNO, 2) = 0;

CEIL : 소수점을 가진 실수값을 정수값으로 반환하는 함수로서, 주어진 숫자보다 크거나 같은 최소 정수값을 반환한다. 음수값 설정도 가능하다.

CEIL( col | exp )

SELECT CEIL(10.6) FROM DUAL;	--> 11
SELECT CEIL(-10.6) FROM DUAL;	--> -10

FLOOR : 소수점을 가진 실수값을 정수값으로 반환하는 함수로서, 주어진 숫자보다 작거나 같은 최대 정수값을 반환한다. 음수값 설정도 가능하다.

FLOOR( col | exp )

SELECT FLOOR(10.6), FLOOR(-10.6) FROM DUAL;
--> 10, -11

SIGN : 지정된 값이 양수인지 음수인지 또는 0인지 판단할 수 있는 함수이다. 양수는 1, 음수는 -1, 0은 0이다.

SIGN( col | exp )

SELECT SIGN(100), SIGN(-20), SIGN(0) FROM DUAL;
-->	1, -1, 0

• 월급이 1000 이상인 사원의 이름과 월급을 SELECT

SELECT ENAME, SAL FROM EMP WHERE SAL >= 1000 ORDER BY SAL;
SELECT ENAME, SAL FROM EMP WHERE SIGN(SAL - 1000)>= 0 ORDER BY SAL;

---

날짜 함수

SYSDATE :
DB서버에 설정된 날짜를 반환한다. 형식은 RR/MM/DD이다. YY가 아니라 RR을 쓰는건 자동으로 반환 년도를 결정한다. YY형식은 오라클 서버의 현재 날짜 세기와 동일하게 처리하기 때문에, 현재 2023년도인데 YY값이 95면 2095년으로 처리된다.
반면에 RR형식은 0~49/50~99를 기준으로 현재 세기와 이전 세기를 판별한다. 그래서 RR값이 95면 1995년으로 판별한다.

• 예시
  

SELECT SYSDATE FROM DUAL;	--> DB에 저장된 현재 날짜 출력

• 기본적인 날짜 연산은 다음과 같다.
  

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

-- 근무 년수 계산

SELECT ename, hiredate, TRUNC((SYSDATE - HIREDATE)/365)년 FROM EMP ORDER BY 3 DESC;
SELECT ename, hiredate, FLOOR((SYSDATE - HIREDATE)/365)년 FROM EMP ORDER BY 3 DESC;

MONTHS_BETWEEN : 날짜와 날짜 사이의 개월 수를 반환하는 함수이다. 소수점 앞은 월, 소수점 뒤는 월의 일부분을 나타낸다.

MONTHS_BETWEEN( date1, date2 )

• 근무 월수 계산

SELECT ENAME, hiredate, TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) "근무 월수" FROM EMP ORDER BY 3 DESC;

ADD_MONTHS : 지정된 날짜에 특정 개월 수를 더하거나 뺀 날짜를 반환하는 함수이다. date1은 지정된 날짜를 의미하고 n값은 더하기나 뺄 개월 수를 의미한다. n값은 양수이거나 음수일 수 있다.

ADD_MONTHS( date1, n )

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

• 입사일로부터 5달 뒤의 날짜 SELECT

SELECT ENAME, HIREDATE, ADD_MONTHS(hiredate, 5) FROM EMP ORDER BY 3 DESC;

NEXT_DAY : 지정된 날짜를 기준으로 돌아오는 가장 가까운 요일에 해당하는 날짜를 반환하는 함수.date 1은 지정된 날짜를 의미하고 'string' 값은 돌아오는 요일을 의미한다. '일' '월' 화' 또는 '일요일' '월요일' '화요일' 등으로 기술한다. 또한 n값은 문자 요일 대신에 숫자 값으로 표현 가능하다. 일요일은 1, 월요일은 2... 토요일의 7까지 기술할 수 있다.

NEXT_DAY( date1, 'string' | n )

SELECT  NEXT_DAY(SYSDATE, '금') FROM DUAL;
SELECT  NEXT_DAY(SYSDATE, '금요일') FROM DUAL;
SELECT  NEXT_DAY(SYSDATE, 6) FROM DUAL;
--> 셋 다 같은 입력
SELECT  NEXT_DAY(SYSDATE, 'friday') FROM DUAL;	--> 영어는 안된다.

LAST_DAY : 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수로써 윤년 및 평년 날짜를 자동으로 계산한다. 대부분은 마지막 날짜가 정해져 있으나 2월달인 경우에는 윤년에 의해 29일로 달라질 수 있다.

LAST_DAY(date 1)

SELECT LAST_DAY(SYSDATE) FROM DUAL;	-- 현재 날짜 달의 마지막 날짜 출력

• 한달 뒤의 가장 빨리 오는 토요일의 날짜 출력

SELECT NEXT_DAY(ADD_MONTHS(SYSDATE, 1), 7) FROM DUAL;

ROUND : 날짜 또한 숫자이기 때문에 반올림할 수 있다. 두번 째 값으로 YEAR로 지정하면 년도를 반올림하고, 'MONTH'를 지정하면 월을 반올림한다.

ROUND( date1, 'YEAR' ) | ROUND( date1, 'MONTH')

SELECT ENAME, hiredate, ROUND(hiredate, 'YEAR'), ROUND(hiredate, 'MONTH')FROM EMP;

TRUNC :두번째 값의 선택사항으로 년과 월을 절삭할 수 있다.

TRUNC( date1, 'YEAR' ) | ROUND( date1, 'MONTH')

SELECT ENAME, HIREDATE, TRUNC(HIREDATE, 'YEAR'), TRUNC(HIREDATE, 'MONTH') FROM EMP;

---

변환함수(중요!!!)

Oracle types = 숫자, 문자, 날짜

• 묵시적 : 오라클이 자동으로 데이터 타입을 변환시키는 방법. 코드 작업이 별도로 필요 없다.
• 명시적 : 자동 변환 X. 코드 작업이 별도로 필요하다.(ex. TO_NUMBER, TO_DATE, TO_CHAR 함수를 사용하여 데이터를 직접 변환)

TO_CHAR : 숫자 및 날짜를 문자로 변환한다. 출력 형식을 지정할 수 있다.

TO_CHAR( number | date, 'format' )

• 날짜 출력 형식

• 시간 출력 형식

SELECT HIREDATE, TO_CHAR(hiredate, 'YYYY') FROM EMP;	--날짜를 문자로 형변환

• 81년도에 입사한 사람만 SELECT

SELECT HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '1981';

• 81년도 2월에 입사한 사람 SELECT

SELECT ename, hiredate FROM EMP WHERE TO_CHAR(hiredate, 'YY') = '81' AND TO_CHAR(hiredate, 'MM') = '02';
SELECT ename, hiredate FROM EMP WHERE TO_CHAR(hiredate, 'YY/MM') = '81/02';

• 현재 날짜와 시간을 특정 형식에 맞게 출력

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

• 숫자 출력 형식
  -- 사용 예

SELECT TO_CHAR(1111, '$99999'), TO_CHAR(1111, '99999.99'), TO_CHAR(1111, '99,999') FROM DUAL;
--> $1111, 1111.00, 1,111

SELECT TO_CHAR(1111, 'L99,999') FROM DUAL;	
--> ￦1,111

• 사원테이블 이름, SAL => $ 1000단위 쉼표, SAL => 원 1000단위 표시

SELECT TO_CHAR(SAL, '$99,999') 달러, TO_CHAR(SAL, 'L99,999') 지역통화 FROM EMP;

TO_NUMBER : '숫자 형태의 문자열'을 '숫자'로 변환하기 위해서 사용된다. 문자 데이터는 숫자 데이터로 자동으로 형변환 되지만 명시적으로 TO_NUMBER 함수를 사용하는 것이 가독성을 높일 수 있다.

TO_NUMBER( str )

SELECT '10'+1, TO_NUMBER('1234') FROM DUAL;     
-- 문자 10 => 숫자 10 묵시적 형변환

SELECT TO_NUMBER('10')+1 FROM DUAL;     
-- 명시적 형변환

TO_DATE : '날짜 형태의 문자열'을 명시된 '날짜' 데이터로 변환하기 위해 사용된다.

TO_DATE( str, 'format' )

SELECT '20230511' FROM DUAL;	-- 문자열 형식인 날짜
SELECT TO_DATE('20170802') FROM DUAL;	--문자열을 숫자 형식의 날짜로 변경 
SELECT TO_DATE('20170802', 'YYYYMMDD') FROM DUAL;	--날짜 형식 지정

조건 함수

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

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

• 보너스를 차등 지급하는데, 월급이 24000이면 30%, 17000이면 20%로 처리하고 나머지 사원은 100% 그대로 지급받는 형태로 출력

SELECT 
ENAME 이름, SAL 월급, 
DECODE(SAL, 
	800, SAL*0.3, 
	1600, SAL*0.1, 
	SAL) 보너스 
FROM EMP ORDER BY 2;

중요!!! 쓸 일이 엄청 많다.
CASE : 조건이 반드시 일치하지 않아도 범위 및 비교가 가는한 경우에 사용하는 함수
==> CASE함수가 더 범용적이다.

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

• 출력 예시

SELECT ENAME 이름, SAL 월급, 
    CASE SAL
        WHEN 800 THEN SAL * 0.3
        WHEN 1600 THEN SAL * 0.2
        ELSE SAL
    END 보너스
FROM EMP;
-- 동등 CASE WHEN THEN ELSE END

SELECT ENAME 이름, SAL 월급,
    CASE	-- 비교 CASE 활용 시 col을 조건식에 넣어준다.
        WHEN SAL < 1300 THEN SAL*0.1
        WHEN SAL < 2000 THEN SAL*0.2
        WHEN SAL > 3000 THEN SAL*0.3
        ELSE SAL * 0.4
    END 보너스
FROM EMP;
-- 비교 CASE WHEN THEN ELSE END

• 각 sal에 해당하는 bonus 출력 1500미만 => 1000 / 1500~1999 => 2000 / 2000~2999 => 3000 / 3000~5000 => 4000

SELECT ENAME 이름, SAL 월급,
    CASE
        WHEN SAL < 1500 THEN 1000
        WHEN SAL < 2000 THEN 2000
        WHEN SAL < 3000 THEN 3000
        ELSE 4000
    END 보너스
FROM EMP
ORDER BY 2;

--> CASE / WHEN / THEN / ELSE / END : 기본 문법 기억하자!