Home >Database >Oracle >what is oracle function

what is oracle function

青灯夜游Original: 2022-02-22 17:29:203808browse

In Oracle, functions are also called PL/SQL subroutines, which are stored in the Oracle database as database objects; the function will process the parameters passed in and return a processing result, that is, return a value. Oracle functions can be divided into two categories: system functions and user-defined functions.

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

Definition of Oracle function

Function is stored in the oracle database as a database object, and the function is also called a PL/SQL subroutine.

The function will process the parameters passed in and return a processing result, that is, return a value. Usually used to return specific data.

Oracle functions can be divided into two categories: system functions and user-defined functions.

ORACLE function single-line function

Does not change the real data, only further modifies or processes the data for display
Can be mixed with fields
Pseudo table: a real table that exists temporarily to facilitate data verification; table name: dual

1.Character function

##upper(char) Convert to uppercaseupper('nice')NICEltrim(char,set)left shearltrim('nice','ni')certrim(char,set)right shearrtrim('nice','e')nic##concat(char1,char2)##substr(char,pos,len)Get substringsubstr('nice',2,2)icCheck the substring position##translate(char,key,value)Translate by character mappingtranslate('nice','ne','01')0ic1replace( char,old,new)String replacementreplace('nice','c','cc')nicce2. Numerical function

Function name	Function	Example	Result
initcap(char)	Capitalize the first letter	initcap('nice')	Nice
lower(char)	Convert to lower case	lower('NICE')	nice



String concatenation	concat('ni','ce')	nice
			##instr(char,substr)
instr('nice','c')	3

Function name

FunctionExampleResultTake the absolute valueabs(-3)mod(3,2)sign(-3)ceil(3.2)floor(3.2)round(3.236,2)power(3,2)##sqrt(n)square rootsqrt(4)2trunc(m,n)truncatedtrunc(3.233,2 )3.2301

##abs(n)

mod(m,n)

remainder

sign(n)

Get the sign

-1

ceil(n)

Round up

floor(n)

Round down

round(m,n)

Round to the specified number of decimal places

3.24

power(m,n)

m to the nth power

##sin(n)

sine

sin(0)

cos(n)

cosine

cos(0)

3.Date function

Function name	Function	Example	Result
months_between(date1,date2)	Returns the months between two days (if date1 is earlier than date2 in the calendar, return one Negative number, otherwise a positive number is returned)	months_between('01-August-2020', '01-August-2020')	0
add_months(date,m)	Returns the new date with the number of months added to the date	add_months('25-August-2020',-1)	25-July-2020
next_day(date,week)	Returns the new date corresponding to the week after the specified new date	next_day('23-August-2020','Monday')	24-August-2020
last_day(date)	Returns the last day of the month where the specified date is located	last_day('25-August-2020')	31-August-2020
round (date,p)	Round the date according to the specified format	round(to_date('25-August-2020'), 'YEAR')	1- January-2021
trunc(date,p)	Truncate the date in the specified way	trunc(to_date('25-8 month- 2020'),'YEAR')	01-January-2020

Note:

p is YEAR, round is based on January to June and July to December, and is rounded to the nearest January of yyyy year; trunc is intercepted to the first day of this year.
p is MONTH, round is based on days 1-15 and 16-30, rounded to the 1st of the nearest mm month; trunc is intercepted to the first day of this month.
p is DAY, round is based on Monday to Wednesday and Thursday to Sunday, rounded to the nearest Sunday. trunc is intercepted to the first day of this week.

Oracle function multi-line function

Perform statistics on query data
Cannot be mixed with ordinary fields and single-line functions, unless grouped
##avg(field name)Return Average value of this fieldcount(*)Returns the number of table recordscount(field name)Return the number of non-null valuescount(distinct field name)Return the number of field values after deduplication

Function name Function

max(field name) Return the maximum value of the field

min(field name) Return the minimum value of the field

sum(field name) Return the sum of the field

oracle function conversion function

to_number (numeric type characters): Convert characters For numerical value
to_char (numeric value or date): Convert the numerical value or date into the character
and specify the display format:
9 represents placeholder, for example :999,999,999 will separate the numbers in groups of three commas.
0 means occupancy. If the actual data digits are insufficient, 0 will be used to fill the digits.
L represents the RMB symbol, $ represents the US dollar symbol.
Note: Implicit conversion between numerical values and characters is possible.
to_date (characters in date format): Convert characters to dates (generally added for new use, query with to_char)
Common date format: yyyy-mm-dd
yyyy/mm/dd
'yyyy"year"mm"month"dd"day"'
Note: The characters must conform to the date format; Oracle's default conversion format is Day, month and year, for example '25-August-2020'.

Other functions of oracle functions

nvl (field name, new value) : If the field value is not null, return the field value; if it is null, return the new value.
nvl2 (field name, process 1, process 2): If the field value is not null, process 1 is executed; if it is null, process 2 is executed.
decode (field name, value 1, processing 1, value 2, processing 2, value 3, processing 3, ..., public processing): If the field value and the condition in decode If the values are the same, the corresponding processing will be performed. If there are none, public processing is performed.

what is oracle function

Related articles