SQL

[SQL] 함수

라텐느 2024. 8. 30. 03:52
반응형

 

 

 

 

SELECT 함수
FROM DUAL;

 

SQL의 함수 : 데이터베이스에서 데이터를 처리하고 조작하는 데 사용되는 내장 또는 사용자 정의 함수.

SQL 함수는 특정 작업을 수행하고 결과를 반환하는 독립적인 블록으로, 주로 다음과 같은 종류로 나눌 수 있다.

이때, 한 개 결과가 출력되는 가상테이블 필요하다 =>(DUAL)

(DUAL 은 출력을 위한 한칸의 공간

 

숫자함수

① ABS(n)

📍ABS 함수는 매개변수로 숫자를 받아 그 절대값을 반환하는 함수
 ABS(10) = ABS(-10) = ABS(-10.123)=>10

② CEIL(n)과 FLOOR(n)

📍CEIL 함수는 매개변수 n 이상이면서 가장 작은 정수를 반환

CEIL(10.123) => 11
CEIL(-10.123) => -10


📍FLOOR 함수는 CEIL 함수와는 반대로 매개변수 n이하이면서 가장 큰 정수를 반환
FLOOR(10.123)=>10 

③ ROUND(n, i)와 TRUNC(n1, n2)

📍ROUND 함수는 매개변수 n을 소수점 기준 (i+1)번 째에서 반올림한 결과를 반환한다. i는 생략할 수 있고 디폴트 값은 0, 즉 소수점 첫 번째 자리에서 반올림이 일어나 정수 부분의 일의 자리에 결과가 반영
📍TRUNC 함수는 반올림을 하지 않고 n1을 소수점 기준 n2자리에서 무조건 잘라낸 결과를 반환한다. n2 역시 생략할 수 있으며 디폴트 값은 0이고, 양수일 때는 소수점 기준으로 오른쪽, 음수일 때는 소수점 기준 왼쪽 자리에서 잘라낸다.

TRUNC((SYSDATE-HIRE_DATE)/365.2422) 근무연수, => 11
TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)/12) 근무연수 => 11
💡이심율때문에 공전주기가 정확히는 365일이아니라 365.2422일이다.(실생활에서는 윤년으로 갈음하고 있다)

④ POWER(n1, n2)와 SQRT(n)

📍POWER 함수는 n1를 n2 제곱한 결과를 반환한다. n2은 정수와 실수 모두 올 수 있는데, n1가 음수일 때 n2은 정수만 올 수 있다.
POWER(3,2)=>9
POWER(3,3.0001)=>27.0029664
SELECT POWER(-3, 3.0001) =>SQL. 오류: ORA-01428: '-3' 인수가 범위를 벗어났습니다.
📍SQRT 함수는 n의 제곱근을 반환한다.
SQRT(2)=>1.41421356

⑤ MOD(n2, n1)와 REMAINDER(n2, n1)

📍MOD 함수는 n2를 n1으로 나눈 나머지 값을 반환한다.
MOD(19,4) =>3
📍REMAINDER 함수 역시 n2를 n1으로 나눈 나머지 값을 반환나머지를 구하는 내부적 연산 방법이 MOD 함수와는 약간 다르다.
• MOD → n2 - n1 * FLOOR (n2/n1)
• REMAINDER → n2 - n1 * ROUND (n2/n1)
REMAINDER(19,4)=>-1

⑥ EXP(n), LN(n) 그리고 LOG(n2, n1)

📍EXP는 지수 함수로 e(e=2.71828183…)의 n제곱 값을 반환한다,.
EXP(2)=>7.3890561 
📍LN 함수는 자연 로그 함수로 밑수가 e인 로그 함수다
LN(2.713)=>0.998055034
📍 반면 LOG는 n2를 밑수로 하는 n1의 로그 값을 반환한다.
LOG(10,100)=>2

삼각함수도 있긴 하나 여기선 생략함.
==============================================

문자함수

① INITCAP(char), LOWER(char), UPPER(char)

📍INITCAP 함수는 매개변수로 들어오는 char의 첫 문자는 대문자로, 나머지는 소문자로 반환하는 함수다.즉 공백이나 알파벳이 아닌 문자를 만난 후 다음 첫 알파벳 문자를 대문자로 변환한다.
INITCAP('never say goodbye')=> Never Say Goodbye
INITCAP('never6say*good가bye')=>Never6say*Good가Bye
                            
📍LOWER 함수는 매개변수로 들어오는 문자를 모두 소문자로, UPPDER 함수는 대문자로 변환해 반환한다.
LOWER('NEVERSAYGOODBYE')=>never say goodbye  
UPPER('NEVERSAYGOODBYE')=>NEVER SAY GOODBYE

② CONCAT(char1, char2), SUBSTR(char, pos, len), SUBSTRB(char, pos, len)

📍CONCAT 함수는 ‘||’ 연산자처럼 매개변수로 들어오는 두 문자를 붙여 반환한다.
CONCAT('IHAVE','ADREAM')=>I Have A Dream     


📍SUBSTR는 문자 함수 중 가장 많이 사용되는 함수로, 잘라올 대사 문자열인 char의 pos번째 문자부터 len길이만큼 잘라낸 결과를 반환하는 함수다. pos 값으로 0이 오면 디폴트 값인 1, 즉 첫 번째 문자를 가리키며, 음수가 오면 char 문자열 맨 끝에서 시작한 상대적 위치를 의미한다. 또한 len 값이 생략되면 pos번째 문자부터 나머지 모든 문자를 반환한다.
 SUBSTR('ABCDEFG',1,4)=>ABCD    
SUBSTR('ABCDEFG',-1,4)=>G


📍SUBSTRB는 문자 개수가 아닌 문자열의 바이트(BYTE) 수만큼 잘라낸 결과를 반환한다
SUBSTRB('ABCDEFG',1,4)=>ABCD                        

③ LTRIM(char, set), RTRIM(char, set)

📍LTRIM 함수는 매개변수로 들어온 char 문자열에서 set으로 지정된 문자열을 왼쪽 끝에서 제거한 후 나머지 문자열을 반환한다. 
LTRIM('ABCDEFGABC','ABC')=>DEFGABC    
 📍RTRIM 함수는 LTRIM 함수와 반대로 오른쪽 끝에서 제거한 뒤 나머지 문자열을 반환한다.
 RTRIM('가나다라','라')=>가나다
📍다음과 같이 set 문자로 명시한 문자가 맨 왼쪽이나 맨 오른쪽에 없을 때, 즉 문자열 중간에 있다면 문자열 전체를 반환한다.
LTRIM('가나다라','나')=>가나다라

④ LPAD(char, length, pad), RPAD(char, length, pad)

  • char:패딩할 원본 문자열
  • length:결과 문자열의 최종길이
  • pad:문자열을 채우는 데 사용할 문자

📍 LPAD 함수는 SQL에서 문자열을 왼쪽에서 패딩(padding)한다.  이 함수는 주어진 문자열을 원하는 길이가 될 때까지 특정 문자로 왼쪽을 채워준다.

LPAD('123', 5, '0') => 00123

LPAD('12345', 5, '0') => 12345

📍 RPAD 함수는 SQL에서 문자열을 오른쪽에서 패딩(padding)한다.  이 함수는 주어진 문자열을 원하는 길이가 될 때까지 특정 문자로 오른쪽을 채워준다.

phone_num = 111-2222

RPAD(phone_num, 12, '(02)') => 111-2222(02)

⑤ REPLACE(char, search_str, replace_str), TRANSLATE(expr, FROM_str, to_str)

📍REPLACE 함수는 char 문자열에서 search_str 문자열을 찾아 이를 replace_str 문자열로 대체한 결과를 반환하는 함수다.
REPLACE('나는 너를 모르는데 너는 나를 알겠는가?', '나', '너')=>너는 너를 모르는데 너는 너를 알겠는가?

⑥LENGTH(chr), LENGTHB(chr)

📍LENGTH 함수는 매개변수로 들어온 문자열의 개수를 반환한다.
LENGTH('대한민국')=>4 
📍LENGTHB 함수는 해당 문자열의 바이트 수를 반환한다.
LENGTHB('대한민국')=>8
한글 한 글자가 2바이트를 차지하므로, LENGTHB는 8을 반환했음을 확인할 수 있다.

날짜 함수

기준시간  GMT   : 그리니치 천문대 시간 한국시간 +09:00 (과거)
                 UTC   : univertial time  한국시간 +09:00 (현재)

날짜 형식 변경 명령어    
ALTER SESSION SET NLS_DATE_FORMAT=>'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT=>'YYYY-MM-DD HH24:MI:SS DAY';
(DAY:목요일, DY:목, HH24: 24시 기준, MI:분, MS:초)

① SYSDATE, SYSTIMESTAMP

📍SYSDATE는 현재 컴퓨터의 시간대를 출력한다.

SYSDATE             = >2024-08-29 16:25:17
📍 SYSTIMESTAMP는 현재 컴퓨터의 시간을 TIMESTAMP형식으로 출력한다. 

SYSTIMESTAMP = >24/08/29 16:25:17.530000000 +09:00

② ADD_MONTHS (date, integer)

📍ADD_MONTHS 함수는 매개변수로 들어온 날짜에 interger 만큼의 월을 더한 날짜를 반환한다.
 ADD_MONTHS(SYSDATE,1) => 2024-09-29 16:28:50

③ MONTHS_BETWEEN(date1, date2)

📍MONTHS_BETWEEN 함수는 두 날짜 사이의 개월 수를 반환하는데, 보통 date2가 date1보다 빠른 날짜가 오는데 반대면 음수값이 출력된다.
MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(SYSDATE, 1)) mon1 => -1

④ LAST_DAY(date)

📍LAST_DAY는 date 날짜를 기준으로 해당 월의 마지막 일자를 반환한다.
LAST_DAY('2000-02-13')=> 2000-02-29 00:00:00

⑤ ROUND(date, format), TRUNC(date, format)

📍ROUND와 TRUNC는 숫자 함수이면서 날짜 함수로도 쓰이는데, ROUND는 format에 따라 반올림한 날짜를, TRUNC는 잘라낸 날짜를 반환한다.
ROUND(SYSDATE,'MONTH')=> 2015-04-01 00:00:00 
TRUNC(SYSDATE,'MONTH') =>  2015-03-01 00:00:00

⑥ NEXT_DAY (date, char)

📍NEXT_DAY는 date를 char에 명시한 날짜로 다음 주 주중 일자를 반환한다.
NEXT_DAY(SYSDATE,'금요일') =>  2024-08-30 16:33:11
*char로 올 수 있는 값은 일반적으로 ‘일요일’에서 ‘토요일’까지이다

변환함수

① TO_CHAR (숫자 혹은 날짜, format)

📍숫자나 날짜를 문자로 변환해 주는 함수가 바로 TO_CHAR로, 매개변수로는 숫자나 날짜가 올 수 있고 반환 결과를 특정 형식에 맞게 출력할 수 있다.
 (L : 해당국가의 화폐단위 : ￦123,456,789)

  • TO_CHAR(123456789, 'L0,000,999,999') =>  ￦0,123,456,789
  • SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') =>  2024-08-29
  • SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY'), TO_CHAR(SYSDATE, 'YYYY-MM-DD DY')
    =>  2024-08-29 목요일 2024-08-29 목
  • SELECT TO_CHAR(SYSDATE, 'YYYY"年 "MM"月 "DD"日" HH"時 "MI"分 "SS"秒" PM DY"曜日"') 날짜 
    =>  2024年 08月 29日 04時 38分 26秒 오후 목曜日

② TO_NUMBER(expr, format)

📍 문자나 다른 유형의 숫자를 NUMBER 형으로 변환하는 함수다.
TO_NUMBER('123456') =>  123456

왼쪾은 문자열, 오른쪽은 숫자

 

③ TO_DATE(char, format), TO_TIMESTAMP(char, format)

📍 문자를 날짜형으로 변환하는 함수다.

TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') =>  2024-08-30 03:06:44
📍 TO_TIMESTAMP는 TIMESTAMP 형으로 변환해 값을 반환한다.

TO_TIMESTAMP(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') =>  24/08/30 03:07:28.000000000

💡날짜끼리 계산할때 용이하다.

'2024-12-25' -SYSDATE=>ORA-00932: 일관성 없는 데이터 유형: CHAR이(가) 필요하지만 DATE임

                                                              '2024-12-25'은 CHAR 데이터고 SYSDATE는 DATE 데이터이다.

TO_DATE('2024-12-25') -SYSDATE=>116.863668981481481481481481481481481481

TO_DATE('2024-12-25') - SYSDATE=>116

NULL관련 함수

① NVL(expr1, expr2), NVL2((expr1, expr2, expr3)

📍 NVL 함수는 expr1이 NULL일 때 expr2를 반환한다.

      SELECT EMPLOYEE_ID, NVL(MANAGER_ID,EMPLOYEE_ID)
      FROM EMPLOYEES
      WHERE MANAGER_ID IS NULL --WHERE(조건연산자) MANAGER_ID가 NULL인 EMPLOYEE일 경우
      ;

결과

📍 NVL2는 NVL을 확장한 함수로 expr1이 NULL이 아니면 expr2를, NULL이면 expr3를 반환하는 함수다.

 SELECT employee_id,
           NVL2(commission_pct,
           salary + (salary * commission_pct), -- IS NOT NULL
           salary)                             -- IS NULL 
           AS salary2
      FROM employees;

결과

 

②NULLIF (expr1, expr2)

📍 NULLIF 함수는 expr1과 expr2을 비교해 같으면 NULL을, 같지 않으면 expr1을 반환한다.

기타함수

DECODE (expr, search1, result1, search2, result2, …, default)

DECODE는 expr과 search1을 비교해 두 값이 같으면 result1을, 같지 않으면 다시 search2와 비교해 값이 같으면 result2를 반환하고, 이런 식으로 계속 비교한 뒤 최종적으로 같은 값이 없으면 default 값을 반환한다.

    SELECT   TO_CHAR(SYSDATE, 'PM'),
             DECODE (TO_CHAR(SYSDATE, 'PM'), '오전', '午前',  
                                                     '午後') AS 한자
     FROM    DUAL;

결과

반응형