What are the functions of oracle?

Oracle's functions include: 1. String functions, including ASCII(), CONCAT(), etc.; 2. Numeric functions, including ABS(), COS(), etc.; 3. Date functions, including EXTRACT( ), ROUND(), etc.; 4. Conversion functions, including TO_CHAR(), TO_DATE(), etc.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

Oracle SQL statements often use Oracle's own functions. These functions enrich the language functions of SQL and provide more operability for Oracle SQL. Oracle functions can accept zero or more input parameters and return an output result. There are two main types of functions used in Oracle database:

1. Single-row function: When each function is applied to the records of the table, only the column values ​​in one row can be entered as input. parameters (or constants), and returns a result.

For example 1: MOD(X,Y) is a remainder function that returns the remainder of X divided by Y, where X and Y can be column values ​​or constants.

For example 2: TO_CHAR(X,'YYYYMMDD') is a function that converts time type to string, where X can be a column of a certain time type (date) in the row, or it can be a time type constant .

Commonly used single-line functions are generally in the following categories:

• String functions: operate on strings, such as: TO_CHAR(), SUBSTR(), DECODE() etc.

• Numeric function: performs calculations or operations on numerical values ​​and returns a number. For example: ABS(), MOD(), ROUND(), etc.

• Conversion function: Convert one data type to another type: for example: TO_CHAR(), TO_NUMBER(), TO_DATE(), etc.

• Date function: A function that operates on time and date. For example: TRUNC(), SYSDATE(), ADD_MONTHS(), etc.

2. Aggregation function: Aggregation function can operate on multiple rows of data at the same time and return a result. For example, SUM(x) returns the sum of the x columns in the result set.

1. String function

Character function accepts character parameters, which can be columns in the table or a string expression.

Commonly used character functions:

##LENGTH(X) Returns the length of X##LOWER(X)UPPER(X)LTRIM(X[,TRIM_STR])RTRIM(X[,TRIM_STR])##TRIM([TRIM_STR FROM]X)REPLACE(X,old,new)SUBSTR(X,start[,length])Examples of the above functions:

Function	Description
ASCII(X)	Returns the ASCII code of character X
CONCAT(X,Y)	Connect strings X and Y
##INSTR(X,STR[, START][,N)	Search str from X, you can specify starting from start, or you can specify starting from n
X is converted to lowercase
X is converted to uppercase
Truncate the trim_str string from the left side of X, default Truncate spaces
Truncate the trim_str string to the right of X , spaces are truncated by default
Truncate both sides of X trim_str string, spaces are truncated by default
in X Find old and replace it with new
Return X String, starting from start, intercepting length characters, default length, default to the end

ExampleSELECT ASCII ('a') FROM dual;Helloworld8##SELECT LENGTH('Hello') FROM dual;5

Example result

##97

##SELECT CONCAT('Hello','world') FROM dual;

##SELECT INSTR('Hello world','or') FROM dual;

##SELECT LOWER('Hello') FROM dual;

hello

SELECT UPPER('hello') FROM dual;

HELLO

SELECT LTRIM('=Hello=','=') FROM dual;

Hello=

SELECT RTRIM('=Hello=','=') FROM dual;

=Hello

SELECT TRIM('='FROM'=Hello=') FROM dual;

Hello

SELECT REPLACE(' ABCDE','CD','AAA')FROM dual;

ABAAAE

##SELECT SUBSTR('ABCDE ',2,3) FROM dual;

BCD

2. Numeric function

Numeric function accepts numeric parameters. The parameter can come from a column in the table, or it can be a numeric expression.

##FLOOR(X)The maximum value less than or equal to XFLOOR(5.8)=5##LOG(X ,Y)MOD(X,Y)##POWER(X,Y)##ROUND(3.456, 2)=3.46The square root of XSQRT(4)=2X is truncated at the Y positionTRUNC(3.456, 2)=3.45

Function

Description

Example

ABS(X)

Absolute value of X

ABS(-3) =3

##ACOS(X)

The inverse cosine of X

ACOS(1)=0

COS(X)

Cosine

COS(1)=0.54030230586814

CEIL(X)

is greater than or equal to X Minimum value

CEIL(5.4)=6

X is the logarithm of base Y

LOG(2,4)=2

The remainder of X divided by Y

MOD(8, 3)=2

X raised to the power of Y

POWER(2,3)=8

##ROUND(X[,Y])

X is rounded at the Yth position

SQRT(X )

TRUNC(X[,Y])

Instructions:

1. ROUND(X[,Y]), Rounding.

When the default y is used, the default y=0; for example: ROUND(3.56)=4.

y is a positive integer, which is rounded to y decimal places. ROUND(5.654,2)=5.65.

y is a negative integer, rounded to |y| places to the left of the decimal point. ROUND(351.654,-2)=400.

2. TRUNC(x[,y]), intercept directly without rounding.

When the default y is used, the default y=0; for example: TRUNC (3.56)=3.

Y is a positive integer, which is rounded to y digits after the decimal point. TRUNC (5.654,2)=5.65.

y is a negative integer, rounded to |y| places to the left of the decimal point. TRUNC (351.654,-2)=300.

3. Date function

The date function operates on dates. Commonly used date functions are:

1, ADD_MONTHS(d,n), add the specified number of months n to a certain date d, and return the calculated new date.

d represents the date, n represents the number of months to be added.

Example:

SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;

2. LAST_DAY(d), returns the last day of the month on the specified date.

Example:

SELECT SYSDATE,last_day(SYSDATE) FROM dual;

3. ROUND(d[,fmt]), returns a rounded date in the format of fmt value, d is the date, fmt is the format

model. The default fmt is DDD, which is a day in the month.

Ø ① If fmt is "YEAR", it will be rounded to January 1 of a certain year, that is, the first half of the year will be discarded, and the second half of the year will be regarded as the next year.

Ø ② If fmt is "MONTH", it will be rounded to the 1st of a certain month, that is, the previous month will be discarded, and the second half of the month will be regarded as the next month.

Ø ③ The default is "DDD", that is, a certain day in the month, the closest day, the first half of the day is discarded, and the second half of the day is regarded as the next day.

Ø ④ If fmt is "DAY", it is rounded to the Sunday of the nearest week, that is, the first half of the week is discarded, and the second half of the week is regarded as the Sunday of the next week.

Example:

SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),
ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

The function corresponding to ROUND is TRUNC (d[,fmt]), which operates on dates. TRUNC is very similar to ROUND, except The date is not rounded and is directly intercepted to the first day of the corresponding format.

4. EXTRACT(fmt FROM d), extract the specific part of the date.

fmt is: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. Among them, YEAR, MONTH, and DAY can match the DATE type or the TIMESTAMP type; but HOUR, MINUTE, and SECOND must match the TIMESTAMP type.

HOUR The time zone is not added to the matching results, so the results running in China are 8 hours smaller.

Example:

SELECT SYSDATE "date",
       EXTRACT(YEAR FROM SYSDATE)"year",
       EXTRACT(MONTH FROM SYSDATE)"month",
       EXTRACT(DAY FROM SYSDATE)"day",
       EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",
       EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",
       EXTRACT(SECOND FROM SYSTIMESTAMP)"second"
FROM dual;

4. Conversion function

Conversion function converts a value from one data type is another data type. Common conversion functions are:

1, TO_CHAR(d|n[,fmt])

Convert dates and numbers into strings in a specified format. Fmt is a formatted string

Code demonstration: TO_CHAR processing of dates

SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;

Code analysis:

In the formatted string , use double quotes to quote non-formatted characters

For formatting of numbers, the formatting characters are:

##$999Returns a dollar sign at the beginning of the numberEEEE9.99EEEEScientific notationLL999Add a local currency symbol before the number##PR

代码演示：TO_CHAR对数字的处理

SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual;

 

2、TO_DATE(X,[,fmt])

把一个字符串以fmt格式转换成一个日期类型

3、TO_NUMBER(X,[,fmt])

把一个字符串以fmt格式转换为一个数字

代码演示：TO_NUM函数

SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;

 

五、其它单行函数

1、NVL(X,VALUE)

如果X为空，返回value，否则返回X

例：对工资是2000元以下的员工，如果没发奖金，每人奖金100元

代码演示：NVL函数

SQL> SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;
-------------------------------------------------------------------
ENAME    JOB    SAL    NVL(COMM,100) 
SMITH    CLERK    800    100 
ALLEN    SALESMAN    1600    300 
WARD    SALESMAN    1250    500 
MARTIN    SALESMAN    1250    1400 
TURNER    SALESMAN    1500    50 
ADAMS    CLERK    1100    100 
JAMES    CLERK    950    100 
-------------------------------------------------------------------
7 rows selected

2、NVL2(x,value1,value2)

如果x非空，返回value1，否则返回value2

例：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元

代码演示：NVL2函数

SQL> SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm"
2   FROM EMP WHERE SAL<2000;
-------------------------------------------------------------------
ENAME    JOB    SAL    comm 
SMITH    CLERK    800    200 
ALLEN    SALESMAN    1600    400 
WARD    SALESMAN    1250    600 
MARTIN    SALESMAN    1250    1500 
TURNER    SALESMAN    1500    150 
ADAMS    CLERK    1100    200 
JAMES    CLERK    950    200 
MILLER    CLERK    1300    200
-------------------------------------------------------------------------------------------------------
8 rows selected

六、聚合函数

聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值

等。

Parameter	Example	Description
##9	999	Display the number at the specified position
.	9.9	Returns the decimal point at the specified position
,	99,99	Returns a comma at the specified position
$
999PR	If the number is negative, use angle brackets to indicate it

名称	作用	语法
AVG	平均值	AVG（表达式）
SUM	求和	SUM(表达式)
MIN、MAX	最小值、最大值	MIN(表达式)、MAX(表达式)
COUNT	数据统计	COUNT（表达式）

例：求本月所有员工的基本工资总和

代码演示：sum函数

SQL> SELECT SUM(sal) FROM emp;
-------------------------------------------------------------------
SUM(SAL)
29025

例：求不同部门的平均工资

代码演示：AVG函数下的分组查询

SQL> SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;  
-------------------------------------------------------------------
DEPTNO    AVG(SAL)
---------    ----------
30    1566.66666
20     2175
10    2916.66666

推荐教程：《Oracle教程》

The above is the detailed content of What are the functions of oracle?. For more information, please follow other related articles on the PHP Chinese website!