PL/SQL (8) - 오라클 내장 함수

​

1. 오라클 내장 함수

- 오라클에서 제공하는 함수가 있기 때문에 이를 사용하여 불필요한 수고를 덜 수 있다.

- 프로그래밍 언어의 명령, 함수, 구문, 사용법 등을 알고 이런 강력한 기능들을 사용하는 것도 능력이다.

- 종류 : 문자, 숫자, 날짜, 변환, 집합, 기타 함수

- 결과를 확인하고자 할 경우 DUAL table을 사용한다.

 

​

SQL 함수

SQL 함수는 Oracle Database에 내장되어 있으며, 다양한 적절한 SQL 문에서 사용할 수 있다. SQL 기능과 PL/SQL에 기록된 사용자 정의 기능을 혼동하지 마십시오.

 

SQL 함수에 의해 예상되는 데이터 형식이 아닌 데이터 형식의 인수로 SQL 함수를 호출하는 경우, Oracle은 SQL 함수를 수행하기 전에 인수를 예상 데이터 유형으로 변환하려고 시도한다. null 인수로 SQL 함수를 호출하면 SQL 함수는 자동으로 null을 반환한다. 이 동작을 반드시 따르지 않는 SQL 함수는 CONCAT, NVL, REPLACE 및 REGEXP_REPLACE뿐이다.

​

​

단일 행 함수

단일 행 함수은 쿼리된 테이블 또는 보기의 각 행에 대해 단일 결과 행을 반환한다. 이러한 기능은 선택 목록, Where 절, Start With 및 Connection By 절 및 HAVING 절에 표시될 수 있다.

​

​

숫자 함수

숫자 함수는 숫자 입력을 허용하고 숫자 값을 반환한다. 소수점 38자리까지 정확한 NUMBER 값을 반환하는 대부분의 숫자 함수. 초월 함수 COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, TANH는 소수점 36자리까지 정확하다. 초월함수 ACOS, ASIN, ATAAN, ATAN2는 소수점 30자리까지 정확하다.

​

숫자 함수는 다음과 같다.

ABS

ACOS

ASIN

ATAN

ATAN2

BITAND

CEIL

COS

COSH

EXP

FLOOR

LN

LOG

MOD

NANVL

POWER

REMAINDER

ROUND (number)

SIGN

SIN

SINH

SQRT

TAN

TANH

TRUNC (number)

WIDTH_BUCKET

​

​

문자를 반환 문자함수

문자 값을 반환하는 문자 함수는 달리 문서화되지 않는 한 다음과 같은 데이터 유형의 값을 반환한다.

• 입력 인수가 CHAR 또는 VARCHAR2인 경우 반환되는 값은 VARCHAR2이다.
• 입력 인수가 NCHAR 또는 NVARCHAR2인 경우 반환되는 값은 NVARCHAR2이다.

​

함수에 의해 반환되는 값의 길이는 반환되는 데이터 유형의 최대 길이로 제한된다.

• CHAR 또는 VARCHAR2를 반환하는 기능의 경우 반환 값의 길이가 한도를 초과하면 Oracle Database가 CHAR를 잘라 오류 메시지 없이 결과를 반환한다.
• CLOB 값을 반환하는 기능의 경우 반환 값의 길이가 한도를 초과하면 Oracle이 오류를 발생시키고 데이터를 반환하지 않는다.

 

문자 값을 반환하는 문자 함수는 다음과 같다.

CHR

CONCAT

INITCAP

LOWER

LPAD

LTRIM

NLS_INITCAP

NLS_LOWER

NLSSORT

NLS_UPPER

REGEXP_REPLACE

REGEXP_SUBSTR

REPLACE

RPAD

RTRIM

SOUNDEX

SUBSTR

TRANSLATE

TREAT

TRIM

UPPER

​

​

NLS 문자 함수

NLS 문자 함수는 문자 집합에 대한 정보를 반환한다. NLS 문자 함수:

​

NLS_CHARSET_DECL_LEN

NLS_CHARSET_ID

NLS_CHARSET_NAME

 

​

숫자 값을 반환하는 문자 함수

숫자 값을 반환하는 문자 기능은 임의의 문자 데이터 형식을 인수할 수 있다.

​

숫자 값을 반환하는 문자 기능은 다음과 같다.

ASCII

INSTR

LENGTH

REGEXP_INSTR

 

Datetime 함수

Datetime 함수는 날짜(날짜), 타임스탬프(TIMESTAMP, TIME ZONE 및 TIMESTAMP(Local Time ZONE 포함), 간격(InTERVAL Day TO SECOND, Interval YEAR to MONTH) 값에 따라 작동한다.

​

데이터타임 기능 중 일부는 Oracle DATE 데이터 유형(ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME 및 NEXT_DAY)에 맞게 설계되었다. 타임스탬프 값을 인수로 제공하면 Oracle Database는 내부적으로 입력 유형을 DATE 값으로 변환하고 DATE 값을 반환한다. 예외는 숫자를 반환하는 MONTHS_BETWEEN 기능과 타임스탬프나 구간 값을 전혀 허용하지 않는 ROUND 및 TRUNC 기능이다.

​

나머지 데이터타임 기능은 세 가지 유형의 데이터(날짜, 타임스탬프 및 간격) 중 하나를 수용하고 이러한 유형의 값을 반환하도록 설계되었다.

​

datetime 함수는 다음과 같다.

​

ADD_MONTHS

CURRENT_DATE

CURRENT_TIMESTAMP

DBTIMEZONE

EXTRACT (datetime)

FROM_TZ

LAST_DAY

LOCALTIMESTAMP

MONTHS_BETWEEN

NEW_TIME

NEXT_DAY

NUMTODSINTERVAL

NUMTOYMINTERVAL

ROUND (date)

SESSIONTIMEZONE

SYS_EXTRACT_UTC

SYSDATE

SYSTIMESTAMP

TO_CHAR (datetime)

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_DSINTERVAL

TO_YMINTERVAL

TRUNC (date)

TZ_OFFSET

 

​

일반 비교 함수

일반 비교 함수는 값 집합에서 최대값과 최소값을 결정한다.

​

일반적인 비교 함수는 다음과 같다.

​

GREATEST

LEAST

 

변환 함수

변환 함수는 값을 한 데이터 유형에서 다른 데이터 유형으로 변환한다. 일반적으로 함수 이름의 형식은 데이터 형식에서 데이터 형식까지의 관례에 따른다. 첫 번째 데이터 형식은 입력 데이터 형식이다. 두 번째 데이터 유형은 출력 데이터 유형이다.

​

SQL 변환 함수는 다음과 같다.

​

ASCIISTR

BIN_TO_NUM

CAST

CHARTOROWID

COMPOSE

CONVERT

DECOMPOSE

HEXTORAW

NUMTODSINTERVAL

NUMTOYMINTERVAL

RAWTOHEX

RAWTONHEX

ROWIDTOCHAR

ROWIDTONCHAR

SCN_TO_TIMESTAMP

TIMESTAMP_TO_SCN

TO_BINARY_DOUBLE

TO_BINARY_FLOAT

TO_CHAR (character)

TO_CHAR (datetime)

TO_CHAR (number)

TO_CLOB

TO_DATE

TO_DSINTERVAL

TO_LOB

TO_MULTI_BYTE

TO_NCHAR (character)

TO_NCHAR (datetime)

TO_NCHAR (number)

TO_NCLOB

TO_NUMBER

TO_DSINTERVAL

TO_SINGLE_BYTE

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_YMINTERVAL

TO_YMINTERVAL

TRANSLATE ... USING

UNISTR

 

큰 객체 함수

큰 객체 함수은 LOB에서 작동한다.

​

큰 객체 함수는 다음과 같다.

BFILENAME

EMPTY_BLOB

EMPTY_CLOB

 

​

Collection 함수

Collection 함수는 중첩된 테이블과 배선에서 작동한다.

​

SQL Collection 함수는 다음과 같다.

CARDINALITY

COLLECT

POWERMULTISET

POWERMULTISET_BY_CARDINALITY

SET

 

​

계층 함수

계층 함수는 결과 집합에 계층적 경로 정보를 적용한다.

​

SYS_CONNECT_BY_PATH

 

​

데이터 마이닝 함수

데이터 마이닝 함수는 DBMS_DATA_MININING 패키지 또는 Oracle Data Mining Java API를 사용하여 구축된 모델에서 작동한다.

​

SQL 데이터 마이닝 함수는 다음과 같다.

CLUSTER_ID

CLUSTER_PROBABILITY

CLUSTER_SET

FEATURE_ID

FEATURE_SET

FEATURE_VALUE

PREDICTION

PREDICTION_COST

PREDICTION_DETAILS

PREDICTION_PROBABILITY

PREDICTION_SET

 

XML 함수

XML 함수는 XML 문서 또는 조각에서 작동하거나 반환한다. 출력 포맷에 대한 정보를 포함하여 이러한 기능을 사용하여 XML 데이터를 선택하고 쿼리하는 방법에 대한 자세한 내용은 Oracle XML DB Developer's Guide를 참조하십시오.

​

SQL XML 함수:

​

APPENDCHILDXML

DELETEXML

DEPTH

EXTRACT (XML)

EXISTSNODE

EXTRACTVALUE

INSERTCHILDXML

INSERTXMLBEFORE

PATH

SYS_DBURIGEN

SYS_XMLAGG

SYS_XMLGEN

UPDATEXML

XMLAGG

XMLCDATA

XMLCOLATTVAL

XMLCOMMENT

XMLCONCAT

XMLFOREST

XMLPARSE

XMLPI

XMLQUERY

XMLROOT

XMLSEQUENCE

XMLSERIALIZE

XMLTABLE

XMLTRANSFORM

 

인코딩 및 디코딩 함수

인코딩 및 디코딩 함수를 통해 데이터베이스의 데이터를 검사하고 디코딩할 수 있다.

​

DECODE

DUMP

ORA_HASH

VSIZE

​

NULL 관련 함수

NULL 관련 함수는 null 처리를 용이하게 한다. NULL 관련 함수는 다음과 같다.

​

COALESCE

LNNVL

NULLIF

NVL

NVL2

 

환경 및 식별자 함수

환경과 식별자 함수은 인스턴스와 세션에 대한 정보를 제공한다. 이러한 함수는 다음과 같다.

​

SYS_CONTEXT

SYS_GUID

SYS_TYPEID

UID

USER

USERENV

 

집계 함수

집계 함수는 단일 행이 아닌 행 그룹에 따라 단일 결과 행을 반환한다. 집계 함수는 선택 목록과 ORDER BY 및 HAVING 절에 표시될 수 있다. Oracle Database는 SELECT 문에서 GROUP BY 절과 함께 일반적으로 사용되며, 여기서 Oracle Database는 조회된 테이블 또는 뷰의 행을 그룹으로 나눈다. GROUP BY 절이 포함된 쿼리에서 선택 목록의 요소는 집계 함수, GROUP BY 식, 상수 또는 이들 중 하나가 포함된 식이 될 수 있다. Oracle은 각 행 그룹에 집계 기능을 적용하고 각 그룹에 대해 단일 결과 행을 반환한다.

​

GROUP BY 절을 생략하면 Oracle은 선택 목록의 집계 함수를 쿼리된 테이블 또는 보기에 있는 모든 행에 적용하십시오. HAVING 절의 Aggregate 함수를 사용하여 쿼리된 표 또는 보기의 개별 행 값이 아닌 집계 함수의 결과에 따라 출력에서 그룹을 제거하십시오.

​

"GROUP BY 절 사용: 쿼리 및 하위 쿼리의 GROUP BY 절 및 HAVING 절에 대한 자세한 내용은 예제 및 "HAVING 절"을 참조하십시오.

단일 인수를 사용하는 많은 (전부는 아니지만) 집계 함수는 다음 조항을 수용한다.

​

DISTINCT는 집계 함수가 인수 표현식의 구별되는 값만 고려하도록 한다.

ALL은 Aggregate 함수가 모든 중복값을 포함하여 모든 값을 고려하도록 한다

​

예를 들어, DISTINCT 평균 1, 1, 3은 2이다. 평균은 1.5이다. 둘 다 지정하지 않으면 기본값은 ALL이다.

​

COUNT(*) 및 GROUPING을 제외한 모든 Aggregate 함수는 null을 무시한다. aggregate 함수에 대한 인수의 NVL 함수를 사용하여 null 값을 대체할 수 있다. COUNT는 null을 반환하지 않지만 숫자나 0을 반환한다. 나머지 모든 Aggregate 함수에 대해 데이터 세트에 행이 없거나 Aggregate 함수에 대한 인수로 null이 있는 행만 포함된 경우 함수는 null을 반환한다.

​

KEEP 키워드에 이어 MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV 등의 Aggregate 함수를 FIRST 또는 LAST 함수와 함께 사용하여 지정된 정렬 규격에 대해 FIRST 또는 LAST로 분류되는 행 집합에서 일련의 값으로 작동할 수 있다. 자세한 내용은 FIRST를 참조하십시오.

​

Aggregate 기능을 내포할 수 있다. 예를 들어, 다음 예는 샘플 스키마 hr에 있는 모든 부서의 최대 급여의 평균을 계산한다.

​

직원으로부터 선택 BY 부서_id;

​

AVG(MAX(SALARY))

----------------

10925

​

이 계산은 GROUP BY 절(부서_id)에 의해 정의된 각 그룹에 대한 내부 집계(MAX(급여)를 평가하여 결과를 다시 집계한다.

​

집계 함수는 다음과 같다.

AVG

COLLECT

CORR

CORR_*

COUNT

COVAR_POP

COVAR_SAMP

CUME_DIST

DENSE_RANK

FIRST

GROUP_ID

GROUPING

GROUPING_ID

LAST

MAX

MEDIAN

MIN

PERCENTILE_CONT

PERCENTILE_DISC

PERCENT_RANK

RANK

REGR_ (Linear Regression) Functions

STATS_BINOMIAL_TEST

STATS_CROSSTAB

STATS_F_TEST

STATS_KS_TEST

STATS_MODE

STATS_MW_TEST

STATS_ONE_WAY_ANOVA

STATS_T_TEST_*

STATS_WSR_TEST

STDDEV

STDDEV_POP

STDDEV_SAMP

SUM

VAR_POP

VAR_SAMP

VARIANCE

 

분석 함수

​

분석 함수는 행 집합을 기준으로 집계값을 계산한다. 각 그룹에 대해 여러 행을 반환한다는 점에서 집계 함수와는 다르다. 행의 그룹을 창이라고 하며 분석_clause에 의해 정의된다. 각 행에 대해 행의 슬라이딩 윈도우가 정의된다. 창은 현재 행에 대한 계산을 수행하는 데 사용되는 행의 범위를 결정한다. 창 크기는 행의 물리적 수 또는 시간 같은 논리적 간격에 기초할 수 있다.

​

분석 함수는 최종 ORDER BY 절을 제외하고 쿼리에서 수행되는 마지막 작업 집합이다. 분석 기능을 처리하기 전에 모든 조인 및 모든 위치, GROUP BY 및 HAVING 절이 완료된다. 따라서 분석 기능은 선택 목록 또는 ORDER BY 절에만 나타날 수 있다.

​

분석 함수는 일반적으로 누적, 이동, 중심 및 보고 집계를 계산하는 데 사용된다.

 

AVG *

CORR *

COVAR_POP *

COVAR_SAMP *

COUNT *

CUME_DIST

DENSE_RANK

FIRST

FIRST_VALUE *

LAG

LAST

LAST_VALUE *

LEAD

MAX *

MIN *

NTILE

PERCENT_RANK

PERCENTILE_CONT

PERCENTILE_DISC

RANK

RATIO_TO_REPORT

REGR_ (Linear Regression) Functions *

ROW_NUMBER

STDDEV *

STDDEV_POP *

STDDEV_SAMP *

SUM *

VAR_POP *

VAR_SAMP *

VARIANCE *