Quick query of common functions in Oracle Database 2

Return the value of N1 rounded to the right of the decimal point. The default value of N2 is 0. This is the nearest integer to the decimal point. If N2 is a negative number, it is rounded to the corresponding digit on the left of the decimal point, n2 must be an integer.

Select round (12345,-2), round (12345.54321, 2)
From dualround (12345,-2) round (12345.54321, 2) 12300 12345.54
 

Sign ()

If n is negative,-1 is returned. If n is positive, 1 is returned. If n = 0, 0 is returned.

Sin ()

Returns the positive and negative values of N, and N is a radian.

Sinh ()

Returns the hyperbolic Xuan value of N, which is radian.

SQRT ()

Returns the square root of N, and N is a radian.

Tan ()

Returns the tangent of N. N is a radian.

Tanh ()

Returns the hyperbolic tangent of N. N is a radian.

Trunc (,) Network Technology Co., http://www.chinaccna.com.

Return the N1 value from the ending point to the N2 decimal point. The default value of N2 is 0. When N2 is the default value, the ending point of N1 is an integer. If N2 is a negative value, it is truncated at the right of the decimal point.

Single Row date functions

A single-row date function operates on data types. The vast majority of data type parameters are available, and the vast majority of returned data types are also values.

Add_months (, <I>)

Returns the result after date D plus I months. I can make any integer. If I is a decimal point, the database implicitly converts it to an integer, and the part after the decimal point is truncated.

Last_day ()

Returns the last day of the month containing the date D.

Months_between (,)

Returns the number of months between D1 and D2. If the date of D1 and D2 is the same, or the last day of the month is used, an integer is returned, otherwise, the returned result contains a score.

New_time (,,)

D1 is a date data type. When the date and time in Zone tz1 are d, the date and time in Zone tz2. Tz1 and tz2 are strings.

Next_day (,)

Returns the first day of the condition given by Dow after the date D. Dow specifies a day in a week using the language given in the current session, and returns the same time component as the time component of D.

Select next_day ('01-Jan-2000'

, 'Monday') "1st Monday ",
Next_day ('01-Nov-2004 ', 'tuesday') + 7 "2nd Tuesday ")
From dual; 1st Monday 2nd Tuesday03-Jan-2000 09-nov-2004
 

Round ([,])

Round the date d in the format specified by FMT, and FMT is a string.

Syadate

The function has no parameters. The current date and time are returned.

Trunc ([,])

Returns the date D of the unit specified by FMT.

Single Row Conversion Function

The single-row conversion function is used to operate on multiple data types and convert data types.

Chartorwid ()

C makes a string, and the function converts C to the rwid data type.

Select test_id from test_case where rowid = chartorwid ('aaaa0saacaaaaliaaa ')
Convert (, [,])

String at the end of C. dset and sset are two character sets. The function converts string C from the sset character set to the dset character set, and the sset is set to the database character set by default.

Hextoraw ()

X is a hexadecimal string. The function converts hexadecimal X to the raw data type.

Rawtohex ()

X is a raw data string. The function converts the raw data class to a hexadecimal data type.

Rowidtochar ()

The function converts the rowid data type to the char data type.

To_char ([[,)

X is a data or number data type. The function converts X to the char data type in the specified format of FMT. If X is the date nlsparm = nls_date_language, the language used to control the returned month and day. If X is the number nlsparm = nls_numeric_characters, it is used to specify the Separators for decimal places and kilobytes, as well as currency symbols.

Nls_numeric_characters = "DG", nls_currency = "string"
 

To_date ([,[,)

C Indicates a string, and FMT indicates a string in a special format. Returns the C language in the FMT Format. The nlsparm indicates the language used. The function converts string C to the date data type.

To_multi_byte ()

C Represents a string, and the function converts the truncation character of C to multi-byte characters.

To_number ([,[,)

C Indicates a string, FMT indicates a special string, and the function return value is displayed in the format specified by FMT. Nlsparm indicates the language, and the function returns numbers represented by C.

To_single_byte ()

 

Converts multiple characters in string C into equivalent single-byte characters. This function is used only when the database character set contains single-byte and multi-byte characters at the same time.

Other single-row Functions

Bfilename (,)

DIR is a directory object, and file is a file name. The function returns an empty bfile Location value indicator. The function is used to initialize the bfile variable or bfile column.

Decode (, [, [])

X is an expression, M1 is a matching expression, and X is compared with M1. If M1 is equal to X, R1 is returned. Otherwise, X is compared with m2, and so on, m5 .... until a result is returned.

Dump (, [, [, [,])

X is an expression or character. FMT indicates octal, decimal, hexadecimal, or single character. The function returns a value of the varchar2 type that contains the internal representation of X. If N1 and N2 are specified, bytes starting from N1 with N2 will be returned.

Empty_blob ()

This function has no parameters. The function returns an empty blob location indicator. Function is used to initialize a blob variable or blob column.

Empty_clob ()

This function has no parameters. The function returns an empty clob location indicator. The function is used to initialize a clob variable or clob column.

Greatest ()

Exp_list is a column expression that returns the largest expression. Each expression is implicitly converted to the Data Type of the first expression. If the first expression is any of the string data types, the returned result is of the varchar2 data type, and the comparison is of the non-padding space type.

Least () Network Technology Co., http://www.chinaccna.com.

Exp_list is a column expression that returns the smallest expression. Each expression is implicitly converted to the Data Type of the first expression. If the first expression is any of the string data types, the returned result is of the varchar2 data type, and the comparison is of the non-padding space type.

UID

This function has no parameters and returns an integer that uniquely identifies the current database user.

User

Returns the username of the current user.

Userenv ()

Returns information about the current session based on OPT. The optional value of OPT is:

Sysdba role response in isdba session, returns true

Sessionid returns the Audit Session identifier

Entryid returns available audit item identifiers

After the instance is connected to the session, the instance identifier is returned. This value is only used when the parallel server is running and multiple instances exist.

Language returns the character set of the language, region, and database.

Lang returns the ISO abbreviation of the language name.

Terminal is the identifier of the operating system returned by the terminal or computer used by the current session.

Vsize ()

X is an expression. Returns the number of bytes in X.

Group functions in SQL

A group function is also called a set function. It returns a single result based on multiple rows. The exact number of rows cannot be determined unless the query is executed and all results are included. Different from a single-row function, all rows are known during parsing. Due to this difference, the requirements and behaviors of group functions and single-row functions are slightly different.

Group (multiple rows) Function

Compared with single-row functions, Oracle provides a wide range of group-based, multi-row functions. These functions can be used in select or select having clauses. They are often used together with group by when used for select substrings.

AVG ([{disyinct | all}])

Returns the average value of a value. The default value is all.

Select AVG (SAL), AVG (all Sal), AVG (distinct Sal)
From Scott. empavg (SAL) AVG (all Sal) AVG (distinct Sal)
1877.94118 1877.94118 1916.071413
 

Count ({* | distinct | all })

Returns the number of rows in the query. The default value is all. * indicates that all rows are returned.

Max ([{distinct | all}])

Returns the maximum value of the selected list item. If X is a string data type, a varchar2 data type is returned. If X is a data type, a date is returned. If X is a numeric data type, returns a number. Note that distinct and all do not work. The maximum value must be the same as the two settings.

Min ([{distinct | all}])

Returns the minimum value of the selected list.

Stddev ([{distinct | all}])

Returns the standard deviation of the selected item list. The so-called standard deviation is the square root of the variance.

Sum ([{distinct | all}])

Returns the total number of items in the selected list.

Variance ([{distinct | all}])

Returns the statistical variance of the selected items.

Group

As the question implies, a group function is used to operate data that has already been divided into groups. We tell the database how to group or classify data using group, when we use group functions in the select clause of the SELECT statement, we must place grouping or extraordinary series in the group by clause. If we do not use group by for special processing, the default classification is to set the entire result to a class.

Select stat, counter (*) zip_count from zip_codes group by state;
St zip_count -- --------- AK 360al 1212ar 1309az 768ca 3982
 

In this example, we use the state field for classification. If we want to sort the results by zip_codes, we can use the order by statement. The order by clause can use columns or group functions.

Select stat, counter (*) zip_count from zip_codes Group
By state order by count (*) DESC; ST count (*)----------
NY 4312 PA 4297tx 4123ca 3982
 

Use having clause to restrict grouped data

Now you know that you can use the primary function in the SELECT statement and order by clause of the query. Group functions can only be used in two substrings. Group functions cannot be used in where substrings, for example, the following query is incorrect:

Error select sales_clerk, Sun (sale_amount)
From gross_sales where sales_dept = 'outside'
And sum (sale_amount)> 10000 group by sales_clerk
 

In this statement, the database does not know what sum () is. When we need to instruct the database to group rows and then limit the output of rows after the grouping, the correct method is to use the having statement:

Select sales_clerk, Sun (sale_amount)
From gross_sales where sales_dept = 'outside'
Group by sales_clerkhaving sum (sale_amount)> 10000;