Database/Oracle2009. 2. 18. 19:09

4. 기타함수(miscellaneous single row function)
 4-1) BFILENAME 함수
 4-2) COALESCE 함수
 4-3) DECODE 함수
 4-4) DEPTH 함수
 4-5) DUMP 함수
 4-6) EMPTY_BLOB 함수
 4-7) EMPTY_CLOB 함수
 4-8) EXISTSNODE 함수
 4-9) EXTRACT(XML) 함수
 4-10) EXTRACTVALUE 함수
 4-11) GREATEST 함수
 4-12) LEAST 함수
 4-13) NLS_CHARSET_DECL_LEN 함수
 4-14) NLS_CHARSET_ID 함수
 4-15) NLS_CHARSET_NAME 함수
 4-16) NULLIF 함수
 4-17) NVL2 함수
 4-18) PATH 함수
 4-19) SYS_CONNECT_BY_PATH 함수
 4-20) SYS_CONTEXT 함수
 4-21) SYS_DBURIGEN 함수
 4-22) SYS_EXTRACT_UTC 함수
 4-23) SYS_GUID 함수
 4-24) SYS_XMLAGG 함수
 4-25) SYS_XMLGEN 함수
 4-26) UID 함수
 4-27) USER 함수
 4-28) USERENV 함수
 4-29) VSIZE 함수
 4-30) XMLAGG 함수
 4-31) XMLCOLATTVAL 함수
 4-32) XMLCONCAT 함수
 4-33) XMLFOREST 함수
 4-34) XMLELEMENT 함수
 
4-1) BFILENAME 함수
--------------------------------------------------------------------------------
 서버 파일 시스템 상에 실제로 위치한 LOB 바이너리 파일의 위치한 BFILE locator를 반환한다.
【형식】
bfilename ('디렉토리‘,’파일이름‘)
【예제】BFILE을 insert하는 예
SQL> connect system/manager

SQL> host mkdir /export/home/oracle/bfile

SQL> create directory bfile_dir as '/export/home/oracle/bfile';
Directory created.

SQL> grant read on directory bfile_dir to jijoe;
Grant succeeded.

SQL> connect jijoe/joe_password

SQL> create table bfile_doc (id number, doc bfile);

SQL> insert into bfile_doc
   1    values(1111,bfilename('bfile_dir','unix.hwp'));
1 row created.

4-2) COALESCE 함수
--------------------------------------------------------------------------------
이 함수는 나열된 값 중에서 NULL이 아닌 첫 번째 값을 반환한다. 
【예제】
SQL> select coalesce('','','arirang','kunsan') from dual;
COALESC
-------
arirang


4-3) DECODE 함수
--------------------------------------------------------------------------------
DECODE는 일반적인 프로그래밍 언어의 IF문을 SQL 문자 또는 PL/SQL 안으로 끌어들여 사용하기 위하여 만들어진 오라클 함수이다.
따라서 일반 프로그래밍 언어의 IF문이 수행 할 수 있는 기능을 포함하고 있다.
  select시의 decode 사용은 from 절만 빼고 어디에서나 사용할 수 있다.
  일반 프로그래밍과 decode 함수를 서로 비교하여 보자.
IF문 Decode 함수 IF A=B THEN RETURN 'T';END IF; DECODE(A,B,'T') IF A=B THENRETURN 'T';ELSIF A=C THENRETURN 'F';ELSERETURN 'X';END IF; DECODE(A,B,'T',C,'F','X')
【형식】
DECODE(검색컬럼,조건1,결과값1,조건2,결과값2,...,기본값);
【예제】
SQL> connect jijoe/jijoe_password

SQL> create table aa(
  2  pid        number(12) primary key,
  3  addr varchar2(20),
  4  name varchar2(10));

SQL> insert into aa values(1234,'kunsan','jijoe')

SQL> insert into aa values(3456,'seoul','sunny')

SQL> select * from aa;
       PID ADDR                 NAME
---------- -------------------- ----------
      1234 kunsan               jijoe
      3456 seoul                sunny

SQL> select decode(pid,1234,name) name from aa;
NAME
----------
jijoe

【예제】
SQL> desc ddd
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NO                                                 NUMBER(4)
 NAME                                               VARCHAR2(10)
 HIRDATE                                            DATE
 DEPTNO                                             NUMBER(5)

SQL> select * from ddd;
        NO NAME       HIRDATE       DEPTNO
---------- ---------- --------- ----------
         1 student1   01-JAN-04         10
         2 student2   01-FEB-04         10
         3 student3   01-MAR-04         20
         4 student4   01-MAY-04         30

SQL> select count(decode(to_char(hirdate,'MM'),'01',1)) "JAN",
  2         count(decode(to_char(hirdate,'MM'),'02',1)) "FEB",
  3         count(decode(to_char(hirdate,'MM'),'03',1)) "MAR",
  4         count(*) "Total"
  5  from ddd
  6  where to_char(hirdate,'MM') >= '01' AND
  7        to_char(hirdate,'MM') <= '06';
       JAN        FEB        MAR      Total
---------- ---------- ---------- ----------
         1          1          1          4

 
4-4) DEPTH 함수
--------------------------------------------------------------------------------
DEPTH( correlation_integer) 함수는 UNDER_PATH나 EQUALS_PATH 조건과 함께 사용되는 보조함수이다.
 이 함수는 UNDER_PATH 조건에 상관관계의 수치를 반환한다.
【예제】
SQL> select * from resource_view;

SQL> select path(1), depth(2)
  2  from resource_view
  3  where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1
  4    and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;
PATH(1)                                      DEPTH(2)
------------------------------------------ ----------
/xml.xsd                                            1

 
4-5) DUMP 함수
--------------------------------------------------------------------------------
지정한 데이터의 위치와 길이 따위를 지정한 형식으로 반환한다.
【형식】
DUMP(expr [,반환형식[,시작위치[,길이]]] )
【예제】
SQL> select dump('Corea', 1016) from dual;
DUMP('COREA',1016)
-----------------------------------------------------
Typ=96 Len=5 CharacterSet=KO16KSC5601: 43,6f,72,65,61

SQL> select dump('Corea', 8,3,2) "Octal" from dual;
Octal
---------------------
Typ=96 Len=5: 162,145

SQL> select dump('Corea',16,3,2) "ASCII" from dual;
ASCII
-------------------
Typ=96 Len=5: 72,65

 
4-6) EMPTY_BLOB 함수
--------------------------------------------------------------------------------
EMPTY_BLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,
 또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.
【예시】
UPDATE print_media SET ad_photo = EMPTY_BLOB();

 
4-7) EMPTY_CLOB 함수
--------------------------------------------------------------------------------
EMPTY_CLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,
 또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.
【예시】
UPDATE print_media SET ad_photo = EMPTY_CLOB();

 
4-8) EXISTSNODE 함수
--------------------------------------------------------------------------------
이 함수는 node의 존재여부를 확인하여 그 결과를 반환한다.
 0은 노드가 남아 있지 않은 경우이고,
 1은 아직 노드가 존재하는 경우이다.
【형식】
EXISTSNODE(XMLType_instance, XPath_string [,namespace_string] )
【예제】
SQL> select * from resource_view;

SQL> select res,any_path
  2  from resource_view
  3  where existsnode(res, 'xdbconfig.xml') =0;
26 rows selected.


4-9) EXTRACT(XML) 함수
--------------------------------------------------------------------------------
이 함수는 existsnode와 유사한 함수이다.
【형식】
EXTRACT(XMLType_instance, XPath_string [,namespace_string] )
【예제】
SQL> select * from resource_view;

SQL> select extract(res,'xdbconfig.xml')
  2  from resource_view;
 
26 rows selected.
 

4-10) EXTRACTVALUE 함수
--------------------------------------------------------------------------------
이 함수는 existsnode와 유사한 함수로써 node의 스칼라 값을 반환한다.
【형식】
EXTRACTVALUE(XMLType_instance, XPath_string [,namespace_string] )
【예제】
SQL> select * from resource_view;

SQL> select extractvalue(res,'xdbconfig.xml')
  2  from resource_view;
 
26 rows selected.
 
4-11) GREATEST 함수
--------------------------------------------------------------------------------
GREATEST (expr,...) 함수는 expr 중에서 가장 큰 값을 반환한다.
【예제】
SQL> select greatest(20,10,30) from dual;
GREATEST(20,10,30)
------------------
                30

 
4-12) LEAST 함수
--------------------------------------------------------------------------------
LEAST (expr,...) 함수는 expr 중에서 가장 작은 값을 반환한다.
【예제】
SQL> select least(20,10,30) from dual;
GREATEST(20,10,30)
------------------
                10

SQL> select least('bb','aa','cc') from dual;
GR
--
aa


4-13) NLS_CHARSET_DECL_LEN 함수
--------------------------------------------------------------------------------
NLS_CHARSET_DECL_LEN (byte_count , char_set_id) 함수는 nchar로 선언된 폭을 반환한다.
【예제】
SQL> select nls_charset_decl_len
  2  (200, nls_charset_id('ja16eucfixed')) from dual
NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED'))
--------------------------------------------------------
                                                     100


4-14) NLS_CHARSET_ID 함수
--------------------------------------------------------------------------------
nls_charset_id('text') 함수는 문자셋 이름에 대응하는 ID 번호를 반환한다.
 여기서 text는 서버에서 지원되는 CHAR_CS나 NCHAR_CS이다.
【예제】
SQL> select nls_charset_id('ja16euc') from dual;
NLS_CHARSET_ID('JA16EUC')
-------------------------
                      830

 
4-15) NLS_CHARSET_NAME 함수
--------------------------------------------------------------------------------
nls_charset_name('number') 함수는 문자섹 ID 번호에 대응하는 문자의 이름을 반환한다.
【예제】
SQL> select nls_charset_name(830) from dual;
NLS_CHA
-------
JA16EUC

SQL> select nls_charset_name(1) from dual;
NLS_CHAR
--------
US7ASCII

4-16) NULLIF 함수
--------------------------------------------------------------------------------
NULLIF(expr1, expr2) 함수는 expr1과 expr2를 비교하여 
같으면 null을 반환하고, 같지 않으면 expr1을 반환한다.
이는 CASE 문으로 쓰면 다음과 같다.
  CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
【예제】
SQL> select nullif('aa','AA') from dual;
NU
--
aa

SQL> select nullif('aa','aa') from dual;
NU
--


4-17) NVL2 함수
--------------------------------------------------------------------------------
NVL2(expr1, expr2, expr3) 함수는
   expr1이 null이 아니면 expr2를 반환하고,
   expr1이 null이면 expr3을 반환한다.
【예제】
SQL> select nvl2('','Corea','Korea') from dual;
NVL2(
-----
Korea

SQL> select nvl2('aa','Corea','Korea') from dual;
NVL2(
-----
Corea

4-18) PATH 함수
--------------------------------------------------------------------------------
PATH(correlation_path) 함수는 under_path나 equals_path의 보조함수로서,
 자원의 관계경로를 반환한다.
【예제】
SQL> select * from resource_view;

SQL> select path(1), depth(2)
  2  from resource_view
  3  where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1?
  4    and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;
PATH(1)                                      DEPTH(2)
------------------------------------------ ----------
/xml.xsd                                            1


4-19) SYS_CONNECT_BY_PATH 함수
--------------------------------------------------------------------------------
SYS_CONNECT_BY_PATH(column, char) 함수는 계층적 쿼리에서만 유효하며,
 column의 절대 경로를 char로 지정한 문자로 분리하여 반환한다.
【예제】
SQL> select sys_connect_by_path(name, '/') from emp
  2  start with name='jijoe'
  3  connect by prior id=1101;
SYS_CONNECT_BY_PATH(NAME,'/')
-----------------------------
/jijoe

SQL> select * from emp;
        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

 
4-20) SYS_CONTEXT 함수
--------------------------------------------------------------------------------
 이 함수는 namespace와 관계되는 parameter의 값을 반환한다.
【형식】
SYS_CONTEXT('namespace','parameter' [,length])
【예제】
SQL> select sys_context('userenv','session_user') from dual;
SYS_CONTEXT('USERENV','SESSION_USER')
-------------------------------------
JIJOE

SQL> select sys_context('userenv','lang') from dual;
SYS_CONTEXT('USERENV','LANG')
-----------------------------
US

SQL>
 userenv에서 사용될 parameter는 다음과 같다.
AUDITED_CURSORID AUTHENTICATION_DATA BG_JOB_ID
CLIENT_IDENTIFIER CLIENT_INFO  CURRENT_SCHEMA
CURRENT_SCHEMAID CURRENT_SQL  CURRENT_USER
CURRENT_USERID  DB_DOMAIN  DB_NAME
ENTRY_ID  EXTERNAL_NAME  FG_JOB_ID
GLOBAL_CONTEXT_MEMORY HOST   INSTANCE
IP_ADDRESS  ISDBA   LANG
LANGUAGE  NETWORK_PROTOCOL NLS_CALENDAR
NLS_CURRENCY  NLS_DATE_FORMAT  NLS_DATE_LANGUAGE
NLS_SORT  NLS_TERRITORY  OS_USER
PROXY_USER  PROXY_USERID  SESSION_USER
SESSION_USERID  SESSIONID  TERMINAL
 

4-21) SYS_DBURIGEN 함수
--------------------------------------------------------------------------------
이 함수는 입력된 argument에 대한 DBURIType의 URL을 반환한다.
【형식】
SYS_DBURIGEN({column|attribute} [rowid],... [,'text()'])
【예제】
SQL> select sys_dburigen(id,name) from emp
  2  where name='jijoe';
SYS_DBURIGEN(ID,NAME)(URL, SPARE)
------------------------------------------------------------------------
DBURITYPE('/PUBLIC/EMP/ROW[ID=''1104'']/NAME', NULL)

SQL> select * from emp;
        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100

 
4-22) SYS_EXTRACT_UTC 함수
--------------------------------------------------------------------------------
sys_extract_utc(datetime_with_timezone) 함수는
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.
【예제】
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM

 
4-23) SYS_GUID 함수
--------------------------------------------------------------------------------
sys_guid() 함수는 globally unique identifier를 반환한다.
【예제】
SQL> select sys_guid() from dual;
SYS_GUID()
--------------------------------
E0F6C6D5767C01ADE034080020B588F4
 
 
4-24) SYS_XMLAGG 함수
--------------------------------------------------------------------------------
이 함수는 sys_xmlgen 문에서 만든 XML 문을 기본적으로 ROWSET 태그를 새로 추가 시킨다.
【형식】
SYS_XMLAGG( expr [fmt] )
【예제】
SQL> select sys_xmlagg(sys_xmlgen(name)) from emp
  2  where name like 'j%';
SYS_XMLAGG(SYS_XMLGEN(NAME))
--------------------------------------------------------------------------
  jijoe
 
 
4-25) SYS_XMLGEN 함수
--------------------------------------------------------------------------------
이 함수는 지정한 행이나 열을 XML 문으로 만들어 반환한다.
【형식】
SYS_XMLGEN( expr [fmt] )
【예제】
SQL> select sys_xmlgen(name) from emp
  2  where name like 'j%';
SYS_XMLGEN(NAME)
--------------------------------------------------------------------------
jijoe

 
4-26) UID 함수
--------------------------------------------------------------------------------
UID 함수는 사용자의 유일한 ID를 정수로 반환한다.
【예제】
SQL> select uid from dual;
       UID
----------
        93

 
4-27) USER 함수
--------------------------------------------------------------------------------
이 함수는 사용자의 이름을 반환한다.
【예제】
SQL> select user,uid from dual;
USER                                  UID
------------------------------ ----------
JIJOE                                  93
 

4-28) USERENV 함수
--------------------------------------------------------------------------------
USERENV('parameter') 함수는 사용자의 환경에 관한 정보를 반환한다.
 parameter는 다음과 같은 것이 있다.
CLIENT_INFO ENTRYID  ISDBA  LANG
LANGUAGE SESSIONID TERMINAL
【예제】
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.KO16KSC5601
 
 
4-29) VSIZE 함수
--------------------------------------------------------------------------------
VSIZE('expr') 함수는 expr이 표시되는 바이트 수를 반환한다.
【예제】
SQL> select name, vsize(name) from emp
  2  where name like 'jijoe';
NAME       VSIZE(NAME)
---------- -----------
jijoe                5

$ cat .profile
..........
NLS_LANG=AMERICAN_AMERICA.KO16KSC5601  ☜ 한글 문자셋으로 설정
export NLS_LANG
NLS_LANG=AMERICAN_AMERICA.UTF8  ☜ UNICODE로 설정
export NLS_LANG
$

테이블에서 저장된 데이터가 한글 문자셋인지 유니코드인지 식별하려면 다음과 같이 확인해 볼 수 있다.
    select 한글컬럼명, vsize(한글컬럼명) from 테이블명;
여기서 한글 컬럼에 한글이 3글자라면, vsize 결과가 9이면 unicode이고,
    6이면 한글 문자셋으로 저장된 것임을 알 수 있다.
【예제】
SQL> select * from test;
 
        ID NAME
---------- ----------------------------------------
      1113 아리랑
      1112 쓰리랑
 
SQL> select name, vsize(name) from test;
 
NAME                                     VSIZE(NAME)
---------------------------------------- -----------
아리랑                                             6
쓰리랑                                             6


4-30) XMLAGG 함수
--------------------------------------------------------------------------------
이 함수는 xmlelement에 의해서 XML 태그를 만든 문장을 모으는 기능이다.
【형식】
XMLAGG( XMLType_instance [order_by_clause])
【예제】
 SQL> select xmlagg(xmlelement("name",e.name)) from emp e;
XMLAGG(XMLELEMENT("NAME",E.NAME))
--------------------------------------------------------------------------
Cho
Joe
kim
jijoe
SQL>
 

4-31) XMLCOLATTVAL 함수
--------------------------------------------------------------------------------
이 함수는 XML fragment를 만드는 기능이다
【형식】
XMLCOLATTVAL( value_expr [AS c_alias],...)
【예제】
SQL> select xmlcolattval(e.name,e.id,e.salary) from emp e;
XMLCOLATTVAL(E.NAME,E.ID,E.SALARY)
--------------------------------------------------------------------------
Cho
1101
Joe
1102
kim
1103
jijoe
1104
 
SQL> select * from emp;
        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100


4-32) XMLCONCAT 함수
--------------------------------------------------------------------------------
XMLCONCAT( XMLType_instance,...) 함수는 XMLType instance를 series로 넣어 만드는 기능이다.
【예제】
SQL> select xmlconcat(
  2    xmlelement("name",e.name),xmlelement("bonus",e.bonus))
  3  from emp e;
XMLCONCAT(XMLELEMENT("NAME",E.NAME),XMLELEMENT("BONUS",E.BONUS))
--------------------------------------------------------------------------
Cho
125
Joe
100
kim
100
jijoe
100

 
4-33) XMLFOREST 함수
--------------------------------------------------------------------------------
이 함수는 각각의 argument parameter를  XML로 변환한다.
【형식】
XMLFOREST( value_expr [AS c_alias],...)
【예제】
SQL> select xmlelement("emp",
  2  xmlforest(e.id, e.name, e.bonus)) from emp e;
XMLELEMENT("EMP",XMLFOREST(E.ID,E.NAME,E.BONUS))
--------------------------------------------------------------------------
  1101
  Cho
  125
 
  1102
  Joe
  100
 
  1103
  kim
  100
 
  1104
  jijoe
  100

SQL> select * from emp;
        ID NAME           SALARY      BONUS
---------- ---------- ---------- ----------
      1101 Cho               250        125
      1102 Joe               240        100
      1103 kim               250        100
      1104 jijoe             220        100
 
 
4-34) XMLELEMENT 함수
--------------------------------------------------------------------------------
이 함수는 XML 태그를 붙이는 기능이다
【예제】
SQL> select xmlelement("name",e.name) from emp e
  2  where name like 'j%';
XMLELEMENT("NAME",E.NAME)
--------------------------------------------------------------------------
jijoe
저작자표시
Posted by Huikyun

티스토리툴바