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

1. Compare the size function sign

Function syntax:

Sign (n)

Function description:

Take the symbol of the number n, greater than 0 returns 1; less than 0 returns-1; equal to 0 returns 0

Example:

1. Select sign (2), sign (- 2), sign (0) from dual

SIGN (2) SIGN (- 2) SIGN (0)

1-1 0

2. Asides 100 breadth 200

Then sign (aMub) returns-1

2. Nvl (EXPR1,EXPR2)

Function syntax:

NVL (EXPR1, EXPR2)

Function description:

Returns a non-null value from two expressions. If EXPR1 evaluates to a null value, NVL () returns EXPR2. If EXPR1 evaluates to a value other than null, EXPR1 is returned. EXPR1 and EXPR2 can be any data type. If the result of both EXPR1 and EXPR1 is a null value, NVL () returns .null.

Return value type:

Character type, date type, date-time type, numerical type, monetary type, logical type or null value

Application:

In cases where the null value is not supported or the null value is irrelevant, you can use NVL () to remove the null value from the calculation or operation.

Select nvl (a.name _ exp1,exp2,exp3 'empty') as name from student a join school b on a.ID=b.ID Note: the type of the two parameters must match. 3. Nvl2 (exp1,exp2,exp3)

Oracle extends the functionality of NVL () to provide the NVL2 function. This function decides which expression to return when exp1 is null or non-null:

If exp1 is null, exp3 is returned. If exp1 is not empty, exp2 is returned. 4. Coalesce () function

Coalesce is a function that (expression_1, expression_2,..., expression_n) refers to each parameter expression in turn, stops and returns a non-null value. If all expressions are null, a null value will eventually be returned.

1. The coalesce function is used to get the value of the first column that is not empty

2. All expressions in the coalesce function must be of the same type or can be converted to the same type.

3. In the view established by CREATE OR REPLACE VIEW v AS SELECT NULL AS c FROM dual;, the data type of column c is char.

Note: COALESCE (expression1,...n) is equivalent to this CASE function: CASE expressionWHEN (expression1 IS NOT NULL) THEN expression1...WHEN (expressionN IS NOT NULL) THEN expressionNELSE NULL

The coalesce () function can be used to handle almost all null values, but it is mentioned in many database systems.

A simplified version of it is provided, in which only two variables are accepted, and the parameter format is as follows:

MySQL:

IFNULL (expression,value) / / expr1 does not return expr1 for null, otherwise returns expr2

Oracle:

NVL (expression,value)

The functions of these functions are equivalent to COALESCE (expression,value). For example, the SQL statement is used to return the person.

Member's "sweetheart". If his name is not empty, then name is regarded as "beloved". If name is empty, NULL is returned:

MySQL: select name,herName,isnull (name,herName) as Love from tableOracle: select name,herName,NVL (name,herName) as Love from table V, isnull, is not null and "",! =

First of all, give some simple instructions on null in Oracle:

1. Null and 0 in oracle, empty strings, spaces, including null and null are also different.

2. The result of arithmetic operation on null in oracle is still null.

3. The processing method has the nvl function, and the comparison method is is null or is not null.

4. Null cannot be indexed, for example, the query result of select count (null) from dual is 0.

5. Null sorting is larger than all other types, with an inverted field that can be empty, with null data in front of it.

"''can only judge empty strings. Is Null is the judgment of Null characters, two completely different kinds of data. One is a null character, which is a string, but there is no value, and the other is a null value (null). Null is a unique kind of data in the database. Is null and is not null in the where clause will not use the index but will perform a full table search. Therefore, to optimize the efficiency, we need to change the query mode, sub-case discussion and other methods to remove is null and is not null in the where clause.

Example 1:

Select a. Field n from tab_a a where a. Field 2 is not null;//a. Field 2 adds indexed, but the query speed is very slow

The following optimizations are made:

Select a. Field n from tab_a a where nvl (a. Field 2minute 0')! ='0'

The increase in speed is obvious.

What is the reason? It's really simple, because is null and is not null invalidate the index of the field.

Example 2:

Select * from table where field = null;select * from table where field is null; if field is null, the result cannot be found under the first condition and the result can be found under the second condition.

The reason is that in oracle, the syntax for determining whether a field or variable is null is "is null". The result returned is Boolean. If "= null" is used, null is returned. If you use it in code or statements, you may not get a result or report an error. In addition, null does not mean that it does not exist, but it is unknown, and we usually become "UNKNOWN".

If the case when condition is written to determine whether it is null, it will result in an unknown error in the result of the query.

Conclusion: if you want to judge whether to use is null or is not null for null,Oracle

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.