개인적인 정리

오라클 NANVL() NANVL() - NANVL(n2 ,n1) 의 형태 The NANVL function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. it instructs Oracle Database to return an alternative value n1 if the input value n2 is NAN(not a number). If n2 is not NaN. then Oracle returns n2. This function takes as arguments any numeric data type or any nonumeric data type that can be implicitly con..

오라클 MONTHS_BETWEEN() MONTHS_BETWEEN() - MONTHS_BETWEEN(date1, date2) 의 형태 MONTHS_BETWEEN returns number of months between dates date1 and date2. The month and the last day of the month are defined by the parameter NLS_CAENDAR. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the ..

오라클 MOD() MOD() - MOD(n2, n1) 의 형태 MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0. This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data ..

오라클 MIN() MIN() - 최소값을 구하는 함수. MIN(expr) MIN(expr) OVER (analytic_clause) MIN([DISTINCT | ALL] expr) MIN([DISTINCT | ALL] expr) OVER (analytic_clause) 의 형태 DISTINCT, ALL 은 생략가능. MIN returns minimum value of expr. You can use it as an aggregate or analytic function. 오라클 HR, OE 계정으로 테스트 가능 SQL> SELECT MIN(hire_date) FROM employees ; -->2001/01/13 SQL>SELECT manager_id, last_name, hire_date, salary..

오라클 MEDIAN MEDIAN() - 분포된 값들에서 중간값 정도를 return 해주는 것 같다. - 처음보는 함수이다. MEDIAN(expr) MEDIAN(expr) OVER (query_partition_clause) 의 형태 MEDIAN is an inverse distribution function that assumes a continuous distribution model. It takes a numeric or datetime value and returns the middle value or an interpolated value that would be the middle value once the values are sorted. Nulls are ignored in the calc..

오라클 max() MAX() - max 값을 return. aggregate 또는 anlaytic 함수로 사용가능 (집계함수, 분석함수 정도) MAX( expr) MAX( expr) OVER (analytic_clause) MAX( [DISTINCT|ALL] expr) MAX( [DISTINCT|ALL] expr) OVER (analytic_clause) 의 형태 MAX returns maximum value of expr. You can use it as a aggregate or analytic function. 오라클 OE 계정으로 테스트 할 수 있다. SQL > SELECT MAX(salary) "Max" FROM employees ; --> 24000 SQL>SELECT manager_id, l..

오라클 LTRIM() LTRIM() - LTRIM(char) LTRIM(char, set) 의 형태 LTRIM removes from the left end of char all of the characters contained in set. If you do not specify set, then it defaults to a single blnak. If char is a character literal, then you must enclose it in single quotation marks. Oracle Database begins scanning char from its first character and removes all characters that apper in set until re..

오라클 LPAD() LPAD() - LPAD(expr1, n) LPAD(expr1, n, expr2) 의 형태 LPAD returns expr1, left-padded to length n characters with the sequence of characters in expr2. This function is useful for formatting the output of a query. Both expr1 and expr2 can be any of data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 data type if expr1 is a character data type, N..