sql 단일행함수 - 문자, 숫자, 날짜, 변환함수

--단일 행 함수 - 문자함수
--; 프로그래머는 복잡한 SQL 문의 이해나
--  오라클이 제공하는 함수를 몰라도 사용에는 문제가 없다
--  But, SQL 문을 잘 이해해야 되는 이유가 있다
--  1) 데이터를 프로그램에서 가공처리하는 것보다
--     DBMS에서 처리해서 결과만 받아오는 것이
--     성능상 훨씬 낫다
--  2) 아키텍처 면에서 프로그램은 받아온 결과를
--     보여주는데 주력하고, DBMS는 데이터를 저장/처리
--     에 집중하면 둘 간에 적정한 역할의 분리가 이루어
--     지므로 유연성이 증대된다
--     (프로그램과 DBMS가 느슨한 연결이 되어서
--     변경/유지보수에 좋다)

--문자함수
--LOWER 문자열을 소문자로 변환한다
--UPPER 문자열을 대문자로 변환한다
--INITCAP 첫문자만 대문자로 나머지는 소문자로

1)ERP 부서가 있는 지역을 검색한다
SELECT * FROM dept;

SELECT loc "ERP 부서 지역", dname 부서명
FROM dept 
WHERE UPPER(dname) ='ERP';


SELECT loc "ERP 부서 지역", dname 부서명
FROM dept 
WHERE LOWER(dname) ='erp';


SELECT loc "ERP 부서 지역", dname 부서명
FROM dept 
WHERE INITCAP(dname) ='Erp';


--문자연산함수
--SUBSTR 문자열내에 지정된 위치의 문자열을 반환
--        SUBSTR('oracle', 1, 2) => or               --오라클은 첫번째가 1.
--LENGTH 문자열의 길이를 반환
--        LENGTH('oragle') => 6
--INSTR 지정된 문자의 위치를 리턴
--    a라는 글짜가 몇번째에 있는지
--      INSTR('oracle', 'a') => 3
--TRIM 접두어나 접미어를 잘라낸다 (많이 쓰임)
-- o라는 글자 잘라내라
--     TRIM('o' FROM 'oracle') => racle
--LPAD, RPAD 지정된 문자열의 길이만큼 빈부분에
--           문자를 채운다
--     10글자 중 *을 왼쪽에 패딩해라
--           LPAD('20000', 10, '*')
--           => *****20000

2)부서의 명과 위치를 하나의 컬럼으로 검색한다
--CONCAT는 문자열을 연결해주는 함수지만
--잘 사용하지 않는다
--왜냐하면 || 을 더 많이 사용한다

CONCAT ('김','연아') => 김연아

SELECT CONCAT(dname, ' '||loc)
FROM dept;

SELECT dname||' '||loc      --연산자가 함수보다 속도가 더 빠름.
FROM dept;
 
 
3) 부서명과 길이를 출력하라
SELECT dname, LENGTH(dname)
FROM dept;



--4)SUBSTR함수를 이용해서 컬럼에 일부 내용만을 검색한다
SELECT ename, SUBSTR(ename, 2),   -- 2번째 글자부터
              SUBSTR(ename,-2),   -- 뒤에서 2번째 글자부터
              SUBSTR(ename,1, 2),   -- 1번째 글자부터 2글자
              SUBSTR(ename,-2, 2)    -- 뒤에서 2번째 글자부터 2글자
FROM emp;
              


--5)사원 이름에 'a'가 나타나는 위치를 출력한다
SELECT INSTR('database','a'),    -- 처음부터
      INSTR('database','a',3),     -- 3이후
      INSTR('database','a',1,3)       -- 1이후 3번째 보이는 위치
FROM dual;

SELECT * FROM emp;

SELECT ename, INSTR(ename, '이')
FROM emp;


--6)TRIM 함수를 이용 다양한 방법으로 문자열을 검색한다
-- 주로 공백문자 없앨 시 많이 사용.
-- 입력 시 '홍길동' 이렇게 입력하지 않고 ' 홍길동' , '홍길동 ' 입력되는 경우 
-- ename='홍길동'; 검색시 검출이 안된다.
-- TRIM(ename)을 하면 앞뒤의 공백 문자를 제거해준다.

SELECT TRIM('남' from '남기남'),      --앞뒤 모두
       TRIM(leading '남' from '남기남'),  --앞에서 잘라라
        TRIM(trailing '남' from '남남남기남남남')  --뒤에서 잘라라
FROM dual;


-- 7)이름과 급여를 각각 10컬럼으로 검색한다
SELECT eno, RPAD(ename, 10, '*'), LPAD(sal, 10, '*')
FROM emp;


-- 8)부서명의 마지막 글자를 제외하고 검색한다
SELECT dno, SUBSTR(dname, 1, LENGTH(dname)-1)   --dname을 첫번째부터 해당 길이보다 작을때까지 추출해라
FROM dept;



--문자치환함수
--TRANSLATE : 문자단위 치환된 값을 리턴한다
--            TRANSLATE('oracle', 'o', '#')
--            => #racle
--REPLACE : 문자열단위 치환된 값을 리턴한다
--            REPLACE('oracle', 'or', '##')
--            => ##acle

-- REPLACE를 더 많이 쓴다

--9) 
SELECT TRANSLATE('World of Warcraft', 'Wo', '-*') Translate,  --한 글자씩
        REPLACE('World of Warcraft', 'Wo', '--') Replace  --연속적으로 나올 때
FROM dual;

내용을 입력하세요.


*숫자 , 날짜 함수


/*

단일 행 함수 - 숫자, 날짜형 함수

ROUND : 그냥 쓰면 소수점을 반올림해서 정수로
        ROUND(m, n)
        n자리까지 반올림한다
        ROUND(123.4567, 3) => 123.457
TRUNC : TRUNC(m, n)
        n자리 미만을 절삭한다
        TRUNC(123.4567, 3) => 123.456
MOD : MOD(m, n)
      m을 n으로 나눈 나머지 계산한다
      MOD(10, 4) => 2
POWER : POWER(m, n)
        m의 n승 계산한다
        POWER(2, 4) => 16
CEIL  : CEIL(m)
        m보다 큰 가장 작은 정수를 찾는다
        CEIL(2.34) => 3
FLOOR : FLOOR(m)
        m보다 작은 가장 큰 수를 찾는다
        FLOOR(2.34) => 2
ABS : ABS(m)
      m의 절대값을 계산한다
      ABS(-4) => 4
SQRT :  SQRT(m)
        m의 제곱근을 계산한다
        SQRT(9) => 3
SIGN : SIGN(m)
        m이 음수일 때 -1, 양수일 때 1, 0이면 0을 반환
        SIGN(-3) => -1
        
*/
        
--1)다양한 숫자 함수를 이용한 결과를 확인한다
SELECT ROUND(98.765), TRUNC(98.765),
       ROUND(98.765, 2), TRUNC(98.765, 2)
FROM dual;

SELECT MOD(19, 3), MOD(-19, 3)
FROM dual;

--2)부서의 연봉을 계산한다. 단 100단위 미만은 절삭한다
SELECT eno, ename, TRUNC(sal*12+NVL(comm, 0), -2) 연봉
FROM emp;

SELECT TRUNC(19786.786, 2), TRUNC(19786.786, -2)
FROM dual;
        
        
--날짜 함수

--3)현재 시간을 검색하자
SELECT sysdate
FROM dual;

ALTER SESSION SET nls_date_format='YYYY/MM/DD:HH24:MI:SS';

SELECT sysdate
FROM dual;

--4)김연아의 
--오늘 날짜, 입사 일자, 입사일로부터 오늘까지 기간,
--입사일 이후 100일이 지난날 등을 검색하고
--날짜 연산의 결과를 보자

ALTER SESSION SET nls_date_format='YY/MM/DD';

SELECT sysdate, hdate, sysdate-hdate, hdate+100
FROM emp
WHERE ename='김연아';

SELECT sysdate, hdate, TRUNC(sysdate-hdate), hdate+100
FROM emp
WHERE ename='김연아';


--날짜 + 숫자 = 날짜(일수 이후 날짜)
--날짜 - 숫자 = 날짜(일수 이전 날짜)
--날짜 + 숫자/24 = 날짜 (시간을 더한 날짜)
--날짜 - 날짜 = 숫자 (두 날짜 간에 차(일수))
--
--날짜 함수는 주로 회계 정산시 많이 사용한다
--ERP 솔루션, SI 회계


/*
YYYY : 년도
MM : 월
DD : 날짜
HH : 시간
MI : 분
SS : 초
        
ROUND : ROUND(날짜, 형식)
        형식에 맞추어 반올림한 날짜를 반환한다
        ROUND(sysdate, 'DD') => 2020/02/26
TRUNC : TRUNC(날짜, 형식)
        형식에 맞추어 절삭한 날짜를 반환한다
        TRUNC(sysdate, 'YYYY') => 2020/01/01
MONTHS_BETWEEN : MONTHS_BETWEEN(날짜1, 날짜2)
                 두 날짜간의 기간을 월 수로 계산한다
                 MONTHS_BETWEEN('2020/03/01', '2020/04/01')
                 => 1
ADD_MONTHS : ADD_MONTHS(날짜, n);
             날짜에 n달을 더한 날짜를 계산한다
             ADD_MONTHS('2020/03/01', 23)
             => '2022/02/01'
NEXT_DAY : NEXT_DAY(날짜, 요일)
           날짜 이후 지정된 요일에 해당하는 날짜를 계산
           요일 표현은 'sun', '일요일', 1과 같이
           다양한 표현이 가능하다
LAST_DAY : LAST_DAY(날짜)
           날짜를 포함한 달의 마지막 날짜를 계산
           LAST_DAY('2020/02/25') => 2020/02/29        
*/

--5) 숫자와 날짜를 반올림하여 출력한다
-- yy12월이니까 반올림
-- mm 12월 1일이니까 1일은 반올림해봤자 초기화돼서 1일로 나옴
-- 지금 오후 12시가 지났기 때문에 12월 2일로 반올림
SELECT sysdate, ROUND(sysdate, 'YY') 년,
      ROUND(sysdate, 'MM') 월,
      ROUND(sysdate, 'DD') 일
FROM dual;

--6) 숫자와 날짜를 절삭하여 출력한다
SELECT sysdate, TRUNC(sysdate, 'YY') 년,
      TRUNC(sysdate, 'MM') 월,
      TRUNC(sysdate, 'DD') 일
FROM dual;

--7) 김연아가 오늘까지 일한 일수를 검색한다
--입사일을 포함하기 위해 +1을 해줬다
SELECT TRUNC(sysdate, 'DD') - TRUNC(hdate, 'DD')+1 "일한 날"
FROM emp
WHERE ename='김연아';


--8) 20번 부서 직원들이 현재까지 근무한 개월 수를 검색한다
--소수점을 자르기 위해 TRUNC를 사용.
SELECT eno, ename, TRUNC(MONTHS_BETWEEN(sysdate, hdate)) 근무개월
FROM emp
WHERE dno='20';


--9) 20번 부서원들이 입사 100일째 되는 날과 10년째 되는 날을 검색한다
--입사 당일 포함이므로 99를 더했다.
SELECT eno, ename, hdate 입사일, 
hdate+99 "입사 100일째", 
ADD_MONTHS(hdate, 10*12) "입사 10년째 되는 날"     --10*12개월 = 10년
FROM emp
WHERE dno='20';


--10) 20번 부서원들이 입사한 이후 첫 번째 일요일을 검색한다
SELECT eno, ename, hdate, 
      NEXT_DAY(hdate, '일요일') "입사 후 첫번째 일요일"   --해당되는 이후의 요일검색시 사용
FROM emp
WHERE dno='20';


--11) 20번 부서원들의 입사한 달의 마지막 날짜와
--입사한 달에 근무 일수를 검색한다  => +1을 해야한다.
--(단 입사일은 근무일에서 제외한다) => +1을 해주지 않아도 된다.
SELECT eno, ename, hdate, LAST_DAY(hdate) "입사한 달 마지막 날짜" ,
       LAST_DAY(TRUNC(hdate))-TRUNC(hdate) "입사한 달 근무일 수"
FROM emp
WHERE dno='20';
내용을 입력하세요.


*변환함수


/*
단일 행 함수 - 변환 함수

날짜 출력 형식
년 : YYYY : 네자리로 표현된 년도(1999, 2020, 2011)
     YY   : 두자리로 표현된 년도(99, 20)
            앞에 두자리는 현재 년도를 사용한다
            99년은 2099년을 의미한다
     RR   : 두자리로 표현된 년도(99, 20)
            앞에 두자리는 현재 년도와 가까운 년도를 
            사용한다
            99년은 1999년, 20년은 2020년
월  MM    : 두자리 숫자로 표현된 월 (03, 04)
   MONTH : 영문이나 한글로 표현된 월(MARCH, APRIL, 3월)
   MON   : 약자로 표현된 영문 및 한글 월(MAR, APR, 3월)
           한글인 경우는 MONTH 와 동일하다
일  DD   : 두자리 숫자로 표현된 일자(01, 02)
   DAY   : 영문이나 한글요일(SUNDAY, MONDAY, 일요일)
   DY   : 약자로 표현된 요일(SUN, MON, 일, 월)
시  HH24 : 1시에서 24시까지 표현
    HH   : 1시에서 12시까지 표현
            정확한 표현을 위해 AM/PM을 추가하는 것이 좋다
분  MI   : 두자리 분 표시
초  SS   : 두자리 초 표시
   SSSS : 하루를 초로 환산한 다음 표현(0-86399)
오전/오후 AM/PM : 오전 오후 표기

숫자 출력 형식
9   숫자의 출력 폭지정(자리수가 부족하면 생략)
0   선행 0표기(자리수를 반드시 맞춘다)
$   화폐 표기(달러)
L   지역 화폐 표기(각 국가 코드에 따라 다르다)
,   쉼표 위치 지정
.   마침표 위치 지정
MI  음수의 -기호를 오른쪽에 표기
EEEE 실수 표현법을 이용

변환 함수
TO_CHAR : 날짜나 숫자를 문자로 변환한다
          출력 데이터 형식을 지정한다(출력 포맷)
          TO_CHAR(날짜, 출력형식)
          TO_CHAR(숫자, 출력형식)
TO_DATE : 데이터를 날짜형으로 해석한다
          TO_DATE(문자, 해석형식)
TO_NUMBER : 데이터를 숫자로 해석한다
            대부분의 경우 오라클의 자동 형변환에 의해
            숫자로 읽을 수 있는 문자는 자동 변환됨으로
            잘 사용하지 않는다
            SQL 보다 PL-SQL에서 가끔 사용한다
*/

--1) 현재 날짜를 다양한 형식으로 출력해보자
SELECT TO_CHAR(sysdate, 'YYYY/MM/DD') 날짜,
       TO_CHAR(sysdate, 'YYYY/MM/DD:HH24:MI:SS') 날짜,
       TO_CHAR(sysdate, 'YY/MM/DD:HH:MI:SS AM') 날짜
FROM dual;

SELECT TO_CHAR(sysdate, 'DD MONTH YYYY') 날짜
FROM dual;

SELECT 2+3 FROM dual;

SELECT TO_CHAR(sysdate, 'DAY Mon YY') 날짜
FROM dual;

SELECT TO_CHAR(sysdate, 'DY Mon YY') 날짜   
FROM dual;

-- 오늘은, 월, 일 입니다 가 문자열이고 YYYY MM DD는 서식문자.
SELECT TO_CHAR(sysdate, '"오늘은 "YYYY"년 "MM"월 "DD"일 입니다."')
FROM dual;

--2) 20번 부서 사원의 입사일을 다음의 형식으로 검색한다
'XXX 사원의 입사일은 XXXX년 XX월 XX일입니다.'
SELECT ename||' 사원의 입사일은 '||
      TO_CHAR(hdate, 'YYYY"년 "MM"월 "DD"일 입니다."') 입사일
FROM emp
WHERE dno='20';

--3) 다양한 형식으로 숫자를 출력해 보자
--   9는 숫자의 출력 폭 지정 (포맷)
SELECT TO_CHAR(12345.678, '999,999.99999') 숫자   --소수점 이하 다섯자리로 표현해라.
FROM dual;
      
SELECT TO_CHAR(12345.678, '099,999.999') 숫자     --앞에 0을 표시하라.
FROM dual;   

SELECT TO_CHAR (12345.678, '9,9999.9') 숫자
FROM dual;

--소수점 앞자리가 부족하면 출력장애 발생된다. 5자리인데 형식에 4자리밖에 없음.
SELECT TO_CHAR (12345.678, '9,999.9') 숫자   
FROM dual;

SELECT TO_CHAR (1234, '$999,999') 숫자   
FROM dual;

SELECT TO_CHAR (1234, 'L999,999') 숫자  
FROM dual;

SELECT TO_CHAR (-1234, '999,999MI') 숫자   
FROM dual;

--네자리만 표기하고 나머지는 EEEE표기법 표시해라. => +8이면 오른쪽으로 8칸. 음수면 왼쪽(앞)으로 8칸.
SELECT TO_CHAR (123456789, '9.999EEEE') 숫자   
FROM dual;

--4) 20번 부서의 사원의 보너스가 급여의 몇 퍼센트인지 검색한다
--급여는 월간급여이고 보너스는 연간 보너스이다
--보너스가 null 인 경우는 0으로 환원해서 검색한다
SELECT eno, ename,
      TO_CHAR(NVL(comm,0)/(sal*12)*100,'99')||'%' 급여비율   --99:두자리로 표현
FROM emp
WHERE dno='20';


SELECT eno, ename,
       ROUND(NVL(comm,0)/(sal*12)*100)||'%' 급여비율   --
FROM emp
WHERE dno='20';

            
--5) 1992년 이전에 입사한 사원의 정보를 검색하자
SELECT eno, ename, hdate
FROM emp
WHERE hdate < TO_DATE('1992/01/1','YYYY/MM/DD');

--세션의 출력 형식이 'DD-MON-YY'등과 같이 달라진다면    
--WHERE hdate < '1992/01/01' 방식의 비교는
--정상적인 실행을 보장할 수 없다
--이럴 때는 세션의 출력형식을 변경하던지 아니면
--비교 문자열을 출력형식에 맞추던지 해야 한다.
--이것은 날짜의 표기 방식이 OS나 오라클의 지원 언어 등에
--따라 달라져서 발생하는 문제이다.
--SQL 문장을 작성하는 개발자가 세션의 날짜 형식과 무관한
--SQL 문장을 작성하고 싶다면 반드시 TO_DATE함수를 사용해서
--비교해야 한다. 
--TO_DATE 로 작성된 SQL 문은 어떤 오라클 환경에서도 동일한
--사용이 보장된다.
--이것을 '이식성이 좋다' 라고 표현한다

--TO_DATE 함수를 사용하면 date라는 자료형으로 만들어주기 때문에 비교가 가능하다