Extract 함수

1. 개요 : 오라클에서 제공해주는 함수로 날짜(date), xml 타입의 데이터에서 특정값을 뽑아내는 함수 2. extract(datetime) EXTRACT( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE | TIMEZONE_REGION | TIMEZONE_ABBR } FROM { expr } ) 예제 :

SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;

SELECT EXTRACT (MONTH FROM DATE '2012-04-13') FROM DUAL;

SELECT EXTRACT (DAY FROM DATE '2012-04-13') FROM DUAL;

3. extract(xml) EXTRACT(XMLType_instance, XPath_string [, namespace_string ]) 예제 :

SELECT warehouse_name,
       EXTRACT(warehouse_spec, '/Warehouse/Docks') "Number of Docks"
  FROM warehouses
  WHERE warehouse_spec IS NOT NULL
  ORDER BY warehouse_name;

WAREHOUSE_NAME            Number of Docks
------------------------- -------------------------
New Jersey
San Francisco             <Docks>1</Docks>
Seattle, Washington       <Docks>3</Docks>
Southlake, Texas          <Docks>2</Docks>

-- 참고 함수 : extractvalue 
SELECT warehouse_name, EXTRACTVALUE(e.warehouse_spec, '/Warehouse/Docks') "Docks"
  FROM warehouses e 
  WHERE warehouse_spec IS NOT NULL
  ORDER BY warehouse_name;

WAREHOUSE_NAME       Docks
-------------------- ------------
New Jersey
San Francisco        1
Seattle, Washington  3
Southlake, Texas     2