[오라클DB] DUAL테이블 및 오라클 함수

DUAL이라는 테이블은 SYS 사용자가 소유하는 오라클 표준 테이블로써, 오직 한 행(Row)에 한 컬럼만 담고 있는 dummy 테이블로써 일시적인 산술연산이나 날짜 연산을 위하여 주로 쓰입니다.

---

1. 숫자 함수

 

1.1. ABS(n) - 절대값을 반환하는 함수

 

 

1.2. COS(n) - COSINE값을 반환하는 함수

 

 

1.3. EXP(n) - 자연대수의 값을 구해서 반환하는 함수

 

 

1.4. FLOOR(n) - n과 같지 않다면 소수점 자리를 버리는 함수

 

 

1.5. LOG(n1, n2), LN(m ,n) → Log값을 구해서 반환하는 함수

 

1.6. POWER(n1, n2) → n1의 값에 대해 n2승(제곱값)을 구하여 반환하는 함수

 

1.7. SIGN( n ) → [양수 / 0 / 음수] 여부를 판단하여 [1 / 0 / -1]을 반환

 

 

1.8. SIN(n) - 삼각함수의 사인 값을 구하는 함수

1번 결과인 0도이기 때문에 0, 2번 결과는 90도는 1, 3번 결과는 180도는 다시 수평이기때문에 0

 

1.9. TAN(n) 함수는 인수 n의 사인(tangent)값을 반환

 

 

1.10. ROUND 함수는 소수점 반올림을 하는 기능을 수행

 

1~5까지는 반올림을 하지않고, 6부터 하는 것을 알 수 있다.

 

1.11. TRUNC 함수는 소수점 자리를 잘라주는 기능

 

 

1.12. MOD(n1, n2) → n1을 n2로 나눈 나머지 값을 반환

---

2. 문자처리 함수

 

2.1. LOWER 함수는 입력된 문자 값을 소문자로 변환

 

 

2.2. UPPER 함수는 입력된 문자 값을 대문자로 변환하는 함수

 

(' ') 가로안 ' 에 주의하자!

 

2.3. INITCAP 함수는 맨 앞 이니셜만 대문자로 변환

 

 

2.4. CONCAT 함수는 첫번째 문자 값을 두번째 문자 값과 연결하는 함수

 

여기서 as문은 생략하고 사용하였습니다.

 

2.5. SUBSTR 함수는 문자의 일부분을 호출

 

주의할 점 ! 두번째 인자 값은 몇번 째 칸부터 자를 것인가에 대한 것이고, 세번째 인자 값은 2번째 인자값에서 설정한 위치에서 해당칸을 포함하여 x칸을 자르겠다라는 의미입니다.

 

2.6. SUBSTRB 함수는 바이트 단위로 문자열을 자를때 사용한다. 한글 같은 경우 문자단위로 자를때 깨지는 경우가 있다. 이럴때는 바이트 단위로 자르는 깨지는걸 방지할 수 있다. 오라클 세팅에 따라서 한글이 3byte 또는 2btye 일수 있으니 아래의 문자셋 쿼리로 오라클 세팅을 확인 후 사용하자.

 

 

2.7. LENGTH 함수는 인자로 주어진 문자열의 길이를 구하는 함수

 

 

2.8. LENGTHB는 문자열의 바이트 길이를 리턴하는 함수

 

 

2.9. INSTR(문자열, 검색할 문자, 시작지점, n번째 검색단어)함수는 찾는 문자의 위치를 반환하는것입니다.

- 찾는 문자가 없으면 0을 반환합니다.

- 찾는 단어의 앞글자의 인덱스를 반환합니다.

- 기본으로 왼쪽부터 시작하여 우측방향으로 스캔합니다.

- 시작지점에 음수를 쓸경우 우측에서 시작하기때문에 스캔방향이 좌측방향입니다.

 

 

2.10. INSTRB

 

instr와의 차이점은 한글을 1바이트로 볼 것인가 2바이트로 볼 것인가에 대한 차이입니다. instrb는 한글을 2byte로 인식합니다.

 

INSTRB(문자열, 찾는 문자, 찾기 시작 위치, 찾은 위치의 순번)

>문자열 : 찾는 문자열

>찾는 문자 : 문자열에서 찾을 문자

>찾기 시작 위치 : 문자열의 몇번째 위치에서 찾을지 설정 ( 예 : 1 > 1번째 위치에서 찾기 / -1 > 맨 뒤에서부터 찾기)

>찾은 위치의 순번 : 찾은 순서 중 몇번째 순번에 해당하는 위치를 리턴할지 설정

 

2.11. LPAD, RPAD - 숫자형 문자의 경우 문자 길이를 똑같이 맞춰야 하는 경우가 있다.

LPAD는 왼쪽, RPAD는 오른쪽부터 총길이 만큼 지정한 문자를 채운다.

 

---

2.13. TRIM 함수는 문자열의 양쪽 공백을 제거하는 기본적인 함수이다. 

LTRIM 함수, RTRIM 함수는 왼쪽과 오른쪽의 공백을 제거할 때 사용가능 하지만, 반복적인 문자나 특정 문자를 제거할 때 자주 사용한다.

 

 

2.14. CONVERT 함수는 문자세트를 다른 문자세트로 문자열을 변환한다. 반환되는 데이터형은 VARCHAR2이다.

 

 

2.15. CHR함수는 함수 인자에 대응하는 문자를 나타낸다.

CHR(13)은 캐리지 리턴이라고 하며, 현재 커서가 위치한 줄의 맨 앞으로 보낸다.

CHR(10)은 라인 피드라고 하며, 현재 커서가 위치한 줄에서 한 칸 아래로 이동한다.

출처: https://shxrecord.tistory.com/31 [첫 발]

---

 

2.16. ASCii 함수는 해당 인자 값을 chr 형식으로 가져오는 함수입니다.

 

 

2.17. REPLACE 오라클에서 특정 문자열을 치환하거나 제거하기 위해서는 replace() 함수를 사용하면 된다.

 

 

---

3. 날짜 관련 함수

3.1. SYSDATE

 - 현재 날짜와 시간을 시스템 기준으로 얻어온다. (최소단위 1초)

 - 얻어온 숫자에 연산이 가능하다.

 - TO_CHAR : 숫자나 날짜를 문자형식으로 변환 해주는 것

 - TO_DATE : 숫자나 문자형식을 날짜형식으로 변환 해주는것

 - 날짜를 넣어줄때는 to date 형식으로 하고 그 날짜를 조회할때는 to char 형식으로 출력

 

 

3.2. MONTHS_BETWEEN

 

 

3.3. ADD_MONTHS 오라클에서 날짜의 월을 빼거나 더하기 위해서는 ADD_MONTHS 함수를 사용한다. 날짜의 일은 변경되지 않으며 연산값에 따라서 년도와 월만 변경이 된다.

 

함수를 사용하면서 주의할 점은 날짜의 이전달이나 다음달에 기준날짜의 일자가 존재하지 않으면 해당 월의 마지막 일자가 리턴된다. 예를 들어 "2019-03-31"의 이전달은 "2019-02-28"이 된다.

ADD_MONTHS( '날짜', '숫자' )

 

 

3.4. NEXT_DAY('기준일자', '찾을요일')

 

1	일요일	일	SUNDAY	SUN
2	월요일	월	MONDAY	MON
3	화요일	화	TUESDAY	TUE
4	수요일	수	WEDNESDAY	WED
5	목요일	목	THURSDAY	THUR
6	금요일	금	FRIDAY	FRI
7	토요일	토	SATURDAY	SAT

 

3.5. LAST_DAY 함수는 입력한 날짜의 마지막 날짜를 가져오는 함수입니다.

 

 

3.6. ROUND - 중복으로 인한 생략

3.7. TRUNC - 중복으로 인한 생략

---

4. 형 변환 함수

 

TO_NUMBER 함수는 문자형을 숫자형으로 변환하는 함수입니다.

아래와 같이 변환을 하지 않으면 계산이 되지 않습니다.

 

---

5. NVL : NULL을 다른 값으로 변환하는 NVL 함수

형식 : NVL(인자값, 바꿀값)

 

---