This article is not ready to introduce all of the Oracle functions, in the current situation, I do not have this time, need to learn too much, to spend most of the time on learning to use the technical aspects:), so if you are ready to learn all the Oracle functions of friends, or to focus on: Oracle SQL Reference official documents are more reliable. This article will cover a number of functions that are often used in everyday life, or that are rarely used, but that are interesting to think about. Divided into two categories, namely:
Well-known function articles-Frequently used functions
A non-famous function-that is, though seldom used, but in some cases it is useful
Note: n is a numeric type, C denotes a character type, D is a date type, [] indicates that the inner parameter can be ignored, and FMT represents the format.
The single-valued function returns a single value in the query, which can be applied to the Select,where clause, start with and the Connect by clause and the HAVING clause.
(i). Numeric type function (number Functions)
The numeric function enters the numeric parameter and returns the value of the numeric type. Most of the return values for this class of functions support 38-bit decimal points, such as: COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH support 36 decimal places. ACOS, ASIN, ATAN, and ATAN2 support 30-bit decimal points.
1, MOD (N1,N2) returns the remainder of N1 except N2, if N2=0 returns the value of N1.
For example: SELECT MOD (24,5) from DUAL;
2. ROUND (N1[,N2]) returns the value of N1 after rounding the right n2 of the decimal point, N2 the default value of 0, and if N2 is negative, rounds to the corresponding bit to the left of the decimal point (although Oracle documents mentions that the value of N2 must be an integer, In fact, the judgment here is not rigorous, even if the N2 is not an integer, it will automatically take the N2 after the completion of the processing, but my document other mentions must be the whole place need to pay special attention, if not for the entire execution will be error.
For example: SELECT ROUND (23.56), ROUND (23.56,1), ROUND (23.56,-1) from DUAL;
3, TRUNC (N1[,N2] Returns the truncated to N2 decimal value of the N1, N2 the default is set to 0, when N2 is the default setting N1 truncated to an integer, if the N2 is negative, it is truncated in the corresponding bit to the left of the decimal point.
For example: SELECT TRUNC (23.56), TRUNC (23.56,1), TRUNC (23.56,-1) from DUAL;
(ii). Character functions return character values (Character Functions returning Character values)
The class function returns the same type as the input type.
? The value of the returned char type is not more than 2000 bytes long;
? The returned VCHAR2 type value is not more than 4000 bytes long;
If the length of the character that should be returned above is exceeded, Oracle does not error but is truncated directly to the maximum supported length return.
? The value of the returned CLOB type is not more than 4G long;
For a function of type CLOB, if the return value is longer than the length, Oracle does not return any errors but throws the error directly.
1, LOWER (c) Change the characters within the specified string to lowercase, support char,varchar2,nchar,nvarchar2,clob,nclob type
For example: SELECT LOWER (' What's This ') from DUAL;
2, UPPER (c) to capitalize the characters in the specified string, support Char,varchar2,nchar,nvarchar2,clob,nclob type
For example: SELECT UPPER (' What's This ') from DUAL;
3, Lpad (C1,N[,C2]) returns the string of the specified length =n, there are several points to note:
? If the n<c1.length is truncated from right to left, the specified length is returned;
? If n>c1.length and C2 is null, the character length is added to N and returned with a space from left to right;
? If N>c1.length and C2 are NOT NULL, the specified character C2 is added from left to right C1 length to n and returned;
For example: SELECT lpad (' What's This ', 5), Lpad (' What's This ', ","), Lpad (' What's This ', ' ", '-') from DUAL;
Finally, let's guess, if n<0, what's the result?
4, Rpad (C1,N[,C2]) returns the string of the specified length =n, basically with the same, but the supplementary character is right-to-left direction is exactly the opposite;
For example: SELECT rpad (' What's This ', 5), Rpad (' What's This ', ","), Rpad (' What's This ', ' ", '-') from DUAL;
5. TRIM ([[Leading| | Trailing| | BOTH] C2 from] C1 haha, by my invincible description of the way to get dizzy, this place or see a picture more clear some.
It looks complicated and it's easy to understand:
? Oracle removes C1 and kinsoku spaces if no parameters are specified
For example: SELECT TRIM (' What's This ') from DUAL;
? If the C2 parameter is specified, Oracle removes the C1 C2 (this recommendation is carefully tested, in a number of different situations yo)
For example: SELECT TRIM (' W ' from ' What's this w w ') from DUAL;
? If the leading parameter is specified, the C1 head is removed C2
For example: SELECT TRIM (Leading ' w ' from ' What's this w w ') from DUAL;
? If the trailing parameter is specified, the C1 tail is removed C2
For example: SELECT TRIM (Trailing ' w ' from ' What's this w w ') from DUAL;
? If the both parameter is specified, it will be removed from C1 C2 (does it differ from unspecified?). No difference! )
For example: SELECT TRIM (both ' w ' from ' What's this w w ') from DUAL;
Note: C2 length =1
6, LTRIM (C1[,C2]) Tens of millions of tables thought with the above the long image, the function is similar to the above, this function is from the left side of the string C1 the same character as the specified string C2 and return. If C2 is empty, the space is truncated by default.
For example: SELECT LTRIM (' wwhhhhhat is this w ', ' Wh ') from DUAL;
7, RTRIM (C1,C2) and the same, but the opposite direction
For example: SELECT RTRIM (' wwhhhhhat is this w ', ' W w ') from DUAL;
8. Replace (C1,C2[,C3]) replaces C2 in the C1 string with C3, and if C3 is empty, removes all C1 from C2.
For example: SELECT REPLACE (' Wwhhhhhat is this w w ', ' W ', '-') from DUAL;
9, SOUNDEX (c) Magical function Ah, this function returns the phonetic representation of a string parameter, which is useful for comparing some of the same pronunciations, but spelling different words. The algorithm for calculating speech is as follows:
? Retains the first letter of the string, but deletes a, E, H, I, O, W, y.
? Assign the numbers in the following table to the corresponding letters:
1:b, F, p, V
2:c, G, K, q, S, X, Z
3:d, T
4:l
5:M, N
6:r
? If there are more than 2 (2) letters in the string that have the same number (for example, B and f), or only H or W, then delete the others, leaving only 1;
? Returns only the first 4 bytes, not enough for 0 padding
For example: SELECT SOUNDEX (' dog '), SOUNDEX (' Boy ') from DUAL;
10, SUBSTR (C1,N1[,N2]) intercepts a string of the specified length. A little attention may be filled with the function of the trap.
n1= beginning length;
n2= the length of the truncated string, or, if NULL, the default intercept to the end of the string;
? If N1=0 then n1=1
? If n1>0, Oracle confirms from left to right that the starting position is intercepted
For example: SELECT SUBSTR (' What's This ', 5,3) from DUAL;
? If n1<0, Oracle confirms the starting position from right-to-left numbers
For example: SELECT SUBSTR (' What's This ', -5,3) from DUAL;
? If the N1>c1.length returns an empty
For example: SELECT SUBSTR (' What's This ', 50,3) from DUAL;
And then ask you to guess, if n2<1, how will return the value?
11, TRANSLATE (C1,C2,C3) in terms of functionality, this function is somewhat similar to replace. However, it is important to note that translate is an absolute match substitution, which differs greatly from the Replace function. What is an absolute match replacement? Simply put, the string C1 is replaced by a certain format C2 with C3. If the words are still not understood, we illustrate by a few examples:
For example:
SELECT TRANSLATE (' What's this ', ' ', '-') from DUAL;
SELECT TRANSLATE (' What's this ', '-', ') from DUAL;
The results are all empty. Let's try this:
SELECT TRANSLATE (' What's this ', ' ', ') from DUAL;
Look at this again:
SELECT TRANSLATE (' What's This ', ' ait ', '-* ') from DUAL;
Do you understand the point? The Replace function is simple to understand, which is to replace the specified character in a string with another character, and its characters must be contiguous. In translate, it is the C2 that appears in the specified string c1, replacing each character in the C2 with the character in the C3 of the position in C3. I see? Replace is the replacement, and the translate is like a filter
(iii). Character function returns a numeric value (Character Functions returning number values)
This type of function supports all data types
1, INSTR (C1,C2[,N1[,N2]) return C2 position in C1
? C1: Original string
? C2: The string to look for
? N1: The starting position of the query, positive values from left to right, negative values from right to left (the size of the position, such as 3 for the left 3rd place to start, 3 for the 3rd right). Black, if 0, the return is also 0
? N2: The first few matches. Greater than 0
For example: SELECT INSTR (' ABCDEFG ', ' e ', -3) from DUAL;
2, Length (c) returns the lengths of the specified string. If
For example: SELECT LENGTH (' A123 ') from DUAL;
Guess what the return value of select LENGTH (') from DUAL;
(iv). Date function (Datetime Functions)
In this type of function, the date is returned except for the Months_between return value.
1, Add_months () returns the value after the specified date month +n, and n can be any integer.
For example: SELECT add_months (sysdate,12), Add_months (sysdate,-12) from DUAL;
2. Current_date returns the default time of the time zone where the current session is located
For example:
Sql> alter session Set Nls_date_format = ' mm-dd-yyyy ';
Sql> select current_date from dual;
3. The Sysdate function is the same as above, returning the default time of the current session's time zone. However, it is important to note that if you use both Sysdate and current_date for the same time, in some cases current_date will be one second faster than Sysdate. After a brief communication with XYF_TCK (the work mechanism of Oracle, written in a very good, comprehensible way), we think that Current_date is returning the current_timestamp in milliseconds after rounding, although no document support was found, But I want to sorta. At the same time, only in some cases there will be a second error, the general situation will not affect your operation, so understand.
For example: SELECT sysdate,current_date from DUAL;
4, Last_day (d) Returns the last day of the month in which the specified time is located
For example: SELECT last_day (sysdate) from DUAL;
5, Next_day (d,n) returns the date of the first n after the specified date, and n is the day of the week. However, it is important to note that if N is a character, its week form needs to be the same as the week of the current session in the default time zone.
For example: Think twice the Chinese nt,nls_language value is Simplified Chinese
SELECT Next_day (sysdate,5) from DUAL;
SELECT next_day (sysdate, ' Thursday ') from DUAL;
Both methods can be used to get the correct return, but:
SELECT next_day (sysdate, ' Thursday ') from DUAL;
Will execute an error, providing you say that the day of the week is invalid, that's why.
6, Months_between (D1,D2) returns the month difference between D1 and D2, depending on the value of the D1,D2, the result can be negative, of course, it may be 0
For example:
SELECT Months_between (Sysdate, Sysdate),
Months_between (Sysdate, Add_months (Sysdate,-1)),
Months_between (Sysdate, Add_months (sysdate, 1))
From DUAL;
7, ROUND (D[,FMT]) in front of the numerical function of the time introduced ROUND, here and the function basically similar, but the operation here is the date. If you do not specify the FMT parameter, the date closest to the specified date is returned by default.
For example: SELECT ROUND (sysdate, ' HH24 ') from DUAL;
8, TRUNC (D[,fmt]) is the same as the numerical TRUNC principle described earlier, but this is also the date type of the operation.
For example: SELECT TRUNC (sysdate, ' HH24 ') from DUAL;
(v). Conversion function (Conversion Functions)
A conversion function converts a specified character from one type to another, typically such functions follow the following conventions: The function name is followed by the type to be converted and the output type.
1, To_char () This function can be divided into three small categories, respectively, is
? Convert character---character To_char (c): Converts the Nchar,nvarchar2,clob,nclob type to a CHAR type;
For example: SELECT to_char (' aabbcc ') from DUAL;
? Conversion time, character To_char (D[,fmt]): Converts the specified time (data,timestamp,timestamp with a zone) to the VARCHAR2 type in the specified format;
For example: SELECT to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from DUAL;
? Convert value, character to_char (N[,fmt]): Converts the specified number n to the VARCHAR2 type in the specified format and returns;
For example: SELECT to_char ( -100, ' L99g999d99mi ') from DUAL;
2, To_date (C[,fmt[,nls]]) converts char,nchar,varchar2,nvarchar2 to a date type, and if the FMT parameter is not empty, it is converted according to the format specified in FMT. Note the FMT parameter here. If the FTM is ' J ' then it is converted according to the ad system (Julian day) and C must be a positive integer greater than 0 and less than 5373484.
For example:
SELECT to_date (2454336, ' J ') from DUAL;
SELECT to_date (' 2007-8-23 23:25:00 ', ' yyyy-mm-dd hh24:mi:ss ') from DUAL;
Why is the value of C must not be greater than 5373484 in the ad system? Because Oracle's date type ranges from January 1, 4712 BC to December 31, 9999. Take a look at the following statement:
SELECT To_char (to_date (' 9999-12-31 ', ' yyyy-mm-dd '), ' J ') from DUAL;
3. To_number (C[,fmt[,nls]) converts the CHAR,NCHAR,VARCHAR2,NVARCHAR2 string to a numeric type and returns it as specified in the FMT format.
For example: SELECT to_number (' -100.00 ', ' 9g999d99 ') from DUAL;
(vi). Other auxiliary functions (Miscellaneous Single-row Functions)
1, DECODE (EXP,S1,R1,S2,R2. S,R[,DEF]) can be interpreted as an enhanced if else, except that it does not pass a multiline statement, but rather implements the if else function within a function.
Exp as the initial parameter. s as the contrast value, the same return R, if S has more than one, then continue to traverse all s until a condition is true, otherwise the default value def (if specified), if there is no default value, and the previous comparison is not true, then return null.
There is no doubt that decode is a very important function that will be used when implementing functions such as row-to-column, which need to be kept in mind and used skillfully.
For example: Select Decode (' A2 ', ' A1 ', ' True1 ', ' A2 ', ' True2 ', ' Default ') from dual;
2, Greatest (N1,N2,... N) returns the maximum value in a sequence
For example: Select Greatest (15,5,75,8) "greatest" from DUAL;
3, LEAST (N1,N2....N) returns the minimum value in the sequence
For example: SELECT LEAST (15,5,75,8) LEAST from DUAL;
4, Nullif (C1,C2)
Nullif is also a very interesting function. Logic equivalent to: case when C1 = C2 then NULL ELSE C1 END
For example: SELECT nullif (' A ', ' B '), Nullif (' A ', ' a ') from DUAL;
5, NVL (C1,C2) logic is equivalent to if C1 is null then C2 ELSE C1 END. C1,C2 can be of any type. If the two types are different, Oracle automatically converts the C2 to the C1 type.
For example: SELECT NVL (null, ' n ') from DUAL;
6, NVL2 (C1,C2,C3) Everyone may use NVL, but have you ever used NVL2? Returns C2 if C1 is not empty and returns if C1 is empty C3
For example: Select NVL2 (' A ', ' B ', ' C ') isnull,nvl2 (null, ' B ', ' C ') isnotnull from dual;
7, Sys_connect_by_path (COL,C) The function can only be applied to a tree query. Returns the path from the root to the node through the C1 connection. The function must be used in conjunction with the Connect by clause.
For example:
CREATE TABLE Tmp3 (
Rootcol VARCHAR2 (10),
Nodecol VARCHAR2 (10)
);
INSERT into Tmp3 values (' ', ' a001 ');
INSERT into Tmp3 values (' ', ' b001 ');
INSERT into Tmp3 values (' a001 ', ' a002 ');
INSERT into Tmp3 values (' a002 ', ' a004 ');
INSERT into Tmp3 values (' a001 ', ' a003 ');
INSERT into Tmp3 values (' a003 ', ' a005 ');
INSERT into Tmp3 values (' a005 ', ' a008 ');
INSERT into Tmp3 values (' b001 ', ' b003 ');
INSERT into Tmp3 values (' b003 ', ' b005 ');
Select Lpad (", level*10, ' = ') | | ' > ' | | Sys_connect_by_path (Nodecol, '/')
From Tmp3
Start with Rootcol = ' a001 '
Connect by prior Nodecol =rootcol;
8, Sys_context (C1,c2[,n]) returns the value of the specified parameter c2 of the specified namespace c1 after the specified length n is truncated.
Oracle9i provides a built-in namespace Userenv that describes the information for the current session, which has the following parameters:
? Current_schema: current mode name;
? Current_User: Current user;
? IP_Address: Current client IP address;
? Os_user: Current client operating system user;
And so on dozens of items, more detailed parameter column please refer directly to Oracle Online Documents
For example: SELECT sys_context (' USERENV ', ' Session_user ') from DUAL;
Note: n is a numeric type, C denotes a character type, D is a date type, [] indicates that the inner parameter can be ignored, and FMT represents the format.
The single-valued function returns a single value in the query, which can be applied to the Select,where clause, start with and the Connect by clause and the HAVING clause.
(i). Numeric type function (number Functions)
The numeric function enters the numeric parameter and returns the value of the numeric type. Most of the return values for this class of functions support 38-bit decimal points, such as: COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH support 36 decimal places. ACOS, ASIN, ATAN, and ATAN2 support 30-bit decimal points.
1, ABS (n) returns the absolute value of the number
Example: SELECT ABS ( -1000000.01) from DUAL;
2, COS (n) returns the cosine of n
For example: SELECT COS ( -2) from DUAL;
3, ACOS (n) inverse cosine function, n between-1 and 1, return value between 0 and pi.
For example: SELECT ACOS (0.9) from DUAL;
4, Bitand (N1,N2) bit and operation, this is too interesting, although did not think of possible use where, detailed explanation:
Assuming that the 3,9 does bits and operations, the binary form of 3 is: the binary form of the 0011,9 is: 1001, then the result is 0001, and the conversion to 10 binary number is 1.
For example: SELECT Bitand (3,9) from DUAL;
5, Ceil (n) returns the smallest integer value greater than or equal to n
For example: SELECT ceil (18.2) from DUAL;
Test you, guess what the value of ceil (-18.2) would be?
6. Floor (n) returns the maximum integer value less than or equal to n
For example: SELECT floor (2.2) from DUAL;
And guess what the value of floor (-2.2) would be.
7, Bin_to_num (n1,n2,.... N) binary Turn decimal
For example: SELECT bin_to_num (1), Bin_to_num (1,0), Bin_to_num (from DUAL);
8, SIN (n) returns the positive value of N, and N is the radian.
Example: SELECT SIN from DUAL;
9, SINH (n) returns the hyperbolic positive value of n, and N is the radian.
For example: SELECT SINH (Ten) from DUAL;
10, ASIN (n) The inverse of the function, n between-1 and 1, the return value between PI/2 AND-PI/2.
For example: SELECT ASIN (0.8) from DUAL;
11, TAN (n) returns the tangent of N, N radians
For example: SELECT TAN (0.8) from DUAL;
12, TANH (n) returns the hyperbolic tangent of N, N radians
For example: SELECT TANH (0.8) from DUAL;
13, ATAN (n) the inverse tangent function, n is the Radian, the return value between PI/2 AND-PI/2.
For example: SELECT ATAN ( -444444.9999999) from DUAL;
14, EXP (n) returns the n power of e, E = 2.71828183 ...
Example: SELECT EXP (3) from DUAL;
15, LN (n) returns the natural logarithm of N, n>0
For example: SELECT LN (0.9) from DUAL;
16. Log (N1,N2) returns the logarithm of the base N2 of N1, N1 >0 and not 1, n2>0
For example: SELECT LOG (1.1,2.2) from DUAL;
17. POWER (N1,N2) returns the N2 of N1. N1,N2 can be any number, but if M is negative, n must be an integer
For example: SELECT POWER (2.2,2.2) from DUAL;
18, sign (n) if n<0 returns-1 if N>0 returns 1 if n=0 returns 0.
For example: SELECT sign (+), sign ( -14), sign (0) from DUAL;
19, SQRT (n) returns the square root of N, and N is radians. N>=0
For example: SELECT SQRT (0.1) from DUAL;
(ii). Character functions return character values (Character Functions returning Character values)
The class function returns the same type as the input type.
? The value of the returned char type is not more than 2000 bytes long;
? The returned VCHAR2 type value is not more than 4000 bytes long;
If the length of the character that should be returned above is exceeded, Oracle does not error but is truncated directly to the maximum supported length return.
? The value of the returned CLOB type is not more than 4G long;
For a function of type CLOB, if the return value is longer than the length, Oracle does not return any errors but throws the error directly.
1. CHR (n[USING Nchar_cs]) returns the corresponding character of the specified numeric value in the current character set
For example: SELECT CHR from DUAL;
2, CONCAT (C1,C2) connection string, equivalent to | |
For example: SELECT concat (' aa ', ' BB ') from DUAL;
3, Initcap (c) Converts the first letter of a word in a string to uppercase, others to lowercase
For example: SELECT initcap (' What's This ') from DUAL;
4, Nls_initcap (c) returns the specified string and capitalizes the first letter in the string, with the other letters lowercase
For example: SELECT nls_initcap (' Zhonghua Minzhu ') from DUAL;
It also has a parameter: Nlsparam is used to specify the collation, which can be ignored, and the default state that the parameter is the collation of the current session.
(iii). Character function returns a numeric value (Character Functions returning number values)
This type of function supports all data types
1, ASCII (c) and the purpose of the CHR function is just the opposite, this function returns the specified character in the current character set corresponding to the value.
For example: SELECT ASCII ('_') from DUAL;
(iv). Date function (Datetime Functions)
In this type of function, the date is returned except for the Months_between return value.
1, Current_timestamp ([n]) returns the date and time of the current session's time zone. n indicates millisecond precision, not greater than 6
For example: SELECT Current_timestamp (3) from DUAL;
2, Localtimestamp ([n]) and up, returns the date and time of the current session's time zone. n indicates millisecond precision, not greater than 6
For example: SELECT Localtimestamp (3) from DUAL;
3, Systimestamp ([n]) and up, returns the date and time of the time zone in which the current database is located, n represents the millisecond precision, >0 and <6
For example: SELECT Systimestamp (4) from DUAL;
4. Dbtimezone returns the current time zone of the database
For example: SELECT dbtimezone from DUAL;
5. Sessiontimezone returns the current time zone of the session
For example: SELECT sessiontimezone from DUAL;
6, EXTRACT (key from date) key= (Year,month,day,hour,minute,second) mentions the specified date column from the specified time
For example: SELECT EXTRACT (year from sysdate) from DUAL;
7, To_timestamp (C1[,FMT]) converts the specified character to TIMESTAMP format as specified.
For example: SELECT to_timestamp (' 2007-8-22 ', ' yyyy-mm-dd HH:MI:SS ') from DUAL;
(v). Conversion function (Conversion Functions)
A conversion function converts a specified character from one type to another, typically such functions follow the following conventions: The function name is followed by the type to be converted and the output type.
1, Bin_to_num (N1,N2...N) converts a set of bit vectors into equivalent decimal forms.
For example: SELECT bin_to_num (1,1,0) from DUAL;
2, CAST (c as Newtype) converts the specified string to the specified type, basically only valid for character types, such as Char,number,date,rowid. This type of conversion has a specialized table that lists which types can be converted to which type, and is not described here as sprinkle.
For example: SELECT CAST (' 1101 ' as number (5)) from DUAL;
3, Chartorowid (c) Convert string to ROWID type
For example: SELECT chartorowid (' a003d1abbefaabsaa0 ') from DUAL;
4, Rowidtochar (ROWID) Conversion rowID value is the VARCHAR2 type. Returns a string length of 18 bytes.
For example: SELECT Rowidtochar (ROWID) from DUAL;
5, To_multi_byte (c) converts the specified character to full width and returns the char type string
For example: SELECT to_multi_byte (' abc abc China ') from DUAL;
6, To_single_byte (c) converts the specified character to a half-width and returns the char type string
For example: SELECT to_single_byte (' abc abc China ') from DUAL;
(vi). Other auxiliary functions (Miscellaneous Single-row Functions)
1, COALESCE (n1,n2,.... N) returns the first non-null value in a sequence
For example: SELECT coalesce (null,5,6,null,9) from DUAL;
2. DUMP (Exp[,fmt[,start[,length]])
Dump is a very powerful function that is useful for people who have a deep understanding of Oracle storage. So for those of us who are just applications, we don't know where to apply them. Only usage is described here, and no in-depth analysis of its functionality is done.
As shown above, dump has a number of parameters. Its essence is to return the VARCHAR2 value of the internal representation of exp of the specified length in the specified format. 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 length indicates the number of strings separated by.
For example: SELECT DUMP (' ABCDEFG ', 17,2,4) from DUAL;
3. Empty_blob,empty_clob both functions return an empty LOB type, which is typically used for statements such as insert and update to initialize the LOB column, or to leave it empty. Empty indicates that the LOB has been initialized, but has not yet been used to store the data.
4, Nls_charset_name (n) returns the character set name corresponding to the specified value.
For example: SELECT nls_charset_name (1) from DUAL;
5, nls_charset_id (c) Returns the character set ID corresponding to the specified character.
For example: SELECT nls_charset_id (' Us7ascii ') from DUAL;
6, Nls_charset_decl_len (N1,N2) returns the declaration width of a nchar value, in characters. N1 is the length of the value in bytes, N2 is the character set ID of the value
For example: SELECT Nls_charset_decl_len (nls_charset_id (' Us7ascii ')) from DUAL;
7, SYS_EXTRACT_UTC (timestamp) return to the standard universal Time is GMT.
For example: SELECT SYS_EXTRACT_UTC (Current_timestamp) from DUAL;
8, Sys_typeid (object_type) returns the ID corresponding to the object type.
For example: This one, has not built a custom object, how do I do an example?
9. UID returns an integer that uniquely identifies the current database user.
For example: SELECT UID from DUAL;
10. User returns to current session
For example: SELECT USER from DUAL;
11, USERENV (c) The function is used to return the information of the current session, according to the Oracle documentation, USERENV is the legacy function to maintain backward compatibility. Oracle recommends that you use the Sys_context function to call the Userenv namespace to get relevant information, so you can understand it.
For example: SELECT USERENV (' LANGUAGE ') from DUAL;
12, Vsize (c) Returns the number of bytes in C.
For example: SELECT vsize (' ABC China ') from DUAL;
Oracle Common numeric functions, conversion functions, String functions
