oracle function usage

Oracle is one of the world's largest relational database management systems. It provides a wealth of functions to process data. These functions are widely used in SQL and are very useful and can help you better process and manipulate data. In this article, we will learn the commonly used functions, syntax and usage in Oracle.

Oracle function types

In Oracle, functions can be divided into two types: single-row functions and multi-row functions.

Single-row functions

Single-row functions (Scalar Functions) calculate a single value for each row and return a single-row result. They can be used in SELECT statements. The following are common single-line functions:

1. String functions: UPPER/LOWER (case conversion), SUBSTR (string interception), LENGTH (string length), TRIM (removal of spaces), etc. For example:

SELECT UPPER('hello world') FROM dual;
-- Output: HELLO WORLD

2. Numeric functions: ROUND (rounding), TRUNC ( Truncation), MOD (remainder), etc. For example:

SELECT ROUND(3.14159, 3) FROM dual;
-- Output: 3.142

3. Date function: SYSDATE (current system time), ADD_MONTHS ( Increment month), MONTHS_BETWEEN (month difference between two dates), etc. For example:

SELECT ADD_MONTHS(SYSDATE, 1) FROM dual;
-- Output: 2022-10-27 11:08:17

Multi-line function

Multiple row functions (Group Functions) calculate and return a single value common to a group of rows. Multi-row functions are often used in the GROUP BY clause of a SELECT statement. The following are common multi-row functions:

1. AVG: Returns the average of a set of values

SELECT AVG(salary) FROM employees;

2. COUNT: Returns the number of a set of values

SELECT COUNT(*) FROM employees;

3. MAX: Returns the maximum value in a set of values

SELECT MAX(salary) FROM employees;

4. MIN: Returns the minimum value in a set of values

SELECT MIN(salary) FROM employees;

5. SUM: Returns the sum of a set of values

SELECT SUM(salary) FROM employees;

Oracle function usage

function The syntax is as follows:

function_name(argument1, argument2, ...)

Where, function_name is the name of the function, argument1, argument2, ... are the parameters of the function. Different functions can have different parameters. For specific usage, please refer to Oracle official documentation.

Let us look at the usage of several common functions:

1. String function - UPPER

The UPPER function will input all the characters in the string Convert to uppercase.

For example:

SELECT UPPER('hello world') FROM dual;
-- Output: HELLO WORLD

2. String function-SUBSTR

SUBSTR function can be used to intercept substrings in a string. The syntax is as follows:

SUBSTR(source_string, start_position, length)

Among them, source_string is the source string to be intercepted, start_position is the position to start intercepting, length is the length to be intercepted (optional) parameter, if not specified, it will be truncated to the end of the source string).

For example:

SELECT SUBSTR('Hello, World', 1, 5) FROM dual;
-- Output: Hello

3. Numeric function- ROUND

The ROUND function rounds a number to a specified number of decimal places.

For example:

SELECT ROUND(3.14159, 3) FROM dual;
-- Output: 3.142

4. Date function- SYSDATE

The SYSDATE function is used to retrieve the current date and time. For example:

SELECT SYSDATE FROM dual;
-- Output: 2021-09-27 11:08:17

5. Multi-line function- COUNT

The COUNT function returns the number of rows in the specified column.

For example:

SELECT COUNT(*) FROM employees;
-- Output: 107

The above are only a small part of the use of Oracle functions. Oracle has rich Function library, each function has its own unique purpose. Understanding the syntax and usage of Oracle functions will allow you to process data more efficiently.

The above is the detailed content of oracle function usage. For more information, please follow other related articles on the PHP Chinese website!