Oracle Common functions

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: the famous function-the frequently used functions of non-famous letters-that is, although rarely used, but in some cases it is very 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 function (number Functions) numeric function Enter a numeric parameter and return 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 char type returned does not exceed 2000 bytes;? The value of the returned VCHAR2 type is not more than 4000 bytes, and 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, and 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) converts the characters within the specified string into lowercase, supporting char,varchar2,nchar,nvarchar2,clob,nclob types such as: SELECT LOWER (' What's This ') from DUAL;

2, UPPER (c) to capitalize the characters within the specified string, supporting CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB types such as: 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 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 supplemented 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 ', 25, '-') 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 ', 25), 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:? If no parameters are specified, Oracle removes C1 kinsoku spaces for example: SELECT TRIM (' What's This ') from DUAL; If the C2 parameter is specified, Oracle removes the C1 kinsoku C2 (this recommendation is carefully tested, in a number of different situations) For example: SELECT TRIM (' W ' from ' What's This 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 ') from DUAL;? If the trailing parameter is specified, the C1 tail C2 is removed, for example: SELECT TRIM (Trailing ' w ' from ' What's This 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:? Keep the first letter of the string, but delete 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;? Only the first 4 bytes are returned, not enough for 0 fills such as: 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= the beginning length of the string, the length of the n2= intercept, or, if NULL, the end of the string by default;? What if N1=0 then n1=1? If n1>0, then Oracle confirms from left to right the starting position interception for example: SELECT SUBSTR (' What's This ', 5,3) from DUAL;? If n1<0, Oracle confirms the starting position from the right-to-left number for example: SELECT SUBSTR (' What's This ', -5,3) from DUAL;? If n1>c1.length returns null 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 literal description still does not understand, we use a few examples to illustrate: For example: SELECT TRANSLATE (' What's this ', ' ', '-') from DUAL; SELECT TRANSLATE (' What's this ', '-', ') from DUAL; The results are all empty. Here's a try: SELECT TRANSLATE (' What's this ', ' ', ' ") from DUAL; See 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: The 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 class of functions, except Months_between returns a value, the other returns the date.

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 current session's timezone, 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 of the specified time 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 taken to 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). The conversion function (Conversion Functions) 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? Convert 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 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 the sequence, for example: Select Greatest (15,5,75,8) "greatest" from DUAL;

3, LEAST (N1,N2....N) returns the smallest 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. The logic is equivalent to: case if 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? If C1 is not empty then return C2, if C1 is empty then return 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. Example: Create TABLE Tmp3 (Rootcol varchar2 (Ten), 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, etc. 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 function (number Functions) numeric function Enter a numeric parameter and return 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 for 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: Suppose 3,9 do bit and operation, 3 binary form is: 0011,9 binary form is: 1001, then the result is 0001, converted to 10 binary number 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 numeric value, but if M is a negative number, then 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 char type returned does not exceed 2000 bytes;? The value of the returned VCHAR2 type is not more than 4000 bytes, and 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, and 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 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). The character function returns a numeric value (Character Functions returning number values) This class function supports all data types 1, ASCII (c) and the use of the CHR function is just the opposite, This function returns the value of the specified character under the current character set. For example: SELECT ASCII ('_') from DUAL;

(iv). Date function (Datetime Functions) in this class of functions, except Months_between returns a value, the other returns the date. 1, Current_timestamp ([n]) returns the date and time of the current session's time zone. n represents 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 represents 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 time zone of the current 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). The conversion function (Conversion Functions) 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) converts a string to a 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 a 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 a 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) return the first non-null value in a sequence such as: 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, the user returns the current session users such as: 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 functions