SQLD_02

[Sam1000won]

함수

---

1. 내장 함수

• 내장 함수는 데이터베이스 시스템에서 제공하는 함수로, 사용자가 정의한 함수와 구분.
• 내장 함수는 입력 값에 따라 단일 행 함수와 다중 행 함수로 나눌 수 있음.

단일 행 함수

• 단일 행 함수는 입력 값이 단일 행에 해당하며, 출력은 항상 하나의 값.

특징

• SELECT, WHERE, ORDER BY 절에 사용 가능
  *- 각 행들에 대해 개별적으로 작용해 데이터 값을 조작하고, 각각의 행에 대한 조작 결과를 리턴
• 여러 인자를 입력해도 단 하나의 결과만 리턴
• 함수의 인자로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수 도 있다.
• 특별한 경우가 아니면 함수의 인자로 사용하는 함수의 중첩이 가능

문자형 함수

• 문자형 함수는 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값을 리턴

---

• LOWER: 문자열을 소문자로 변환.
• UPPER: 문자열을 대문자로 변환.
• SUBSTR/SUBSTRING: 문자열의 일부를 추출.
• LENGTH/LEN: 문자열의 길이를 반환.
• LTRIM: 문자열의 왼쪽 공백을 제거.
• RTRIM: 문자열의 오른쪽 공백을 제거.
• TRIM: 문자열의 양쪽 공백을 제거.
• ASCII: 문자열의 첫 번째 문자에 대한 ASCII 값을 반환.
• CONCAT: 두 개의 문자열을 연결.

문자열 함수의 사용 예

LOWER('SQL EXPERT')
-- 'sql expert'

UPPER('SQL Expert')
-- 'SQL EXPERT'

ASCII('A')
-- 65

CHR(65) / CHAR(65)
-- 'A'

CONCAT('RDBMS', 'SQL') / 'RDBMS' || 'SQL' / 'RDBMS' + 'SQL'
-- 'RDBMS SQL'

SUBSTR('SQL EXPERT', 5, 3) / SUBSTRING('SQL EXPERT', 5, 3)
-- 'EXP'
-- 인덱스 0부터 아니라 그냥 첫 번째 문자, 두 번째 문자 식

LENGTH('SQL EXPERT') / LEN('SQL EXPERT')
-- 10

LTRIM('xxxYYZZxYZ', 'x') / RTRIM('XXYYzzXYzz', 'z') / TRIM('x' FROM 'xxxYYZZxYZxx')
-- 'YYZZxYZ'
-- 'XXYYZZXY'
-- 'YYZZxYZ'

RTRIM('XXYYZZXYZ       ')
-- 공백 제거 및 CHAR와 VARCHAR 데이터 유형 비교 시 용이하게 사용.
-- 'XXYYZZXYZ'

```SQL
SELECT LENGTH('SQL EXPERT')
FROM DUAL;

LENGTH('SQLEXPERT')
-------------------
                 10

• ORACLE은 SELECT 절과 FROM 절 두개의 절을 SELECT 문장의 필수 절로 지정했으므로, 사용자 테이블이 필요 없는 SQL 문장의 경우에도 필수적으로 DUAL이라는 테이블을 FROM 절에 지정.

DUAL 테이블의 특성

-- 사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블.
-- SELECT ~ FROM ~의 형식을 갖추기 위한 일종의 DUMMY 테이블
-- DUMMY라는 문자열 유형의 컬럼에 ‘X’라는 값이 들어있는 행을 1건 포함

숫자형 함수

• 숫자형 함수는 숫자 데이터를 입력 받아 처리하고 숫자를 리턴
• ABS: 절대값을 반환.
• MOD: 나머지를 반환
• ROUND: 숫자를 지정된 소수점 이하 자리수로 반올림.
• TRUNC: 숫자의 소수점 이하 자리 수를 떨굼
• SIGN: 숫자의 부호를 반환 (양수, 음수, 0).
• CHR/CHAR: ASCII 코드에 해당하는 문자를 반환
• CEIL/CEILING: 주어진 숫자보다 크거나 같은 최소 정수를 반환
• FLOOR: 주어진 숫자보다 작거나 같은 최대 정수를 반환
• EXP: 자연상수의 거듭제곱을 계산
• LOG: 주어진 숫자의 자연 로그를 반환
• LN: 자연 로그를 반환 (LOG와 동일).
• POWER: 주어진 숫자의 거듭제곱을 계산
• SIN, COS, TAN: 삼각 함수 값 (사인, 코사인, 탄젠트)을 반환

숫자형 함수들이 적용되었을 때 리턴 값

ABS(-15)
-- 15

SIGN(-20)/ SIGN(0)/ SIGN(+20)
-- -1, 0, 1

MOD(7,3) / 7%3
-- 1

CEIL(38.123) / CEILING(38.123) / CEILING(-38.123)
-- 39
-- 39
-- -38

FLOOR(38.123) / FLOOR(-38.123)
-- 38
-- -39

ROUND(38.5235, 3) / ROUND(38.5234, 1) / ROUND(38.5235, 0), ROUND(38.5235)
-- 38.524
-- 38.5
-- 39
-- 38.5235

TRUNC(38.5235, 3) / TRUNC(38.5235, 1) / TRUNC(38.5235, 0) / TRUNC(38.5235)
-- 38.523
-- 38.5
-- 38
-- 38 (인수 0 DEFAULT)

EX) 소수점 이하 한 자리까지 반올림 및 내림해 출력

SELECT ENAME, ROUND(SAL/12,1) "월급 반올림", TRUNC(SAL/12,1) "월급 소수점 버림"
FROM EMP;

변환 함수

• 변환형 함수는 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 때 사용하는 함수

• 명시적 데이터 유형 변환
• 데이터 변환형 함수로 데이터 유형을 변환하도록 명시해주는 경우
• 암시적 데이터 유형 변환
  • 데이터베이스가 자동으로 데이터 유형을 변환해서 계산하는 경우

• 암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있음
• 자동으로 데이터베이스가 알아서 계산하지 않은 경우 있어 명시적인 데이터 유형법을 사용하는 것을 바람
• TO_NUMBER: 문자열을 숫자로 변환
• TO_CHAR: 숫자나 날짜를 문자열로 변환
• TO_DATE: 문자열을 날짜 형식으로 변환
• CAST: 데이터 타입을 변환
• CONVERT: 데이터 형식을 변환

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜,
TO_CHAR(SYSDATE, 'YYYY, MON, DAY') 문자형
FROM DUAL;

날짜                 문자형
-------------------- --------------------------------------------------------
2021/03/02           2021, 3월 , 화요일

SELECT TO_CHAR(123456789/1200, '$999,999,999,99') 환율반영달러,
TO_CHAR(123456789, 'L999,999,999') 원화
FROM DUAL;
-- L은 로칼 화폐 단위

환율반영달러                     원화
-------------------------------- --------------------------------------------
       $1,028,81                         ￦123,456,789

SELECT TEAM_ID, TO_NUMBER(ZIP_CODE1, '999') + TO_NUMBER(ZIP_CODE2, '999') 우편번호합
FROM TEAM;

TEAM_I 우편번호합
------ ----------
K05           750
K08           592
K03           840
K07           554

NULL 관련 함수

• NVL/ISNULL: NULL 값을 대체할 값을 반환
• NULLIF: 두 값이 같으면 NULL을 반환하고, 다르면 첫 번째 값을 반환
• COALESCE: 인수 중 NULL이 아닌 첫 번째 값을 반환