SYSTIMESTAMP()
RETURNS timestamptz;

SELECT SYSTIMESTAMP();

         systimestamp          
-------------------------------
 2025-03-06 23:41:29.160617+09
(1 row)

SESSIONTIMEZONE()
RETURNS text;

select sessiontimezone();

 sessiontimezone 
-----------------
 Asia/Seoul
(1 row)

{schema_name}.{function_name}

-- 예시) o2views extension 설치 후, 새로 추가된 sysdate라는 함수 사용
-- select oracle.sysdate();

SYSDATE()
RETURN TIMESTAMP;

select sysdate();

       sysdate       
---------------------
 2025-03-06 22:39:05
(1 row)

-- 현재 접속 세션에서 스키마 'oracle'를 가장 우선순위 높은 search_path로 설정
set search_path to oracle, public;

{function_name}

-- 예시) o2views extension 설치 후, 새로 추가된 sysdate라는 함수 사용
-- select sysdate();

LAST_DAY
(
  value  IN date
)
RETURNS date;

LAST_DAY
(
  value  IN TIMESTAMP with time zone
)
RETURNS TIMESTAMP without time zone;

-- DATE 타입 예제: '2023-05-15'가 속한 달의 마지막 날짜 반환
SELECT LAST_DAY('2023-05-15'::date);
-- 결과: '2023-05-31' (2023년 5월의 마지막 날)

  last_day  
------------
 2023-05-31
(1 row)

-- TIMESTAMPTZ 타입 예제: '2023-05-15 14:30:00+09'가 속한 달의 마지막 날짜 반환
SELECT LAST_DAY('2023-05-15 14:30:00+09'::timestamptz);
-- 결과: 타임스탬프 값으로 해당 달의 마지막 날과 원래 시간 정보가 결합되어 반환됨

      last_day       
---------------------
 2023-05-31 14:30:00
(1 row)

COSH(num)

SELECT TANH(0);
 tanh 
------
    0

HEXTORAW
(
  str IN text
)
RETURNS bytea;

SELECT HEXTORAW('DB') AS col;
 col  
------
 \xdb
(1개 행)

BIN_TO_NUM
(
  expr1, expr2, ..., exprn IN numeric  -- variadic: treated as numeric[]
)
RETURNS numeric;

SELECT BIN_TO_NUM(1,0,1);
 bin_to_num 
------------
          5
(1개 행)

SELECT BIN_TO_NUM('1',0,1);
 bin_to_num 
------------
          5
(1개 행)

SELECT BIN_TO_NUM('2',0,1);
ERROR:  Invalid value in array, only 0 and 1 are allowed

COSH(num)

SELECT COSH(0);
 cosh 
------
    1

LENGTH(str)

 SELECT LENGTH('abc'::char(6));
 length 
--------
      6
(1 row)

 SELECT LENGTH(''::char(6));
 length 
--------
      6
(1 row)

TO_YMINTERVAL
(
  sql_format IN text -- format: '[+|-]years-months'
)
RETURNS interval;

TO_YMINTERVAL
(
  ym_iso_format IN text -- format: '[-]P[<years>Y][<months>M]'
)
RETURNS interval;

SELECT DATE '2008-03-20' + TO_YMINTERVAL('2-7') AFTER;
         after          
------------------------
 2010-10-20 09:00:00+09
(1개 행)

SELECT DATE '2008-03-20' + TO_YMINTERVAL('P2Y7M') AFTER;
         after          
------------------------
 2010-10-20 09:00:00+09
(1개 행)

MONTHS_BETWEEN
(
  date1  IN date,
  date2  IN date
)
RETURNS numeric;

MONTHS_BETWEEN
(
  timestamptz1  IN timestamptz,
  timestamptz2  IN timestamptz
)
RETURNS numeric;

# 테스트 1
SELECT MONTHS_BETWEEN('2023-05-15'::date, '2022-01-10'::date);

  months_between  
------------------
 16.1612903225806
(1 row)

# 테스트 2
SELECT MONTHS_BETWEEN('2023-05-15 12:00:00+09'::timestamptz, '2022-01-10 08:30:00+09'::timestamptz);

  months_between  
------------------
 16.1659946143627

LNNVL
(
  value  IN boolean
)
RETURNS boolean;

# 테스트 1
SELECT LNNVL(true);  

 lnnvl 
-------
 f
(1 row)

# 테스트 2
SELECT LNNVL(false);  

 lnnvl 
-------
 t
(1 row)

# 테스트 3
SELECT LNNVL(NULL);  

 lnnvl 
-------
 t
(1 row)

NANVL
(
  value         IN { real, double precision, numeric },
  replacement   IN { varchar, real, double precision, numeric }
)
RETURNS { real, double precision, numeric };

# 테스트 1
SELECT NANVL(3.14, 0.0); -- 결과: 3.14 (3.14가 NaN이 아니므로 원래 값 반환)

 nanvl 
-------
  3.14
(1 row)

# 테스트 2
SELECT NANVL('NaN'::float4, 0.0); -- 결과: 0.0 (첫 번째 값이 NaN이어서 대체값 0.0 반환)

 nanvl 
-------
     0
(1 row)

NUMTOYMINTERVAL
(
  expr             IN numeric,
  interval_unit    IN text
)
RETURNS interval;

SELECT NUMTOYMINTERVAL (10, 'YEAR');
 numtoyminterval 
-----------------
 10 years
(1개 행)

SELECT NUMTOYMINTERVAL (10, 'month');
 numtoyminterval 
-----------------
 10 mons
(1개 행)

SELECT NUMTOYMINTERVAL (0, 'month');
 numtoyminterval 
-----------------
 00:00:00
(1개 행)

SELECT DATE '2008-03-20' - TO_DSINTERVAL('50 00:00:00') before;
         before         
------------------------
 2008-01-30 09:00:00+09
(1개 행)

SELECT DATE '2008-03-20' - TO_DSINTERVAL('P50DT0H0M0S') before;
         before         
------------------------
 2008-01-30 09:00:00+09
(1개 행)

# 테스트 1
SELECT GREATEST(5, 3, 9);

 greatest 
----------
        9
(1 row)

# 테스트 2
SELECT GREATEST('apple'::text, 'banana', 'cherry'); -- 결과: cherry (문자열 사전순 비교)

 greatest 
----------
 cherry
(1 row)

# 테스트 3
SELECT GREATEST(10, NULL, 7); -- 결과 NULL

 greatest 
----------
         

# 테스트 1
SELECT LEAST(5, 3, 9);

 least 
-------
     3
(1 row)


# 테스트 2
SELECT LEAST('apple'::text, 'banana', 'cherry'); -- 결과: apple (문자열 사전순 비교)

 least 
-------
 apple
(1 row)

# 테스트 3
SELECT LEAST(10, NULL, 7); -- 결과: NULL

 least 
-------
      
(1 row)

# 테스트 1
SELECT SYS_EXTRACT_UTC('2023-06-01 12:34:56+07'::timestamptz);

   sys_extract_utc   
---------------------
 2023-06-01 05:34:56
(1 row)

# 테스트 2
-- 세션 시간대 '+9:00' 기준으로 timestamptz 변환 후 UTC로 변경
SELECT SYS_EXTRACT_UTC('2023-06-01 12:34:56'::timestamp);

   sys_extract_utc   
---------------------
 2023-06-01 03:34:56
(1 row)

SELECT INSTR('ABCDEABCDEABCDE', 'CD');
 instr 
-------
     3

SELECT LTRIM('ABCDEFGHIJKLMNOP', 'ABCDEF');
   ltrim    
------------
 GHIJKLMNOP
 (1 row)

SELECT MOD(-11, 4), MOD(11, -4);
 mod | mod 
-----+-----
  -3 |   3

SELECT REGEXP_LIKE('Hello World', 'world', 'i');
 regexp_like 
-------------
 t
(1 row)

 SELECT LPAD('LPAD', 10, '-=');
    lpad    
------------
 -=-=-=LPAD
(1 row)

SELECT TO_TIMESTAMP_TZ('2023-06-01 12:34:56+09', 'YYYY-MM-DD HH24:MI:SSOF');

    to_timestamp_tz     
------------------------
 2023-06-01 12:34:56+09
(1 row)

SELECT SINH(0);
 sinh 
------
    0

SELECT BITAND(6,3);

 bitand 
--------
      2

SELECT NAME FROM T ORDER BY NLSSORT(NAME);
 name 
------
 BAR
 FOO
(2 rows)

SELECT TO_SINGLE_BYTE('Ｈｅｌｌｏ, ｗｏｒｌｄ!');
-- 예제 결과: 'Hello, world!'

 to_single_byte 
----------------
 Hello, world!
(1 row)

ASCIISTR
(
  str IN text
)
RETURNS text;

SELECT ASCIISTR('A한글B');
   asciistr   
--------------
 A\D55C\AE00B
(1개 행)

RAWTOHEX
(
  raw IN { bytea | text }
)
RETURNS text;

SELECT RAWTOHEX('AB');
 rawtohex 
----------
 4142
(1개 행)

ROUND
(
  expr  IN date [, fmt   IN text]
)
RETURN date;

ROUND
(
  expr  IN timestamp without time zone [, fmt   IN text]
)
RETURN timestamp without time zone;

ROUND
(
  expr  IN timestamp with time zone [, fmt   IN text]
)
RETURN timestamp with time zone;

# 테스트 1
-- 날짜 반올림 예제: '2023-05-15' 날짜를 월 단위로 반올림
SELECT ROUND('2023-05-15'::date, 'MONTH');
-- 결과: 해당 월의 시작일 또는 월의 특정 기준일로 조정된 날짜 반환

   round    
------------
 2023-05-01
(1 row)

# 테스트 2
-- 날짜 반올림 예제 (포맷 미지정): 기본 포맷('DDD')으로 반올림
SELECT ROUND('2023-05-15'::date);
-- 결과: '2023-05-15' 날짜가 포맷 모델에 따라 반올림된 결과 반환

   round    
------------
 2023-05-15
(1 row)


# 테스트 3
-- 타임스탬프 반올림 예제: '2023-05-15 14:35:20' 타임스탬프를 시간 단위로 반올림
SELECT ROUND('2023-05-15 14:35:20'::timestamp, 'HH24');
-- 결과: '2023-05-15 15:00:00' (분과 초가 0으로 조정됨)

        round        
---------------------
 2023-05-15 15:00:00
(1 row)

# 테스트 4
-- 타임스탬프 with time zone 반올림 예제: 기본 포맷('DDD')으로 반올림
SELECT ROUND('2023-05-15 14:35:20+09'::timestamptz);
-- 결과: 포맷 모델에 따라 반올림된 타임스탬프 반환

         round          
------------------------
 2023-05-16 00:00:00+09
(1 row)

NVL(expr1, expr2)

# 테스트 데이터
create table employees (first_name varchar, last_name varchar, salary integer, hire_date timestamptz);

INSERT INTO employees (first_name, last_name, salary, hire_date) VALUES
('John', 'Doe', NULL, '2020-03-15 09:00:00'),
(NULL, 'Smith', 62000, '2019-07-22 10:30:00'),
('Michael', 'Johnson', 72000, '2018-11-10 08:45:00'),
('Emily', 'Davis', 48000, '2021-05-01 12:00:00'),
('David', 'Wilson', 53000, '2017-09-17 14:20:00'),
(NULL, 'Brown', NULL, '2016-12-05 09:15:00'),
('James', 'Taylor', NULL, '2015-06-30 16:45:00'),
('Jessica', 'Martinez', 68000, '2022-01-25 11:10:00'),
(NULL, 'Anderson', 58000, '2020-10-05 13:35:00'),
('Laura', 'Thomas', 49500, '2023-08-12 08:00:00');


# 테스트 1
# salary 값이 NULL이면 0 반환; first_name 값이 NULL이면 last_name 반환;
select NVL(salary,0), NVL(first_name, last_name) from employees ;

  nvl  |   nvl   
-------+---------
 55000 | John
 62000 | Jane
 72000 | Michael
 48000 | Emily
 53000 | David
 60000 | Sarah
 75000 | James
 68000 | Jessica
 58000 | Daniel
 49500 | Laura
(10 rows)

WM_CONCAT
(
  expr  IN text
)
RETURNS text;

# 테스트 테이블
create table employees ( first_name varchar, last_name varchar );

INSERT INTO employees (first_name, last_name) VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Michael', 'Johnson'),
('Emily', 'Davis'),
('David', 'Wilson'),
('Sarah', 'Brown'),
('James', 'Taylor'),
('Jessica', 'Martinez'),
('Daniel', 'Anderson'),
('Laura', 'Thomas');


# 테스트 1
select wm_concat(last_name) from employees ;

                              wm_concat                               
----------------------------------------------------------------------
 Doe,Smith,Johnson,Davis,Wilson,Brown,Taylor,Martinez,Anderson,Thomas
(1 row)

# 테스트 2
select wm_concat(first_name) from employees ;

                           wm_concat                            
----------------------------------------------------------------
 John,Jane,Michael,Emily,David,Sarah,James,Jessica,Daniel,Laura
(1 row)

LISTAGG( 
    [ ALL | DISTINCT ] 
    expression 
    [, delimite] 
) 
[OVER ( [query_partition_clause] );
RETURNS text;

query_partition_caluse : 
PARTITION BY
  { expr[, expr ]...
  | ( expr[, expr ]... )
  }

# 테스트 테이블
create table employees ( first_name varchar, last_name varchar );

INSERT INTO employees (first_name, last_name) VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Michael', 'Johnson'),
('Emily', 'Davis'),
('David', 'Wilson'),
('Sarah', 'Brown'),
('James', 'Taylor'),
('Jessica', 'Martinez'),
('Daniel', 'Anderson'),
('Laura', 'Thomas');


# 테스트 1
select listagg(last_name, ',') from employees ;

                               listagg                                
----------------------------------------------------------------------
 Doe,Smith,Johnson,Davis,Wilson,Brown,Taylor,Martinez,Anderson,Thomas
(1 row)

# 테스트 2
select listagg(last_name) from employees ;
                           listagg                           
-------------------------------------------------------------
 DoeSmithJohnsonDavisWilsonBrownTaylorMartinezAndersonThomas
(1 row)

NUMTODSINTERVAL
(
  number  IN double precision,
  unit    IN text
)
RETURNS interval;

-- 1.5 DAY는 1일 12시간으로 변환됨
SELECT NUMTODSINTERVAL(1.5, 'DAY');

 numtodsinterval 
-----------------
 1 day 12:00:00
(1 row)

-- 2.75 HOUR는 2시간 45분으로 변환됨
SELECT NUMTODSINTERVAL(2.75, 'HOUR');

 numtodsinterval 
-----------------
 02:45:00
(1 row)

-- 30 MINUTE는 30분 0초로 변환됨
SELECT NUMTODSINTERVAL(30, 'MINUTE');

 numtodsinterval 
-----------------
 00:30:00
(1 row)

-- 90 SECOND는 90초 그대로 반환됨
SELECT NUMTODSINTERVAL(90, 'SECOND');

 numtodsinterval 
-----------------
 00:01:30
(1 row)

REMAINDER(num1, num2);

SELECT REMAINDER(3,2);
 remainder 
-----------
        -1

RPAD(expr1, num [, expr2])

 SELECT RPAD('RPAD', 10, '-=');
    rpad    
------------
 RPAD-=-=-=
(1 row)

SUBSTR(str, position [, length])

SELECT SUBSTR('ABCDEFG', 3, 2), SUBSTR('ABCDEFG', -3, 2);
 substr | substr 
--------+--------
 CD     | EF

DUMP(expr[, return_fmt [, start_position [, length ] ] ])

SELECT DUMP('abc'::TEXT, 1016);
                   dump                   
------------------------------------------
 Typ=25 Len=3 CharacterSet=UTF8: 61,62,63
(1 row)

EXISTSNODE
(
  xml IN xml,
  xpath_str IN text,
  namespace_str IN text DEFAULT NULL
)
RETURNS integer;

select existsnode('<root><a>1</a><b>2</b></root>'::xml, '/root/a');
 existsnode 
------------
          1
(1개 행)

select existsnode('<root><a>1</a><b>2</b></root>'::xml, null);
 existsnode 
------------
           
(1개 행)

RTRIM(str [, char_set])

SELECT RTRIM('ABCDEFGHIJKLMNOP', 'LMNOP');
    rtrim    
-------------
 ABCDEFGHIJK
 (1 row)

REGEXP_LIKE(str, pattern [, replace_str [, position [, occurrence [, match_param]]]])

SELECT REGEXP_REPLACE('aaaaaaa','([[:alpha:]])', 'x');
 regexp_replace 
----------------
 xxxxxxx
(1 row)

REGEXP_SUBSTR(str, pattern [, position [, occurrence [, match_param [, subexp]]]])

SELECT REGEXP_COUNT('abcabcabc','abc', 2);
 regexp_count 
--------------
            2
(1 row)

REGEXP_COUNT(str, pattern [, position [, match_param]])

SELECT REGEXP_COUNT('abcabcabc','abc', 2);
 regexp_count 
--------------
            2
(1 row)

CREATE TABLE T (a date);
INSERT INTO T VALUES ('4713-01-01 01:11:30 BC');
INSERT INTO T VALUES ('2023-03-19 13:29:30');

VARCHAR2[(size)]

EMP_NAME VARCHAR2(10)

NVARCHAR(size)

clob

SELECT 'Hello, '::clob || 'World!'::clob;

blob

INSERT INTO files (data) VALUES (E'\\xDEADBEEF'::blob);

# 테스트 1
-- 날짜 절삭: '2023-05-15'를 월 단위로 절삭하여, 해당 월의 첫 날을 반환함
SELECT TRUNC('2023-05-15'::date, 'MONTH');
-- 결과: '2023-05-01'

   trunc    
------------
 2023-05-01
(1 row)

# 테스트 2
-- 날짜 절삭: 포맷 미지정 시, 원래 날짜가 그대로 반환됨
SELECT TRUNC('2023-05-15'::date);
-- 결과: '2023-05-15'

   trunc    
------------
 2023-05-15\q
 
(1 row)

# 테스트 3
-- 타임스탬프 절삭: '2023-05-15 14:35:20'를 시간 단위로 절삭하여, 분과 초를 제거함
SELECT TRUNC('2023-05-15 14:35:20'::timestamp, 'HH24');
-- 결과: '2023-05-15 14:00:00'

        trunc        
---------------------
 2023-05-15 14:00:00
(1 row)

# 테스트 4
-- timestamptz 절삭: '2023-05-15 14:35:20+09'의 경우, 포맷 미지정 시 기본 포맷('DDD')으로  절삭됨
SELECT TRUNC('2023-05-15 14:35:20+09'::timestamptz);
-- 결과: 타임스탬프 값이 절삭된 결과 반환 (일 단위로 절삭)

         trunc          
------------------------
 2023-05-15 00:00:00+09
(1 row)

-- 문자형 요일 예제: '2023-05-15' 이후의 첫 번째 Monday 반환
SELECT NEXT_DAY('2023-05-15'::date, 'Monday');

  next_day  
------------
 2023-05-22
(1 row)

-- 인덱스 기반 요일 예제: '2023-05-15' 이후의 첫 번째 월요일(인덱스 2) 반환
SELECT NEXT_DAY('2023-05-15'::date, 2);

  next_day  
------------
 2023-05-22
(1 row)

-- TIMESTAMP WITH TIME ZONE 예제 (문자형):
SELECT NEXT_DAY('2023-05-15 14:30:00+09'::timestamptz, 'Friday');

      next_day       
---------------------
 2023-05-19 14:30:00
(1 row)

-- TIMESTAMP WITH TIME ZONE 예제 (인덱스):
SELECT NEXT_DAY('2023-05-15 14:30:00+09'::timestamptz, 6);

      next_day       
---------------------
 2023-05-19 14:30:00
(1 row)

# 테스트 데이터
create table employees (
  first_name varchar,
  last_name varchar,
  salary integer,
  hire_date timestamptz,
  commission_pct integer,
  bonus integer,
  status varchar
);

INSERT INTO employees (first_name, last_name, salary, hire_date, commission_pct, bonus, status) VALUES
('John', 'Doe', 55000, '2020-03-15 09:00:00', NULL, 2000, 'Active'),
('Jane', 'Smith', 62000, '2019-07-22 10:30:00', 10, 3000, 'Active'),
('Michael', 'Johnson', 72000, '2018-11-10 08:45:00', 7, 2500, 'Inactive'),
('Emily', 'Davis', 48000, '2021-05-01 12:00:00', NULL, 1500, NULL),
('David', 'Wilson', 53000, '2017-09-17 14:20:00', 8, 2200, NULL),
('Sarah', 'Brown', 60000, '2016-12-05 09:15:00', 12, 2800, 'Active'),
('James', 'Taylor', 75000, '2015-06-30 16:45:00', NULL, 2600, 'Resigned'),
('Jessica', 'Martinez', 68000, '2022-01-25 11:10:00', 5, 1800, NULL),
('Daniel', 'Anderson', 58000, '2020-10-05 13:35:00', 6, 2000, 'Active'),
('Laura', 'Thomas', 49500, '2023-08-12 08:00:00', 4, 1200, 'Probation');

# 테스트 1
-- commission_pct 값이 NULL이 아니면 bonus 값을, NULL이면 0을 반환함
SELECT NVL2(commission_pct, bonus, 0)
FROM employees;

 nvl2 
------
    0
 3000
 2500
    0
 2200
 2800
    0
 1800
 2000
 1200
(10 rows)

# 테스트 2
-- 첫 번째 인자가 NULL이 아니면 두 번째 인자(예: 'Active')를, NULL이면 세 번째 인자(예: 'Inactive')를 반환함
SELECT NVL2(status, 'Active', 'Inactive')
FROM employees;


   nvl2   
----------
 Active
 Active
 Active
 Inactive
 Inactive
 Active
 Active
 Inactive
 Active
 Active
(10 rows)

-- 4자리 16진수 Unicode escape를 사용한 예제 (예: \0041는 'A'로 변환)
SELECT UNISTR('\0041\0042\0043');
-- 결과: 'ABC'

 unistr 
--------
 ABC
(1 row)

-- 다양한 형식의 escape 시퀀스를 포함한 예제
SELECT UNISTR('\u0041 \+00420042 \U00000041');

  unistr  
----------
 A 䈀42 A
(1 row)


select unistr('\0441\043B\043E\043D');

 unistr 
--------
 слон
(1 row)

select unistr('d\u0061t\U00000061');

 unistr 
--------
 data
(1 row)

-- 잘못된 형식 예제
SELECT unistr('wrong: \db99');
ERROR:  invalid Unicode surrogate pair

SELECT unistr('wrong: \db99\0061');
ERROR:  invalid Unicode surrogate pair

SELECT unistr('wrong: \+00db99\+000061');
ERROR:  invalid Unicode surrogate pair

SELECT unistr('wrong: \+2FFFFF');
ERROR:  invalid Unicode escape value

SELECT unistr('wrong: \udb99\u0061');
ERROR:  invalid Unicode surrogate pair

SELECT unistr('wrong: \U0000db99\U00000061');
ERROR:  invalid Unicode surrogate pair

SELECT unistr('wrong: \U002FFFFF');
ERROR:  invalid Unicode escape value

SELECT unistr('wrong: \0000');
ERROR:  invalid Unicode code point

SELECT unistr('wrong: \u0000');
ERROR:  invalid Unicode code point

SELECT unistr('wrong: \+000000');
ERROR:  invalid Unicode code point

SELECT unistr('wrong: \U00000000');
ERROR:  invalid Unicode code point