SQL 단일행 함수
IT 위키
더 많은 작업
- 상위 문서: SQL
- Single-Row Function
- 테이블의 각 행마다 하나씩 결과를 돌려주는 SQL 내장 함수
단일행 함수는 입력된 행 하나에 대해 결과 값 하나를 돌려준다. 6개 행에 적용하면 결과도 6개 행이다. 처리하는 데이터 형식에 따라 문자 함수, 숫자 함수, 날짜 함수, 변환 함수, NULL 관련 함수로 나누고, 조건에 따라 값을 고르는 CASE 표현과 DECODE도 함께 다룬다. SELECT 절뿐 아니라 WHERE, ORDER BY, GROUP BY 절에도 쓸 수 있고, 함수 안에 함수를 겹쳐 쓸(중첩) 수 있다.
| 구분 | 단일행 함수 | 다중행 함수 |
|---|---|---|
| 결과 수 | 행마다 1개 | 여러 행(그룹)마다 1개 |
| 예 | UPPER, SUBSTR, ROUND, NVL, TO_CHAR | COUNT, SUM, AVG, MAX, MIN (SQL 그룹 함수) |
| 사용 위치 | SELECT, WHERE, ORDER BY 등 | SELECT, HAVING, ORDER BY (WHERE 불가) |
이 문서의 예제는 B_EMP 테이블을 쓴다. 날짜 함수 예제는 실행 시점에 따라 결과가 달라지지 않도록 SYSDATE 대신 고정된 DATE 리터럴을 쓴다.
| 사원번호 | 이름 | 부서번호 | 관리자번호 | 급여 | 입사일 | 커미션 |
|---|---|---|---|---|---|---|
| 1001 | 김대표 | 10 | NULL | 9000 | 2015-01-05 | NULL |
| 1002 | 이부장 | 20 | 1001 | 6000 | 2017-03-15 | NULL |
| 1003 | 박과장 | 20 | 1002 | 4500 | 2019-07-01 | NULL |
| 1004 | 최대리 | 30 | 1001 | 4000 | 2020-11-20 | 500 |
| 1005 | 정사원 | 30 | 1004 | 3000 | 2022-02-28 | 0 |
| 1006 | 한신입 | NULL | 1002 | 2500 | 2024-08-01 | NULL |
| 함수 | 예 | 결과 | 설명 |
|---|---|---|---|
| LOWER / UPPER | LOWER('SQL Dev') |
sql dev | 소문자 / 대문자로 바꾼다. |
| INITCAP | INITCAP('sql developer') |
Sql Developer | 단어 첫 글자만 대문자로 바꾼다. |
| CONCAT | CONCAT('SQL','D') |
SQLD | 두 문자열을 잇는다. Oracle CONCAT은 인수가 2개뿐이다. |
|| |
'SQL'||'D' |
SQLD | 연결 연산자. 여러 개를 이어 쓸 수 있다. |
| SUBSTR | SUBSTR('DATABASE',3,4) |
TABA | 3번째 글자부터 4글자. 길이를 생략하면 끝까지, 시작 위치가 음수면 뒤에서부터 센다(SUBSTR('DATABASE',-4) = BASE).
|
| LENGTH | LENGTH('데이터') |
3 | 글자 수. 뒤 공백도 센다(LENGTH('SQL ') = 4).
|
| LENGTHB | LENGTHB('데이터') |
9 | 바이트 수. 문자 집합에 따라 다르며, AL32UTF8에서는 한글 1자가 3바이트이다. |
| LTRIM / RTRIM | LTRIM('xxSQLxx','x') |
SQLxx | 왼쪽 / 오른쪽에서 지정 문자를 지운다. 문자를 생략하면 공백을 지운다. |
| TRIM | TRIM('x' FROM 'xxSQLxx') |
SQL | 양쪽(LEADING, TRAILING, BOTH 지정 가능)에서 지정 문자 하나를 지운다. |
| LPAD / RPAD | LPAD('SQL',6,'*') |
***SQL | 전체 길이 6이 되도록 왼쪽 / 오른쪽을 채운다. 원래 문자열보다 짧게 지정하면 잘린다(LPAD('SQLD',2) = SQ).
|
| REPLACE | REPLACE('SQL Server','Server','Developer') |
SQL Developer | 바꿀 문자열을 생략하면 찾은 문자열을 지운다(REPLACE('A-B-C','-') = ABC).
|
| INSTR | INSTR('CORPORATE FLOOR','OR',3,2) |
14 | 3번째 글자부터 찾아 2번째로 나오는 'OR'의 위치. 없으면 0이다. |
- Oracle에서 빈 문자열
은 NULL로 취급한다. 따라서LENGTH()는 0이 아니라 NULL이다. - Oracle의 CONCAT과
||는 NULL을 빈 문자열처럼 다룬다.'A'||NULL의 결과는 'A'이다.
| 기능 | Oracle | SQL Server |
|---|---|---|
| 문자열 연결 | ||, CONCAT(인수 2개) |
+, CONCAT(인수 여러 개)
|
| 부분 문자열 | SUBSTR(문자열, 시작[, 길이]) | SUBSTRING(문자열, 시작, 길이), 길이 생략 불가 |
| 길이 | LENGTH(뒤 공백 포함) | LEN(뒤 공백 제외), 바이트 수는 DATALENGTH |
| 위치 찾기 | INSTR(문자열, 찾을 문자열) | CHARINDEX(찾을 문자열, 문자열), 인수 순서가 반대 |
| 공백 제거 | LTRIM, RTRIM, TRIM | LTRIM, RTRIM, TRIM(2017 이상) |
| 함수 | 예 | 결과 | 설명 |
|---|---|---|---|
| ROUND(n, m) | ROUND(1234.567,1) |
1234.6 | 소수점 아래 m자리로 반올림. m을 생략하면 0(ROUND(1234.567) = 1235)
|
| ROUND(n, 음수) | ROUND(1234.567,-2) |
1200 | m이 음수면 정수 부분에서 반올림한다. -2는 십의 자리에서 반올림해 백의 자리까지 남긴다(ROUND(1250,-2) = 1300).
|
| TRUNC(n, m) | TRUNC(1234.567,1) |
1234.5 | 소수점 아래 m자리 아래를 버린다. |
| TRUNC(n, 음수) | TRUNC(1234.567,-2) |
1200 | 정수 부분에서 버린다. |
| CEIL | CEIL(3.1), CEIL(-3.1) |
4, -3 | 크거나 같은 가장 작은 정수(SQL Server는 CEILING) |
| FLOOR | FLOOR(3.9), FLOOR(-3.1) |
3, -4 | 작거나 같은 가장 큰 정수 |
| MOD | MOD(10,3), MOD(-10,3) |
1, -1 | 나머지. 부호는 앞 인수를 따른다. SQL Server는 % 연산자를 쓴다.
|
| ABS | ABS(-7) |
7 | 절댓값 |
| SIGN | SIGN(-7), SIGN(0) |
-1, 0 | 양수 1, 0이면 0, 음수 -1 |
| POWER / SQRT | POWER(2,10), SQRT(16) |
1024, 4 | 거듭제곱, 제곱근 |
Oracle의 DATE는 연·월·일과 시·분·초를 함께 저장한다. 날짜에 숫자를 더하고 빼면 일(日) 단위로 계산된다.
| 연산·함수 | 예 | 결과 | 설명 |
|---|---|---|---|
| SYSDATE | SYSDATE |
(현재 일시) | 데이터베이스 서버의 현재 날짜와 시각. SQL Server는 GETDATE() |
| 날짜 + 숫자 | DATE '2024-01-31' + 1 |
2024-02-01 | 1일 뒤. + 1/24는 1시간 뒤이다.
|
| 날짜 - 날짜 | DATE '2024-03-01' - DATE '2024-02-01' |
29 | 두 날짜 사이의 일수(숫자). 날짜끼리 더할 수는 없다. |
| ADD_MONTHS | ADD_MONTHS(DATE '2024-01-31',1) |
2024-02-29 | n개월 뒤. 말일이면 결과 달의 말일로 맞춘다. |
| MONTHS_BETWEEN | MONTHS_BETWEEN(DATE '2024-07-01', DATE '2024-01-01') |
6 | 앞 날짜 - 뒤 날짜의 개월 수. 순서를 바꾸면 -6이다. |
| LAST_DAY | LAST_DAY(DATE '2024-02-10') |
2024-02-29 | 그 달의 마지막 날 |
| NEXT_DAY | NEXT_DAY(DATE '2024-02-10','MONDAY') |
2024-02-12 | 지정 날짜 이후 처음 오는 해당 요일. 요일 이름은 세션 언어(NLS_DATE_LANGUAGE)를 따른다. |
| EXTRACT | EXTRACT(MONTH FROM DATE '2024-02-10') |
2 | 연(YEAR), 월(MONTH), 일(DAY) 등 일부를 숫자로 꺼낸다. |
| TRUNC(날짜) | TRUNC(DATE '2024-02-10','MM') |
2024-02-01 | 지정 단위 아래를 버린다. 단위를 생략하면 시각을 버려 그날 0시가 된다. 'YYYY'면 1월 1일 |
| 함수 | 예 | 결과 |
|---|---|---|
| TO_CHAR(날짜, 형식) | TO_CHAR(DATE '2024-02-10','YYYY"년" MM"월" DD"일"') |
2024년 02월 10일 |
| TO_CHAR(숫자, 형식) | TO_CHAR(1234567,'9,999,999') |
1,234,567 |
| TO_NUMBER(문자, 형식) | TO_NUMBER('1,234','9,999') |
1234 |
| TO_DATE(문자, 형식) | TO_DATE('20240210','YYYYMMDD') |
2024-02-10 (DATE 값) |
| CAST(값 AS 형식) | CAST('123' AS NUMBER) + 1 |
124 |
- CAST는 표준 SQL 함수로 Oracle과 SQL Server 모두 쓴다. SQL Server는 CONVERT도 쓴다.
- 숫자로 바꿀 수 없는 문자열을 TO_NUMBER에 넣으면 ORA-01722 오류가 난다.
형식이 다른 값을 비교하거나 연산하면 DBMS가 자동으로 형식을 바꾼다. Oracle은 문자와 숫자를 비교할 때 문자를 숫자로 바꾼다.
SELECT '10' + 5 AS A, 10 || 5 AS B FROM DUAL; -- A = 15, B = '105'
SELECT 1 FROM DUAL WHERE '10' > '9'; -- 0행: 문자끼리는 사전 순 비교('1' < '9')
SELECT 1 FROM DUAL WHERE 10 > '9'; -- 1행: '9'가 숫자 9로 바뀐다
인덱스가 있는 문자형 칼럼을 숫자와 비교하면 칼럼 쪽이 변환되어 인덱스를 쓰지 못한다. VARCHAR2 칼럼 코드에 인덱스를 만들고 실행 계획을 비교한 결과는 다음과 같다.
| 조건 | 실행 계획 | 조건 처리 |
|---|---|---|
WHERE 코드 = 123 |
TABLE ACCESS FULL | filter(TO_NUMBER("코드")=123)
|
WHERE 코드 = '123' |
INDEX RANGE SCAN | access("코드"='123')
|
- 칼럼에 함수가 씌워지는 것과 같아서 인덱스를 탈 수 없다(데이터베이스 인덱스). 따라서 비교 값의 형식을 칼럼에 맞추거나 명시적 변환 함수를 상수 쪽에 쓴다.
- 암시적 형변환은 성능 저하뿐 아니라 변환할 수 없는 값이 섞여 있을 때 실행 중 오류의 원인이 된다.
CASE는 조건에 따라 다른 값을 돌려주는 표준 SQL 표현이다. 자세한 내용은 CASE 문서를 참고한다.
SELECT 이름, 급여,
CASE 부서번호 WHEN 10 THEN '총무' -- 단순 CASE: 값이 같은지만 비교
WHEN 20 THEN '개발'
ELSE '기타' END AS 단순,
CASE WHEN 급여 >= 6000 THEN '고' -- 검색 CASE: 임의의 조건식
WHEN 급여 >= 4000 THEN '중' END AS 검색, -- ELSE 생략
DECODE(부서번호, 10, '총무', 20, '개발', '기타') AS 디코드
FROM B_EMP
ORDER BY 사원번호;
| 이름 | 급여 | 단순 | 검색 | 디코드 |
|---|---|---|---|---|
| 김대표 | 9000 | 총무 | 고 | 총무 |
| 이부장 | 6000 | 개발 | 고 | 개발 |
| 박과장 | 4500 | 개발 | 중 | 개발 |
| 최대리 | 4000 | 기타 | 중 | 기타 |
| 정사원 | 3000 | 기타 | NULL | 기타 |
| 한신입 | 2500 | 기타 | NULL | 기타 |
- ELSE를 생략하면 어느 WHEN에도 맞지 않는 행은 NULL이 된다(정사원, 한신입의 검색 열).
- 검색 CASE는 위에서부터 처음 참이 되는 WHEN 하나만 적용한다. 이부장(6000)은 두 조건을 모두 만족하지만 '고'가 된다.
- 부서번호가 NULL인 한신입은 단순 CASE에서 '기타'가 된다.
CASE NULL WHEN NULL THEN 'Y' ELSE 'N' END는 N이지만DECODE(NULL, NULL, 'Y', 'N')은 Y이다. DECODE는 NULL끼리 같다고 본다. - DECODE는 Oracle 전용 함수로, 같음(=) 비교만 할 수 있다.
NULL과의 산술 연산 결과는 NULL이므로 급여*12+커미션은 커미션이 NULL인 사원에게 NULL을 돌려준다. 이를 막기 위해 NULL 관련 함수를 쓴다(데이터베이스 Null).
| 함수 | 설명 | SQL Server |
|---|---|---|
| NVL(a, b) | a가 NULL이면 b, 아니면 a | ISNULL(a, b) |
| NVL2(a, b, c) | a가 NULL이 아니면 b, NULL이면 c | 없음(CASE로 작성) |
| NULLIF(a, b) | a = b이면 NULL, 아니면 a | NULLIF(a, b) |
| COALESCE(a, b, ...) | 처음으로 NULL이 아닌 값 | COALESCE(a, b, ...) |
SELECT 이름, 커미션, NVL(커미션,0) AS A, NVL2(커미션,'있음','없음') AS B,
NULLIF(커미션,0) AS C, COALESCE(커미션, 부서번호, -1) AS D,
급여*12+커미션 AS E, 급여*12+NVL(커미션,0) AS F
FROM B_EMP ORDER BY 사원번호;
| 이름 | 커미션 | A | B | C | D | E | F |
|---|---|---|---|---|---|---|---|
| 김대표 | NULL | 0 | 없음 | NULL | 10 | NULL | 108000 |
| 이부장 | NULL | 0 | 없음 | NULL | 20 | NULL | 72000 |
| 박과장 | NULL | 0 | 없음 | NULL | 20 | NULL | 54000 |
| 최대리 | 500 | 500 | 있음 | 500 | 500 | 48500 | 48500 |
| 정사원 | 0 | 0 | 있음 | NULL | 0 | 36000 | 36000 |
| 한신입 | NULL | 0 | 없음 | NULL | -1 | NULL | 30000 |
- 커미션이 0인 정사원은 NVL2에서 '있음'이다. 0은 NULL이 아니다.
- NULLIF(커미션, 0)은 0을 NULL로 바꾼다.
- ROUND·TRUNC의 자릿수 인수가 음수일 때 결과(
ROUND(1250,-2)= 1300,TRUNC(1234.567,-2)= 1200)와 CEIL·FLOOR의 음수 처리 - SUBSTR·INSTR·LPAD 결과 문자열, LENGTH와 LENGTHB의 차이, Oracle
||와 SQL Server+의 대응 - 날짜 - 날짜 = 일수, 날짜 + 숫자 = 날짜, MONTHS_BETWEEN의 인수 순서에 따른 부호
- CASE에서 ELSE를 생략하면 NULL, DECODE와 단순 CASE의 NULL 비교 차이
- NVL·NVL2·NULLIF·COALESCE의 결괏값, 그리고 인덱스 칼럼에 대한 암시적 형변환이 인덱스 사용을 막는다는 점