Oracle Common functions

First, mathematical functions 　　ABS (x)　　返回x的绝对值； 　　BITAND(x,y)　　返回对x,y进行位与( AND )操作的结果； 　　CEIL(x)　　返回大于或等于x的最小整数(注意负数)； 　　FLOOR(x)　　返回小于或等于x的最大整数； 　　EXP(x)　　返回e的x次幂，其中e约为2.71828183； 　　LN(x)　　返回x的自然对数； 　　LOG(x,y)　　返回以x为底y的对数； 　　POWER(x,y)　　返回x的y次幂； 　　SQRT(x)　　返回x的平方根; 　　MOD(x,y)　　返回x除以y的余数； 　　SIGN(x)　　返回x的符号； 　　 ROUND(x[,y])    返回对x取整的结果。y为可选参数，说明对第几位小数处取整。没有指定y的时候The 0 decimal places of x are rounded, and if negative, the |y| of X on the left side of the decimal point is rounded. This function is rounded to rounding 　　TRUNC(x[,y])    与ROUND类似，之不过是直接舍去尾数Second, aggregate function

　　AVG (distinct| All) to find the average;

　　MAX (distinct| All) to find the maximum value;

　　MIN (distinct| All) to find the minimum value;

　　STDDEV (Distinct|all) to seek standard deviation;

　　VARIANCE (distinct| All) to seek covariance;

　　SUM (distinct| All) sum;

　　COUNT (distinct| All) to find the number of records, data;

　　MEDIAN ()　　 to find the median number;

Third, date function

　sysdate used to get the current date of the system

　　add_months Add or subtract months

　　Last_day The last day of the return date

　　Months_between (date2,date1) gives the month of Date2-date1

　　new_time (date, ' This ', ' that ') gives the date and time of the =other time zone in this time zone

Abbreviated Time zone

AST OR ADT Atlantic Standard Time
HST OR HDT Alaska-Hawaii Time
BST OR BDT UK Daylight Time
MST OR MDT US Mountain Time
CST OR CDT US Central Time zone
NST Newland Standard Time
EST OR EDT US Eastern Time
PST OR PDT Pacific Standard Time
GMT Grenwy Standard Time
YST OR YDT Yukon Standard Time

　　next_day Returns the exact date of the Sunday number that you requested in the same one-week or one-week period after the date of enactment.

Sunday = 1 week one = 2 weeks two = 3 weeks three = 4 weeks four = 5 weeks five = 6 weeks six = 7

　　current_date Current date in the current session time zone

　　Current_timestamp to return the current date in the current session's time zone with the TIMESTAMP with date zone data type

　　Dbtimezone () return time zone

　　Sessiontimezone Return to the time zone where Dbtimezone is the database and the session is for the current session, because the time zone can be changed at the session level

　　EXTRACT Find field values for date or interval values

　　Localtimestamp Returns the date and time in a session

　　TRUNC (for dates) The date value that the Trunc function truncates for the specified element.

Its specific syntax format is as follows: TRUNC (Date[,fmt]) Where: Date a datetime value FMT date format, the date will be truncated by the specified element format. Ignoring it is a recent date that is truncated by SELECT TRUNC (sysdate, ' YYYY ') from DUAL; 　　-Return to the first day of the year. SELECT TRUNC (sysdate, ' MM ') from DUAL; 　　-Returns the first day of the month. SELECT TRUNC (sysdate, ' D ') from DUAL; 　　--Returns the first day of the current week. SELECT TRUNC (sysdate, ' DD ') from dual;--returns the current month day Example:

----Last day of last month
SELECT To_char (Last_day (Add_months (Sysdate,-1)), ' Yyyy/mm/dd ') from DUAL;

----: Last month's Today
SELECT To_char (Add_months (Sysdate,-1), ' Yyyy-mm-dd ') from DUAL;

----The first day of last month
SELECT To_char (Add_months (Last_day (sysdate) +1,-2), ' Yyyy-mm-dd ') firstday from DUAL;

---by weekly statistics
SELECT to_char (sysdate, ' WW ') from DUAL;

---by monthly statistics
SELECT to_char (sysdate, ' MM ') from DUAL;

----Statistics per quarter
SELECT to_char (sysdate, ' Q ') from DUAL;

---According to yearly statistics
SELECT to_char (sysdate, ' YYYY ') from DUAL;

　　 To format a date function:
Q quarter, January-March for the first quarter
SELECT to_char (sysdate, ' Q ') from DUAL; --2 indicates the second quarter

MM number of months
SELECT to_char (sysdate, ' MM ') from DUAL; --04 says April

The Roman representation of the RM month
SELECT to_char (sysdate, ' RM ') from DUAL; --iv says April

Month name in month with 9 character length
SELECT to_char (sysdate, ' MONTH ') from DUAL; -April

The first few weeks of WW year
SELECT to_char (sysdate, ' WW ') from DUAL; --24 means the 24th week of June 13, 2002

W this month the first few weeks
SELECT to_char (sysdate, ' W ') from DUAL; -April 26, 2011 is the 4th week

The first few days of DDD year. January 1 is 001, February 1 is 032
SELECT to_char (sysdate, ' DDD ') from DUAL;

DD Day of the month
SELECT to_char (sysdate, ' DD ') from DUAL;

Days within D week
SELECT to_char (sysdate, ' D ') from DUAL;

DY week abbreviation for the first day
SELECT to_char (sysdate, ' DY ') from DUAL;

HH or HH12 12 hours of input
SELECT to_char (sysdate, ' HH ') from DUAL;

HH24 24-hour system
SELECT to_char (sysdate, ' HH24 ') from DUAL;

MI minutes (0~59)
SELECT to_char (sysdate, ' MI ') from DUAL;
Note Do not use mm format for minutes (MI should be used in minutes). MM is the format used for the month, and it works for minutes, but the result is wrong.

SS Number of seconds (0~59)
SELECT to_char (sysdate, ' SS ') from DUAL;

Four, character functions

　　ASCII (str) 　 returns the ASCII value of a string

　　CHR(x) 　　 returns the ASCII character corresponding to the integer

　　CONCAT (b) connection string A and string B

If you want to join the two fields in the table can be used | |

　initcap Returns a string and capitalizes the first letter of the string;

　　 Length Returns the lengths of the strings;

Returns the actual length of a column of a table data, returns 0 if the table has no data

　　LOWER Returns a string, and all characters are lowercase

　　UPPER Returns a string and capitalizes all characters

　　LTRIM (x,[trim_string]) Delete the string trim_string that appears to the left of X, default to an empty string

　　RTRIM (X, [trim_string]) deletes the string trim_string that appears to the right, and defaults to an empty string.

　　SUBSTR (String,start,count) takes a substring, starting with start, taking count

　　INSTR (c1,c2,i,j) searches a string for the specified character and returns the location of the specified character;

C1 string to be searched
C2 the string you want to search
I Search start position, default is 1
Where J appears, default is 1

　　Rpad (STR,X,STR1) pastes the characters on the right side of Str str1

Note that the length value x is not the length of the pasted character, but the length of the entire string, if the length is less than the original string before the original string x bits

　　Lpad (STR,X,STR1) pastes the characters on the left side of str str1

Note the same thing as above

　　Replace (' string ', ' s1 ', ' s2 ') replaces string S1 with string s2

　　SOUNDEX Returns a string with the same pronunciation as the given string

　　trim (' s ' from ' string ') cuts the character and cuts s out of the string

Leading cut off the front character
TRAILING cut off the back of the character
If not specified, the default is whitespace

　　NVL (x, Value) if x is a null value, return value, otherwise return x

　　NVL2 (x, VALUE1, VALUE2) if x is a null value, return VALUE1, otherwise return VALUE2

　　Nanvi (x, Value) returns VALUE if X is not a number, otherwise returns x

V. Conversion functions

　　to_char (DATE, ' FORMAT ') converts the corresponding data to a string type

　　to_date (String, ' FORMAT ') converts a string to a date in Oracle

　　to_number (str) converts the given character to a number

　　Runc ( for number) intercepts the specified precision by one digit

　　TRUNC (number[,decimals]) returns the processed numeric value, which works very much like the round function, except that the function does not make the corresponding rounding selection processing of the specified fractional number before or after it, and it is truncated altogether.

Number to be intercepted and processed
Decimals indicates the number of digits after the decimal point to be retained. Optional, ignore it to truncate all the decimal parts

　　Chartorowid Converts a char or VARCHAR2 value of ROWID that contains an external format to an internal binary format. The argument string must be a 18-character string that contains an external format of ROWID.

　　Rowidtochar Converts the ROWID data type to a character type, converting the numeric rowid of the rowID type to a string representation of 18 characters outside of it.

　　Convert (c,dset,sset) converts the source string sset from one language character set to another dset character set

　　Hextoraw (x) converts a hexadecimal-formed string x to Raw

　　Rawtohex Converts the raw class numeric rawvalue to a corresponding hexadecimal-represented string. Each byte in the RawValue is converted to a double-byte string. Rawtohex and Hextoraw are the two opposite functions.

　　To_multi_byte Converts a single-byte character in a string to a multibyte character converts the specified character to full width and returns a char type string

DUMP (s,fmt,start,length) The dump function returns a value of type VARCHAR2 in the internal number format specified by the FMT

FMT includes 4 types of formats: 8| | 10| | 16| | 17, respectively, 8 binary, 10 binary, 16 binary and single-character, the default is 10 binary. The start parameter represents the starting position, and the length represents the number of string lengths.

　　Empty_blob (), Empty_clob () both functions are functions used to initialize a field of a large data type

Vi. Control Flow functions (commonly used in PL/SQL statements)

　　Process Control:

if (expression) then executes the statement end if; (End if: End if) to determine conditions that satisfy a situation

if (expression) then executes the statement that else executes the statement end if; determine conditions that satisfy both conditions

The IF (expression) then executes the statement ElseIf executes the statement ElseIf executed by the statement .... End If; Determine conditions that satisfy a variety of circumstances

case (expression) when (conditional expression result 1) Then executes statement 1 when (conditional expression result 2) then executes the statement 2 else executes the statement n end Caase; (end case ends case)

case when (conditional expression 1) Then executes statement 1 when (conditional expression 2) then executes statement 2 else executes the statement n end case;

　　Loop control:

　　　Loop statement segment, exit [when/if conditional expression] End loop, execute at least once equivalent to do{} while (conditional expression), exit with exit if when/if condition is met, or end loop;

The while conditional expression loop executes the statement end loop; Only the conditional expression is true when it is run;

For loop variable in [reverse] initial value expression ... End-Value expression loop execution statement END loop

PS: Due to the limited ability of the author, such as wrong, please understand;

Oracle Common functions