what is oracle function

The oracle function is a PL/SQL subroutine. It is stored in the Oracle database as a database object. It will process the parameters passed in and return a processing result, that is, return a value. It is usually used to return a specific Data, oracle functions can be divided into two categories: system functions and user-defined functions.

The operating environment of this tutorial: Windows 10 system, Oracle version 19c, 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 Use

  pseudo table: a real table that exists temporarily to facilitate data verification; table name: dual

1. Character function

##initcap(char) Capitalize the first letterinitcap('nice')Nicelower(char) Convert to lowercaselower('NICE')niceupper(char)Convert to uppercaseupper('nice')NICE##ltrim(char,set)rtrim(char,set)##concat(char1,char2)String concatenationconcat(' ni','ce')nicesubstr(char,pos,len)Get substringsubstr ('nice',2,2)icinstr(char,substr)Check substring position instr('nice','c')3Map translation by character##replace(char,old,new) String replacementreplace('nice','c','cc')nicce

Function name	Function	Example	Result
left shear	ltrim( 'nice','ni')	ce
Right cut	rtrim(' nice','e')	nic
			##translate(char,key,value)
translate('nice','ne','01')	0ic1
			2. Numerical function

Function name

FunctionExampleResultGet the absolute valueabs(-3)##mod( m,n)Modulemod(3,2)1sign(n)Get the signsign(-3)-1ceil(n)Round upceil(3.2)4floor(n)Round downfloor( 3.2)33.24923.2301

##abs(n)

3

##round(m,n)

Round to the specified number of decimal places

round(3.236,2 )

power(m,n)

m to the nth power

power(3,2)

sqrt(n)

square root

sqrt(4)

trunc(m,n)

Truncation

trunc(3.233,2)

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, a negative number is returned, otherwise a Positive number)	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-7 Month-2020
next_day(date,week)	Returns the new date corresponding to the week after the specified new date	next_day('23-8 Month-2020', 'Monday')	24-August-2020
last_day(date)	Returns the month of the specified date Last day	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 from January to June and July-December, rounded to the nearest January of year yyyy; 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-row function

• Perform statistics on query data

• Cannot be combined with ordinary fields and single rows Functions are mixed unless grouped

  ##max( Field name)Returns the maximum value of the fieldmin(Field name)Returns the minimum value of the fieldsum(field name)Returns the sum of the fieldavg(field name)Returns the average value of the fieldcount(*)Returns the number of table recordscount(field name)Returns non-null Number of valuescount(distinct field name)Returns the number of field values ​​after deduplication

  Function name	Function

Oracle function conversion function

• to_number (numeric type character): Convert characters to numerical values

• to_char (numeric value or date): Convert a numerical value or date into a character
  Specify the display format:
  9 represents a placeholder, for example: 999,999,999 will separate the numbers in groups of three commas open.
  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 If it is null, the field value is returned; if it is null, the new value is returned.

• 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.

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