오라클 함수

✏️함수의 구분

문자열 내장 함수
숫자 내장 함수
날짜 함수
형식 변환 함수
NULL관련 함수

---

1. 문자열 함수

문자열 추출 함수

• SUBSTR(문자열, 시작위치, 길이)
  SUBSTRB : BYTE단위로 추출

	SELECT SUBSTR('APPLE', 1, 3) FROM DUAL;   > APP
    SELECT SUBSTF('APPLE', 3) FROM DUAL; 	  > PLE
    SELECT SUBSTF**B**('APPLE', 3) FROM DUAL; >	PLE

• Q.모든 학생의 이름과 생년월일의 월 까지만 출력하시오.

      SELECT NAME, SUBSTR(BIRTHDAY,6,2) MONTH FROM STUDENTS;

  Q.회원 중 전화번호가 011으로 시작하는 회원의 모든 정보를 출력하시오.

  ```
    SELECT * FROM MEMBERS WHERE SUBSTR(PHONE,1,3)='011';
    SELECT * FROM MEMBERS WHERE PHONE LIKE'011%';
  ```

  Q.생년월일이 3,4,5 월인 회원의 모든 정보를 출력하시오.

    SELELCT * FROM MEMBERS 
    WHERE SUBSTR(BIRTHDAY,7,1)IN('03','04','05');

  Q.생년월일이 3,4,5 월이고, 전화번호가 NULL인 회원.

  SELECT * FROM MEMBERS 
  WHERE SUBSTR(BIRTHDAY,6,2) IN ('03', '04','05') 
  AND PHONE IS NULL;

문자열 덧셈 함수

• SELECT CONCAT('고','양이') FROM DUAL;
  SELECT '고' ||'양이' FROM DUAL;

문자열 트림 함수(공백 제거)

함수	역할
SELECT LTRIM(' HI ') FROM DUAL;	왼쪽 공백 제거
SELECT RTRIM(' HI ') FROM DUAL;	오른쪽 공백 제거
SELECT TRIM(' HI ') FROM DUAL;	양쪽 공백 제거

문자열 소문자/대문자 변경 함수

함수	역할
SELECT LOWER('ASasaSD') FROM DUAL;	문자열 소문자 변경
SELECT UPPER('ASasaSD') FROM DUAL;	문자열 대문자 변경

Q. 회원 아이디가 'apple100'인 회원 정보 조회(대소문자를 가리지 않고)

	SELECT * FROM MEMBER WHERE UPPER (ID)='APPLE100';

문자열 대치 함수

• REPLACE(문자열, 찾는 문자열, 대치할 문자열)
  TRANSELATE() : 문자별 대치

  SELECT REPLACE('HAPPY','P','Z') FROM DUAL; >HAZZY
  	- P > Z
  SELECT TRANSLATE('CHAR','HA','NOW') FROM DUAL; >CNOR
  	- H > N
      A > O
      W > 대체 대상 없음

• Q.회원의 이름과 전화번호를 빈칸 없이 출력하시오.

  SELECT NAME REPLACE(PHONE,'-','') NUM 
  FROM MEMBERS;

문자열 패딩 함수(너비 고정시 사용)

• LPAD(문자,너비(BYTE),영역을 채울 문자 )
  RPAD( )

  SELECT LPAD('HELLO',5) FROM DUAL; >HELLO
      > 패딩 영역이 없기 때문에 문자가 출력되지 않음
  SELECT LPAD('HELLO',5,'0') FROM DUAL; >HELLO
  SELECT LPAD('HELLO',10,'0') FROM DUAL; >00000HELLO
  SELECT RPAD('HELLO',10,'0') FROM DUAL; >HELLO00000

• 문자가 한글일 경우 ( 바이트 단위 )

  SELECT RPAD('김땡땡',6,'0') FROM DUAL; > 김땡땡 
  SELECT RPAD('김김'6,'0') FROM DUAL; > 김김00

첫 글자를 대문자로 바꾸는 함수

• SELECT INITCAP('문자열') FROM DUAL

  SELECT INITCAP('the') FROM DUAL; > The
  SELECT INITCAP('my name') FROM DUAL; > My Name

문자열 검색 함수

• INSTR(문자열, 검색문자열, 위치, 찾을 수)

  SELECT INSTR('IT IS AN APPLE AND THAT IS A DOG', 'IS') FROM DUAL; 
  > 6
  SELECT INSTR('IT IS AN APPLE AND THAT IS A DOG', 'IS', 8) FROM DUAL; 
  > 8번째 위치부터 찾음, 25
  SELECT INSTR('IT IS AN APPLE AND THAT IS A DOG', 'IS', 1, 2) FROM DUAL; 
  > 1번째 문자부터 찾음, 두번째 인자 반환, 25

• Q. 회원의 전화번호에서 두번째 대시 문자가 존재하는 위치 출력

  	SELECT INSTR(PHONE,'-',1,2) FROM MEMBERS;

• Q. 전화번호의 첫번째 대시와 두번째 대시 간의 간격

  	SELECT INSTR(PHONE,'-',1,2) - INSTR(PHONE,'-') -1 FROM MEMBERS;

• Q 전화번호에서 첫번째와 두번째 사이의 국번 출력

  	SELECT SUBSTR(PHONE, INSTR(PHONE,'-')+1,INSTR(PHONE,'-',1,2)-1) FROM MEMBERS;

문자열 길이 함수

• LENGTH('문자열')

문자 '-'을 없앤 주민번호의 길이를 출력하고 싶을 때

SELECT LENGTH(REPLACE(ID_NUM,'-','')) FROM MEMBERS;

코드 값 반환

• 코드 값을 반환하는 함수: ASCII('A')
  코드 값으로 문자를 반환하는 함수: CHR(65)

---

2. 숫자 함수

절대값을 구하는 함수

• ABS(n)

	SELECT ABS(35), ABS(-35) FROM DUAL; > 35, 35

음수/양수를 알려주는 함수

• SIGN(n)

	SELECT SING(25), SIGN(-25), SIGN(0) FROM DUAL > 1, -1, 0

숫자의 반올림 값을 알려주는 함수

• ROUND(n,i)

SELECT ROUND(123.45678), ROUND(12.5678) FROM DUAL; > 123, 13
SELECT ROUND(123.45678,2), ROUND(12.5678,3) FROM DUAL; > 123.46, 12.568

숫자의 몫과 나머지 값 반환 함수

함수	반환값
SELECT TRUNC(27/5) 몫 FROM DUAL;	5
SELECT MOD(27,5) 나머지 FROM DUAL;	2

숫자의 제곱을 구하는 함수와 제곱근을 구하는 함수

함수	반환값
SELECT POWER(2,4) 제곱 FROM DUAL;	16
SELECT SQRT(36) 제곱근 FROM DUAL;	6

---

3. 날짜 함수

현재 시간을 얻는 함수

함수	역할
SYSDATE	SYS : 오라클 서버가 설치된 곳의 시간(DATE)CURRENT_DATE

SYSTIMESTAMP|SYS : 오라클 서버가 설치된 곳의 시간(TIMESTAMP)
CURRNET_TIMESTAMP|CURRNET : 사용자 세션의 시간대(TIMESTAMP)

세션 시간과 포멧 변경

ALTER SESSION SET TIME_ZONE='09:00' : TIME_ZOME 설정 (한국 기준)
ALTER SESSION SET TIME_ZONE='-08:00' : TIME_ZOME 설정 (LA)

함수	SYSDATE / CURRENT DATE
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';	2023-09-05 15:24:56
TIME_ZONE='-08:00'설정	2023-09-05 15:24:56 / 2023-09-04 22:24:56

NLS : NATIONAL LANGUATE SET
HH24 : 24시간 기준

날짜 추출 함수

EXTRACT(YEAR/MONTH/DAY/HOUR/MINUIT/SECOND)

• Q. 현재 월을 반환

  SELECT EXTRACT(MONTH FROM SYSDATE) || '월' FROM DUAL; > 9월

  Q. 가입일이 5, 7, 9월인 멤버의 이름, 가입일을 반환

  SELECT NAME, REGDATE FROM MEMBERS 
   WHERE EXTRACT(MONTH FROM REGDATE) IN (5, 7, 9);

날짜 누적 함수

SELELCT ADD_MONTHS(SYSDATE, 1) FROM DUAL;
SELELCT ADD_MONTHS(SYSDATE, -1) FROM DUAL;

• Q. 가입한지 6개월이 안된 회원 조회.

  SELELCT * FROM MEMBERS
  WHERE REGDATE) > ADD_MONTHS(SYSDATE, -6);

날짜의 차이를 알아내는 함수

MONTHS_BETWEEN(날짜, 날짜)

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2022-04-12')) FROM DUAL;

• Q. 가입한지 6개월이 안된 회원 조회.

  SELECT NAME, REGDATE FROM MEMBERS
  WHERE MONTHS_BETWEEN(SYSDATE, REGDATE) < 6;

다음 요일을 알려주는 함수

NEXT_DAY(현재날짜, 다음요일)

입력	출력값
SELECT NEXT_DAY(SYSDATE, '월') FROM DUAL;	2023-09-11
SELECT NEXT_DAY(SYSDATE, '월요일') FROM DUAL;	2023-09-11
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL;	2023-09-11

월의 마지막 일자를 알려주는 함수

LAST_DAY(현재날짜)

지정된 범위해서 날짜를 반올림/자르는 함수

ROUND(날짜, 포멧)
TRUNC(날짜, 포멧)

입력	단위
'CC'	세기
'YEAR'	년
'Q'	분기(쿼터)
'MONTH'	월
'W'	주
'DAY'	일
'D'	DATE
'HH'	시간
'MI'	분

---

4. 변환 함수

숫자 <-> 문자열

숫자 -> 문자열 : TO_CHAR()
문자열 -> 숫자 : TO_NUMBER()

포맷문자

포맷문자	설명
9	숫자
0	빈자리를 채우는 문자
$	앞에 $ 표시
,	천 단위 구분자 표시
.	소수점 표시

작성

질의 작성	질의 결과
SELECT TO_CHAR(12345678,'99,999,999') FROM DUAL;	12,345,678
SELECT TO_CHAR(12345678,'999,999') FROM DUAL;	자리가 안맞아서 출력 X
SELECT TO_CHAR(12345678,'9,999,999,999') FROM DUAL;	12,345,678(부족한 만큼 빈 공백이 생김)
SELECT TO_CHAR(12345678,'09,999,999,999') FROM DUAL;	00,012,345,678(공백이 0으로 들어감)

문자열 <-> 날짜

문자열 -> 날짜 : TO_DATE()
TO_TIMESTAMP(문자열)
날짜 -> 문자열 : TO_CHAR()

포맷문자

포맷문자	설명
YYYY/RRRR/YY/YEAR	년도표시 : 4자리/Y2K/2자리/영문
MM/MON/MONTH	월표시 : 2자리/영문3자리/영문전체
DD/DAY/DDTH	일표시 : 2자리/영문/2자리ST
AM/PM	오전/오후표시
HH/HH24	시간표시 : 12시간/24시간
MI	분표시 : 0~59분
SS	초표시 : 0~59초

작성

<날짜 -> 문자>
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD PM HH24:MI') FROM DUAL;

<문자 -> 날짜>
SLELECT TO_DATE('2014-03-22') FROM DUAL;

SELECT TO_DATE('2014-03-22 01:37:12', 'YYYY-MM-DD HH:MI:SS') TIME 
FROM DUAL; // 포멧 설명

---

5. NULL관련 함수

SELECT NULL + 3 FROM DUAL; > NULL

반환 값이 NULL일 경우에 대체 값을 제공하는 함수

NVL(NULL, 대체값)

<NVL 에서 조건을 하나 더 확장한 함수>

NVL2(입력값, NOTNULL 대체값, NULL 대체값)

두 값이 같은 경우 NULL 그렇지 않은 경우 첫번재 값 반환

NULLIF(값1, 값2)

SELECT NULLIF(AGE, 20) FROM MEMBERS;

---

기타 함수

조건에 따른 값 선택하기

DECODE(기준값, 비교값, 출력값, 비교값, 출력값)

입력	출력
SELECT DECODE(AGE, 20, 1, 2) FROM MEMBERS;	20세일 경우 1, 아닐경우 2
SELECT DECODE(GENDER,'남자',1,'여자',2,'기타') FROM MEMBERS;	여자:1/남자:2/나머지:기타