Shulou(Shulou.com)06/01 Report--

This chapter introduces the built-in functions of the Oracle database. The function can take zero or more arguments and return an output parameter. There are two main types of parameters that can be used in Oracle databases:

A single-line function (single row function) A single-line function can only operate on one row at a time and returns a row of output results for each line of input. An example of an one-line function is CONCAT (xrecoery y), which adds y to x and returns the resulting string

Aggregate function (aggregate function) the aggregate function can operate on multiple rows at the same time and return one row of output results. An example of an aggregate function is AVG (x), which returns the average of x, where x can be a column, or an expression

Use one-line functions. There are five main types of one-line functions:

Character function. You can manipulate strings made up of characters

Numeric function. You can calculate the numbers.

Conversion function. You can convert one database type to another.

Date function. Date and time can be processed

Regular expression function. You can use regular expressions when querying data

1.1 character function

Character function list

Function description ASCII (x) returns the ASCII code CHR (x) of the character x returns the character CONCAT (xdepartment y) with the ASCII code x, adds y to x, and returns the resulting string as a result, INITCAP (x) converts the first letter of each word in x to uppercase, and returns the processing result INSET (x start findwords string [, start] [, occurentce]) looks for the string find_string in x, and then returns the location of the string. You can provide an optional start location to specify that the function starts looking from this location. Similarly, you can specify an optional occurrence parameter to indicate the position where the find_string occurs for the first time LENGTH (x) returns the length of the x string LOWER (x)

1.2 numeric function

Numeric functions can be used for calculations. A numeric function can accept input numeric parameters, which can come from a numeric column of a table or an expression that results in a numeric result. The numeric function calculates the input numbers and returns a numeric result. An example of a numeric function is SQRT (x), which returns the square root of x

Function

Description

Examples

ABS (x)

Returns the absolute value of x

ABS (10) = 10

ABS (- 10) = 10

ACOS (x)

Returns the inverse cosine of x

ACOS (1) = 0

ACOS (- 1) = 3.1415926

ASIN (x)

Returns the arcsine of x

These interested friends can verify by themselves.

ATAN (x)

Returns the inverse tangent of x

ATAN2 (x)

Returns the inverse tangent of x and y

BITAND (XBI y)

Returns the result of bit and (AND) operations on x and y

COS (x)

Returns the cosine of x

COSH (x)

Returns the hyperbolic cosine function of x

CEIL (X)

Returns the smallest integer greater than or equal to x

EXP (X)

Returns the x power of e, x equals approximately 2.7.18

FLOOR (X)

Returns the largest integer less than or equal to x

LOG (XQuery Y)

Returns the logarithm of y based on x

LN (x)

Returns the natural logarithm of x

MOD (XBI y)

Returns the remainder of x divided by y

POWER (XBI y)

Returns the y power of x

ROUND (x [, y])

Returns the result of rounding x

SIGN (x)

Returns-1 if x is negative, 1 if it is an integer, 0 if 0

SIN (X)

Returns the sine function of x

SINH (x)

Returns the hyperbolic sine function of x

SQRT (x)

Returns the square root of x

TAN (x)

Returns the tangent function of x

1.3 conversion function

Sometimes it may be necessary to convert a value from one data type to another.

ASCIISTR (x)

Convert x to an ASCII string

BIN_TO_NUM (x)

Convert binary number x to NUMBER type

DECODE (xresearch.resultResult default)

Compare x with the value in search, if equal, return the value of result, otherwise return default

TO_BINARY_DOUBLE (x)

Convert x to a BINARY_DOUBLE type

TO_BINARY_FLOAT (x)

Convert x to a BINARY_FLOAT type TO_BLOB (x)

Convert x to a binary large object (BLOB) type.

TO_CHAR (x [, format])

Convert x to a VARCHAR2 string. You can specify an optional parameter format to describe the format of x

TO_CLOB (x)

Convert x to a character large object CLOB type.

TO_DATE (x [, format])

Convert x to a DATE type.

TO_TIMESTAMP (x)

Convert the string x to a TIMESTAMP type

1.4 regular expression function

Regular expressions contain many metacharacters. ^ can match the beginning of a string: [5-8] can match a number between 5 and 8; $can match the end of a string.

Metacharacters commonly used in orcle

Metacharacter

Description

Examples

\

Indicates that the character to be matched is a special character, constant

\ nmatch newline characters

\ match\

\ (match (

\) match)

^

Matches the beginning of a string

$

Match the end position of the string

*

Match the previous character 0 or more times

Ba*rk can match brk,bark,baark

+

Match the previous string 1 this or more times

Ba+rk can match bark, baark, etc.

?

Match the previous character 0 or 1 times

{n}

Match a character exactly n times

Hob {2} it can match hobbit

{n,m}

Match a character at least n times and m times at most

.

Match any single character outside of null

(pattern)

Matches a specified subexpression

X | y

Match x or y

{abc}

Match any character in abc

[aMuz]

Matches any character in the specified range

[::]

Specify a character class that can match any character in the class

[: alphanum:] can match characters 0-9, Amurz and Amurz

[: alpha:] can match characters Amurz and Amurz

[: blank:] can match spaces and tab keys

[: digit:] can match the numbers 0-9

[: graph:] can match non-null characters

[: lower:] can match lowercase letters

[=]

Specify equivalence class

\ n

This is a reverse reference to the previous capture, where n is a positive integer

\ d

Numeric character

\ d

Non-numeric character

\ w

Alphabetic character

\ W

Non-alphabetic character

\ s

White space character

\ s

Non-white space character

\ a

Matches only the beginning of the string

\ Z

Matches only the end position of the string

*?

Match the previous pattern element 0 or more times

+?

Match the previous pattern element one or more times

??

Match the previous pattern element 0 times or

Regular expression function

Function

Description

REGEXP_LIKE (xQuery pattern [, match_option])

Search for the regular expression defined in the pattern parameter from x. You can use match_option to modify the default matching options

REGEXP_INSRT (xtraining pattern [, start [, occurence [, rerturn_option [, match_option])

Look for pattern in x and return the location of pattern. You can specify some optional parameters:

Start: the location to start the search. The default is 1, which means the first character of x

Occurrence states that the location of the current occurrence of pattern should be returned.

Return_option indicates what integer should be returned

Match_option modifies the default matching settings

REGEXP_REPLACE (xtraining pattern [, replace_string [, start [, occurrence [, match_option])

Look for pattern in x and replace it with replace_string. For other options, please refer to the previous method

Second, use aggregate functions

Function

Description

AVG (x)

Returns the average of x

COUNT (x)

Returns the number of rows of a query containing x

MAX (x)

Returns the maximum value of x

MEDIA (x)

Returns the intermediate value of x

MIN (x)

Returns the minimum value of x

STDDEV (x)

Returns the standard deviation of x

SUM (x)

Returns the sum of x

VARIANCE (x)

Returns the variance of x

Third, the rows are grouped into GROUP BY, and the row group is filtered using the having clause. Having is put after group. Group by may not be used with having, but having must be used with group by.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.