Oracle9 Study Notes

Oracle learning notes: Sean

 

The nvl () function is used to replace null with the specified default value.

Syntax format: nvl (exp1, exp2)

Select empno, ename, Sal, COM, Sal + nvl (Comm., 0) from EMP;

If comm is empty, the default value is 0.

 

Bytes ------------------------------------------------------------------------------------------

Nvl2 (exp1, exp2, exp3)

If the expression exp1 value is not null, the value of exp2 is returned. Otherwise, the value of exp3 is returned.

Bytes ------------------------------------------------------------------------------------------

Nullif (exp1, exp2)

If the values of the two expressions are equal, null is returned. Otherwise, the value of exp1 is returned.

Bytes ------------------------------------------------------------------------------------------

The coalesce () function is used to implement data joining.

Evaluate the parameter expressions in sequence. If a non-null value is specified, stop and return the value. Generally, the last entry is set to Changshu.

Bytes ------------------------------------------------------------------------------------------

Use Case expressions to implement multiple branches

Select empno, ename, Sal,

Case deptno when 10 then 'finance author'

When 20 then 'r & D amount'

When 30 then 'sales directory'

Else 'unknown departments'

End Department

From EMP;

Bytes ------------------------------------------------------------------------------------------

The decode expression is similar to the case expression.

Same as above, the decode expression is:

Select empno, ename, Sal,

Decode (deptno, 10, 'Finance author ',

20. 'r & D Center ',

30, 'sales directory ',

'Unknown departments ')

Department

From EMP;

Bytes ------------------------------------------------------------------------------------------

Single-row function nesting

Select empno, ename, initcap (TRIM (rpad (job, 10 ,'-')))

From EMP;

Bytes ------------------------------------------------------------------------------------------

Fill the rpad (job, 10, '-') to the right, fill in less than the length of 10-

Trim ignores spaces and initcap starts with uppercase letters

 

 

Group by statement

Functions that appear in the select clause must appear in the group by clause if they are not included in group functions (such as sum and AVG.

-------------------------------------------------------------------------------

If the group by clause is not available, a single row function and a grouping function cannot be mixed in the SELECT statement.

Grouping functions are not allowed in the WHERE clause.

The having clause is used to filter groups.

Grouping functions can be nested at most two layers

 

If the input length is not enough when the char length is set, spaces are filled by default to read the original length. Therefore, you may need to use the trim function for filtering.

Varchar2 will not automatically add Spaces

 

Contact (ename, job) connects two strings, similar to |

Instr ('Hello world', 'or') locates the second string position in the previous string. If no value is found, 0 is returned. The position starts from 1.

 

To_char (date), to_char (date, 'formal _ date') is used to convert date to char type

'Fm yyyy-mm-dd hh24: MI: SS am', FM indicates removing zero, and am indicates displaying on the afternoon