DAY06 데이터베이스 함수

DATABASE FUNTION

형변환함수

1. 문자에서 숫자로 변환하기 
	TO_NUMBER(문자)      
2. 숫자 -> 문자로 변환하기 
   TO_CHAR(숫자,[형식])
  1) 형식
     999999 -> '  1234'
     000000 -> '001324'
     9,999  -> '1,234'
 3. 날짜 -> 문자로 변환하기 
   TO_CHAR(날짜, [형식])
   1) 날짜/시간 형식
     YY/YYYY : 년도 2자리 / 4자리 
     MM : 월 2자리
     DD : 일 2자리 
     AM : 오전 / 오후 
     HH/HH24 : 12시각 / 24시간
     MI : 분
     SS : 초 
 4. 문자 -> 날짜로 변환하기
 	TO_DATE(문자,[형식])

null 함수

 1. NVL(표현식, 결과가 NULL인 경우에 사용할 값)
 2. NVL2(표현식, 결과가 NULL이 아닌 경우에 사용할 값, 결과가 NULL인 경우에 사용할 값)

통계함수

1. SUM(표현식)   	: 합계
2. AVG(표현식)   	: 평균
3. MAX(표현식)   	: 최댓값
4. MIN(표현식)       : 최솟값
5. COUNT(표현식) 	: 갯수

수학함수

1. ABS(숫자) : 절대값 			 		    (ex) -5의 절대값 5
2. SQRT(숫자) : 제곱근(루트) 	    			(ex) 루트 25
3. SIGN(숫자) : 부호 판별  					(ex) 양수는 1
4. POWER(숫자1, 숫자2) : 제곱	   				(ex) 2(숫자1)의 10(숫자2)제곱 1024
5. MOD(숫자1, 숫자2) : 나머지	  			    (ex) 5(숫자1)를 3(숫자2)으로 나눈 나머지 2
6. CEIL(실수) : 정수로 올림 	  			    (ex) 1 (1.1보다 큰 정수)
7. FLOOR(실수) : 정수로 내림  				(ex) 1  (1.9보다 작은 정수)
8. ROUND(123.456, 1) : 원하는 자릿수로 반올림  (ex) 소수 1자리로 반올림
   ROUND(123.456, -1)                       (ex) 120    (일의 자리에서 반올림)
9. TRUNC(123.456) : 정수로 절사  			    (ex) 123         
   TRUNC(123.456, 1)             			(ex) 123.4  (소수 1자리로 절사)
   TRUNC(123.456, -1)          				(ex) 120    (일의 자리에서 절사)원하는 자릿수로 절사

날짜/시간 함수

1. 현재 날짜 및 시간

오라클이 설치된 서버 기준 시간
SYSDATE   : DATE 형식
SYSTIMESTAMP  : TIMESTAMP 형식
  
새션타임존 기준 시간
SESSIONTIMEZONE  : 해외에 오라클 서버를 두었다면 외국으로 나옴
CURRENT_DATE
CURRENT_TIMESTAMP
  
2. 날짜 원하는 형식으로 조회하기 //TO_DATE -는 원하는 형식으로 날짜를 불러오는 것 
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD AM HH24:MI:SS.FF3')  밀리초(천분의 1초)포함

3. DATE 형식의 날짜 연산
   1) 1일을 숫자 1로 처리한다. 
   2) 1 = 1일, 1/24=1시간, 1/24/60 =1분, 1/24/60/60=1초
  TO_CHAR(SYSDATE + 1, 'YYYY-MM-DD AM HH:MI:SS')  --1일 후
  TO_CHAR(SYSDATE +1/24, 'YYYY-MM-DD AM HH:MI:SS')  --1시간 후
  TO_CHAR(SYSDATE + 1/24/60, 'YYYY-MM-DD AM HH:MI:SS')  --1분 후
  TO_CHAR(SYSDATE + 1/24/60/60, 'YYYY-MM-DD AM HH:MI:SS') --1초 후 
    
 4. TIMESTAMP 형식의 날짜 연산
    1) INTERVAL 키워드를 이용한다. 
    2) YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 단위를 사용한다. 
 SYSTIMESTAMP + INTERVAL '1' DAY  --1일 후
 SYSTIMESTAMP + INTERVAL '1' SECOND --1초 후 
  
SELECT SYSTIMESTAMP - TO_TIMESTAMP('23/07/01', 'YY/MM/DD') --경과한 기간이 TIMESTAMP 형식으로 반환, DAY 만 추출하려면 별도의 함수가 필요하다. 
     , EXTRACT(DAY FROM SYSTIMESTAMP - TO_TIMESTAMP('23/07/01', 'YY/MM/DD')) --경과한 기간에서 일수를 추출
  FROM DUAL;
  
 5. 필요한 단위 추출하기 
SELECT EXTRACT(YEAR FROM SYSDATE)  -- 년
     , EXTRACT(MONTH FROM SYSDATE)  -- 월
     , EXTRACT(DAY FROM SYSDATE)   -- 일
     , EXTRACT(HOUR FROM SYSTIMESTAMP)   -- 시, UCT(표준시) 기준
     , EXTRACT(HOUR FROM SYSTIMESTAMP) + 9  -- 시, Asia.Seoul 기준
     , EXTRACT(MINUTE FROM SYSTIMESTAMP) -- 분
     , EXTRACT(SECOND FROM SYSTIMESTAMP) -- 초
     , TRUNC(EXTRACT(SECOND FROM SYSTIMESTAMP))
     , TO_CHAR(SYSDATE, 'YYYY')  -- TO_CHAR 함수를 단위 추출용도로 사용
  FROM DUAL; 
  
 6. 요일을 기준으로 특정날짜 구하기.
SELECT NEXT_DAY(SYSDATE, '수')  --요일은 한글로 적을 수 있음. '다음수요일' 
     , NEXT_DAY(SYSDATE-8, '수') ---지난 수요일, 안전하게 -8일로 하자  (-7이 아님에 주의 )
  FROM DUAL;

 7. N 개월 전후 날짜 구하기
SELECT ADD_MONTHS(SYSDATE,1)  --1개월 후 
     , ADD_MONTHS(SYSDATE, -1) --1개월 전
     , ADD_MONTHS(SYSDATE, 5 * 12) --5년 후 
  FROM DUAL;

8. 경과한 개월 수 구하기 
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('23/01/01', 'YY/MM/DD'))
     , TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('23/01/01', 'YY/MM/DD')))
  FROM DUAL;

문자함수

1. 대소문자 변환하기
  UPPER(EMAIL)    : 대문자     	   ex) EMAIL
  LOWER(EMAIL)    : 소문자 		   ex) email		
  INITCAP(EMAIL)  : 첫 글자만 대문자   ex) Email
   
2. 글자 수
  LENGTH(TOM) : 글자수 반환 		    ex) 3

3. 바이트 수
  LENGTHB(FIRST_NAME)
  

 4. 연결하기
   1) || 연산자 (오라클 전용이므로 다른 DB에서는 오류가 난다.)
    2) CONCAT 함수
       CONCAT(A, B) : 인수를 2개만 전달할 수 있다.
       CONCAT(CONCAT(A, B), C) : 인수 3개 이상은 CONCAT 함수 여러 개로 해결한다.
SELECT *
  FROM EMPLOYEES
 WHERE PHONE_NUMBER LIKE CONCAT('515', '%');

SELECT *
  FROM EMPLOYEES
 WHERE EMAIL LIKE CONCAT(CONCAT('%', 'A'), '%');  -- A를 포함('%' || 'A' || '%')

5. 일부만 반환하기
SELECT SUBSTR(PHONE_NUMBER, 1, 3)  -- 전화번호 1번째 글자부터 3글자를 반환
     , SUBSTR(PHONE_NUMBER, 5)     -- 전화번호 5번째 글자부터 끝까지 반환
  FROM EMPLOYEES;

6. 특정 문자의 위치 반환하기
   문자의 위치는 1부터 시작한다.
   못 찾으면 0을 반환한다.
SELECT EMAIL
     , INSTR(EMAIL, 'A')
  FROM EMPLOYEES;

 7. 바꾸기
SELECT EMAIL
     , REPLACE(EMAIL, 'A', '$')  -- 모든 A를 찾아서 $로 바꾸기
  FROM EMPLOYEES;

 8. 채우기
    1) LPAD(표현식, 전체폭, 채울문자)
    2) RPAD(표현식, 전체폭, 채울문자)
SELECT DEPARTMENT_ID
     , LPAD(DEPARTMENT_ID, 3, 0)
     , EMAIL
     , RPAD(SUBSTR(EMAIL, 1, 2), 5, '*')
  FROM EMPLOYEES;

 9. 공백 제거
SELECT '[' || LTRIM('     HELLO     WORLD     ') || ']'   왼쪽 공백 제거
     , '[' || RTRIM('     HELLO     WORLD     ') || ']'   오른쪽 공백 제거
     , '[' ||  TRIM('     HELLO     WORLD     ') || ']'   왼쪽/오른쪽 공백 제거
  FROM DUAL;

기타함수

 1. 순위 구하기
 RANK() OVER(ORDER BY 칼럼 ASC)  : 낮은 값이 1등
 RANK() OVER(ORDER BY 칼럼 DESC) : 높은 값이 1등

 2. 행 번호 구하기
SELECT EMPLOYEE_ID
     , SALARY
     , ROW_NUMBER() OVER(ORDER BY SALARY DESC)  -- 연봉 내림차순 정렬 후 번호 매기기(동점자 처리 방식 없음)
  FROM EMPLOYEES;


 3. 암호화 함수
SELECT STANDARD_HASH('1111', 'SHA1')   -- 암호화 알고리즘 SHA1
     , STANDARD_HASH('1111', 'SHA256') -- 암호화 알고리즘 SHA256
     , STANDARD_HASH('1111', 'SHA384') -- 암호화 알고리즘 SHA384
     , STANDARD_HASH('1111', 'SHA512') -- 암호화 알고리즘 SHA512
     , STANDARD_HASH('1111', 'MD5')    -- 암호화 알고리즘 MD5
  FROM DUAL;


 4. 분기 처리 함수
SELECT EMPLOYEE_ID
     , DEPARTMENT_ID
     , DECODE(DEPARTMENT_ID
        , 10, 'Administration'
        , 20, 'Marketing'
        , 30, 'Purchasing'
        , 40, 'Human Resources'
        , 50, 'Shipping'
        , 60, 'IT') AS DEPARTMENT_NAME
  FROM EMPLOYEES;


 5. 분기 처리 표현식
SELECT EMPLOYEE_ID
     , DEPARTMENT_ID
     , CASE
         WHEN DEPARTMENT_ID = 10 THEN 'Administration'
         WHEN DEPARTMENT_ID = 20 THEN 'Marketing'
         WHEN DEPARTMENT_ID = 30 THEN 'Purchasing'
         WHEN DEPARTMENT_ID = 40 THEN 'Human Resources'
         WHEN DEPARTMENT_ID = 50 THEN 'Shipping'
         WHEN DEPARTMENT_ID = 60 THEN 'IT'
         ELSE 'Unknown'
       END AS DEPARTMENT_NAME
  FROM EMPLOYEES;