Common oracle Functions

Common oracle Functions

 

1. wmsys. wm_concat

Integrate multiple rows of data into one row, separated by commas.

 

2. dbms_random.random and dbms_random.value (0,100)

1 -- randomly arrange 2 select * from t_znf order by dbms_random.random; 3 -- random integer 4 select trunc (dbms_random.value (1,100) from dual;

3. decode

Decode (field, result1, value1, result2, value2,..., default)

Equivalent to if. else if. else.

Select. *, decode (id, 1, 'Id = 1', 2, 'Id = 2', 3, 'Id = 3', 'Id default value ') from t_znf;

When t_znf.id = 1, 'Id = 1' is returned, 2 is returned, 3 is returned, and 'id = 3' is returned ', if the ID value is another value, the default value 'id default' is returned '.

4. NULLIF

The NULLIF (exp1, expr2) function returns NULL if exp1 and exp2 are equal. Otherwise, the first value is returned.

select a.*,nullif(id,3) from t_znf a;

5. NVL2

The format of the NVL2 function is as follows: NVL2 (expr1, expr2, expr3)
Meaning: if the first parameter of the function is not null, the value of the second parameter is returned. If the value of the first parameter is null, the value of the third parameter is returned. I personally feel a bit like java's three-goal budget expr1? Expr1: expr3

select a.*,nvl2(a.state,a.state,a.t_desc) from t_znf a ;

If t_znf.state is not empty, return state. If it is empty, return a. t_desc;

 

6. substr

Substr (field, start position, truncation length). Note that the start field of the string is 1.

select a.*, substr(a.t_desc,1,5),a.rowid from t_znf a;

7. length

Calculated Field Length

select A.*,LENGTH(A.T_DESC) from T_ZNF A;

8. replace

Replace (exp, old_str, new_str): replace all old_str in exp with new_str.

select a.*,replace(a.t_desc,'A','BBB') from t_znf a;

Use BBB to replace A in t_znf.t_desc

Replace can be used to remove all spaces in the string.

select replace('  hello world  ',' ','') from dual;

9. TRIM, RTRIM, and LTRIM

Trim (exp): removes spaces before and after the exp string

Rtrim (exp): remove the space at the right of the exp string, that is, remove the space at the end of the string.

Ltrim (exp): removes spaces on the left of the exp string, that is, spaces starting with the string.

To remove all spaces, replace

select trim('  hello world  ') from dual;

Remove trailing Spaces

Remove leading space

10. round

Rounding by precision

11. mod

Returns the remainder function, which can be used to concurrently process data updates. For example, if you have saved and updated a large table, you can perform the remainder operation on the ID, and then execute the operation concurrently.

Create or replace procedure P_update_T_ZNF (seq in number) CURSOR C1 IS select t. ID from T_ZNF t where t. flag = 'n' -- indicates whether the ID and mod (t. id, 10) = seq and rownum <160001; BEGIN -- Data Processing END;

You can open 10 windows and execute call P_update_T_ZNF (seq) in parallel. The seq value is 0-9, which greatly speeds up data processing.
Once there was a data update, and the execution was not completed in one night. Later, the remainder function was used for concurrent execution, and the execution was completed in two hours.