Oracle SQL 요약정리 - DB (함수 FUNCTION)

안녕하세요.

#yeoneeblog 여니입니다 :)

 

---

<함수 FUNCTION>
    자바로 따지면 메소드와 같은 존재
    매개변수로 전달된 값들을 읽어서 계산한 결과를 반환 => 호출해서 쓸 것
    
    - 단일행 함수 : n개의 값을 읽어서 n개의 결과를 리턴 (매 행마다 함수 실행 후 결과 반환)
    - 그룹 함수  : n개의 값을 읽어서 1개의 결과를 리턴 (하나의 그룹별로 함수 실행후 결과를 반환)
    
    단일행 함수와 그룹함수는 함께 사용할수 없음 : 결과 행의 갯수가 다르기 때문

 

 <문자열과 관련된 함수>
        LENGTH / LENGTHB
        
        - LENGTH(문자열) : 전달된 문자열의 글자 수 반환
        - LENGTHB(문자열) : 전달된 문자열의 바이트 수 반환
        
        결과값은 숫자로 반환 => NUMBER
        문자열 : 문자열 형식의 리터럴, 문자열정보가 저장된 칼럼
        
        한글은 글자당 3BYTE OR 2BYTE
        영문, 숫자, 특수문자 : 글자당 1BYTE 취급됨.

 

- DUAL : 가상테이블(DUMMY TABLE) : 산술연산이나 가상칼럼등 값을 한번만 테스트용도로 출력하고 싶을때 사용하는 테이블

---

* INSTR
    - INSTR(문자열, 특정문자, 찾을위치의 시작값, 순번) : 문자열로부터 특정문자의 위치값 반환
    
    찾을 위치의 시작값, 순번은 생략 가능. 결과값은 NUMBER타입으로 반환
    찾을 위치의 시작값(1/-1)
    1 : 앞에서부터 찾겠다(생략시 기본값)
    -1 : 뒤에서부터 찾겠다.

ex) SELECT INSTR('AABAACAABBAA','B') -- 3

      SELECT INSTR('AABAACAABBAA','B', 1) -- 3

      SELECT INSTR('AABAACAABBAA','B', -1) -- 10 (뒤에서 세어서 처음 나온 B, 숫자는 왼쪽에서부터 셈)

      SELECT INSTR('AABAACAABBAA','B', -1, 2) -- 9(두번째 B를 찾는다)

      SELECT INSTR('AABAACAABBAA','B', -1, 0) -- 범위를 벗어난 순번을 제시했을경우 오류발생
      -- 인덱스처럼 글자의 위치를 찾는 것은 맞지만 자바처럼 0부터 시작이 아니라 1부터 시작한다.

---

* SUBSTR
    문자열로부터 특정 문자열을 추출하는 함수
    
    - SUBSTR(문자열, 처음위치, 추출할 문자갯수)
    
    결과값은 CHARACTER타입으로 반환(문자열 형태)
    추출할 문자 갯수는 생략 가능(생략시 문자열 끝까지 추출하겠다라는 의미)
    처음 위치는 음수로 제시가능 : 뒤에서부터 N번째 위치로부터 문자를 추출하겠다 라는 뜻.

ex) SELECT SUBSTR('SHOWMETHEMONEY',7) -- THEMONEY

      SELECT SUBSTR('SHOWMETHEMONEY',5,2) -- ME

      SELECT SUBSTR('SHOWMETHEMONEY', -8) -- THEMONEY

      SELECT SUBSTR('SHOWMETHEMONEY',-8, 3) --THE

---

* LPAD / RPAD 
    
    - LPAD / RPAD (문자열, 최종적으로 반환할 문자의 길이, 덧붙이고자하는문자)
    : 제시한 문자열에 덧붙이고자하는 문자를 왼쪽에, 오른쪽에 덧붙여서 최종적으로 N길이만큼의 문자열을 반환

    결과값은 CHARACTER타입으로 반환
    덧붙이고자하는 문자 생략가능(기본값 '')

 

ex) SELECT LPAD(EMAIL, 16), EMAIL --(찐EMAIL과 비교)
--SELECT LPAD(EMAIL, 16)
FROM EMPLOYEE;

---

* LTRIM / RTRIM
    - LTRIM / RTRLIM(문자열, 제거시키고자하는 문자)
    : 문자열의 왼쪽, 오른쪽에서 제거시키고자하는 문자들을 찾아서 제거한 나머지 문자열을 반환
    
    결과값은 CHARACTER 형태로 나옴
    제거시키고자하는 문자 생략 가능 => ' '이 제거됨.

 

ex) SELECT LTRIM('131322KH123', '123K')
FROM DUAL;
-- 제거시키고자하는 문자열을 통으로 지워주는게 아니라
-- 문자 하나하나가 다 존재하면 지워주는 원리

 

* TRIM
    - TRIM(BOTH/LEADING/TRAILING '제거하고자하는 문자' FROM '문자열')
    : 문자열의 양쪽/앞쪽/뒤쪽에 있는 특정문자를 제거한 나머지 문자열을 반환
    
    결과값은 문자열타입으로 반환
    옵션은 생략가능, 기본값은 BOTH

 

---

* LOWER / UPPER / INITCAP
    
    -LOWER(문자열)
    : 소문자로 변경
    
    -UPPER(문자열)
    : 대문자로 변경
    
    -INITCAP(문자열)
    : 각 단어의 첫번째 글자만 대문자로 변경

---

* CONCAT
    
    - CONCAT(문자열1,문자열2)
    : 전달된 문자열 두개를 하나의 문자열로 합쳐서 반환

 

ex) SELECT CONCAT(CONCAT('김소연','KSY'),'YEON')
FROM DUAL; -- 김소연KSYYEON

 

ex) SELECT '김소연' || 'KSY' || 'YEON' -- 연결연산자 의외로 많이 사용됨. (★여러 문자를 여러개 덧붙이고자 할 때 사용)
FROM DUAL;

---

* REPLACE
    
    - REPLACE(문자열, 찾을문자, 바꿀문자)
    : 문자열로부터 찾을문자를 찾아서 바꿀문자로 바꾼 문자열을 반환

---

<숫자와 관련된 함수>
* ABS
    
    - ABS(절대값을 구할 숫자) : 절대값을 구해주는  함수.
    
    결과값은 NUMBER형태로 반환

---

* MOD
    
    - MOD(숫자, 나눌값) : % 두 수를 나눈 나머지 값을 반환해주는 함수

* 음수, 소수점 나누는 값 반환도 가능

---

* ROUND
    
    - ROUND(반올림하고자하는 수, 반올림할 위치) : 반올림처리해줌
    
    반올림할 위치 : 소숫점 기준으로 아래 N번째 수에서 반올림 하겠다.
    생략가능(위치 생략시 기본값 0, 소숫점 첫번째 자리에서 반올림을 하겠다)

 

ex) SELECT ROUND(123.456,-1)
FROM DUAL; -- 음수로도 가능(소수점 기준 .의 정수부분; 거꾸로)

---

★ 가장 많이 사용됨 ★
    
* CEIL
    
    - CEIL(올림처리할 숫자) : 소숫점아래의 수를 무조건 올림처리해주는 함수
    
* FLOOR
    
    - FLOOR(버림처리하고자하는 숫자) : 소숫점 아래의 수를 무조건 버림처리해주는 함수

 

ex) -- 각 직원별 근무일수 구하기 (오늘날짜 - 고용일 ==> 소숫점)
SELECT EMP_NAME, CONCAT(FLOOR(SYSDATE-HIRE_DATE),'일') AS 근무일수
FROM EMPLOYEE;

---

* TRUNC
    - TRUNC(버림처리할숫자, 위치) : 위치지정이 가능한 버림처리 함수
    
    위치 생략시 기본값은 0 == FLOOR함수와 동일

* 음수값도 지정가능

---

<날짜 관련 함수>
    
    DATE 타입 : 년도, 월, 일, 시, 분, 초를 다 포함한 자료형

 

-- 1. MONTHS_BETWEEN(DATE1,DATE2) : 두 날짜 사이의 개월수 반환(반환값은 NUMBER)
-- DATE2가 더 미래일경우 음수가 나옴.

 

-- 2. ADD_MONTHS(DATE, NUMBER) : 특정 날짜에 해당 숫자만큼 개월수를 더한 날짜반환(결과값은 DATE타입)
--★ 비밀번호 변경날짜이동 및 디데이 설정시 짱 많이 사용됨 ★

ex) -- 오늘 날짜로부터 5개월 이후
SELECT ADD_MONTHS(SYSDATE,5)
FROM DUAL;

-- 전체 사원들의 1년 근속일(==입사일 기준 1주년)
SELECT EMP_NAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE,12)
FROM EMPLOYEE;

 

-- 3. NEXT_DAY(DATE, 요일(문자/숫자)) : 특정 날짜에서 가장 가까운 요일을 찾아서 그 날짜를 반환

 

-- 4. LAST_DAY(DATE) : 해당 특정날짜 달의 마지막 날짜를 구해서 반환(DATE자료형)

 

-- 5. EXTRACT : 년도, 월, 일 등의 정보를 추출해서 반환(NUMBER타입)

---

-- 1:일요일, 2:월요일, 3:화... 7:토요일

ex) SELECT NEXT_DAY(SYSDATE, 7)
FROM DUAL;

 

-- DDL(데이터 정의언어) : CREATE, ALTER, DRCP

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;

 

-- 한국어로 언어를 변경
ALTER SESSION SET NLS_LANGUAGE = KOREAN;

---

   - EXTRACT(YEAR FROM 날짜) : 특정 날짜로부터 YEAR(년도)만 추출
    - EXTRACT(MONTH FROM 날짜) : 특정 날짜로부터 MONTH(월)만 추출
    - EXTRACT(DAY FROM 날짜) : 특정 날짜로부터 DAY(일)만 추출

---

<형변환 함수>
    NUMBER/DATE => CHARACTER
    
    - TO_CHAR(NUMBER/DATE, 포맷)
    : 숫자형 또는 날짜형 데이터를 문자열 타입으로 반환(포맷에 맞춰서)

 

 

---

ex)-- '2023-05-30'
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') --★데이터 타입의 형태로 변환하는데 날짜형태로 집어 넣고 출력★
FROM DUAL;

-- 시 분 초 : 오전(AM)/오후(PM)
SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS')
FROM DUAL;

-- 시분초 : 24시간 형태
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS')
FROM DUAL;

 

-- 년도로 쓸 수 있는 포맷 ★주민번호 몇년생인지 파악시 사용★
SELECT TO_CHAR(SYSDATE, 'YYYY'),
       TO_CHAR(SYSDATE, 'RRRR'),
       TO_CHAR(SYSDATE, 'YY'),
       TO_CHAR(SYSDATE, 'RR'),
       TO_CHAR(SYSDATE, 'YEAR')
FROM DUAL;
-- YY와 RR의 차이점
-- R이 뜻하는 단어 : ROUND(반올림)
-- YY : 앞자리에 무조건 20이 붙음 => (20)21
-- RR : 50년 기준으로 작으면 20, 크면 19가 붙음. => 1989

-- 월로써 쓸수있는 포맷
SELECT TO_CHAR(SYSDATE, 'MM')
      ,TO_CHAR(SYSDATE, 'MON')
      ,TO_CHAR(SYSDATE, 'MONTH')
      ,TO_CHAR(SYSDATE, 'RM') -- RM : 로마숫자표현
FROM DUAL;

-- 일로써 쓸수있는 포맷
SELECT TO_CHAR(SYSDATE, 'D'), -- 일요일 기준 일주일의 '3'일째 날이다(화요일기준)
       TO_CHAR(SYSDATE, 'DD'), -- 일요일 기준 한달의 'n'일째 날이다.
       TO_CHAR(SYSDATE, 'DDD') -- 1년 기준 'n'번째 날이다.
FROM DUAL;
-- D : 1주일 기준으로 일요일부터 며칠째인지 알려주는 포맷
-- DD : 1달 기준으로 1일부터 며칠째인지 알려주는 포맷
-- DDD : 1년 기준으로 1월 1일부터 며칠째인지 알려주는 포맷

---

 * NUMBER/CHARACTER => DATE
    - TO_DATE(NUMBER/CHARATER , 포맷) : 숫자형, 문자열 데이터를 날짜형 데이터로 변환

 

ex) SELECT TO_DATE(20210101)
FROM DUAL; --기본포맷은 YY/MM/DD로 변환이 된다

SELECT TO_DATE('20210101')
FROM DUAL; 

SELECT TO_DATE(000101)
FROM DUAL; -- 000101 == 101 : 0 으로 시작하는 숫자로 인식하여 에러발생

SELECT TO_DATE('000101')
FROM DUAL; -- 0으로 시작하는 년도는 반드시 홀따옴표로 묶어서 관리해야한다.

SELECT TO_DATE('20100101','YYYYMMDD')
FROM DUAL;

SELECT TO_DATE('140330', 'YYMMDD')
FROM DUAL; --2014년

SELECT TO_DATE('880218', 'RRMMDD')
FROM DUAL; 
-- 두자리 년도에 대해 RR포맷을 적용시켰을경우 => 50년 이상이면 19, 50 미만이면 20이 붙는다
--(표기에는 88년도지만 더블클릭->지도 확인시 1988년도 확인)