DB/SQL

SQL :: 내장함수(STORED FUNCTION)

관리자ID 2024. 4. 25. 14:33

 

함수(Function)
 - 데이터베이스에서 입력값을 처리하여 결과값을 반환하는 프로그램으로, 크게 밴더에서 제공하는 내장 함수(Built-in Function)와 사용자가 작성하는 사용자 정의 함수(User Defined Function)로 나뉜다.

 

- 단일 행 함수(Single Row Function): 문자형, 숫자형, 날짜형, 변환형

- 다중 행 함수(Multi Row Function): 그룹 함수

 

 

내장 함수(STORED FUNCTION)

 

1) 문자형 함수

- UPPER(''): 대문자 변환

- LOWER(''): 소문자 변환

- INITCAP(''): 첫 글자만 대문자로 변환

- CONCAT('', ''): 두 문자값 결합

- SUBSTR('',a,b): 문자 추출 // a -> 시작인덱스 / b -> 추출 개수

- LENGTH(''): 문자열 길이 반환

- LPAD('a',b,'c'), RPAD('a',b,'c'): 'a'를 b크기의 공간에 입력하고 좌우측 공간을 'c'로 채움

- LTRIM, RTRIM('a', 'b'): 'a'에서 'b'를 만날 때 까지 좌우측에서부터 삭제

- TRANSLATE('a','b','c'): 'a' 문자열 중에서 'b'를 'c'로 대체   // 'b'와 'c'의 1:1 변환

- REPLACE('a','b','c'): 'a' 문자열 중에서 'b'를 'c'로 대체 // 'b'에 정확히 일치하지 않으면 변환X

* ('ABCDEFGHI','BDF','123') 수행 시 replace는 변환X, translate는 'A1C2E3GHI' 반환

 

--컬럼 내의 값을 소문자로 변환해서 검색하기 때문에 대소문자 구별 없이 조회 가능
SELECT *
	FROM PLAYER p
	WHERE LOWER(E_PLAYER_NAME) = 'leomar leiria';
    
    
--길이 함수 LENGTH, 문자자르기 SUBSTR
SELECT PLAYER_NAME, LENGTH(PLAYER_NAME), LENGTH(SUBSTR(PLAYER_NAME,4)),
	SUBSTR(PLAYER_NAME,5),SUBSTR(PLAYER_NAME,-1,1)
	FROM PLAYER p;
    
 --INSTR 문자열의 시작점을 찾아서 INDEX를 반환해준다
-- 없으면 0반환, 있으면 1부터 연산하에 INDEX를 반환

SELECT 'HAPPY NEW YEAR',
	SUBSTR('HAPPY NEW YEAR', INSTR('HAPPY NEW YEAR', 'NEW'), LENGTH ('NEW'))
	FROM DUAL;

--종합예시<NAVER 글자 찾기. 고유값 @를 잡아서 잘라야한다 NAVER라는 아이디가 있을 수 있기 때문
SELECT 'GODDEE@NAVER.COM',
	SUBSTR('GODDEE@NAVER.COM', INSTR('GODDEE@NAVER.COM', '@')+1, LENGTH('NAVER'))
	FROM DUAL;


--1(DEFAULT:순차검색), 2(뒤에서부터 검색), 그 이외의 숫자는 위치 INDEX 건너뛰고 검색
--('CABC','C',3) 3번 인덱스 이후부터 C가 처음 나오는 인덱스를 찾는다
SELECT 'OOP AND FOO',INSTR('OOP AND FOO', 'OO', 3)
	FROM DUAL;


--화이트스페이스 제거 (아이디 생성 등에 필수적 사용)
--TRIM, LTRIM, RTRIM
--TRIM : 화이트스페이스 제거
--LTRIM : 왼쪽부터 검색. 해당 값을 보유한 경우 지워준다. 보유하지 않은 값을 만나면 멈춘다.
--RTRIM : 오른쪽부터 검색. 해당 값을 보유한 경우 지워준다. 보유하지 않은 값을 만나면 멈춘다.

SELECT '      xxxxXXXXXyyyzzzz     ' AS TEST1,
	TRIM('      xxxxXXXXXyyyzzzz     ') TEST2,
	LTRIM('xxxxXXXXXyyyzzzzZ','Xzy') TEST3,
	RTRIM('xxxxXXXXXyyyzzzzZ','Zzy')
	FROM DUAL;


--LPAD, RPAD //문서번호 생성하기
SELECT EMPNO, DEPTNO, RPAD(EMPNO, 6,'-'),
	EMPNO || LPAD('1','6','-'),
	TO_CHAR(SYSDATE, 'YYYY') || LPAD(INFO.SEQ.NEXTVAL,'3','0')
	FROM EMP;

 

2) 숫자형 함수

- ROUND(a, b): 반올림 // a를 소수점 b자리수까지 반환 (아래값 반올림)

- TRUNC(a, b): 절사 // a를 소수점 b자리까지 반환 (아래값 버림)

- MOD(a, b): 나머지 반환 // a를 b로 나눈 나머지 반환

- POWER(a, b): 거듭제곱 // a의 b승

- SQRT(a): 제곱근 // 루트a

- SIGN(a): 양수, 음수, 0 판별 // -1, 0, 1만 반환

- CHR(a): ASCII값 해당 문자 반환 // if(a=65) return A;

--CEIL/FLOOR
--CEIL 최근접최대정수
--FLOOR 최근접최소정수
SELECT CEIL (0.1), FLOOR(0.1)
	FROM DUAL;

 

 

 

 

3) 날짜형 함수

- SYSDATE: 현재 시스템의 날짜 및 시간 반환

- LAST_DAY('a'): a에 해당하는 월의 마지막 날짜 반환 // ex) 날짜입력 예시 18/11/27

- MONTHS_BETWEEN('a','b'): a와 b의 차이 일자 반환  

- ADD_MONTHS('a',b): 'a'로부터 b달 후 반환

- ROUND('a', 'b'): 'a'에 가까운 'b' 반환(반올림) // ex) '18/11/27', 'MONTH' -> '18/12/01'

- TRUNC('a', 'b'): 'a'에 해당하는 'b'의 첫 날 반환 // ex) '18/11/27', 'year' -> '18/01/01'

*sysdate 입력시 시간이 삭제되고 날짜까지만 표시

 

    
    
날짜형 함수
-- 날짜 java.util.Date / java.util.Calendar <=> 문자열변경 java.til.SimpleDateFormat("")
-- javascript new Date() -> '2023 1 1'
-- TO_CHAR(), TO_DATE

-- SYS 하드웨어의 메모리 (설치된 곳)
-- CURRENT 는 사용하고 있는 Application의 SESSION 시간
SELECT SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP
	FROM DUAL;

--EXTRACT : 특정한 년월일시분초 숫자로 반환
--TO_CHAR() : 특정한 년월일시분초 문자로 반환

SELECT *
	FROM EMP e;

SELECT HIREDATE, EXTRACT(YEAR FROM HIREDATE),
	TO_CHAR(HIREDATE, 'YYYY.MM.DD HH24 MI SS'),
	TO_CHAR(HIREDATE, 'YYYY.MM.DD HH24:MI:SS'),
	TO_CHAR(HIREDATE, 'MM')+0,
	TO_NUMBER(TO_CHAR(HIREDATE, 'MM'))
	FROM EMP e;

SELECT '01'+1, '01'+'02'
	FROM DUAL;

-- 한달 전, 한달 후 혹은 몇달 후
SELECT SYSDATE, ADD_MONTHS(SYSDATE,-2),
	SYSDATE +7, SYSDATE+1/24 --하루를 24로 나눈것의 1. 즉, 1시간
	FROM DUAL;

--마지막날 구하기
SELECT LAST_DAY(TO_DATE('2024/04', 'YYYY/MM'))
	FROM DUAL;

--첫째날 구하기
--TRUNC를 사용하면 월을 제외하고 삭제되기 때문에 초기값을 집어넣는다
--자동으로 첫째날을 가져온다
SELECT TRUNC(SYSDATE, 'MM') AS "first_day"
	FROM DUAL;
    
    
--변환형 함수
--1)명시적 함수 : 수동
--2)암시적(암묵적) : 자동

--문자형 날짜를 날짜타입으로 변환 : VARCHAR2 -> DATE
-- **** 문자열 날짜 (DATE)로 변경한다면 반드시 구분자를 기준으로 작성해야 한다
-- 단, 문자열이 모두 "년월일시분초"에 해당하는 길이를 가지고 있다면 상관 없다

SELECT TO_DATE('20240425121201', 'YYYY-MM-DD HH24MISS'),
	TO_DATE('20240425121201', 'YYYYMMDD HH24MISS'),
	TO_DATE('2024-1-25-2','YYYY-MM-DD-HH24-MI-SS')
	FROM DUAL;

 

 

4) 변환형 함수

- TO_CHAR(문자값,'형식'): 숫자나 날짜를 문자열로 변환

*0: 빈자리를 0으로 채움 / $: 달러 / L: 지역통화단위 / .: 소수점 표시 / ,: 천단위 표시

ex) (3500000, '$999,999') -> $350,000 / (SYSDATE, 'YYYY/MM/DD') -> 2018/11/27

- TO_NUMBER('a'): 문자를 숫자로 변환 // ex) '1234' -> 1234

- TO_DATE(문자값, '형식'): 문자를 날짜로 변환 

CHAR와 VARCHAR2의 차이점 비교
CREATE TABLE STRING(
	NAMEC CHAR(10),
	NAMEV VARCHAR2(10)
);

INSERT INTO STRING s VALUES('ABC', 'ABC');

SELECT *
	FROM STRING s
	WHERE NAMEC=NAMEV;
	
--CHAR는 고정문자열 값은 VARCHAR와 같은 가변 문자열과 비교가 되지 않는다

SELECT LENGTH(NAMEC) AS C길이,
		LENGTH(NAMEV) AS V길이,
		LENGTHB(NAMEC) AS C바이트길이,
		LENGTHB(NAMEV) AS V바이트길이
	FROM STRING s;
	
SELECT *
	FROM STRING s
	WHERE TRIM(NAMEC)=NAMEV;
--위와 아래는 동일 코드
SELECT *
	FROM STRING s 
	WHERE NAMEC = NAMEV||'       ';
요소 의미 요소 의미
SCC 세기 (S: 기원전) DAY 해당 요일
YEAR 연도 DY 해당 요일 약어
YYYY 네자리 연도 DDD,DD,D 연, 월, 일 중의 날짜를 숫자표시
YY 끝 두 자리 연도 HH, HH24 (1-12), (0-23)
MONTH 월(9월 OR SEPTEMBER) MI 분
MON 월(9월 OR SEP) SS 초
MM 두 자리 월 AM(A.A.)
PM(P.M.)		 오전 OR 오후

 

 

5) 집계함수

- COUNT(a): 행의 수 반환

- AVG(a): 평균

- SUM(a): 합계

- MIN(a): 최소값

- MAX(a): 최대값

- STDDEV(a): 표준 편차

- VARIANCE(a): 분산

*count(*)을 제외한 그룹함수는 null값을 고려하지 않음