Function usage in database

What role does the function play in the database

The functions in the database encapsulate some common functions, such as conversion between date types and string types. Each database system has some built-in functions. Of course, users can also define their own functions.

These functions can be used in SQL and background stored procedures or triggers, but the SQL using these functions is no longer standard SQL and has no portability. Therefore, in general, try not to use custom functions in SQL. If you want to use them, it is best to use them in stored procedures or triggers. Even the built-in functions of the database should be avoided as much as possible, because each database is written differently. The conversion of date types and strings is an example.

When using the advantages brought by functions, portability is also lost.

Of course, when transplanting a program to a different database, using functions can sometimes avoid modifying sql. For example, when using the source database A, the sql contains a large number of functions fa, but the corresponding function in the target database B is fb. In this case, a function fa can be established in the database B to avoid modifying the sql statement.

How to use functions in oracle database

There are many functions in the oracle database, and each function has its own usage, so you need to query the specific usage method according to the specific function, such as:

decode(condition, value 1, return value 1, value 2, return value 2, ... value n, return value n, default value)

The result of this function is that when the value of the field or field operation is equal to the value 1, the function returns the value 2, otherwise it returns the value 3

Of course value 1, value 2 and value 3 can also be expressions. This function makes certain sql statements much simpler

Instructions:

1. Compare size

select decode(sign(variable 1-variable 2),-1, variable 1, variable 2) from dual;

--Take the smaller value

The sign() function returns 0, 1, and -1 respectively depending on whether a value is 0, a positive number, or a negative number

For example:

Variable 1=10, Variable 2=20

Then sign (variable 1-variable 2) returns -1, and the decode decoding result is "variable 1", achieving the purpose of taking a smaller value.

2. This function is used in SQL statements. The function description is as follows:

Decode function and a series of nested

IF-THEN-ELSE statements are similar. base_exp is compared with compare1, compare2, etc. in sequence. If base_exp and i

Compare items match, then the i-th corresponding value is returned

. If base_exp does not match any compare value, default is returned. Each compare value is evaluated sequentially, and if a match is found, the remaining compare values ​​(if any) are no longer valid. A NULL base_exp is considered the same as NULL

Compare values ​​are equivalent. If necessary, each compare value is converted to the same data type as the first compare value, which is also the type of the return value.

What are the commonly used function formulas in excel

1. ABS function

Function name: ABS

Main function: Get the absolute value of the corresponding number.

Use format: ABS(number)

Parameter description: number represents the numerical value or referenced cell that requires an absolute value.

Application example: If you enter the formula: =ABS(A2) in cell B2, no matter whether you enter a positive number (such as 100) or a negative number (such as -100) in cell A2, the positive number will be displayed in B2 (like 100).

Special reminder: If the number parameter is not a numerical value, but some characters (such as A, etc.), the error value "#VALUE!" will be returned in B2.

2. AND function

Function name: AND

Main functions: Return logical values: If all parameter values ​​are logical "TRUE", then return logical "TRUE", otherwise return logical "FALSE".

Use format: AND(logical1,logical2, ...)

Parameter description: Logical1, Logical2, Logical3...: Indicates the condition value or expression to be tested, up to 30 of these.

Application example: Enter the formula in cell C5: =AND(A5>=60,B5>=60) and confirm. If TRUE is returned in C5, it means that the values ​​in A5 and B5 are both greater than or equal to 60. If FALSE is returned, it means that at least one of the values ​​in A5 and B5 is less than 60.

Special reminder: If the specified logical condition parameter contains a non-logical value, the function returns the error value "#VALUE!" or "#NAME".

3. AVERAGE function

Function name: AVERAGE

Main function: Get the arithmetic mean of all parameters.

Using format: AVERAGE(number1,number2,……)

Parameter description: number1, number2,...: The numerical value or reference cell (region) that requires the average value, no more than 30 parameters.

Application example: Enter the formula in cell B8: =AVERAGE(B7:D7,F7:H7,7,8). After confirmation, the values ​​in the B7 to D7 area, F7 to H7 area and 7 , the average value of 8.

Special reminder: If the reference range contains "0" value cells, they will be counted; if the reference range contains blank or character cells, they will not be counted.

4. COLUMN function

Function name: COLUMN

Main function: Display the column label value of the referenced cell.

Use format: COLUMN(reference)

Parameter description: reference is the referenced cell.

Application example: Enter the formula in cell C11: =COLUMN(B11). After confirmation, it will be displayed as 2 (i.e. column B).

Special reminder: If you enter the formula: =COLUMN() in cell B11, 2 will also be displayed; correspondingly, there is a function that returns the row label value-ROW(reference).

5. CONCATENATE function

Function name: CONCATENATE

Main functions: Connect multiple character texts or data in cells together and display them in one cell.

Use format: CONCATENATE(Text1,Text……)

Parameter description: Text1, Text2...are the character text or referenced cells that need to be connected.

Application example: Enter the formula in cell C14: =CONCATENATE(A14,"@",B14,".com"). After confirmation, the characters in cell A14, @, and the characters in cell B14 can be The characters and .com are connected into a whole and displayed in cell C14.

Special reminder: If the parameter is not a referenced cell and is in text format, please add double quotes in English to the parameter. If the above formula is changed to: =A14&"@"&B14&".com", The same purpose can be achieved.

The above is the detailed content of Function usage in database. For more information, please follow other related articles on the PHP Chinese website!