기타 함수
기타 함수#
BASE64_DECODE#
구문#
BASE64_DECODE(expr)
설명#
이 함수는 base64형태로 인코딩된 VARBYTE 데이터 타입의 입력 문자열을 디코딩하여 VARBYTE 데이터 타입의 원 데이터를 반환한다.
예제#
SELECT BASE64_DECODE(VARBYTE'71673D3D') FROM DUAL;
BASE64_DECODE(VARBYTE'71673D3D')
-----------------------------
AA
1 row selected.
BASE64_DECODE_STR#
구문#
BASE64_DECODE_STR(expr)
설명#
이 함수는 base64로 인코딩된 입력 문자열을 디코딩한 원 데이터를 16진수 문자열로 반환한다. 입력 문자열은 0-9, a-z, A-Z, +, / 문자로 구성되어야 하며, 길이는 4의 배수이어야 한다. 입력 데이터 끝에 입력 문자열의 길이를 4의 배수로 만들기 위한 패딩용으로 '=' 문자가 허용된다. 반환 데이터 타입은 VARCHAR이다.
예제#
SELECT BASE64_DECODE_STR('qw==') FROM DUAL;
BASE64_DECODE_STR('qw==')
-----------------------------
AB
1 row selected.
BASE64_ENCODE#
구문#
BASE64_ENCODE(expr)
설명#
이 함수는 VARBYTE 데이터 타입의 값을 base64형태로 인코딩하여 VARBYTE 데이터 타입의 문자열을 반환한다.
예제#
SELECT BASE64_ENCODE(VARBYTE'AA') FROM DUAL;
BASE64_ENCODE(VARBYTE'AA')
-----------------------------
71673D3D
1 row selected.
BASE64_ENCODE_STR#
구문#
BASE64_ENCODE_STR(expr)
설명#
이 함수는 16진수 입력 문자열을 base64로 인코딩한 결과를 VARCHAR 데이터 타입의 문자열로 반환한다. 입력 문자열은 0-9, a-z, A-Z 문자로 구성되어야 하며, 길이는 2의 배수이어야 한다.
예제#
SELECT BASE64_ENCODE_STR('AB') FROM DUAL;
BASE64_ENCODE_STR('AB')
---------------------------
qw==
1 row selected.
BINARY_LENGTH#
구문#
BINARY_LENGTH (expr)
설명#
이 함수는 BLOB, BYTE, NIBBLE과 같은 이진 데이터 타입의 데이터 길이를 반환한다.
예제#
<질의> 세 가지 이진 데이터 타입 값의 길이를 출력하라.
CREATE TABLE t1 (i1 BLOB, i2 Byte(10), i3 NIBBLE(10) );
INSERT INTO t1 VALUES ( BLOB'3FD', Byte'123FD', NIBBLE'90BCD');
SELECT BINARY_LENGTH (i1)
, BINARY_LENGTH (i2)
, BINARY_LENGTH (i3)
FROM t1;
BINARY_LENGTH (I1) BINARY_LENGTH (I2) BINARY_LENGTH (I3)
------------------------------------------------------
2 10 5
1 row selected.
CASE2#
구문#
CASE2 (expr1, ret_expr1,
[,expr2, ret_expr2,..,]
[, default])
설명#
이 함수는 expr1이 참이면 ret_expr1 을 반환한다. 만약 expr1이 거짓이면 expr2를 계산해서 참이면 ret_expr2를 반환한다. 참인 수식이 나올 때까지 이 과정을 계속한다. 참인 수식이 나오지 않으면, default 가 반환된다. 참인 수식이 없고 default도 지정되어 있지 않으면 NULL이 반환된다.
예제#
<질의> 직원들의 급여를 출력하라. 월급여가 2000 보다 크면 'HIGH'를, 1500 보다 작으면 'LOW'을 출력하고, 급여 정보가 없으면 NULL을 출력하라.
SELECT e_lastname
, e_firstname
, emp_job
, CASE2(salary > 2000, 'HIGH', salary < 1500, 'LOW', salary IS NULL, 'NULL', TO_CHAR(salary)) salary
FROM employees;
E_LASTNAME E_FIRSTNAME EMP_JOB SALARY
---------------------------------------------------------------------------
Moon Chan-seung CEO NULL
Davenport Susan designer 1500
Kobain Ken engineer 2000
Foster Aaron PL 1800
Ghorbani Farhad PL HIGH
Momoi Ryu programmer 1700
Fleischer Gottlieb manager LOW
Wang Xiong manager NULL
.
.
.
20 rows selected.
CASE WHEN#
구문#
case
simple_case_expr
searched_case_expr
else_clause
설명#
CASE WHEN에 searched_case_expr이 사용되면, 이는 CASE2 함수와 동일하다. 즉, 첫 번째 condition이 참이면 첫번째 return_expr을 반환한다. 명시된 condition 모두 거짓이면, else_clause가 있을 경우 else_expr이 반환되고 그렇지 않을 경우 NULL이 반환된다. 이런 방식으로 이 함수를 사용하면 여러 종류의 비교 연산자가 사용될 수 있다.
CASE WHEN에 simple_case_expr 이 사용되면, expr은 동등 연산자(=)를 사용해서 각 comparison_expr과 비교될 것이다.
예제#
<질의> c1 칼럼의 세 번째 문자가 a이면 'aaaaa'를, b이면 'bbbbb'를, c이면 'ccccc'를 출력하라.
CREATE TABLE test (c1 CHAR(10));
INSERT INTO test VALUES('abcdefghi');
SELECT CASE SUBSTRING(c1, 3, 1)
WHEN 'a' THEN 'aaaaa'
WHEN 'b' THEN 'bbbbb'
WHEN 'c' THEN 'ccccc'
END
FROM test;
CASESUBSTRING(C1,3,1)WHEN'A'THEN'AAAAA'WHE
----------------------------------------------
ccccc
1 row selected.
COALESCE#
구문#
COALESCE (expr1, expr2, …, exprn)
설명#
입력된 수식들 중에서 NULL이 아닌 첫 번째 수식을 반환하는 함수이다.
expr1이 NULL이 아니면 expr1 수식을 반환하고, NULL이면 다음 수식들 중에서 NULL 이 아닌 첫 번째 수식을 반환한다. 만약 입력한 모든 수식이 NULL이면, 이 함수는 NULL을 반환한다.
COALESCE 함수는 두 개 이상의 수식을 명시해야 한다.
예제#
<질의> 입력된 수식들 중 NULL이 아닌 첫 번째 수식을 구하라.
SELECT COALESCE( NULL, 'a') FROM DUAL;
COALESCE( NULL, 'a')
------------------------
a
1 row selected.
SELECT COALESCE( NULL, NULL, NULL) FROM DUAL;
COALESCE( NULL, NULL, NULL)
------------------------------
1 row selected.
DECODE#
구문#
DECODE (expr, comparison_expr1, ret_expr1,
[, comparison_expr2, ret_expr2,..,]
[, default])
설명#
DECODE 함수는 simple_case_expr이 사용된 CASE WHEN 과 동일하다. 즉, expr이 각각의 comparison_expr과 동등 연산자(=)를 사용해서 순차적으로 비교된 후, 처음으로 그 결과가 참이 되는 comparison_expr에 해당하는 ret_expr이 반환된다. 어떤 comparison_expr과의 비교도 참이 아니면, default가 반환된다. 참이 되는 comparison_expr이 없고, default도 명시되지 않았으면, NULL이 반환된다.
예제#
<질의> i가 NULL 이면 'NULL', 1 이면 'ONE', 2 이면 'TWO'를 반환하라.
CREATE TABLE t2(i NUMBER);
INSERT INTO t2 VALUES(NULL);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(2);
INSERT INTO t2 VALUES(3);
SELECT DECODE(i, NULL, 'NULL', 1, 'ONE', 2, 'TWO') revised_i FROM t2;
REVISED_I
-------------
NULL
ONE
TWO
4 rows selected.
<질의> 모든 사원의 현재 급여를 출력하라. emp_job이 'engineer'이면 급여를 10% 인상, 'sales rep'이면 12% 인상, 'manager'이면 20% 인상하며, 그 외의 사원은 실제 급여를 출력하라.
SELECT emp_job
, salary
, DECODE(RTRIM(emp_job, ' '),
'engineer', salary*1.1,
'sales rep', salary*1.12,
'manager', salary*1.20,
salary) revised_salary
FROM employees;
EMP_JOB SALARY REVISED_SALARY
------------------------------------------------
CEO
designer 1500 1500
engineer 2000 2200
engineer 1800 1980
engineer 2500 2750
programmer 1700 1700
manager 500 600
.
.
.
20 rows selected.
DIGEST#
구문#
DIGEST(expr, algorithm_name)
설명#
이 함수는 표준 암호화 해쉬 알고리즘을 사용해서 expr의 해쉬 다이제스트를 VARCHAR타입으로 반환한다. 현재 Altibase에서 지원하는 알고리즘은 SHA-1, SHA-256 및 SHA-512이다.
예제#
<질의> 입력 문자열에 대한 다이제스트를 SHA-1 알고리즘을 사용해서 구하라.
SELECT DIGEST('I am a boy.', 'SHA-1') FROM DUAL;
DIGEST('I am a boy. ', 'SHA-1')
--------------------------------------------
A817613E0B781BBF01816F36A8B0DC7C98B2C0CC
1 row selected.
<질의> 입력 문자열에 대한 다이제스트를 SHA-256과 SHA-512 알고리즘을 사용해서 각각 구하라.
SELECT DIGEST('TEST', 'SHA-256') FROM DUAL;
DIGEST('TEST', 'SHA-256')
--------------------------------------------------------------------
94EE059335E587E501CC4BF90613E0814F00A7B08BC7C648FD865A2AF6A22CC2
1 row selected.
SELECT DIGEST('TEST', 'SHA-512') FROM DUAL;
DIGEST('TEST', 'SHA-512')
------------------------------------------------------------------------------------------------------------------------------------
7BFA95A688924C47C7D22381F20CC926F524BEACB13F84E203D4BD8CB6BA2FCE81C57A5F059BF3D509926487BDE925B3BCEE0635E4F7BAEBA054E5DBA696B2BF
1 row selected.
DUMP#
구문#
DUMP (expr)
설명#
이 함수는 입력된 자료를 분석하여 '자료형, 길이, 내용'의 형식으로 반환한다.
예제
<질의> 사원의 번호와 이름에 입력된 자료형에 관한 정보를 3개만 출력하라.
SELECT DUMP(eno) emp_Number
, DUMP(e_lastname) last_Name
, DUMP(e_firstname) first_Name
FROM employees
LIMIT 3;
EMP_NUMBER LAST_NAME FIRST_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Type=INTEGER(MS949) Length=4: 1,0,0,0 Type=CHAR(MS949) Length=22: 20,0,77,111,111,110,32 Type=CHAR(MS949) Length=22: 20,0,67,104,97,110,45,
,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32 115,101,117,110,103,32,32,32,32,32,32,32,32,32,32
Type=INTEGER(MS949) Length=4: 2,0,0,0 Type=CHAR(MS949) Length=22: 20,0,68,97,118,101,110 Type=CHAR(MS949) Length=22: 20,0,83,117,115,97,110
,112,111,114,116,32,32,32,32,32,32,32,32,32,32,32 ,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
Type=INTEGER(MS949) Length=4: 3,0,0,0 Type=CHAR(MS949) Length=22: 20,0,75,111,98,97,105, Type=CHAR(MS949) Length=22: 20,0,75,101,110,32,32,
110,32,32,32,32,32,32,32,32,32,32,32,32,32,32 32,32,32,32,32,32,32,32,32,32,32,32,32,32,32
3 rows selected.
EMPTY_BLOB, EMPTY_CLOB#
구문#
EMPTY_BLOB()
EMPTY_CLOB()
설명#
이 함수들은 INSERT와 UPDATE에서 사용할 수 있는 함수로써, LOB 칼럼이 초기화되었지만 데이터는 보이지 않는 empty 상태를 의미한다. 따라서 이 함수를 사용한 칼럼은 NOT NULL 상태를 나타낸다.
예제#
CREATE TABLE test (id NUMBER, text CLOB);
INSERT INTO test VALUES (1, NULL);
INSERT INTO test VALUES (2, 'some data is here');
INSERT INTO test VALUES (3, EMPTY_CLOB());
SELECT * FROM test WHERE text IS NULL;
ID TEXT
------------------------------------------------
1
1 row selected.
SELECT * FROM test WHERE text IS NOT NULL;
ID TEXT
------------------------------------------------
2 some data is here
3
2 rows selected.
GREATEST#
구문#
GREATEST (expr1 [, expr2, expr3…])
설명#
이 함수는 입력 표현식들 중에서 가장 큰 값, 즉 알파벳 순으로 정렬했을 때 가장 마지막에 오는 값을 반환한다. 반환형은 VARCHAR 이다.
예제#
<질의> 입력 식들을 알파벳 순으로 정렬했을 때 가장 마지막에 오는 수식을 반환하라.
SELECT GREATEST('HARRY', 'HARRIOT', 'HAROLD') greatest FROM DUAL;
GREATEST
------------
HARRY
1 row selected.
GROUPING#
구문#
GROUPING (expr1)
설명#
이 함수의 인자로 지정한 칼럼의 값이 ROLLUP, CUBE, 또는 GROUPING SETS 연산에 의해 NULL로 반환되는 행의 경우, 이 함수는 1을 반환한다. 즉, 이 함수가 반환하는 값이 1이면, 그 행이 소계를 반환하는 행임을 나타낸다. 그렇지 않은 행일 경우 0이 반환된다.
GROUP BY 절에 속하는 수식만 이 함수의 인자가 될 수 있다.
예제#
GROUPING_ID 함수의 예제를 참고하라.
HASH#
구문#
HASH (expr1 [, expr2])
설명#
해시 값을 구하는 함수이다. 선택적으로 나머지 연산을 수행할 수 있다.
- expr2가 없는 경우는 expr1의 해시 값을 반환한다.
- expr2가 있는 경우에는, expr1의 해시 값을 expr2로 나머지 연산을 수행한 값을 반환한다.
- 반환 값은 BIGINT 데이터 타입이다.
- 동일한 데이터 타입의 동일한 값은 같은 해시 값을 가진다.
- 상이한 데이터 타입의 동일한 값은 서로 다른 해시 값을 가질 수 있다.
예제#
SELECT DUMMY, HASH(DUMMY), HASH('X') FROM DUAL;
DUMMY HASH(DUMMY) HASH('X')
--------------------------------------------------------------------
X 2870066102 2870066102
1 row selected.
SELECT HASH(1), HASH(1,3), MOD(HASH(1),3) FROM DUAL;
HASH(1) HASH(1,3) MOD(HASH(1),3)
-------------------------------------------------------------------
1780082249 2 2
1 row selected.
SELECT HASH(SMALLINT'1'), HASH(BIGINT'1'), HASH(VARCHAR'1') FROM DUAL;
HASH(SMALLINT'1') HASH(BIGINT'1') HASH(VARCHAR'1')
-------------------------------------------------------------------
1780082249 4027148833 424066797
1 row selected.
HOST_NAME#
구문#
HOST_NAME ()
설명#
이 함수는 현재 접속한 호스트의 이름을 반환한다. 최대 길이는 64이며, 반환 데이터 타입은 VARCHAR이다.
예제#
SELECT HOST_NAME() FROM DUAL;
HOST_NAME()
--------------------------------------------------
HDB-NODE1
1 row selected.
GROUPING_ID#
구문#
GROUPING_ID (expr1 [, expr2, expr3…])
설명#
이 함수는 인자로 명시한 각 expr을 조회(SELECT)한 값이 GROUP BY 아래의 ROLLUP, CUBE 또는 GROUPING SETS절에 의해 NULL이 되었는지 여부를 나타내는 비트 문자열에 대응하는 이진수를 10진수 값으로 반환한다. 즉, 각 행에 대해, GROUPING_ID는 인자의 각 expr에 대해 GROUPING 함수를 사용했을 때 반환될 1과 0의 조합으로 2진수를 만들어 그 값을 10진수로 반환한다.
GROUP BY 절에 속하는 수식만 이 함수의 인자가 될 수 있다.
이 함수의 최대 인자수는 60개이다.
예제#
<질의> 그룹화 칼럼 각각의 GROUPING 값과 GROUPING_ID 값을 비교한다.
SELECT dno
, sex
, SUM(salary)
, GROUPING(dno) d
, GROUPING(sex) s
, GROUPING_ID(dno, sex ) ds
FROM employees
GROUP BY CUBE(dno, sex);
DNO SEX SUM(SALARY) D S DS
----------------------------------------------------------------------------------------
31223 1 1 3
1001 F 2300 0 0 0
1001 M 2000 0 0 0
1001 4300 0 1 1
1002 M 2680 0 0 0
1002 2680 0 1 1
1003 F 4000 0 0 0
1003 M 5753 0 0 0
1003 9753 0 1 1
2001 M 1400 0 0 0
2001 1400 0 1 1
3001 M 1800 0 0 0
3001 1800 0 1 1
3002 M 2500 0 0 0
3002 2500 0 1 1
4001 M 3100 0 0 0
4001 3100 0 1 1
4002 F 1890 0 0 0
4002 M 2300 0 0 0
4002 4190 0 1 1
F 1500 0 0 0
1500 0 1 1
F 9690 1 0 2
M 21533 1 0 2
24 rows selected.
INVOKE_USER_ID#
구문#
INVOKE_USER_ID()
설명#
이 함수는 현재 실행하고 있는 사용자의 ID를 반환한다. 반환 데이터 타입은 INTEGER이다.
INVOKE_USER_NAME#
구문#
INVOKE_USER_NAME()
설명#
이 함수는 현재 실행하고 있는 사용자의 이름을 반환한다. 반환 데이터 타입은 VARCHAR이다.
LEAST#
구문#
LEAST(expr1 [, expr2, expr3…])
설명#
이 함수는 입력 표현식들 중에서 가장 작은 값, 즉 알파벳 순으로 정렬했을 때 가장 처음에 오는 값을 반환한다. 반환형은 VARCHAR 이다.
예제#
<질의> 입력 식들을 알파벳 순으로 정렬했을 때 가장 처음에 오는 수식을 반환하라.
SELECT LEAST('HARRY','HARRIOT','HAROLD') least FROM DUAL;
LEAST
-----------
HAROLD
1 row selected.
LNNVL#
구문#
LNNVL (condition)
설명#
이 함수는 아래 표와 같이 조건의 결과가 FALSE 또는 NULL인 경우 TRUE를 반환하고, TRUE인 경우 FALSE를 반환한다.
| 조건의 결과 | LNNVL의 결과 |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| NULL | TRUE |
예제#
<질의> 급여 데이터가 존재하지 않거나 급여가 2000 미만인 사원들의 이름과 급여를 출력하라.
SELECT e_firstname
, e_lastname
, salary
FROM employees
WHERE LNNVL(salary >= 2000);
E_FIRSTNAME E_LASTNAME SALARY
------------------------------------------------------------
Chan-seung Moon
Susan Davenport 1500
Aaron Foster 1800
Ryu Momoi 1700
Gottlieb Fleischer 500
Xiong Wang
Curtis Diaz 1200
Sandra Hammond 1890
Mitch Jones 980
Jason Davenport 1000
Takahiro Fubuki 1400
John Huxley 1900
Alvar Marquez 1800
William Blake
14 rows selected.
MSG_CREATE_QUEUE#
구문#
MSG_CREATE_QUEUE (key)
설명#
이 함수는 명시한 key 값을 가진 메시지 큐를 생성한다. 메시지 큐가 정상적으로 생성되면 0을 반환하며, 같은 key 값을 가진 메시지 큐가 이미 있거나 시스템 콜이 실패하면 1을 반환한다. 반환 값의 데이터 타입은 INTEGER이다.
예제#
<질의> key 값이 1004인 메시지 큐를 생성한다.
SELECT MSG_CREATE_QUEUE(1004) FROM DUAL;
MSG_CREATE_QUEUE(1004)
-----------------------------
0
1 row selected.
MSG_DROP_QUEUE#
구문#
MSG_DROP_QUEUE(key)
설명#
이 함수는 명시한 key값을 가진 메시지 큐를 삭제한다. 메시지 큐가 정상적으로 삭제되면 0을 반환하고, 메시지 큐가 없거나 시스템 콜이 실패하면 1을 반환한다. 반환 값의 데이터 타입은 INTEGER이다.
예제#
<질의> key 값이 1004인 메시지 큐를 삭제한다.
SELECT MSG_DROP_QUEUE(1004) FROM DUAL;
MSG_DROP_QUEUE(1004)
-----------------------------
0
1 row selected.
MSG_SND_QUEUE#
구문#
MSG_SND_QUEUE(key,message)
설명#
이 함수는 명시한 key값을 가진 메시지 큐에 메시지를 전송(Enqueue)한다. 메시지 전송이 성공하면 0을 반환하며, 메시지 크기가 메시지 버퍼(8K)보다 크거나 시스템 콜이 실패하면 1을 반환한다. 반환 값의 데이터 타입은 INTEGER이다.
예제#
<질의> key값이 1004인 메시지 큐에 altibase메시지를 전송한다.
SELECT MSG_SND_QUEUE(1004, VARCHAR'altibase') FROM DUAL;
MSG_SND_QUEUE(1004,VARCHAR'ALTIBASE')
----------------------------------------
0
1 row selected.
MSG_RCV_QUEUE#
구문#
MSG_RCV_QUEUE(key)
설명#
이 함수는 명시한 key값을 가진 메시지 큐의 메시지를 수신(Dequeue)한다. 메시지가 정상적으로 수신되면 메시지를 반환하며, 메시지가 없거나 시스템 콜이 실패하면 NULL을 반환한다. 반환 값의 데이터 타입은 VARBYTE이다.
예제#
<질의> key가 1004인 메시지 큐의 메시지를 수신한다.
SELECT RAW_TO_VARCHAR(MSG_RCV_QUEUE(1004)) FROM DUAL;
RAW_TO_VARCHAR(MSG_RCV_QUEUE(1004))
-----------------------------
altibase
1 row selected.
NULLIF#
구문#
NULLIF (expr1, expr2)
설명#
입력한 expr1과 expr2이 동일하면 NULL을 반환하고, 그렇지 않으면 expr1을 반환한다.
예제#
<질의> 입력한 두 개의 수식이 동일한지 판단하라.
SELECT NULLIF(10, 9) FROM DUAL;
NULLIF(10,9)
---------------
10
1 row selected.
SELECT NULLIF(10, 10) FROM DUAL;
NULLIF(10,10)
----------------
1 row selected.
NVL#
구문#
NVL (expr1, expr2)
설명#
이 함수는 expr1이 NULL이면 expr2를, NULL이 아니면 expr1을 그대로 반환한다.
expr1의 데이터 타입은 DATE, CHAR 및 NUMBER일 수 있다. expr2와 expr1의 데이터 타입이 일치해야 한다.
예제#
<질의> 모든 사원의 이름과 급여를 출력한다. 급여 데이터가 없는 사원에 대해서는 'Unknown'을 출력하라.
SELECT e_firstname
, e_lastname
, NVL(TO_CHAR(salary), 'Unknown')
FROM employees;
E_FIRSTNAME E_LASTNAME NVL(TO_CHAR(SALARY), 'Unknown')
----------------------------------------------------------------------------
Chan-seung Moon Unknown
Susan Davenport 1500
Ken Kobain 2000
.
.
.
20 rows selected.
NVL2#
구문#
NVL2 (expr1, expr2, expr3)
설명#
expr1이 NULL이 아니면 NVL2는 expr2를 반환하고, NULL이면 expr3를 반환한다.
예제#
<질의> 사원의 이름과 급여를 출력하되, 급여 데이터가 있을 경우는 10% 인상된 급여를, 없을 경우는 'Unknown'을 출력하라.
SELECT e_firstname
, e_lastname
, salary
, NVL2(TO_CHAR(salary), TO_CHAR(salary * 1.1), 'Unknown') nvl2_salary
FROM employees;
E_FIRSTNAME E_LASTNAME SALARY NVL2_SALARY
--------------------------------------------------------------------------
Chan-seung Moon Unknown
Susan Davenport 1500 1650
Ken Kobain 2000 2200
Aaron Foster 1800 1980
.
.
.
20 rows selected.
NVL_EQUAL#
구문#
NVL_EQUAL (expr1, expr2, expr3)
설명#
expr1이 NULL이면, expr2와 expr3를 비교한다.
expr1이 NULL이 아니면, expr1과 expr3를 비교한다.
즉, "NVL_EQUAL(expr1, expr2, expr3)"은 "NVL(expr1, expr2) = expr3"과 동치이다.
아래의 예제를 보면, 두 쿼리의 결과는 동일하나 NVL_EQUAL 의 경우 expr1이 인덱스 칼럼이고 expr3이 상수인 경우 인덱스를 사용하는 반면, NVL 함수는 인덱스를 사용하지 않는 차이가 있다.
주의 사항
expr1의 데이터 타입은 DATE, CHAR 및 NUMBER일 수 있으며, expr1, expr2, expr3 의 데이터 타입은 일치해야 한다.
NVL_EQUAL 에서 인덱스를 사용하기 위해서는 expr1이 인덱스 칼럼이어야 하고, expr3은 상수여야 한다.
예제#
아래의 두 쿼리는 동일한 결과를 출력하지만, NVL_EQUAL 경우 인덱스를 이용한다.
ALTER SESSION SET EXPLAIN PLAN = ON;
SELECT e_firstname
, e_lastname
FROM employees
WHERE NVL_EQUAL(TO_CHAR(salary), 'Unknown','Unknown');
E_FIRSTNAME E_LASTNAME
-----------------------------------------------
Chan-seung Moon
Xiong Wang
William Blake
3 rows selected.
ALTER SESSION SET EXPLAIN PLAN = ON;
SELECT e_firstname
, e_lastname
FROM employees
WHERE NVL(TO_CHAR(salary), 'Unknown') = 'Unknown';
E_FIRSTNAME E_LASTNAME
-----------------------------------------------
Chan-seung Moon
Xiong Wang
William Blake
3 rows selected.
NVL_NOT_EQUAL#
구문#
NVL_NOT_EQUAL (expr1, expr2, expr3)
설명#
expr1이 NULL이면, expr2와 expr3를 비교한다.
expr1이 NULL이 아니면, expr1과 expr3를 비교한다.
"NVL_NOT_EQUAL(expr1, expr2, expr3)"은 "NVL(expr1, expr2) != expr3"과 동치이다.
아래의 예제를 보면, 두 쿼리의 결과는 동일하나 NVL_NOT_EQUAL 의 경우 expr1이 인덱스 칼럼이고 expr3이 상수인 경우 인덱스를 사용하는 반면, NVL 함수는 인덱스를 사용하지 않는 차이가 있다.
주의 사항
expr1의 데이터 타입은 DATE, CHAR 및 NUMBER일 수 있으며, expr1, expr2, expr3 의 데이터 타입은 일치해야 한다.
NVL_NOT_EQUAL 에서 인덱스를 사용하기 위해서는 expr1이 인덱스 칼럼이어야 하고, expr3은 상수여야 한다.
예제#
아래의 두 쿼리는 동일한 결과를 출력하지만, NVL_NOT_EQUAL 경우 인덱스를 이용한다.
SELECT e_firstname, e_lastname, birth
FROM employees
WHERE NVL_NOT_EQUAL(birth, 'Unknown', 'Unknown');
_FIRSTNAME E_LASTNAME BIRTH
--------------------------------------------------------
Susan Davenport 721219
Ken Kobain 650226
Aaron Foster 820730
Ryu Momoi 790822
Gottlieb Fleischer 840417
Xiong Wang 810726
Curtis Diaz 660102
Elizabeth Bae 710213
Sandra Hammond 810211
Mitch Jones 801102
Jason Davenport 901212
Wei-Wei Chen 780509
Takahiro Fubuki 781026
13 rows selected.
SELECT e_firstname, e_lastname, birth
FROM employees
WHERE NVL(birth, 'Unknown') != 'Unknown';
E_FIRSTNAME E_LASTNAME BIRTH
--------------------------------------------------------
Susan Davenport 721219
Ken Kobain 650226
Aaron Foster 820730
Ryu Momoi 790822
Gottlieb Fleischer 840417
Xiong Wang 810726
Curtis Diaz 660102
Elizabeth Bae 710213
Sandra Hammond 810211
Mitch Jones 801102
Jason Davenport 901212
Wei-Wei Chen 780509
Takahiro Fubuki 781026
13 rows selected.
RAW_CONCAT#
구문#
RAW_CONCAT (expr1, expr2)
설명#
이 함수는 NULL이 아닌 입력된 복수의 VARBYTE 데이터 타입의 값을 연결하여 반환한다. VARBYTE에 정의할 수 있는 입력 값은 '00'부터 'FF'까지 입력이 가능하다.
예제#
<질의> VARBYTE 타입의 'AABB'와 'CCDD'를 연결한 문자열을 출력하라.
SELECT RAW_CONCAT(VARBYTE'AABB', VARBYTE'CCDD') FROM DUAL;
RAW_CONCAT(VARBYTE'AABB',VARBYTE'CCDD')
-------------------------------------------
AABBCCDD
1 row selected.
SELECT RAW_CONCAT(VARBYTE'AABB', VARBYTE'GGDD') FROM DUAL;
[ERR-21011 : Invalid literal]
RAW_SIZEOF#
구문#
RAW_SIZEOF (expr)
설명#
이 함수는 입력된 expr 에 할당된 데이터 공간의 실제 크기를 반환한다. 모든 데이터 타입의 값을 입력할 수 있으며, 출력 시에는 헤더가 포함된 값을 BYTE 단위로 반환된다. 이 값은 DUMP 함수가 출력하는 길이(length)의 값과 동일하다.
예제#
<질의> 테이블 dual의 칼럼 dummy에 할당된 크기를 출력하라. SIZEOF 함수와 달리 헤더의 2바이트를 포함한다.
SELECT RAW_SIZEOF(DUMMY) FROM DUAL;
RAW_SIZEOF(DUMMY)
--------------------
3
1 row selected.
ROWNUM#
구문#
ROWNUM
설명#
ROWNUM은 의사 레코드 번호(pseudo rownum)를 BIGINT 데이터 타입으로 반환한다. 반환 범위는 1부터 BIGINT형의 최대값이다.
레코드 번호는 테이블이나 뷰에 나타나는 레코드 순서에 따라 부여된다. 그러나, ORDER BY, GROUP BY, HAVING 절이 사용되면 순서가 바뀔 수 있다.
예제#
<질의> 사원 이름순으로 검색하여 앞에서 3명까지만 사원의 사번, 이름, 전화번호, 상태를 출력하라.
SELECT eno, e_lastname, e_firstname, emp_tel
FROM employees
WHERE ROWNUM < 4
ORDER BY e_lastname;
ENO E_LASTNAME E_FIRSTNAME EMP_TEL
---------------------------------------------------------------------------
2 Davenport Susan 0113654540
3 Kobain Ken 0162581369
1 Moon Chan-seung 01195662365
3 rows selected.
SENDMSG#
구문#
SENDMSG ( VARCHAR ipaddr,
INTEGER port,
VARCHAR msg,
INTEGER ttl )
설명#
SENDMSG는 사용자 메시지를 지정된 IP 주소와 포트 번호에 Socket datagram으로 전송한다. 일반 IP 주소를 입력하면 UDP datagram을 전송하며, Multicast IP 주소를 입력하면 Multicast datagram을 전송한다.
Multicast IP 주소는 예약된 Multicast group을 제외한 225.0.0.0 ~ 238.0.0.255로 제한된다.
사용가능한 포트 번호는 1025에서 65535까지이다.
msg는 최대 2048바이트를 넘을 수 없다.
ttl은 TimeToLive의 준말로, 이 인자는 Multicast IP 주소로 메시지 전송시 유용하다. 이는 아래와 같이 멀티캐스트 전송시 전송 범위를 제한한다. 가능한 ttl 값의 범위는 0에서 255 까지이다.
| TTL | 범위 |
|---|---|
| 0 | 호스트 내부로 제한되어, 네트워크 인터페이스를 지나서 출력되지 않음. |
| 1 | 동일 서브넷으로 제한되어, 라우터를 넘어서 포워딩하지 않음. |
| < 32 | 동일 사이트(SITE)로 제한, 조직이나 부서로 제한됨. |
| < 64 | 동일 지역(Region)으로 제한. |
| < 128 | 동일 대륙으로 제한. |
| ≤ 255 | 무제한, 전세계. |
반환 값은 전송된 메시지의 길이를 나타내는 INTEGER형이다.
예제#
<질의> 일반 IP 주소로 사용자 메시지를 전송하라 (이 경우, ttl 값은 무시된다).
SELECT SENDMSG( '192.168.1.60', 12345, 'THIS IS A MESSAGE', 1 )
FROM T1;
SENDMSG( '192.168.1.60', 12345, 'THIS IS
-------------------------------------------
17
1 row selected.
<질의> Multicast IP 주소로 사용자 메시지를 전송하라 (이 경우, ttl 값이 적용된다).
SELECT SENDMSG( '192.168.1.60', 12345, 'THIS IS A MESSAGE', 0 )
FROM T1;
SENDMSG( '192.168.1.60', 12345, 'THIS IS
-------------------------------------------
17
1 row selected.
USER_ID#
구문#
USER_ID()
설명#
이 함수는 현재 접속한 사용자의 ID를 반환한다. 반환 데이터 타입은 INTEGER이다.
예제#
<질의> 현재 접속한 사용자의 모든 테이블 정보를 조회하라.
SELECT TABLE_NAME
FROM SYSTEM_.SYS_TABLES_
WHERE USER_ID = USER_ID();
USER_LOCK_REQUEST#
구문#
USER_LOCK_REQUEST(INTEGER id)
설명#
사용자가 파라미터에 해당 id를 명시하여 잠금(lock)을 요청하는 함수이다. id 파라미터에 입력 가능한 값은 0 ~ 1073741823이며, 반환 값은 아래와 같다. 반환 데이터 타입은 INTEGER이다.
-
0: 성공
-
1: 타임아웃
-
3: 파라미터 에러
-
4: 동일 ID로 사용자 잠금 요청이 성공한 상태
예제#
<질의> ID가 5인 사용자 잠금을 요청한다.
SELECT USER_LOCK_REQUEST( 5 ) FROM DUAL;
USER_LOCK_REQUEST( 5 )
-------------------------
0
1 row selected.
USER_LOCK_RELEASE#
구문#
USER_LOCK_RELEASE(INTEGER id)
설명#
사용자가 파라미터에 해당 id를 명시하여 잠금(lock)을 해제하는 함수이다. 세션에 해당하는 사용자 잠금을 갖고 있지 않은 경우, 해제할 수 없다.
id 파라미터에 입력 가능한 값은 0 ~ 1073741823이며, 반환 값은 아래와 같다. 반환 데이터 타입은 INTEGER이다.
-
0: 성공
-
3: 파라미터 에러
-
4: 보유하지 않은 사용자 잠금 해제 불가
예제#
<질의> ID가 5인 사용자 잠금을 해제한다.
SELECT USER_LOCK_RELEASE( 5 ) FROM DUAL;
USER_LOCK_RELEASE( 5 )
-------------------------
0
1 row selected.
USER_NAME#
구문#
USER_NAME()
설명#
이 함수는 현재 접속한 사용자의 이름을 반환한다. 반환 데이터 타입은 VARCHAR이다.
예제#
<질의> 현재 접속한 사용자의 이름을 조회하라.
SELECT USER_NAME(), USER_ID() FROM DUAL;
SESSION_ID#
구문#
SESSION_ID()
설명#
이 함수는 현재 접속한 사용자의 세션 식별자를 반환한다. 반환 데이터 타입은 INTEGER이다.
예제#
<질의> 현재 접속한 사용자가 사용중인 캐릭터셋을 조회하라.
SELECT CLIENT_NLS FROM V$SESSION WHERE ID = SESSION_ID();
CLIENT_NLS
--------------------------------------------
US7ASCII
1 row selected.
SUBRAW#
구문#
SUBRAW (expr1, start [, length])
설명#
SUBRAW 함수는 입력한 expr 문자열에서 start번째의 byte부터 length 길이만큼의 byte열을 반환한다.
start는 expr 내에서 탐색을 시작하는 위치를 가리킨다. 기본 start 값은 1, 즉 첫 번째 문자이다. 0으로 지정하면 1을 지정한 것처럼 처리된다. 음수이면 expr 의 맨 끝에서부터 찾기 시작한다.
start 값이 expr 의 길이보다 크면 에러가 발생하며, length 값을 생략하면 문자열이 끝까지 반환된다.
BYTE 또는 VARBYTE 데이터 타입으로 입력할 수 있으며, 반환 값의 데이터 타입은 VARBYTE이다.
예제#
<질의> 입력한 문자열 'AABBCC'의 첫 번째부터 2 byte 만큼의 문자열을 출력한다.
SELECT SUBRAW(VARBYTE'AABBCC', 1, 2) FROM DUAL;
SUBRAW(VARBYTE'AABBCC',1,2)
-------------------------------
AABB
1 row selected.
SYS_CONNECT_BY_PATH#
구문#
SYS_CONNECT_BY_PATH(column, delimiter)
설명#
최상위 노드에서 현재 노드까지의 칼럼 값 경로를 구하는 함수이다. 함수 인자 delimiter에 구분자를 지정할 수 있다.
반환 데이터 타입은 VARCHAR(32000)이다.
예제#
<질의> 아래는 루트로부터 각 id에 이르는 경로를 구하는 예제이다. 출력 결과의 가독성을 높이기 위해 CAST 연산자를 사용하였다.
CREATE TABLE hier_order( id INTEGER, parent INTEGER );
INSERT INTO hier_order VALUES(0, NULL);
INSERT INTO hier_order VALUES(1, 0);
INSERT INTO hier_order VALUES(2, 1);
INSERT INTO hier_order VALUES(3, 1);
INSERT INTO hier_order VALUES(4, 1);
INSERT INTO hier_order VALUES(5, 0);
INSERT INTO hier_order VALUES(6, 0);
INSERT INTO hier_order VALUES(7, 6);
INSERT INTO hier_order VALUES(8, 7);
INSERT INTO hier_order VALUES(9, 7);
INSERT INTO hier_order VALUES(10, 6);
SELECT ID
, CAST(SYS_CONNECT_BY_PATH(ID, '/') AS VARCHAR(20)) "PATH"
FROM HIER_ORDER START WITH ID = 0 CONNECT BY PRIOR ID = PARENT
ORDER BY ID;
ID Path
-------------------------------------
0 /0
1 /0/1
2 /0/1/2
3 /0/1/3
4 /0/1/4
5 /0/5
6 /0/6
7 /0/6/7
8 /0/6/7/8
9 /0/6/7/9
10 /0/6/10
11 rows selected.
SYS_CONTEXT#
구문#
설명#
이 함수는 현재 세션에 접속한 환경 정보(context)를 namespace로 하여 관련된 파라미터의 결과값을 반환한다
파라미터#
| 파라미터 | 결과값 |
|---|---|
| CLIENT_INFO | 사용자 세션 정보를 128바이트까지 반환 |
| IP_ADDRESS | 연결된 IP 주소 |
| ISDBA | DBA 권한의 사용자인지 여부 [TRUE | FALSE] |
| LANGUAGE | 사용자 세션에서 사용중인 언어 |
| NLS_CURRENCY | 현재 세션의 화폐 |
| NLS_DATE_FORMAT | 현재 세션의 날짜 형식 |
| NLS_TERRITORY | 현재 세션의 지역 |
| ACTION | DBMS_APPLICATION_INFO 패키지로 설정된 모듈의 상태 |
| CURRENT_SCHEMA | 현재 스키마에서 사용중인 기본 스키마의 이름 |
| DB_NAME | DB_NAME으로 지정한 이름 |
| HOST | 클라이언트에 연결된 호스트 이름 |
| INSTANCE | 현재 인스턴스의 ID 숫자 |
| MODULE | DBMS_APPLICATION_INFO 패키지로 설정한 모듈 이름 |
| SESSION_USER | 로그온 할 때 데이터베이스 사용자의 이름 |
| SID | 세션 ID |
예제#
SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
SYS_CONTEXT('USERENV','SID')
-------------------------------------------------------
87
SYS_GUID#
구문#
SYS_GUID()
설명#
이 함수는 16바이트로 구성된 전역적으로 고유한 식별자를 생성하여 길이가 16인 16진수 문자열로 반환한다. 반환 데이터 타입은 BYTE이다.
예제#
SELECT SYS_GUID() FROM DUAL;
SYS_GUID()
------------------------------------
080027253126D13B69F892D46508FD9D
1 row selected.
SELECT DUMP(SYS_GUID()) FROM DUAL;
DUMP(SYS_GUID())
------------------------------------------------------------------------------------------------------------------------------
Type=BYTE(UTF8) Length=18: 16,0,8,0,39,37,49,38,209,59,105,248,146,214,101,8,253,157
1 row selected.
SYS_GUID_STR#
구문#
SYS_GUID_STR()
설명#
이 함수는 16바이트로 구성된 전역적으로 고유한 식별자를 생성하여 길이가 32인 16진수 문자열로 반환한다. 반환 데이터 타입은 VARCHAR이다.
예제#
SELECT SYS_GUID_STR() FROM DUAL;
SYS_GUID_STR
------------------------------------
A8C09B011C02856E092284D95091D27A
1 row selected.
SELECT DUMP(SYS_GUID_STR()) FROM DUAL;
DUMP(SYS_GUID_STR())
--------------------------------------------------------------------------------------------------------
Type=VARCHAR(KSC5601) Length=34: 32,0,65,56,67,48,57,66,48,49,49,67,48,50,56,53,54,69,48,57,50,50,56,52,68,67,53,48,57,49,68,50,55,65
1 row selected.
QUOTE_PRINTABLE_DECODE#
구문#
QUOTE_PRINTABLE_DECODE(expr)
설명#
이 함수는 Quoted printable형태로 변환된 VARBYTE 데이터 타입의 문자열을 디코딩하여 VARBYTE 데이터 타입의 데이터로 반환한다.
예제#
SELECT QUOTED_PRINTABLE_DECODE(VARBYTE'3D4142') FROM DUAL;
QUOTED_PRINTABLE_DECODE(VARBYTE'3D4142')
-----------------------------
AB
1 row selected.
QUOTE_PRINTABLE_ENCODE#
구문#
QUOTE_PRINTABLE_ENCODE(expr)
설명#
이 함수는 VARBYTE 데이터 타입의 문자열을 Quoted printable형태로 변환하여 VARBYTE 타입으로 반환한다.
예제#
SELECT QUOTED_PRINTABLE_ENCODE(VARBYTE'ABCD') FROM DUAL;
QUOTED_PRINTABLE_ENCODE('ABCD'
-----------------------------
3D41423D4344
1 row selected.
중첩 함수#
설명#
단일 행 함수는 여러 번 중첩될 수 있다. 중첩 함수는 가장 안쪽부터 시작해서 바깥쪽으로 계산 된다.
예제#
<질의> 각 사원이 입사한 날로부터 여섯 달이 경과한 뒤 첫 번째 월요일의 날짜를 입사일 순으로 출력하라.
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(join_date, 6), 'MONDAY'), 'DD-Mon-YYYY') monday_six_months
FROM employees
ORDER BY join_date;
MONDAY_SIX_MONTHS
--------------------------------------
26-Jul-2004
21-May-2007
05-May-2008
24-May-2010
.
.
.
20 rows selected.