Oracle Function Highlights

Oracle has a number of functions, which are sorted out:

Absolute value of ABS (m) m
MoD (m,n) m is removed after n remainder
Power (M,n) m n-Th Square
Round (M[,n]) m rounded to the value of n bits after the decimal point (n defaults to 0)
Trunc (M[,n]) m truncates the value of n-bit decimal digits (n defaults to 0)

Character functions:

initcap (ST)   return St capitalize each word first letter, all other letters lowercase
Lower (ST)   return St to each word letter all lowercase
Upper (ST)   Return St Capitalize the letters of each word in all
Concat (st1,st2)    return St to the end of the St2 st1 (available operator | |
Lpad (St1,n[,st2])   Returns the right-aligned st,st to the left of St1 with st2 padding until the default of N,st2 (Rpad])   Returns the left-aligned St, St is filled with st2 on the right side of the ST1 until the length is n,st2 the default is a space
LTrim (St[,set])    return st,st to remove the character from the left in set, until the first one is not in set. By default, it refers to a space
RTrim (St[,set])    returns st,st to remove characters from the set in the right until the first one is not in set. By default, the space
Replace (St,search_st[,replace_st])   replaces each search_st that appears in St with Replace_st, returning an St. By default, Search_st
{

is deleted

SUBSTR (String,a[,b]) returns part of a string, A and B, in characters.
SUBSTRB (String,a[,b]) returns part of a string, A and B, in bytes.
SUBSTRC (String,a[,b]) returns part of a string, where A and B are in Unicode full characters.
SUBSTR2 (String,a[,b]) returns a portion of a string, and A and B are in UCS2 code point units.
SUBSTR4 (String,a[,b]) returns a portion of a string, and A and B are in UCS4 code point units.
All of the above functions return part of a string, starting at character position A and being B characters long. If a is 0, then it is considered to be 1 (the starting position of the string). If a is a positive number, then the character starts at the left. If it is a negative number, it starts at the end of the string and from the right. If B does not exist, then the default is the entire string. If B is less than 1, NULL is returned. If A or b uses a floating-point number, the value is first taken as an integer and the return type is the same as string.
}
{
LENGTH (String)
LENGTHB (String)
LENGTHC (String)
LENGTH2 (String)
LENGTH4 (String)
The above function returns the length of the string. Because the value of the char type is filled with spaces, if the string is a char data type, the trailing space is counted within the length. If string is null, the function returns NULL.
}

{

INSTR (String1,string2[,a][,b]) returns the location of the string2 contained in the string1. A and B are in characters.
INSTRB (String1,string2[,a][,b]) returns the location of the string2 contained in the string1. A and B are in bytes.
INSTRC (String1,string2[,a][,b]) returns the location of the string2 contained in the string1. A and B are in Unicode full character units.
INSTR2 (String1,string2[,a][,b]) returns the location of the string2 contained in the string1. A and B are in UCS2 code point units
INSTR4 (String1,string2[,a][,b]) returns the location where the string2 is contained in string1 A and B are in UCS4 code point units.
The above function returns the position of the string1 containing the string2. Scan the string1 from the left, starting at a. If a is negative then the scan starts from the right. The position of the second occurrence will be returned. A and B defaults to 1, which returns the position of the first occurrence of string2 in string1. If the string2 is not found under a and B, then return 0. The location is calculated relative to the starting position of the string1, not the values of A and B.
}

Conversion functions:
NVL (M,n) if the M value is null, returns N, otherwise returns m
To_char (M[,fmt]) m converts from a numeric value to a string of the specified format FMT defaults to the width of the FMT value to accommodate all valid digits
To_number (St[,fmt]) St converts from character data to numeric values in the specified format, by default the size of the numeric format string is exactly the entire number
Report:
Format of the To_char () function:
---------------------------------
Symbol description
---------------------------------
9 Each 9 represents one digit in the result
0 represents the pilot 0 to display
$ dollar sign printed on the left of the number
L arbitrary local currency symbol
. Print decimal decimal point
, print a comma representing the thousand bits
---------------------------------

Grouping functions:

AVG ([Distinct/all] n) The average of column n
Count ([all] *) returns the number of rows in the query range including duplicate values and null values
Count ([Distinct/all] n) Number of rows with non-null values
Max ([Distinct/all] n) The maximum value of the column or expression
MIN ([Distinct/all] n) The minimum value of the column or expression
Stdev ([Distinct/all] n) standard deviation of the column or expression, ignoring null values
SUM ([Distinct/all] n) The sum of the column or expression
Variance ([Distinct/all] n) The variance of the column or expression, ignoring null values

Date function:

Add_months (d,n) Date d plus n months
Last_day (d) Date of the last day of the month containing D
Month_between (d,e) Number of months between date D and E, E precedes D
New_time (d,a,b) a time zone date and time D in the B time zone date and time
Next_day (d,day) Date D, day of the week specified by day
Sysdate current system date and time
Greatest (D1,d2,... dn) The last date in the list of dates given
Least (D1,K2,... dn) The earliest date in the list of dates given
To_char (d [, FMT]) Date D converted to a string in the format specified by FMT
The To_date (St [, FMT]) string St is converted to a date value in the format specified by FMT, and if FMT is omitted, the ST is in the default format
Round (d [, FMT]) Date D rounded to the nearest date in FMT-specified format
Trunc (d [, FMT]) Date D truncated to most recent date by FMT-specified format
Report:
Date format:
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Format code Description The range of examples or desirable values
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
DD one day of the month 1-3
DY three capital letters of the week SUN, ... Sat
Day full week, capital English SUNDAY, ... SATURDAY
MM Month 1-12
MON Three capital letter month of JAN, ... DEC
MONTH full January,... December
Roman numeral I,... of the RM month XII
yy or yyyy two-bit, four digit year
HH:MI:SS hours: minutes: seconds
HH12 or HH24 in 12-hour or 24-hour display
MI points
SS sec
Am or PM on PM indicator
SP suffix SP requires spelling out any numeric fields
th suffix th indicates that the added number is ordinal 4th,1st
FM prefix to month or day or year value, no padding
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

43. Find the difference between a part of two dates (e.g. seconds)
Subtract directly from two dates (e.g. d1-d2=12.3)
SELECT (D1-D2) *24*60*60 vaule from DUAL;

44. Date of novelty based on difference (e.g. minutes)
SELECT sysdate+8/60/24 vaule from DUAL;

45. Find time in different time zones
SELECT new_time (sysdate, ' ydt ', ' GMT ') vaule from DUAL;

-----Time zone parameter, Beijing should be ydt in the East 8 district-------
AST ADT Atlantic Standard Time
BST BDT Bering Sea Standard Time
CST CDT Central Standard Time
EST EDT Eastern Standard Time
GMT Greenwich Standard Time
HST HDT Alaska-Hawaii Standard Time
MST MDT Mountain Standard Time
NST Newfoundland Standard Time
PST PDT Pacific Standard Time
YST YDT Yukon Standard Time

Time minus 7 minutes for the current time
Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual
The current time minus 7 hours of time
Select Sysdate-interval ' 7 ' hour from dual
The current time minus 7 days
Select Sysdate-interval ' 7 ' Day from dual
Time minus July for current time
Select Sysdate,sysdate-interval ' 7 ' month from dual
Time minus 7 years in the current time
Select Sysdate,sysdate-interval ' 7 ' year from dual
Time interval multiplied by a number
Select Sysdate,sysdate-8 *interval ' 2 ' hour from dual

<!--[if!supportlists]-->, <!--[endif]--> A few notes about null:

1, add, subtract, multiply and divide the null value, and the result is still empty.
2. When comparing null values, only "is null" and "is not NULL" are used to judge.
3, when the field is sorted in ascending order, if the data in this field is empty, then NULL will be the last.
4, when using the like '% ' to match, the null value will not be matched. Because '% ' denotes "any number of characters", but does not include "no characters".
5, when calculating the sum of a number field A, if there is data in this field is empty, then sum (A) to sum, the record is ignored.
6, when calculating the count of a number field A, if there is data in this field is empty, then count (A) is counted, will not ignore such records, will also be counted in such records.

7. In the Where condition, Oracle handles the null condition as false, and a SELECT statement with such a condition will not return a result, and no error message is returned. But null and false are still different.

NLS function
In addition to NCHR, these functions return a character type value as a character type parameter.
CONVERT (String,dest_charset[,source_charset])
Converts the input string to the specified character set Dest_charset. Source_charset is the character set of the input value--if it is not specified, the default is the database character set. The input values can be char, VARCHAR2, NCHAR, NVARCHAR2, CLOB, and NCLOB types. The return value is
The VARCHAR2 type. If there is not one character in the input string in Dest_charset, a substitution character (defined by Dest_charset) will be used
NCHR (X)
Returns a character with a value of x in the database national character set. NCHR (x) is equivalent to Chr (x USING nchar_cs).
Nls_charset_decl_len (Byte_width,charset)
Returns the declaration width, in characters, of a nchar value. Byte_width is the length of the value in bytes CharSet is the character set ID of the value.
NLS_CHARSET_ID (Charset_name)
Returns the numeric ID of the specified character set charset_name. Specifying "Char_cs" for Charset_name returns the ID of the database character set, and specifying "Nchar_cs" for Charset_name returns the ID of the database national character set. If Charset_name is an invalid character set name, it will return
Null. NLS_CHARSET_ID and Nls_charset_name are reciprocal functions.
Nls_charset_name ([charset_id])
Returns the name of the specified character set ID charset_id. If charset_id is an invalid character set ID, NULL will be returned;
Nls_initcap (String[,nlsparams])
Returns String.nlsparams as the first character of each word in a string and the rest of the letter in a word lowercase specifies a different sort order from the session default. If this parameter is not specified, Nls_initcap is the same as Initcap. Nlsparams should take the following form:
' Nls_sort=sort ', where sort is a sort of language sequence.
Nls_upper (String[,nlsparams])
A string is returned in uppercase, and characters that are not letters are not affected. If not specified Nlsparams,nls_upper is the same as upper.
Nls_lower
Returns a string in lowercase, not a character that is not affected by the letter, if no nlsparams,nls_lower is specified as lower.
Nlssort (String[,nlsparams])
Returns the string byte used to sort string. All values are converted to byte strings, so that consistency is maintained between the different databases.
If Nlsparams is not specified, the default sort sequence in the session is used.
TRANSLATE (String USING {Char_csinchar_cs})
TRANSLATE ... The USING converts the input string parameter to the database character set (specified Char_cs) or to the database national character set (specified Nchar_cs). String can be char, VARCHAR2, NCHAR, NVARCHAR2 type. If Char_cs is specified, the return type is VARCHAR2, and if Nchar_cs is specified, the return type is NVARCHAR2. TRANSLATE ... Using is a subset of the convert feature.

Userenv: Returns information about the current user's environment, opt can be:

ENTRYID: Return session population flag

SESSIONID: Return to the conversation ID

TERMINAL: Returns the terminal or the machine's target

ISDBA: Check whether the pre-user is dba, if it returns true;

Language: return to the current environmental change

Client_info: Client Information

Lang: returns to the pre-environmental language of the acronym

Contents of Sys_context:
Select
Sys_context (' USERENV ', ' TERMINAL ') TERMINAL,
Sys_context (' USERENV ', ' LANGUAGE ') LANGUAGE,
Sys_context (' USERENV ', ' SESSIONID ') SESSIONID,
Sys_context (' USERENV ', ' INSTANCE ') INSTANCE,
Sys_context (' USERENV ', ' ENTRYID ') ENTRYID,
Sys_context (' USERENV ', ' isdba ') ISDBA,
Sys_context (' USERENV ', ' nls_territory ') nls_territory,
Sys_context (' USERENV ', ' nls_currency ') nls_currency,
Sys_context (' USERENV ', ' Nls_calendar ') Nls_calendar,
Sys_context (' USERENV ', ' Nls_date_format ') Nls_date_format,
Sys_context (' USERENV ', ' nls_date_language ') nls_date_language,
Sys_context (' USERENV ', ' nls_sort ') Nls_sort,
Sys_context (' USERENV ', ' current_user ') Current_User,
Sys_context (' USERENV ', ' Current_userid ') Current_userid,
Sys_context (' USERENV ', ' session_user ') Session_user,
Sys_context (' USERENV ', ' Session_userid ') Session_userid,
Sys_context (' USERENV ', ' Proxy_user ') Proxy_user,
Sys_context (' USERENV ', ' Proxy_userid ') Proxy_userid,
Sys_context (' USERENV ', ' db_domain ') Db_domain,
Sys_context (' USERENV ', ' db_name ') db_name,
Sys_context (' USERENV ', ' host ') HOST,
Sys_context (' USERENV ', ' Os_user ') Os_user,
Sys_context (' USERENV ', ' external_name ') External_name,
Sys_context (' USERENV ', ' ip_address ') ip_address,
Sys_context (' USERENV ', ' Network_protocol ') Network_protocol,
Sys_context (' USERENV ', ' bg_job_id ') bg_job_id,
Sys_context (' USERENV ', ' fg_job_id ') fg_job_id,
Sys_context (' USERENV ', ' Authentication_type ') Authentication_type,
Sys_context (' USERENV ', ' authentication_data ') authentication_data
From dual
<!--[If!supportlinebreaknewline]-->

Case correspondence
(http://www.eygle.com/archives/2005/10/oracle_howto_user_case_function.html)
Example
Create sequence student_sequence;
CREATE TABLE Students (
ID Number (5) PRIMARY KEY,
Stu_name VARCHAR2 (20),
Score Number (10,0),
Grade VARCHAR2 (2));

INSERT into students (ID, stu_name, score, grade)
VALUES (student_sequence. Nextval, ' Scott ', 98,null);

INSERT into students (ID, stu_name, score, grade)
VALUES (student_sequence. Nextval, ' Margaret ', 88,null);

INSERT into students (ID, stu_name, score, grade)
VALUES (student_sequence. Nextval, ' Joanne ', 75,null);

INSERT into students (ID, stu_name, score, grade)
VALUES (student_sequence. Nextval, ' Manish ', 66,null);


Update students
Set grade =
case where score > Then ' a '
When score > ' B '
When score > B Then ' C '
Else ' d ' end
----------------------------------------------------------------------------------

update students
Set grade = (
Select grade from
(
Select ID,
case when current_credits > "A '
     when current_credits >" B "
     when cur Rent_credits > "C '
Else ' d ' end grade
from students
) a
where a.id = Students.id
)