Summarize and organize commonly used functions in Oracle

This article brings you relevant knowledge about Oracle. It explains in detail the commonly used functions in Oracle and introduces them through sample codes. I hope it will be of certain reference value for everyone's study or work. ,I hope everyone has to help.

Recommended tutorial: "Oracle Video Tutorial"

There are two main types of functions used in the oracle database:

1. Single-line functions: operate one line of data and return a result.

• Commonly used single-line functions are:
• String functions: operate on strings.
• Number function: calculates numbers and returns a number.
• Date function: Process date and time.
• Conversion function: can convert one data type to another data type.

2. Aggregation functions (multi-row functions, group functions, group functions): operate on multiple rows of data and return a result. For example, SUM

1. String function

The character function accepts character parameters. These parameters can be columns in the table or a string expression.

Commonly used character functions:

#INSTR(X,STR[,START][,N)Find str from X, you can specify starting from start or starting from nLENGTH(X)Return the length of XLOWER(X)X is converted to lower caseUPPER(X)Convert X to uppercaseLTRIM(X[,TRIM_STR])Cut the left side of X Remove trim_str string, truncate spaces by default RTRIM(X[,TRIM_STR])Truncate trim_str string on the right side of X, truncate by default SpacesTruncate the trim_str string on both sides of X. Spaces are cut off by defaultFind old in X and replace it with newReturn the string of ExampleExample result

Function	Description
ASCII(X)	Return the ASCII code of character X
CONCAT(X,Y)	Connect strings
	##TRIM([TRIM_STR FROM]X)
	REPLACE(X,old,new)
	SUBSTR(X,start[,length])

SELECT ASCII('a') FROM dual;97SELECT CONCAT('Hello','world') FROM dual;HelloworldSELECT INSTR ('Hello world','or') FROM dual;8SELECT LENGTH('Hello') FROM dual;5SELECT LOWER('Hello') FROM dual;helloSELECT UPPER('hello') FROM dual; HELLOHello==HelloHello ABAAAEBCD二, Numeric functionFunction

	##SELECT LTRIM('=Hello=','=') FROM dual;
	SELECT RTRIM('=Hello=','=') FROM dual;
	SELECT TRIM('='FROM'= Hello=') FROM dual;
	SELECT REPLACE('ABCDE','CD','AAA')FROM dual;
	SELECT SUBSTR('ABCDE',2,3) FROM dual;
Numeric function accepts numeric parameters. The parameter can come from a column in the table, or it can be a numeric expression.

Description

Example

ABS(X)CosineThe minimum value greater than or equal to XThe maximum value less than or equal to XX is Logarithm of base YRemainder of X divided by Y##POWER(X,Y)X to the power of YPOWER( 2, 3)=8ROUND(X[,Y])X is rounded at the Yth positionROUND(3.456, 2) =3.46SQRT(X)The square root of XSQRT(4)=2TRUNC(X[,Y])X is truncated at the Y positionTRUNC(3.456, 2)=3.45

Instructions:

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

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 fmt is the rounded date value of the format, d is the date, and 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 is rounded to the 1st of a certain month, that is, the previous month is discarded, and the second half of the month is 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 will be rounded to the Sunday of the nearest week, that is, the first half of the week will be discarded, and the second half of the week will be 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 Very similar to ROUND, except that the date is not rounded and is directly intercepted to the first day of the corresponding format.

4, EXTRACT(fmt FROM d), extract a 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 to 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:

The absolute value of #ACOS(1)=0		COS(X)
COS(1)=0.54030230586814		CEIL(X)
CEIL(5.4)=6		FLOOR(X)
FLOOR(5.8)=5		LOG(X,Y)
LOG(2,4)=2		MOD(X,Y)
MOD(8,3)=2

Parameters	Example	Description
9	999	Display the number at the specified position
.	9.9	Specify the position to return the decimal point
,	99,99	Specify the position Returns a comma
$	$999	Returns a dollar sign at the beginning of the number
EEEE	9.99EEEE	Scientific notation
L	L999	Add a local currency symbol before the number
PR	999PR	If the number is negative, use angle brackets to express it

代码演示：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函数

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函数

SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm" 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

六、聚合函数

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

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

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

代码演示：sum函数

SELECT SUM(sal) FROM emp;

-------------------------------------------------------------------

SUM(SAL)

29025

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

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

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 Summarize and organize commonly used functions in Oracle. For more information, please follow other related articles on the PHP Chinese website!