Quick query of Oracle function List 2

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 ()

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 = 'outline' 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 = 'outline' group by sales_clerkhaving sum (sale_amount)> 10000;

　　Nested Functions

Functions can be nested. The output of a function can be the input of another function. The operands have an inherited execution process. However, the priority of a function is only based on the position, and the function follows the principle from inside to outside, from left to right. Nested technology is generally used for functions such as decode that can be used to logically determine the statement if... then... else.

Nested functions can include nested single-row functions in group functions, or nested group functions into single-row functions or group functions. For example:

Select deptno, greatest (count (distinct job), count (distinct MGR) CNT, count (distinct job) jobs, count (distinct MGR) mgrsfrom empgroup by deptno; deptno CNT jobs MGRS ------ ---- 10 4 4 220 4 3 430 3 3 2