HPL/SQL 함수

HPL/SQL 함수 (Functions)

HPL/SQL이 제공하는 여러 내장 함수들을 정리한 문서예요. 문자열·날짜·파티션·집계 등 다양한 범주의 함수가 있으며, 각 함수는 대체로 타 DBMS(Oracle, IBM DB2, Teradata 등)와 호환됩니다.

출처: 문서

본문

참고: 아래 소개하는 함수 외에도 개별 함수 문서가 존재합니다. 함수별 상세 페이지는 해당 이름의 문서를 참고하세요.

CAST 함수

CAST 함수는 표현식을 지정한 데이터 타입으로 변환합니다.

CAST(expression AS datatype[(length)]);

CAST를 CHAR 또는 VARCHAR 함수로 사용할 때 length를 지정하면, 결과 문자열이 이 길이로 잘려요.

CAST('Abc' AS CHAR(1));
--
A

CAST(TIMESTAMP '2015-03-12 10:58:34.111' AS CHAR(10));
--
2015-03-12

호환성: Oracle, Microsoft SQL Server, IBM DB2, Teradata, PostgreSQL, MySQL, Netezza

CHAR 함수

CHAR 함수는 숫자를 문자열로 변환합니다.

CHAR(num_expression);

반환 타입: STRING

CHAR(1000);
--
1000

호환성: IBM DB2. 참고: TO_CHAR

COALESCE 함수

COALESCE 함수는 첫 번째 NULL이 아닌 표현식을 반환합니다.

COALESCE(expr1, expr2 [, expr3, ...]);

첫 번째 NULL이 아닌 표현식을 찾으면 이후 표현식은 평가되지 않아요.
COALESCE와 NVL은 동의어입니다.
반환값: 첫 번째 NULL이 아닌 표현식. 모두 NULL이면 NULL.
반환 타입: 첫 번째 NULL이 아닌 표현식의 데이터 타입.

COALESCE(NULL, 1, 2, 3);

결과: 1

CONCAT 함수

CONCAT 함수는 두 개 이상의 문자열을 이어붙입니다.

CONCAT(expr, expr2 [, expr3, ...]);

표현식이 NULL로 평가되면 빈 문자열로 취급돼요.
모든 표현식이 NULL일 때만 CONCAT이 NULL을 반환합니다.
반환 타입: STRING

CONCAT('a', 'b', NULL, 'c');

결과: abc

호환성: Oracle, IBM DB2, Teradata, Microsoft SQL Server, PostgreSQL, MySQL, Netezza. 참고: || 연산자

CURRENT_DATE 함수

CURRENT_DATE 함수는 현재 날짜(년, 월, 일)를 반환합니다.

CURRENT_DATE | CURRENT DATE

반환 타입: DATE

호환성: IBM DB2, Teradata, MySQL. 참고: CURRENT_TIMESTAMP, FROM_UNIXTIME, NOW, SYSDATE, UNIX_TIMESTAMP

CURRENT_TIMESTAMP 함수

CURRENT_TIMESTAMP 함수는 현재 날짜와 시간(년, 월, 일, 시, 분, 초, 소수 초)을 반환합니다.

CURRENT_TIMESTAMP | CURRENT TIMESTAMP [(precision)]

precision: 소수 초 정밀도(0~3). 기본값 3.
반환 타입: TIMESTAMP

소수를 제외한 현재 날짜·시간을 얻을 때:

CURRENT_TIMESTAMP(0)
--
2015-03-02 13:04:42

호환성: Oracle, IBM DB2, Teradata, MySQL.

CURRENT_USER 함수

CURRENT_USER 함수는 현재 HPL/SQL 스크립트를 실행하는 사용자의 이름을 반환합니다.

CURRENT_USER | CURRENT USER

반환 타입: STRING

CURRENT_USER
--
paul

호환성: IBM DB2, Teradata. 참고: USER

DATE 함수

DATE 함수는 표현식을 DATE 데이터 타입으로 변환합니다.

DATE(expression);

반환 타입: DATE

DATE('2015-03-12');
DATE('2015' || '-03-' || '12');
DATE(TIMESTAMP '2015-03-12 10:58:34.111');

호환성: IBM DB2. 참고: DATE Literal, TIMESTAMP Literal, TIMESTAMP_ISO

DBMS_OUTPUT 패키지

DBMS_OUTPUT 패키지는 메시지를 보내 프로그램을 디버깅하는 데 유용해요.

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, world!');
END;

호환성: Oracle

PUT_LINE 함수: 표준 출력(기본적으로 화면)에 텍스트 문자열을 씁니다. 줄 종결자를 추가해요.

DBMS_OUTPUT.PUT_LINE(text);

DECODE 함수

DECODE 함수는 IF-THEN-ELSE 논리를 구현하게 해줍니다.

DECODE(expr, when_exp1, then_expr1 [, ...n] [, else_expr])

expr이 NULL이면 첫 번째 NULL인 when_exprN과 매칭됩니다.
when_exprN이 매칭되지 않으면 then_exprN은 평가되지 않아요.

DECLARE var1 INT DEFAULT 3;
PRINT DECODE (var1, 1, 'A', 2, 'B', 3, 'C');
-- Result: C
PRINT DECODE (var1, 1, 'A', 2, 'B', 'C');
-- Result: C

SET var1 = NULL;
PRINT DECODE (var1, 1, 'A', 2, 'B', NULL, 'C');
-- Result: C

호환성: Oracle, IBM DB2, Teradata

FROM_UNIXTIME 함수

FROM_UNIXTIME 함수는 1970-01-01 00:00:00 이후의 초 수를 타임스탬프 값으로 변환합니다.

FROM_UNIXTIME(epoch, [format])

epoch: 1970-01-01 00:00:00 이후의 초 수.
format: 타임스탬프 형식(선택). 기본 형식은 yyyy-MM-dd HH:mm:ss.
반환 타입: STRING

from_unixtime(1447141681);
---
2015-11-10 04:48:01

from_unixtime(1447141681, 'yyyy-MM-dd');
---
2015-11-10

호환성: Hive.

INSTR 함수

INSTR 함수는 문자열 안에서 부분 문자열의 시작 위치를 반환합니다.

INSTR(string, substring [, position [, occurrence]])

position: 검색 시작 위치, 기본값 1(문자열의 시작). 음수면 문자열 끝에서 거꾸로 세어 검색합니다.
occurrence: 찾을 부분 문자열의 발생 횟수, 기본값 1(첫 번째 발생).
string이 NULL이면 반환값은 NULL. string이 NULL이 아니고 부분 문자열을 찾지 못하면 0.

LEN 함수

LEN 함수는 지정한 문자열 표현식의 길이를 문자 수로 반환하며, 끝의 공백은 제외합니다.

LEN(string_expression);

LEN('Abc ');
---
3

호환성: Microsoft SQL Server. 참고: LENGTH

LENGTH 함수

LENGTH 함수는 지정한 문자열 표현식의 길이를 문자 수로 반환합니다.

LENGTH(string_expression);

LENGTH('Abc ');
---
4

호환성: Oracle, IBM DB2, Teradata, PostgreSQL, MySQL, Netezza. 참고: LEN

LOWER 함수

LOWER 함수는 문자열 표현식을 소문자로 변환합니다.

LOWER(expression);

LOWER('ABC');
---
abc

호환성: Oracle, Microsoft SQL Server, IBM DB2, Teradata, PostgreSQL, MySQL, Netezza

MAX_PART_DATE 함수

MAX_PART_DATE 함수는 지정한 DATE 타입 파티션 컬럼의 최댓값을 찾습니다.

MAX_PART_DATE([db_name.]table_name [, column_name [, part_col=filter, ...]]);

컬럼 이름을 지정하지 않으면 첫 번째 파티션 컬럼이 사용됩니다. 파티션 필터는 최댓값을 찾기 전에 적용돼요.

MAX_PART_INT 함수

MAX_PART_INT 함수는 지정한 INT 타입 파티션 컬럼의 최댓값을 찾습니다.

MAX_PART_INT([db_name.]table_name [, column_name [, part_col=filter, ...]]);

파티션이 정수가 아닌 값을 포함하면 무시됩니다.

MAX_PART_STRING 함수

MAX_PART_STRING 함수는 지정한 STRING(VARCHAR/CHAR) 타입 파티션 컬럼의 최댓값(알파벳 순서상 마지막)을 찾습니다.

MAX_PART_STRING([db_name.]table_name [, column_name [, part_col=filter, ...]]);

MIN_PART_DATE 함수

MIN_PART_DATE 함수는 지정한 DATE 타입 파티션 컬럼의 최솟값을 찾습니다.

MIN_PART_DATE([db_name.]table_name [, column_name [, part_col=filter, ...]]);

MIN_PART_INT 함수

MIN_PART_INT 함수는 지정한 INT 타입 파티션 컬럼의 최솟값을 찾습니다.

MIN_PART_INT([db_name.]table_name [, column_name [, part_col=filter, ...]]);

파티션이 정수가 아닌 값을 포함하면 무시됩니다.

MIN_PART_STRING 함수

MIN_PART_STRING 함수는 지정한 STRING(VARCHAR/CHAR) 타입 파티션 컬럼의 최솟값(알파벳 순서상 첫 번째)을 찾습니다.

MIN_PART_STRING([db_name.]table_name [, column_name [, part_col=filter, ...]]);

NOW 함수

NOW 함수는 현재 날짜와 시간(년, 월, 일, 시, 분, 초, 소수 초)을 반환합니다.

NOW()

반환 타입: TIMESTAMP

NOW()
--
2015-11-02 07:59:25.833

호환성: PostgreSQL, MySQL.

NVL 함수

NVL 함수는 첫 번째 NULL이 아닌 표현식을 반환합니다.

NVL(expr1, expr2 [, expr3, ...]);

NVL과 COALESCE는 동의어입니다. 첫 번째 NULL이 아닌 표현식을 찾으면 이후 표현식은 평가되지 않아요.

NVL(NULL, 1);

결과: 1

NVL2 함수

첫 번째 표현식이 NOT NULL이면 NVL2 함수는 두 번째 표현식의 결과를 반환하고, 그렇지 않으면 세 번째 표현식의 결과를 반환합니다.

NVL2(expr1, expr2, expr3);

expr1이 NULL이 아니면 expr2만, expr1이 NULL이면 expr3만 평가됩니다.
반환 타입: expr1이 NULL인지에 따라 expr2 또는 expr3가 반환하는 타입.

PART_COUNT 함수

PART_COUNT 함수는 지정한 테이블의 파티션 수를 반환합니다.

PART_COUNT([db_name.]table_name, part_col=filter, ...);

HPL/SQL은 파티션 정보를 얻기 위해 다음 Hive 문을 사용합니다: SHOW PARTITIONS db_name.tab_name [PARTITION (part_col=filter, ...)]
반환값: 파티션 수. 테이블이 없거나 오류가 발생하면 NULL.

PART_COUNT_BY 함수

PART_COUNT_BY 함수는 테이블에서 지정한 파티션 컬럼별로 그룹화된 파티션 수를 반환합니다.

PART_COUNT_BY([db_name.]table_name, [part_col, ...]);

part_col을 지정하지 않으면 최상위 파티션 수.
part_col을 지정하면 파티션 값과 같은 값을 가진 기존 파티션의 총 개수.

PART_LOC 함수

PART_LOC 함수는 지정한 테이블 파티션의 위치를 HDFS 또는 다른 저장소에서 반환합니다.

PART_LOC([db_name.]table_name, part_col=filter, ... [, with_hostname]);

with_hostname: 1 – 호스트 이름과 함께 경로 반환, 0 – 호스트 이름 없이(기본값).
HPL/SQL은 파티션 정보를 얻기 위해 DESCRIBE EXTENDED db_name.tab_name PARTITION (part_col=filter, ...)를 사용합니다.

REPLACE 함수

REPLACE 함수는 지정한 부분 문자열의 모든 발생을 다른 부분 문자열로 교체합니다.

REPLACE(string, what, with)

replace('2016-03-03', '-', '');
--
20160303

호환성: Oracle, Microsoft SQL Server, IBM DB2, MySQL.

SUBSTR 함수

SUBSTR 함수는 문자열에서 부분 문자열을 반환합니다.

SUBSTR(string, start_pos [, substring_len])

start_pos가 0이면 1로 처리됨.
SUBSTR와 SUBSTRING은 동의어.
반환 타입: String.

SUBSTRING 함수

SUBSTRING 함수는 문자열에서 부분 문자열을 반환합니다.

SUBSTRING(string, start_pos [, substring_len])
|
SUBSTRING(string FROM start_pos [FOR substring_len])

start_pos가 0이면 1로 처리됨. SUBSTRING과 SUBSTR은 동의어.

SYSDATE 함수

SYSDATE 함수는 현재 날짜와 시간(년, 월, 일, 시, 분, 초)을 반환합니다.

SYSDATE

반환 타입: TIMESTAMP

SYSDATE
--
2015-03-03 11:06:31

호환성: Oracle.

TIMESTAMP_ISO 함수

TIMESTAMP_ISO 함수는 문자열 또는 날짜 표현식을 TIMESTAMP 데이터 타입으로 변환합니다. 문자열은 'YYYY-MM-DD HH24:MI:SS.FF' 또는 'YYYY-MM-DD' 형식이어야 해요.

TIMESTAMP_ISO(expression);

TIMESTAMP_ISO('2015-03-12');
--
2015-03-12 00:00:00

TIMESTAMP_ISO(DATE '2015-03-12');
--
2015-03-12 00:00:00

호환성: IBM DB2. 참고: DATE Literal, TIMESTAMP Literal, DATE, TO_TIMESTAMP

TO_CHAR 함수

TO_CHAR 함수는 표현식을 문자열로 변환합니다.

TO_CHAR(expression);

TO_CHAR(CURRENT_DATE);

호환성: Oracle, IBM DB2, Teradata. 참고: CHAR

TO_TIMESTAMP 함수

TO_TIMESTAMP 함수는 지정한 형식을 사용해 문자열을 TIMESTAMP 데이터 타입으로 변환합니다.

TO_TIMESTAMP(string_expression, format_expression);

Format Elements

Element	Description
YYYY	4자리 연도
MM	월 (1-12)
DD	일 (1-31)
HH24	시간 (0-23)
MI	분 (0-59)
SS	초 (0-59)

TO_TIMESTAMP('2015-04-02', 'YYYY-MM-DD');
TO_TIMESTAMP('04/02/2015', 'mm/dd/yyyy');
TO_TIMESTAMP('2015-04-02 13:51:31', 'YYYY-MM-DD HH24:MI:SS');

호환성: Oracle, IBM DB2, Teradata.

TRIM 함수

TRIM 함수는 문자열의 앞뒤 문자를 제거합니다.

TRIM(string_expression);

'#' || TRIM(' Hello ') || '#';
--
#Hello#

호환성: Oracle, IBM DB2, Teradata, Microsoft SQL Server, PostgreSQL, MySQL, Netezza

UNIX_TIMESTAMP 함수

UNIX_TIMESTAMP 함수는 현재 날짜와 시간을 1970-01-01 00:00:00 이후의 초로 반환합니다.

UNIX_TIMESTAMP()

반환 타입: INT

UNIX_TIMESTAMP()
--
1446631617

호환성: Hive.

UPPER 함수

UPPER 함수는 문자열 표현식을 대문자로 변환합니다.

UPPER(expression);

UPPER('abc');
---
ABC

호환성: Oracle, Microsoft SQL Server, IBM DB2, Teradata, PostgreSQL, MySQL, Netezza

USER 함수

USER 함수는 현재 HPL/SQL 스크립트를 실행하는 사용자의 이름을 반환합니다.

USER

USER
--
paul

호환성: Oracle, IBM DB2, Teradata. 참고: CURRENT_USER

더 알아보기 (Learn more)

HPL/SQL 함수는 문자열(CAST, SUBSTR, REPLACE, LOWER/UPPER, LENGTH), 날짜(CURRENT_DATE, NOW, SYSDATE, FROM_UNIXTIME, TO_TIMESTAMP), NULL 처리(COALESCE, NVL, NVL2), 파티션(MIN/MAX_PART_*, PART_COUNT) 등을 폭넓게 제공해요. 개별 함수 상세는 각 함수 문서를 참고하세요.