Oracle pl/SQL common functions, oracleplsql Functions

Oracle pl/SQL common functions, oracleplsql Functions

On csdn, I also saw a post called Oracle common function 110. after a rough look, there are many data calculations, such as covariance. I don't think I have used much. I can see it today, but it is not very comprehensive. I have added it a little. I can check it later.

1. The character function name is used by LOWER (xland) to convert all uppercase letters in the string expression xland to lowercase letters UPPER (xland) convert all lowercase letters in string expression xland to uppercase letters INITCAP (xland) xland string to uppercase letters SUBSTR (xland, start, length) returns the length of the string expression xland starting from the start (length calculated by the number of characters) substrb (xland, start, length) returns the length (in bytes) of the string expression xland starting from start. The LENGTH (xland) returns the length of the string xland (LENGTHb (xland) returns the byte length of the string xland. LTRIM (xland) removes the space on the left side of the string xland, returns the RTRIM (xland) value, removes the space on the right side of the string xland, and returns the Trim (xlan D) remove the spaces at both ends of the string xland and return the REPLACE (xland, str1, str2) value to REPLACE all str1 in the string xland with str2 INSTR (xland, str, start, times) search for the str string in the xland string. start is the start position of the search operation, and times is the start position of the specified expression in the string returned by CHARINDEX. Syntax CHARINDEX (expression1, expression2 [, start_location]) If expression1 is not found in expression2, CHARINDEX returns 0. 2: date function name function SYSDATE returns the current date and time of the system NEXT_DAY (day, char) day is date type, char is string type represents the day of the week, suppose Today is Saturday, next_day (sysdate, 'monday'); returns 2009-7-20LAST_DAY (day), returns the date ADD_MONTH (day, n) corresponding to the last day of the month specified by the day) returns the date MONTHS_BETWEEN (day1, day2) of the day after n months (n is positive) or before (n is negative). returns the month with a difference between day1 and day2, the format of the two parameters must be consistent. This function is not easy to use. 3. Data Type Conversion Function Name function to_char (xland, [format]) convert a date or numeric variable xland to a string type to a format string yyyy-mm-dd hh-mi-ssto_date (xland, [format]) convert string to the date format specified by TO_NUMBER (xland) to convert numeric data to numeric data 4: set function name function AVG calculate the average value of a column value COUNT the number of values in a column MAX find the maximum value in a column value MIN find the minimum value SUM in a column value calculate the total value of a column 5: the pre-defined error name function zero_divide is divided by 0. case_not_found does not include the required where clause no_data_found select into statement in the case statement, and does not return any data. dup_val_on_index tries to put a column with unique constraints insert duplicate value invalid_error in an algorithm, conversion, truncation, and size constraint error (in SQL statement) value_error an algorithm, conversion, truncation, and size constraint error occurs (in procedural statements) 6: explicit cursor attribute name function cursor_name % isopen open status true, close state falsecursor_name % found last time to extract cursor operation, success is true, failure is falsecursor_name % notfound and cursor_name % found reverse cursor_name % rowcount last extracted data row number 7: implicit cursor attribute value select insert update deletesql % isopen false falsesql % found true result successful SQL % found false no result failed SQL % notfound true no result failed SQL % notfound false successful results SQL % rowcount returns the number of rows that are only 1 inserted rows modified the number of rows deleted