Oracle SQL - DB (함수 FUNCTION)

안녕하세요.

#yeoneeblog 여니입니다 :)

 

 

/*
    <함수 FUNCTION>
    자바로 따지면 메소드와 같은 존재
    매개변수로 전달된 값들을 읽어서 계산한 결과를 반환 => 호출해서 쓸 것
    
    - 단일행 함수 : n개의 값을 읽어서 n개의 결과를 리턴 (매 행마다 함수 실행 후 결과 반환)
    - 그룹 함수  : n개의 값을 읽어서 1개의 결과를 리턴 (하나의 그룹별로 함수 실행후 결과를 반환)
    
    단일행 함수와 그룹함수는 함께 사용할수 없음 : 결과 행의 갯수가 다르기 때문
*/
    
    -----------------------------<단일행 함수>--------------------------------
/*
        <문자열과 관련된 함수>
        LENGTH / LENGTHB
        
        - LENGTH(문자열) : 전달된 문자열의 글자 수 반환
        - LENGTHB(문자열) : 전달된 문자열의 바이트 수 반환
        
        결과값은 숫자로 반환 => NUMBER
        문자열 : 문자열 형식의 리터럴, 문자열정보가 저장된 칼럼
        
        한글은 글자당 3BYTE OR 2BYTE
        영문, 숫자, 특수문자 : 글자당 1BYTE 취급됨.
*/
    
SELECT LENGTH('오라클!'), LENGTHB('오라클!')
FROM DUAL;
-- DUAL : 가상테이블(DUMMY TABLE) : 산술연산이나 가상칼럼등 값을 한번만 테스트용도로 출력하고 싶을때 사용하는 테이블

SELECT '오라클', 1,2,3, SYSDATE
FROM DUAL;

SELECT EMAIL, LENGTH(EMAIL), LENGTHB(EMAIL), EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME)
FROM EMPLOYEE;

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

SELECT INSTR('AABAACAABBAA','B')
FROM DUAL; -- 3

SELECT INSTR('AABAACAABBAA','B', 1)
FROM DUAL; -- 3

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

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

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

-- EMAIL에서 @의 위치찾기
SELECT 
    INSTR(EMAIL, '@') AS "@의 위치"
FROM EMPLOYEE;

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

SELECT SUBSTR('SHOWMETHEMONEY',7)
FROM DUAL;

SELECT SUBSTR('SHOWMETHEMONEY',5,2)
FROM DUAL;

SELECT SUBSTR('SHOWMETHEMONEY',1,6)
FROM DUAL;

SELECT SUBSTR('SHOWMETHEMONEY', -8)
FROM DUAL;

SELECT SUBSTR('SHOWMETHEMONEY',-8, 3)
FROM DUAL;

-- 주민등록번호에서 성별부분을 추출해서 남자(1), 여자(2)를 체크
SELECT EMP_NAME, SUBSTR(EMP_NO,8,1) AS 성별
FROM EMPLOYEE;

-- 이메일에서 ID부분만 추출해서 조회(INSTR + SUBSTR)
SELECT EMP_NAME, EMAIL, SUBSTR(EMAIL, 1, INSTR(EMAIL,'@') -1 ) AS ID
FROM EMPLOYEE;

-- 남자인 사원들만 조회
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) IN ('1','3');

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

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

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

SELECT RPAD(EMAIL, 20, '#')
FROM EMPLOYEE;

-- 주민등록번호 조회 : 621235 - 1985634 = > 651235-1******
-- EMP_NAME, 주민등록번호조회
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO,1,8),14,'*') AS 주민번호
FROM EMPLOYEE;

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

SELECT LTRIM('00012304568100', '0')
FROM DUAL;

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

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

SELECT TRIM('   K   H   ')
FROM DUAL;

SELECT TRIM('Z' FROM 'ZZZKHZZZZ')
FROM DUAL;

SELECT TRIM(LEADING'Z' FROM 'ZZZKHZZZZ')
FROM DUAL; -- == LTRIM과 비슷

SELECT TRIM(TRAILING 'Z' FROM 'ZZZKHZZZZ')
FROM DUAL; -- == RTRIM과 비슷한 녀석

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

SELECT LOWER('WELCOME'), UPPER('welcome'), INITCAP('welcome to C class')
FROM DUAL;

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

SELECT CONCAT('김소연','KSY')
FROM DUAL;

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

SELECT '김소연' || 'KSY' || 'YEON' -- 연결연산자 의외로 많이 사용됨.
FROM DUAL;


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

SELECT REPLACE('서울시 강남구 역삼동','역삼동','삼성동')
FROM DUAL;

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

SELECT ABS(-50)
FROM DUAL;

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

SELECT MOD(10,3)
FROM DUAL;

SELECT MOD(-10,3)
FROM DUAL;

SELECT MOD(10.9,3)
FROM DUAL;

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

SELECT ROUND(123.456)
FROM DUAL;

SELECT ROUND(123.456,1)
FROM DUAL;

SELECT ROUND(123.456,2)
FROM DUAL;

SELECT ROUND(123.456,-1)
FROM DUAL; -- 음수로도 가능(소수점 거꾸로)

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

SELECT CEIL(123.111111111)
FROM DUAL;

SELECT CEIL(123.999)
FROM DUAL;

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

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

SELECT TRUNC(123.786)
FROM DUAL;

SELECT TRUNC(123.786,1)
FROM DUAL;

SELECT TRUNC(123.786,2)
FROM DUAL;

SELECT TRUNC(123.786,-1)
FROM DUAL; -- 음수값도 지정가능

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

-- 1. MONTHS_BETWEEN(DATE1,DATE2) : 두 날짜 사이의 개월수 반환(반환값은 NUMBER)
-- DATE2가 더 미래일경우 음수가 나옴.
-- 각 직원별 근무일수, 근무 개월수
SELECT EMP_NAME
      ,FLOOR(SYSDATE - HIRE_DATE) || '일' 근무일수
      ,FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월' 근무개월수
FROM EMPLOYEE;

SELECT EMP_NAME
      ,FLOOR(SYSDATE - HIRE_DATE) || '일' 근무일수
      ,FLOOR(ABS(MONTHS_BETWEEN(SYSDATE, HIRE_DATE))) || '개월' 근무개월수
FROM EMPLOYEE;

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

-- 오늘 날짜로부터 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, 요일(문자/숫자)) : 특정 날짜에서 가장 가까운 요일을 찾아서 그 날짜를 반환
SELECT NEXT_DAY(SYSDATE, '토요일')
FROM DUAL;

SELECT NEXT_DAY(SYSDATE, '토')
FROM DUAL;

SELECT NEXT_DAY(SYSDATE, 'SAT')
FROM DUAL; -- 영어셋팅시 정상작동

-- 1:일요일, 2:월요일, 3:화... 7:토요일
SELECT NEXT_DAY(SYSDATE, '7')
FROM DUAL;

-- DDL(데이터 정의언어) : CREATE, ALTER, DRCP
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;

SELECT NEXT_DAY(SYSDATE, 'SUN')
FROM DUAL;

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

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

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

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

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

-- 숫자를 문자열로
SELECT TO_CHAR(1234)
FROM DUAL; -- 1234 => '1234'

SELECT TO_CHAR(1234,'00000')
FROM DUAL; -- 1234 => '01234' : 빈공간을 0으로 채워줌

SELECT TO_CHAR(1234,'99999')
FROM DUAL; -- 1234 => ' 1234' : 빈칸을 ' '으로 채워줌

SELECT TO_CHAR(1234,'L00000')
FROM DUAL; -- L : LOCAL => 현재 설정된 나라의 화폐 단위

SELECT TO_CHAR(1234,'L99,999')
FROM DUAL;
--------------------------------------------------------------------------------
SELECT TO_CHAR(SYSDATE)
FROM DUAL; -- '23/05/30' 날짜로서 의미 말고 그냥 문자로서 의미

-- '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, 'MON DY, YYYY')
FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'MON DAY, YYYY')
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'),
       TO_CHAR(SYSDATE, 'DDD')
FROM DUAL;
-- D : 1주일 기준으로 일요일부터 며칠째인지 알려주는 포맷
-- DD : 1달 기준으로 1일부터 며칠째인지 알려주는 포맷
-- DDD : 1년 기준으로 1월 1일부터 며칠째인지 알려주는 포맷

-- 요일로써 쓸수있는 포맷
SELECT TO_CHAR(SYSDATE,'DY'),
       TO_CHAR(SYSDATE,'DAY')
FROM DUAL; -- '요일' 이라는 단위가 있냐 없냐의 차이

-- EMPLOYEE에서 2010년 이후에 입사한 사원들의 사원명, 입사일(XXXX년 XX월 XX일(수))을 조회
SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일" (DY)')
FROM EMPLOYEE
WHERE EXTRACT(YEAR FROM HIRE_DATE) >= 2010;
-- WHERE HIRE_DATE >= '10/01/01';
/*
    NUMBER/CHARACTER => DATE
    - TO_DATE(NUMBER/CHARATER , 포맷) : 숫자형, 문자열 데이터를 날짜형 데이터로 변환
*/

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년도 확인)