개인적인 정리

오라클 CONCAT() -오라클에서 문자열을 이어붙일 수 있는 함수 CONCAT(char1, char2) CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB. The string returned is in the same character set as char1. Its data type depends on the data types of the arguments. In concatenations of two different data types, Oracle Database returns the da..

오라클 COLLECT() COLLECT is an aggregate function that takes as its argument a column of any type and creates a nested table of the input type out of the rows selected. Te get accurate results form this function you must use it within a CAST function. 아래 예제를 확인해볼려면 오라클에서 oe 계정을 활성화시켜야 한다. SQL> ATLER USER OE ACCOUNT UNLOCK ; SQL> ATLER USER OE IDENTIFIED BY 'OE12345678' ; SQL> SELECT COLLECT(warehou..

오라클 COALESCE() COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurences of expr evaluate to null, then the function returns null. SQL > SELECT COALESCE(NULL, 5,4,3,2,1, 0) FROM DUAL ; --> 5 SQL > SELECT COALESCE(NULL, 5,NULL,3,2,1, 0) FROM DUAL ; --> 5 SQL > SELECT COALESCE(NULL, NULL, 0) FROM DUAL ; --> 0 SQL > SELECT COALESCE..

오라클 CHR() CHR returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set or, if you specify USING NCHAR_CS, the national character set. SQL> SELECT CHR(67) || CHR(65) || CHR(84) FROM DUAL ; --> CAT 이 함수를 유용하게 사용한 적이 있다. 쿼리내에서 https://~~~~?aaa=value1&bbb=value2 이런 것을 만들어서 vo에 던진 후에 사용할려고 보니 자꾸 오류가 발생을 해서 CHR을 이용하여 해결한적이 있었다. SQL > SELECT CHR(6..

오라클 CEIL() CEIL returns the smallest integer that is greater than or equal to n. The number n can always be written as the difference of an integer k and a positive fraction f suhc that 0 101 SQL> SELECT CEIL(-100.1) FROM DUAL ; --> -100

오라클 CAST() CAST converts one built-in data type or collection-typed value into another built-in data type or collection-typed value. CAST lets you convert built-in data types or collection-typed values of one type into another build-in data type or collection type. You can cast an unnamed operand(such as a date or the result set of a subquery) or a named collection(such as a varry or a nested ta..

오라클 AVG() AVG returns average value of expr. This function takes as an argument any numeric data type or any nonumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument. If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and wind..

오라클 ATAN2() ATAN2 returns the arc tangent of n1 and n2. The argument n1 can be in an unbounded range and returns a value in the range of -pi to pi, depending on the signs of n1 and n2, expressed in radians. SQL > SELECT ATAN2(-2, 1) FROM DUAL ; --> -1.10714871779409