19 commonly used Oracle built-in functions

1. nvl(expression1, expression2)

Function function: Return a non-null value from two expressions

Use example: select nvl(father_name, mother_name) parent_name from student where student_id = '12345'

Note:

If the value of expression1 is not empty, the value of expression1 will be taken first;

If the value of expression1 is empty and If the value of expression2 is not empty, the value of expression2 is taken;

If expression1 and expression2 are both empty, the result is NULL

2. decode(field_name, value1, new_value1, value2 , new_value2, default_value)

Function function: similar to if...else... statement block, for a certain field, if its value is value1, it is converted to newValue1, if the value is value2 , it is converted to newValue2, and the default value is displayed in other cases

Use case: select decode(id,'1','A','2','B',id) from A;

Note:

decode(field_name, value1, new_value1, value2, new_value2, default_value) where value1, newValue1, etc. can be an expression

3. row_number(order by field_name )

Function function: Sort the data set according to a certain field and generate a serial number field

Use example: select row_number() over(order by name) no,id,name from a;

4. to_date(source_string, formater_string)

Function: Convert string to date type

Use example: select to_date(' 20190809','yyyyMMdd') from dual;

Note:

'yyyyMMdd','yyyymmdd','yyyy-MM-dd','yyyy-mm-dd' are all acceptable

5. to_char()

Function function: Convert other types to string types

Use case 1: select to_char(sysdate, 'yyyymmdd' ) from dual

Use case 2: select to_char(99, 'fm999.00') from dual

Note:

There are many other date formats in use case 1, Such as yyyy, mm, dd, D, DD, DDD, etc.

In use case 2, fm, 9, 0 have different meanings, as shown in the following table

Character flag	Meaning
9	If a number exists, it will display the number, if it does not exist, it will display a space
0	If the number exists, the number will be displayed. If it does not exist, it will display 0, that is, the placeholder
fm	Delete the spaces caused by 9

6. wm_concat

function function: line Column transfer, aggregating multi-row query results into a certain column of a row

Use example: select wm_concat(distinct name) from student

Note: higher versions of Oracle may remove

7. listagg() within group(order by field_name) over(partition by field_name)

Function function: same as wm_concat

Use example: select listagg(distinct name) within group(order by name desc) from student

8. concat(expression1, expression2)

Function function: String splicing function

Use case ：select concat('left', 'Right') from dual

Note: You can also use || to splice, select 'a'||'b' from dual

9. sys_guid()

Function: Generate and return a globally unique identifier (original value) consisting of 16 bytes and 32 characters

Use example: select sys_guid () from dual;

Note: often used as the primary key of the table

10. over(partition by field_name, order by field_name)

Function: The over function is an analytical function. When used together with the aggregate function, the code can be simplified.

Use example:

select name, job, sal, deptno,
sum(sal) over(partition by deptno) sum_sal,     --统计某组中的总计值
avg(sal) over(partition by deptno) avg_sal,     --统计某组中的平均值
from emp;

Note: According to the previous writing method, advanced grouping statistics are generated to generate temporary tables and associate the original tables. Only then can you get other information, which is no longer needed now

Usually used with aggregate functions such as max(), min(), avg(), sum()

11 . nlssort

Function: Provide special sorting of Simplified Chinese

Use example:

select * from student order by nlssort(name, 'nls_sort = schinese_pinyin_m') --拼音
select * from team order by nlssort(name, 'nls_sort = schinese_stroke_m')    --笔画
select * from team order by nlssort(name, 'nls_sort = schinese_radical_m')   --部首

12. trunc

Function: intercept dates or numbers and return the specified value according to the rules

Use case 1: select trunc(sysdate, 'yyyy') from dual

Use case 2: select trunc(126.56, 0 ) from dual

Note:

There are other rules in use case 1 that represent different meanings

Rules	Meanings
mm	Return to the first day of the month
yy	Return The first day of the year
dd	Returns the current year, month and day
yyyy	Returns the first day of the year Day
d	Returns the first day of the current week

Use Case 2 There are other rules that represent different Meaning

Rule	Meaning
0	Default Value, take and round
Positive number a	Retain a decimal place after the decimal point, and erase the others. If a is more than the number of decimal places after the decimal point, keep it Original value
Negative number b	Remove everything after the decimal point, move the decimal point abs(b) places to the left, and replace the erased numbers with 0. If all the numbers are If erased, 0

is returned.

13. rank() over(partition by field_name order by field_name)

Function function: Let the returned results generate a ranking relationship based on the partition and sorting fields

Use case ：select rank() over(partition by birthday order by score), s.* from student s;

Note: The usage of dense_rank() is the same as rank(), the difference is whether the ranking jumps

14. substr(source, start [,length])

Function: intercept string

Use example: select substr('abcde', 2, 3) from dual

Note: oracle string index starts from 1

15. replace(field_name, sub_str, replace_str)

Function function: will specify Replace the string with the specified string

Use example: select replace(name, 'hello', 'world') from student;

Note: It can also be used in the set part of the update statement

16. trim

Function function: remove the blank characters at the left and right ends

Use example: select trim(' dsf ') from dual;

Note: Use ltrim to remove only the left blank characters, and rtrim to remove only the right blank characters

17. sign

Function: Get the sign of the number n, If greater than 0, return 1, if less than 0, return -1, if equal to 0, return 0

Use example: select sign(50),sign(-100),sign(0) from dual;

18. round(number[,decimal])

Function function: round the number n, retaining decimal digits

Use example: select round(123.34),round( 123.51),round(123.56,1),round(123.34,-1) from dual;

19. coalesce(expression1,expression2...)

function Function: Return the first value in the expression that is not empty. If all are empty, return a null value

Use example: select coalesce(null,3 5,4 6) value from dual;

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of 19 commonly used Oracle built-in functions. For more information, please follow other related articles on the PHP Chinese website!