Oracle function usage

-- If ware_id has no data, it is 1
Select nvl (A. ware_id, 1) from dtf_test;

-- Partition to which the table is moved tablename table name new_name partition name --
Alter table tablename move
Tablespace new_name
Storage (initial 50 m next 50 m
Pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging;

-- Move partition syntax
Alter table tablename move (partition partname)
[Update global indexes]

-- If it is null, it will not be spliced --
Select Concat (ware_id, '000000') | 'to 23' Ding tengfei call from dtf_test;

-- Uppercase letters
Select initcap (ware_name) capital from dtf_test;

-- Returns the string and lowercase letters of all characters.
Select lower ('abbccdd') lower case from dual;

-- Returns a string and upper-case all characters.
Select upper ('abbccdd') capital from dual;

-- Ltrim: Delete the string on the left
-- Rtrim deletes the string on the right.
Select ltrim (rtrim ('gao Qian jing', ''),'') from dual;

-- Take the sub-string, starting from start, Count 08888888
Select substr ('20140901', 13088888899) from dual;

-- String the character or variable to be replaced
-- S1 string to be replaced
-- String to be replaced by S2
Select Replace ('He love you', 'hes', 'I') from dual;

-- Returns an integer to a given number.
Select floor (2345.67) from dual;

-- Rounding --
Select round (1.56), round (1.56, 1), round (12.34,-2) from dual

Nvl (A, B) if A is null, B is returned. If a is not null, A is returned.
Nvl2 nvl2 (a, B, c) if A is not null, B if A is null, C is returned.

-----------------
Select nvl (0, F () from dual; 0 is not empty, so it is still equal to 0. In fact, F () is still triggered, that is, all the expressions in nvl, whether or not satisfied, all must be executed

Select nvl2 (0, 'not null', F () from dual; 0 is not empty. Therefore, if not null is returned directly, F () is still executed () that is, any expression in nvl and nvl2 must be executed.
-----------------
Select nvl2 (0, 'not null', 'null') from dual; 0 if not empty, the display of 2nd expressions "not null"

Select coalesce (0, F () from dual; coalesce (0, F () is to display 1st non-empty expressions, 0 is not empty, then f () will it be executed? Like nvl, if F () coalesce 9i is forcibly executed, F () 10 Gb will be calculated without F ()
-----------------
Select coalesce (null,) from dual; coalesce (null,) is the first non-null expression returned here the first non-null is 1

Select decode (0, 0, 'Do not execute F () ', F () from dual; only execute the satisfied expression

Select decode (I, 5, 'five', 6, 'six', 'else I just don't know! ') From t;, 6, and 4 are not listed in decode
So show else I just don't know!

// If there is qualified data, it will be filled. If no, dd
Select decode (T. A, 1, 'one', 2, 'two', 3, 'three ', 'dd') A from T;

// When the maximum and minimum values are obtained, if a single SQL statement is used to retrieve them, the entire index needs to be scanned.
// If we use two SQL clauses to obtain one Max and one min in parallel
1. Select min (created), max (created) from big_table;

// Better efficiency
2. Select min (created), max (created)
From (
Select min (created) created from big_table
Union all
Select max (created) created from big_table
)

// The maximum data in each row of all data
Select T. *, greatest (a, B, c) row_max from T;

Select nvl2 (0, 'not null', 'null') from dual;

Select coalesce (0, F () from dual;