오라클에서 쓰이는 단일행 함수를 정리해 보았다.
단일행 함수를 알고 적재적소에 잘 쓰는 것이 SQL의 실력을 한층 더 높여주는 것이라 했다.
필요할 때 또는 심심할 때 한번씩 읽어봐도 괜찮을 것 같다.
* Numeric Functions
1. ABS
: ABS(n)은 n의 절대값을 반환한다.
- usage : ABS(n)
- example : SELECT ABS(-37) AS absolute FROM DUAL;
ABSOLUTE
----------
37
2. ACOS
: ACOS(n)은 n의 역코사인(arc cosine)값을 반환한다. 인수 n은 -1부터 1까지의 범위를 갖는다.
- usage : ACOS(n)
- example : SELECT ACOS(.7) AS arc_cosine FROM DUAL;
ARC_COSINE
----------
.79539883
3. ASIN
: ASIN(n)은 n의 역사인(arc sine)값을 반환한다. 인수 n은 -1부터 1까지의 범위를 갖는다.
- usage : ASIN(n)
- example : SELECT ASIN(.7) AS arc_sine FROM DUAL;
ARC_SINE
----------
.775397497
4. ATAN
: ATAN(n)은 n의 역탄젠트(arc tangent)값을 반환한다. 인수 n은 -1부터 1까지의 범위를 갖는다.
- usage : ATAN(n)
- example : SELECT ATAN(.7) AS arc_tangent FROM DUAL;
ARC_TANGENT
-----------
.610725964
5. ATAN2
: ATAN2(m,n)은 ATAN2(m/n)과 같으며, m/n의 역탄젠트(arc tangent)값을 반환한다.
인수 n은 범위가 제한이 없다.
- usage : ATAN2(m,n), ATAN2(m/n)
- example : SELECT ATAN2(.5, .3) AS arc_tangent2 FROM DUAL;
ARC_TANGENT2
------------
1.03037683
6. BITAND
: BITAND(m, n)은 인수 m과 인수 n의 비트에 대한 AND연산을 수행하여 정수를 반환한다.
m과 n은 음이 아닌 정수로 변환되어, AND 연산을 계산하여 정수를 반환한다.
이 함수는 일반적으로 DECODE함수와 함께 이용한다. AND연산은 2개의 비트를 비교한다.
값이 같을 경우에는 1을 반환하고 값이 다를 경우에는 0을 반환한다.
예를 들어, 정수 5(이진수 101)과 1(이진수 001)의 AND연산은 1(이진수 1)이다.
- usage : BITAND(m, n)
- example : SELECT BITAND(5, 1) AS bitand FROM DUAL;
BITAND
----------
1
7. CEIL
: CEIL은 인수에서 지정한 수를 올림하여 정수를 구하는 함수이다. 음의 수치를 부여하면
음수측의 가장 가까운 정수를 반환한다.
- usage : CEIL(n)
- example : SELECT CEIL(8.6) AS ceiling FROM DUAL;
CEILING
----------
9
8. COS
: COS은 파라미터 n(라디안으로 표현되는 각도)의 코사인값을 반환한다.
- usage : COS(n)
- example : SELECT COS(180 * 3.14159265359/180) AS "cosine of 180" FROM DUAL;
cosine of 180
-------------
-1
9. COSH
: COSH은 파라미터 n(라디안으로 표현되는 각도)의 쌍곡 코사인값(hyperbolic cosine)을 반환한다.
- usage : COSH(n)
- example : SELECT COSH(0) AS "hyperbolic cosine of 0" FROM DUAL;
hyperbolic cosine of 0
----------------------
1
10. EXP
: EXP는 e의 n 제곱 값을 반환한다. (e=2.71828183) 이 함수는 인수와 같은 타입의 값을 반환한다.
- usage : EXP(5)
- example : SELECT EXP(5) AS "e to the 5th power" FROM DUAL;
e to the 5th power
------------------
148.413159
11. FLOOR
: FLOOR는 지정한 숫자보다 작거나 같은 정수 중에서 최대값을 반환한다.
- usage : FLOOR(n)
- example : SELECT FLOOR(8.6) AS floor FROM DUAL;
FLOOR
---------
8
12. LN
: LN함수는 입력값의 자연 로그 값을 반환한다. LN은 0보다 큰 값이다.
- usage : LN(n)
- example : SELECT LN(37) AS "Natural log of 37" FROM DUAL;
Natural log of 37
-----------------
3.61091791
13. LOG
: LOG(m,n)에서 밑을 m으로 한 n의 로그 값을 반환한다. 밑 m은 0 또는 1 이외의 정수값, n은 양수값이다.
- usage : LOG(m, n)
- example : SELECT LOG(5, 125) AS "Log base 5 of 125" FROM DUAL;
Log base 5 of 125
-----------------
3
14. MOD
: MOD는 m을 n으로 나눈 나머지값을 반환한다. n이 0이면, m를 반환한다.
MOD 공식은 m - n * TRUNC(m/n)이다.
- usage : MOD(m, n)
- example : SELECT MOD(17, 5) AS modulus FROM DUAL;
MODULUS
-----------
2
15. NANVL
: NANVL는 10g에서 추가된 BINARY_FLOAT, BINARY_DOUBLE과 같은 datatype에만 적용되는 함수이다.
m의 값이 NaN(Not A Number)일 경우에 n을 반환하고, NaN이 아닐 경우 그냥 m를 반환한다.
이 함수는 NaN값을 Null으로 매핑하는데 위해 유용하다.
- usage : NANVL(m, n)
- example : CREATE TABLE float_point_demo (
dec_num NUMBER(10,2)
, bin_double BINARY_DOUBLE
, bin_float BINARY_FLOAT
)
INSERT INTO float_point_demo VALUES (1234.56, 1234.56, 1234.56)
INSERT INTO float_point_demo VALUES (0, 'NaN', 'NaN')
SELECT * FROM float_point_demo
DEC_NUM BIN_DOUBLE BIN_FLOAT
-------- ---------- ----------
1234.56 1.235E+003 1.235E+003
0 Nan Nan
SELECT bin_float, NANVL(bin_float, 0)
FROM float_point_demo
BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
1.235E+003 1.235E+003
Nan 0
16. POWER
: POWER는 m의 n승 값을 반환한다. m와 지수 n은 임의의 수이나, m이 음수이면, n은 정수여야 한다.
- usage : POWER(m, n)
- example : SELECT POWER(3, 3) AS raised FROM DUAL;
RAISED
---------
27
17. REMAINDER
: REMAINDER는 m를 n으로 나눈 나머지를 반환한다. MOD와 유사한데 공식은 m - n * ROUND(m/n)이다.
- usage : REMAINDER(m, n)
- example : SELECT MOD (13, 5), REMAINDER(13, 5) AS remainder FROM DUAL;
MOD(13,5) REMAINDER
------------ ------------
3 -2
18. SIGN
: SIGN은 파라미터로 주어지는 값의 부호(+, -, 0)에 따라 다음과 같이 반환한다.
n < 0 일 경우 -1 을 반환
n = 0 일 경우 0 을 반환
n > 0 일 경우 1 을 반환
- usage : SIGN(n)
- example : SELECT SIGN(-13) AS sign FROM DUAL;
SIGN
---------
-1
19. SIN
: SIN은 인수 n의 사인(sine)값을 반환한다.(라디안으로 표현되는 각도)
datatype이 BINARY_FLOAT일 경우, 반환값의 datatype은 BINARY_DOUBLE, 그 외에는 숫자 값을 반환한다.
- usage : SIN(n)
- example : SELECT SIN(50 * 3.14159265359/180) AS "sine of 50" FROM DUAL;
sine of 50
----------
.766044443
20. SINH
: SINH함수는 인수 n의 쌍곡선 사인(hyperbolic sine)을 반환한다.
SINH(n)의 값은 (EXP(n) - EXP(-n))/2 의 값과 같다.
- usage : SINH(n)
- example : SELECT SINH(2) AS "hyperbolic sine of 2" FROM DUAL;
hyperbolic sine of 2
--------------------
3.62686041
21. SQRT
: SQRT는 n의 제곱근을 반환한다. n이 number 타입일 경우에는 n은 음수가 되어서는 안된다.
BINARY_FLOAT 또는 BINARY_DOUBLE 타입일 경우에는 다음과 같은 값을 반환한다.
n >= 0 일 경우 양수를 반환
n = -0 일 경우 -0 을 반환
n < 0 일 경우 NaN 을 반환
- usage : SQRT(n)
- example : SELECT SQRT(7) AS "square root" FROM DUAL;
square root
-----------
2.64575131
22. TAN
: TAN은 인수 n의 사인(tangent)값을 반환한다.(라디안으로 표현되는 각도)
- usage : TAN(n)
- example : SELECT TAN(105 * 3.14159265359 / 180) AS "tangent of 105" FROM DUAL;
tangent of 105
--------------
-3.7320508
23. TANH
: TANH는 인수 n의 쌍곡선 탄젠트(hyperbolic tangent)을 반환한다.
TANH(n)의 값은 (EXP(n) - EXP(-n))/(EXP(n) + EXP(-n)) 의 값과 같다.
- usage : TANH(n)
- example : SELECT TANH(.7) AS "Hyperbolic tangent of .7" FROM DUAL;
Hyperbolic tangent of .7
------------------------
.604367777
* Character Functions Returning Character Values
1. CHR
: CHR는 해당 DB의 character set 에서 파라미터로 주어지는 10진수 n에 대응하는 아스키코드를 반환한다.
- usage : CHR(n)
- example : SELECT CHR(68)||CHR(66) AS oracle FROM DUAL;
ORACLE
------
DB
2. NCHR
: NCHR은 유니코드 문자를 반환한다. 이 함수는 USING NCHAR_CS구문을 가진 CHR과 동등하다.
- usage : NCHR(n)
- example : SELECT NCHR(187) AS nchr, CHR(187 USING NCHAR_CS) AS chr FROM DUAL;
NCHR CHR
---- ---
> >
3. NLS_INITCAP
: NLS_INITCAP는 각 단어의 처음 문자를 대문자로, 나머지 문자를 소문자로 변환하여 char를 반환한다.
단어는 공백 또는 영숫자 이외의 문자로 단락을 구분한다.
- usage : NLS_INITCAP('c', 'nlsparam')
- example : SELECT NLS_INITCAP ('database') AS initcap FROM DUAL;
INITCAP
--------
Database
4. NLS_LOWER
: NLS_LOWER는 모든 문자를 소문자로 변환하여 반환한다.
'nlsparam'은 NLS_INITCAP함수와 같은 형태와 목적을 가질수 있다.
- usage : NLS_LOWER('c', 'nlsparam')
- example : SELECT NLS_LOWER('ORACLE''', 'NLS_SORT = XGerman') AS lowercase FROM DUAL;
LOWERCASE
---------
oracle
5. NLSSORT
: NLSSORT는 char 소트에 이용되는 bytes의 문자열을 반환한다.
- usage : NLSSORT('c', 'nlsparam')
- example : CREATE TABLE t1 (name VARCHAR2(15));
INSERT INTO t1 VALUES ('Gaardiner');
INSERT INTO t1 VALUES ('Gaberd');
INSERT INTO t1 VALUES ('Gaasten');
SELECT * FROM t1 ORDER BY name;
NAME
----------
Gaardiner
Gaasten
Gaberd
SELECT * FROM t1 ORDER BY NLSSORT(name, 'NLS_SORT = XDanish');
NAME
---------------
Gaberd
Gaardiner
Gaasten
6. NLS_UPPER
: NLS_UPPER는 입력 문자열을 모두 대문자로 변환한 문자열을 반환한다.
- usage : NLS_UPPER('c', 'nlsparam')
- example : SELECT NLS_UPPER ('grobe') AS uppercase FROM DUAL;
UPPERCASE
---------
GROBE
7. SOUNDEX
: SOUNDEX는 char의 음성 표현을 가지는 문자열을 반환한다.
이 함수는 스펠링이 틀려도 발음이 유사한 영단어를 비교할수 있다.
- usage : SOUNDEX('c')
- example : SELECT last_name, first_name FROM employees
WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE');
LAST_NAME FIRST_NAME
---------- ----------
Smith Lindsey
Smith William
8. TRANSLATE
: TRANSLATE는 첫번째 문자열 expr에서 두번째 문자열(from_string) 각각의 문자를 순서대로
세번째 문자열(to_string)의 대응되는 각각의 문자로 바꿔준다. 즉, 문자단위로 일대일 변환을 해준다.
from_string은 to_string 보다 더 많은 문자를 가져야 하며, from_string 의 이 여분의 문자들
(to_string에 대응되는 문자가 없는)이 expr에 있는 경우, 해당 문자를 삭제하는 기능을 한다.
- usage : TRANSLATE(expr, from_string, to_string)
- example : SELECT TRANSLATE ('SQL*Plus User''s Guide', ' */''', '___') AS translate FROM DUAL
TRANSLATE
--------------------
SQL_Plus_Users_Guide
9. ASCII
: ASCII는 주어진 char의 첫 문자의 아스키 값에 상응하는 10진수값을 반환한다.
- usage : ASCII('c')
- example : SELECT ASCII('A') AS ascii FROM DUAL;
ASCII
-----
65
* Datetime Functions
1. CURRENT_DATE
: CURRENT_DATE는 현재 세션의 날짜 정보를 Date 데이터 형으로 반환한다.
- usage : CURRENT_DATE
- example : ALTER SESSION SET TIME_ZONE = '-5:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-05:00 08-APR-2009 13:14:03
2. DBTIMEZONE
: DBTIMEZONE는 데이터 베이스 time zone의 값을 반환한다.
- usage : DBTIMEZONE
- example : SELECT DBTIMEZONE FROM DUAL;
DBTIME
------
+00:00
3. EXTRACT
: EXTRACT는 특정 날짜,시간 값이나 날짜 값 표현식으로부터 지정된 날짜 영역의 값을 추출하여 반환한다.
- usage : EXTRACT(t FROM value_expr)
- example : SELECT EXTRACT(YEAR FROM DATE '2009-03-07') FROM DUAL;
EXTRACT(YEARFROMDATE'2009-03-07')
---------------------------------
2009
4. SESSIONTIMEZONE
: SESSIONTIMEZONE은 현재 세션의 시간대역(time zone)을 반환한다.
- usage : SESSIONTIMEZONE
- example : SELECT SESSIONTIMEZONE FROM DUAL;
SESSION
-------
-08:00
5. SYS_EXTRACT_UTC
: SYS_EXTRACT_UTC는 time zone offset 또는 time zone region name을 포함한 datetime값으로 부터
협정 세계시간 UTC (Coordinated Universal Time?formerly Greenwich Mean Time)을 추출한다.
- usage : SYS_EXTRACT_UTC(datetime)
- example : SELECT SYS_EXTRACT_UTC(TIMESTAMP '2000-03-28 11:30:00.00 -08:00') AS utc FROM DUAL;
UTC
---------------------
28-MAR-00 07.30.00 PM
* General Comparison Functions
1. GREATEST
: GREATEST는 하나 이상의 인수중에서 가장 큰 값을 반환한다.
- usage : GREATEST(expr)
- example : SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD') AS greatest FROM DUAL;
GREATEST
--------
HARRY
2. LEAST
: LEAST는 인수 EXPR의 리스트 중에서 가장 작은 값을 반환을 한다.
- usage : LEAST(expr)
- example : SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD') AS least FROM DUAL;
LEAST
------
HAROLD
* Conversion Functions
1. ASCIISTR
: ASCIISTR은 주어진 문자열의 아스키 문자열을 반환한다.
- usage : ASCIISTR('c')
- example : SELECT ASCIISTR('ABACDE') AS asciistr FROM DUAL;
ASCIISTR
----------
AB\00C4CDE
2. BIN_TO_NUM
: BIN_TO_NUM는 비트(2진수) 벡터를 동등한 수(10진수)로 변환한다.
- usage : BIN_TO_NUM(expr)
- example : SELECT BIN_TO_NUM(1,1,0) FROM DUAL;
BIN_TO_NUM(1,1,0)
-----------------
6
3. CHARTOROWID
: CHARTOROWID는 CHAR, VARCHAR2, NCHAR, or NVARCHAR2 데이터형태의 값으로부터 ROWID형으로 변환한다.
- usage : CHARTOROWID('c')
- example : SELECT last_name FROM employees WHERE ROWID = CHARTOROWID('AAAFd1AAFAAAABSAA/');
LAST_NAME
-----------
Greene
* Collection Functions
1. CARDINALITY
: CARDINALITY는 중첩 테이블(nested table)에서 원소의 수를 반환한다.
반환된 형식은 number이고 만약 nested table이 공백이거나 null 집합인 경우 null을 반환한다.
- usage : CARDINALITY(nested_table)
- example : SELECT product_id, CARDINALITY(ad_textdocs_ntab) FROM print_media;
PRODUCT_ID CARDINALITY(AD_TEXTDOCS_NTAB)
---------- -----------------------------
3060 3
2056 3
3106 3
2268 3
2. SET
: SET은 중첩 테이블에서 중복을 배제하여 반환한다. 반환된 중첩 테이블은 입력된 중첩 테이블과 같은 형태이다.
- usage : SET(nested_table)
- example : SELECT customer_id, SET(cust_address_ntab) address FROM customers_demo;
CUSTOMER_ID ADDRESS(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
----------- -------------------------------------------------------------------------------------------------
101 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'))
102 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'))
103 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))
104 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('6445 Bay Harbor Ln', '46254', 'Indianapolis', 'IN', 'US'))
105 CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP('4019 W 3Rd St', '47404', 'Bloomington', 'IN', 'US'))
* Hierarchical Function
1. SYS_CONNECT_BY_PATH
: SYS_CONNECT_BY_PATH는 계층적 쿼리에서 유효하다. 이 함수는 루트로부터 node로 열의 값 Path를 반환환다.
column 값은 CONNECT BY 조건에 의해 반환되는 각행을 char에 의해 분리된다.
- usage : SYS_CONNECT_BY_PATH(column, 'c')
- example : SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') AS path FROM employees
START WITH last_name = 'Kochhar' CONNECT BY PRIOR employee_id = manager_id;
PATH
-------------------------------
/Kochhar
/Kochhar/Greenberg
/Kochhar/Greenberg/Faviet
/Kochhar/Greenberg/Chen
/Kochhar/Greenberg/Sciarra
/Kochhar/Greenberg/Urman
/Kochhar/Greenberg/Popp
/Kochhar/Whalen
/Kochhar/Mavris
/Kochhar/Baer
/Kochhar/Higgins
/Kochhar/Higgins/Gietz
* Encoding and Decoding Functions
1. DUMP
: DUMP는 지정한 데이터의 위치와 길이 등을 지정한 형식으로 반환한다. return_fmt는 반환값의 서식을 지정한다.
- usage : DUMP(expr, return_fmt, start_position, length)
- example : SELECT DUMP('abc', 1016) AS dump FROM DUAL;
DUMP
------------------------------------------
Typ=96 Len=3 CharacterSet=WE8DEC: 61,62,63
2. VSIZE
: VSIZE는 expr의 내부 표현에서 바이트의 수를 반환한다. 만약 expr이 Null이라면, 이 함수는 Null을 반환한다.
- usage : VSIZE(expr)
- example : SELECT last_name, VSIZE (last_name) AS bytes FROM employees WHERE department_id = 10;
LAST_NAME BYTES
--------------- ----------
Whalen 6
* NULL-Related Functions
1. LNNVL
: LNNVL은 조건식에 주어지는 항의 한쪽 혹은 양쪽에 NULL 값이 나타날 경우에, 조건식을 평가하는 간결한 방법을
제공한다.
LNNVL은L where 절에만 사용할 수 있다. LNNVL 함수는 조건식을 파라미터로 가지며 조건식이 FALSE나
UNKNOWN 일 경우에 TRUE 를 반환하고, 조건식이 TRUE 일 경우에 FALSE 를 반환한다. LNNVL 함수는
IS [NOT] NULL, AND, OR 조건식을 사용할수 없으나 잠재적으로 NULL 값이 나올 수 있는 상황에서 NULL 값을
처리하기 위해 사용할 수 있다. 파라미터로 주어지는 조건식에는 AND, OR, BETWEEN 을 포함하는
다중조건식(compound condition)은 올 수 없다.
- usage : LNNVL(condition)
- example : SELECT COUNT (*) FROM emp WHERE LNNVL (comm >= 500)
COUNT(*)
--------
12
* Environment and Identifier Functions
1. UID
: UID는 세션 사용자의 유일한 식별하는 정수를 반환한다.(로그인 유저)
- usage : UID
- example : SELECT UID FROM DUAL;
UID
----------
0
2. USER
: USER는 VARCHAR2 형태를 가지는 세션 사용자(로그인 유저)의 이름을 반환한다.
- usage : USER
- example : SELECT USER FROM DUAL;
USER
----------
SYS
