22/04/22 내장함수(Single-Row Functions) -1

Numeric Functions(숫자형 함수)

값을 인수로 받아 NUMBER타입의 값을 반환하는 함수이다

ABS(n)

절대값을 계산하는 함수

---ABS예제
SELECT ABS(-1.234) absolute FROM DUAL;
ABSOLUTE 
---------- 
     1.234

CEIL(n)

올림값을 반환하는 함수

---CEIL 양수 예제
SELECT CEIL(10.1234) "CEIL" FROM DUAL;
CEIL 
---------- 
        11 
---CEIL 음수 예제
SELECT CEIL(-10.1234) "CEIL" FROM DUAL;
CEIL 
---------- 
      -10

FLOOR(n)

버림값을 반환하는 함수

---FLOOR양수 예제
SELECT FLOOR(10.1234) "FLOOR" FROM DUAL;
FLOOR
-----
   10
   
----FLOOR 음수 예제
SELECT FLOOR(-10.1234) "FLOOR" FROM DUAL;
FLOOR
-----
   -11

MOD(m,n)

m을 n으로 나눈 나머지를 반환, n이 0일경우 m을반환

--- MOD예제
SELECT MOD(9,4) "MOD" FROM DUAL;
MOD
----
	1

ROUND(n,[m])

n값의 반올림을 하는 함수로 m은 소숫점아래 자리숫를 의미

SELECT (ROUND(192.153,1) "ROUND" FROM DUAL;
ROUND
------
 192.2
 
 SELECT ROUND(192.153, -1) "ROUND" FROM DUAL;
 ROUND
 ------
 	190

TRUNC(n,m)

n값을 절삭하는 함수로 m은 소숫점아래 자릿수를 나타냄

SELECT TRUNC(7.5592) "TRUNC" FROM DUAL;

TRUNC
-----
  7.55

SELECT TRUNC(789.5597,-2) "TRUNC" FROM DUAL;
TRUNC
-----
700

Character Functions(문자형함수)

CHAR,VARCHAR2 타입을 인수로 받아 VARCHAR2타입의 값을 반환하는 함수

CONCAT(char1, char2)

Concatenation의 약자로 두문자를 결합하는 역할을 하며, "||"연산자와 같은 역할을 함

---CONCAT 예제
SELECT CONCAT('www.','oracleclub')||'.com' name FROM DUAL;

NAME
--------
www.oracleclub.com

INITCAP(char),LOWER(char),UPPER(char)

INITCAP:주어진 문자열의 첫 번째 문자를 대문자로 변환시켜줌
LOWER: 주어진 문자열을 소문자로 변환시킴
UPPER:주어진 문자열을 대문자로 변환시킴

--INITCAP,LOWER,UPPER 예제
SELECT INITCAP('oracleclub') name FROM DUAL
	UNION ALL
SELECT LOWER('OracleCLUB') name FROM DUAL
	UNION ALL
SELECT UPPER('oracleclub') name FROM DUAL;

NAME
-----------
Oracleclub
oracleclub
ORACLECLUB

LPAD(char1,n[,char2]),RPAD(char1,n,[,char2])

LPAD: 왼쪽에 문자열을 끼어 놓는 역할을 함
RPAD: 오른쪽에 문자열을 끼어 놓는 역할을 함
n은 반환되는 문자열의 전체 길이를 나타냄, char1은 문자열이 n보다 클 경우 char1을 n개 문자열만큼 반환

--- LPAD,RPAD 예제
SELECT LPAD('oracleclub',12,'*') name FROM DUAL
	UNION ALL
SELECT RPAD('oracleclub',12,'*') name FROM DUAL;
NAME
-------------
**oracleclub
oracleclub**

SUBSTR(char,m,[n]),SUBSTRB(char,m,[n])

SUBSTR함수는 m번째 자리부터 길이가 n개인 문자열을 반환
m이 음수인 경우 뒤에서 m번째 문자부터 n개의 문자가 반환
SUBSTRB함수에서는 B는 Byte단위로 처리하겠다는의미이다

--세번째 이후 문자열 반환
SELECT SUBSTR('oracleclub',3) name FROM DUAL;

NAME
----------------
acleclub

---세번째 이후 네개의 문자열 반환
SELECT SUBSTR('oracleclub',3,4) name FROM DUAL;

NAME
---------
acle

---뒤에서 세번째 이후 두개의 문자열 반환
SELECT SUBSTR('oracleclub',-3,2) name FROM DUAL;

NAME
---------
lu

--- DB가 UTF-8인경우 SQL결과는?
SEELCT SUBSTRB('오라클클럽',1) name FROM DUAL
 	UNION ALL
SELECT SUBSTRB('오라클클럽',3) name FROM DUAL;

LENGTH(char),LENGTHB(char)

문자열의 길이를 반환

---문자열의 LENGTH를 조회하는 예제
SELECT LENGTH('오라클클럽') len FROM DUAL
	UNION ALL
SELECT LENGTHB('오라클클럽') len FROM DUAL;

--- DB가 UFT-8인경우
LEN
-----------
		5
        15

REPLACE(char1,str1,str2)

REPLACE는 문자열의 특정문자를 다른 문자로 변환함

SELECT REPLACE('oracleclub','oracle','db') name FROM DUAL;

NAME
------------
dbclub

대소문자를 구분한다는 것을 알려주는 예제
SELECT REPLACE('OracleClub','oracle','db') name FROM DUAL
	UNION ALL
SELECT REPLACE('OracleClub','Oracle','DB') name FROM DUAL;

NAME
------
OracleClub
DBClub

INSTR(char1,str1,m,n)

문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환
지정한 문자열이 발견되지 않으면 0이 반환
char1: 지정문자, str1: 검색문자, m: 시작위치, n:검색순위

지정한 문자 OK가 발견되지 않아 0을반환하는 예제
SELECT INSTR('CORPORATE FLOOR','OK') idx FROM DUAL;

IDX
----------
		0
        
OR이 있는 위치 2를 반환하는예제-- 왼쪽부터 비교함을 알 수 있음
SELECT INSTR('CORPORATE FLOOR','OR') idx FROM DUAL;

IDX
-----------
		2
        
왼쪽에서 세번째부터 검색을 해서 검색된 OR의 위치를 반환하는 예제
SELECT INSTR('CORPORATE FLOOR','OR',3) idx FROM DUAL;

IDX
---------
		5
왼쪽에서 세 번째부터 시작을 해서 비교를 하여 OR이 두 번째 검색되는 지점의 위치를 반환 한다

SELECT INSTR('CORPORATE FLOOR','OR',3,2) idx FROM DUAL;

IDX
---------
		14

TRIM(char1[,char2]), LTRIM(char1[,char2]),RTRIM(char1[,char2])

TRIM: 특정한 문자를 제거함, 제거할 문자를 입력하지 않으면 기본적으로 공백이 제거
LTRIM: 왼쪽부터 문자를 제거, 제거할 문자 입력 안할 시 기본적으로 왼쪽 공백이 제거
RTRIM: 오른쪽부터 문자를 제거, 제거할 문자입력안할시 기본적으로 오른쪽 공백이 제거

o와 공백을 제거하는 TRIM 예제
SELECT TRIM('o' FROM 'oracleclub') name FROM DUAL
	UNION ALL
SELECT TRIM(' oracleclub ') name FROM DUAL;

NAME
----------
racleclub
oracleclub

왼쪽 문자열을 TRIM하는 예제
공백의 경우 왼쪽 공백만 제거되는것을 확인할 수 있다.
SELECT LTRIM('oracleclub','oracle') name FROM DUAL
	UNION ALL
SELECT REPLACE(LTRIM(' oracleclub '),' ' '*') name FROM DUAL;

NAME
----------
	 club
oracleclub*

오른쪽 문자열을 TRIM 하는예제
공백의 경우 오른쪽 공백만 제거되는 것을 확인가능
SELECT RTRIM('oracleclub','club') name FROM DUAL;
	UNION ALL
SELECT REPLACE(RTRIM(' oracleclub '),' ','*') name FROM DUAL;

NAME
--------
oracle
*oracleclub

Datetime Functions(날짜 함수)

날짜 함수는 오라클 날짜에 대해 연산을 함
날짜 함수는 NUMBER형 값 또는 DATE형의 값을 RETURN함

SYSDATE

SYSDATE함수를 사용하면 현재 일자와 시간(시스템기준)을 얻을 수 있다.(최소 단위 1초)

--SYSDATE 예제
SELECT TO_CHAR(SYSDATE,'RRRR-MM-DD HH24:MI:SS') "지금시간" 
  FROM DUAL ; 
SELECT TO_CHAR(SYSDATE-1,'RRRR-MM-DD HH24:MI:SS') "하루전지금시간" 
  FROM DUAL ; 
SELECT TO_CHAR(SYSDATE-1/24,'RRRR-MM-DD HH24:MI:SS') "1시간전시간" 
  FROM DUAL ; 
SELECT TO_CHAR(SYSDATE-1/24/60,'RRRR-MM-DD HH24:MI:SS') "1분전시간" 
  FROM DUAL ; 
SELECT TO_CHAR(SYSDATE-1/24/60/10,'RRRR-MM-DD HH24:MI:SS') "6초전시간" 
  FROM DUAL ; 
SELECT TO_CHAR(SYSDATE-(5/24 + 30/24/60 + 10/24/60/60),'RRRR-MM-DD HH24:MI:SS') "5시간 30분 10초전" 
  FROM DUAL ;

SYSTIMESTAMP

SYSTIMESTAMP 함수를 사용하면 현재 일자와 시간(시스템기준)을 얻을 수 있다.
최소단위 =10억분의 1초(10의 9승)

-- SYSTIMESTAMP 예제 
SELECT TO_CHAR(SYSTIMESTAMP,'RRRR-MM-DD HH24:MI:SS.FF3')   
  FROM DUAL ; 
SELECT TO_CHAR(SYSTIMESTAMP,'RRRR-MM-DD HH24:MI:SS.FF9')   
  FROM DUAL ; 
SELECT TO_CHAR(SYSTIMESTAMP -1/24,'RRRR-MM-DD HH24:MI:SS') "1시간전시간" 
  FROM DUAL ; 
SELECT TO_CHAR(SYSTIMESTAMP -1/24/60,'RRRR-MM-DD HH24:MI:SS') "1분전시간" 
  FROM DUAL ;

ADD_MONTHS(a,b)

a의 날짜에 b의 달을 더한 값을 반환

ADD_MONTHS 예제
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,3),'RRRR-MM-DD')	FROM DUAL;

SELECT TO_CHAR(ADD_MONTHS(SYSTIMESTAMP,3),'RRRR-MM-DD')  FROM DUAL;

MONTHS_BETWEEN(a1,a2)

a1과 a2사이의 달의 수를 NUMBER형 타입으로 반환

-- 두 일자 사이에 달의 수를 조회하는 예제
SELECT MONTHS_BETWEEN(TO_DATE('2010-06-05','RRRR-MM-DD'),
					  TO_DATE('2010-05-01','RRRR-MM-DD')) FROM DUAL;
                      
                      
 --두 일자 사이에 일자 수를 조회하는 예제
SELECT TO_DATE('2010-06-05','RRRR-MM-DD') -  
       TO_DATE('2010-05-01','RRRR-MM-DD')  "Day"   
  FROM DUAL;

LAST_DAY(d)

LAST_DAY함수는 달의 마지막 날의 날짜를 반환

SELECT SYSDATE today, LAST_DAY(SYSDATE) lastday FROM DUAL;

TODAY					LASTDAY
----------				----------
2022-05-02 13:46:59		2022-05-31 13:46:59

NEXT_DAY(d,c1)

돌아오는 명시된 요일의 일자를 반환 요일(c1에 적는듯)은 1(일)~7(토)로 입력

--NEXT DAY 예제
SELECT NEXT_DAY(SYSDATE,4) FROM DUAL;

값
---------------
2022-05-04 13:48:41

SELECT NEXT_DAY(TO_DATE('20220502','RRRRMMDD'),2) FROM DUAL;

값
---------------
2022-05-09 00:00:00

ROUND(d[,F])

F에 지정된 단위로 반올림을 한다. F가 연도라면 연도 단위로 반올림함, F가 생략되면 날짜를 가장 가까운 날짜로 반올림

--ROUND 예제
SELECT TO_CHAR(ROUND(TO_DATE('2011-09-11 21:00:01',
						'RRRR-MM-DD HH24:MI:SS'),'YEAR'), 'RRRR-MM-DD HH24:MI:SS')	FROM DUAL;

결과
-----------
2012-01-01 00:00:00

SELECT TO_CHAR(ROUND(TO_DATE('2011-09-11 21:00:01', 
                             'RRRR-MM-DD HH24:MI:SS'), 'MONTH'), 
               'RRRR-MM-DD HH24:MI:SS')   
  FROM DUAL; 
  
결과
-------------
2011-09-01 00:00:00


SELECT TO_CHAR(ROUND(TO_DATE('2011-09-11 21:00:01', 
                             'RRRR-MM-DD HH24:MI:SS'), 'DD'), 
               'RRRR-MM-DD HH24:MI:SS')   
  FROM DUAL; 

결과
-------------
2011-09-12 00:00:00
                    

SELECT TO_CHAR(ROUND(TO_DATE('2011-09-11 21:00:01', 
                             'RRRR-MM-DD HH24:MI:SS')),  
               'RRRR-MM-DD HH24:MI:SS')    
    FROM DUAL;              
결과
----------------
2011-09-12 00:00:00
               

TRUNC(n,m)

TRUNC함수는 n값을 절삭하는 함수로 m은 소숫점 아래 자릿수를 나타냄

---TRUNC 예제
SELECT TRUNC(7.5597,2) "TRUNC" FROM DUAL;

TRUNC
-----------
7.55

SELECT TRUNC(789.5597,-2) "TRUNC"FROM DUAL;
TRUNC
----------
700