개인적인 정리

오라클 LISTAGG() LISTAGG() - LISTAGG(measure_expr, 'delimiter') WITHIN GROUP(order_by_clause), LISTAGG(measure_expr, 'delimiter') WITHIN GROUP(order_by_clause) OVER query_partition_clause 의 형태 For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column. As a single-set aggregate function, LISTAGG operates on ..

오라클 LENGTH() LENGTH() - 문자열의 길이를 구해주는 함수 LENGTH(char) 의 형태 The LENGTH functions return the length of char. LENGTH calculates length using caracters as defined by the input character set. LENGTHB uses bytes instead of charactes. LENGTHC uses Unicode complete characters. LENGTH2 uses UCS2 code points. LENGTH4 uses UCS4 code points. char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2..

오라클 LEAD() LEAD() - LEAD(value_expr, offset) RESPECT | IGNORE NULLS OVER (query_partition_clause order_by_clause) LEAD(value_expr RESPECT NULLS, 1) OVER (query_partition_clause order_by_clause) LEAD(value_expr, 1) OVER (query_partition_clause order_by_clause) 의 형태 LEAD is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series..

오라클 LAST_VALUE() LAST_VALUE() - LAST_VALUE(expr) OVER (analytic_clause) LAST_VALUE(expr RESPECT|IGNORE NULL) OVER (analytic_clause) 의 형태 LAST_VALUE is an analytic function that is useful for data densification. It returns the last value in an ordered set of values. {RESPECT | IGNORE} NULLS determine whether null values of expr are included in or eliminated from the calculation. the default is RE..

오라클 LAST_DAY() LAST_DAY() - 달의 마지막 날짜를 구해준다. LAST_DAY(date) 의 형태 LAST_DAY returns the date of the last day of the month that contains date. The last day of the month is defined by the session parameter NLS_CALENDAR. The return type is always DATE, regardless of the data type of date. SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL ; --> 2020/04/30 오후 2:42:01 SQL> SELECT LAST_DAY('2020-04-01') FROM DUAL;..

오라클 LAST() LAST() 함수는 FIRST() 함수를 참조한다. https://web-obj.tistory.com/393 ORACLE FIRST() 오라클 FIRST() 이런 함수가 있는지 처음 알았다. FIRST AND LAST are very similar functions. Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as th.. web-obj.tistory.com

오라클 INSTR() INSTR() - 문자열과 비교하여 문자열의 위치를 return 해준다. - 비교할 문자열의 위치와, 발견횟수등을 정하여서도 사용이 가능하다. (position, occurence) - 검색관련해서도 사용가능 INSTR(string, substring) INSTR(string, substring, position, occurence) 의 형태 The INSTR functions search string for substring. The search operation is defined as comparing the substring argument with substrings of string of the same length for equality until a match is f..

오라클 INSERTCHILDXML) INSERTCHILDXML() - INSERTCHILDXML inserts a user-supplied value into the target XML at the note indicated by the XPath expression.