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
1. Character function
| Function name | Function | Example | Result |
|---|
##initcap(char) | Capitalize the first letter | initcap('nice') | Nice |
lower(char) | Convert to lowercase | lower('NICE') | nice |
upper(char) | Convert to uppercase | upper('nice') | NICE |
##ltrim(char,set)| left shear | ltrim( 'nice','ni') | ce | |
rtrim(char,set)| Right cut | rtrim(' nice','e') | nic | | ##concat(char1,char2)
String concatenation | concat(' ni','ce') | nice | | substr(char,pos,len)
Get substring | substr ('nice',2,2) | ic | | instr(char,substr)
Check substring position | instr('nice','c') | 3 | ##translate(char,key,value) |
Map translation by character| translate('nice','ne','01') | 0ic1 | | ##replace(char,old,new) | String replacement
replace('nice','c','cc') | nicce | | 2. Numerical function |
Function name
FunctionExample | Result | | ##abs(n) |
Get the absolute valueabs(-3)| 3 | | ##mod( m,n) | Module | mod(3,2)
1 | | sign(n) | Get the sign | sign(-3)
-1 | | ceil(n) | Round up | ceil(3.2)
4 | | floor(n) | Round down | floor( 3.2)
3 | ##round(m,n) | Round to the specified number of decimal places | round(3.236,2 ) |
3.24 | power(m,n) | m to the nth power | power(3,2) |
9 | sqrt(n) | square root | sqrt(4) |
2 | trunc(m,n) | Truncation | trunc(3.233,2) |
3.23 | sin(n) | sine | sin(0) |
0 | cos(n) | cosine | cos(0) |
1 | 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 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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
