Oracle Database Functions

The DECODE syntax of the Oracle utility function is as follows: DECODE (value, if1, then1, if2, then2, if3, then3,... else) Value represents any class of a table.

The DECODE syntax of the Oracle utility function is as follows: DECODE (value, if1, then1, if2, then2, if3, then3,... else) Value represents any class of a table.

Oracle Functions

DECODE

Syntax:

DECODE (value, if1, then1, if2, then2, if3, then3,... else) Value represents any column of any type in a table or any result obtained through computation. When each value is tested, if the value is if1, the result of the Decode function is then1; if the value is if2, the result of the Decode function is then2; and so on. In fact, multiple if/then pairs can be provided. If the value result is not equal to any given pairing, the Decode result returns else. Note that if, then, and else can both be functions or computing expressions.

Instance

INSTR

Returns the position of the character to be searched.

CASEWHEN

For details, see

-- Simple Case Function

CASE sex

WHEN '1' THEN 'male'

WHEN '2' THEN 'female'

ELSE 'others' END

-- Case search function

CASE

WHEN sex = '1' THEN 'male'

WHEN sex = '2' THEN 'female'

ELSE 'others' END

Decode can only be equivalent, case when can be used for range, range can be used for condition differentiation, decode can do, and case when can also do; in terms of efficiency, I personally think the efficiency of the two is almost the same; however, the decode syntax is simpler, but it can only be used for equivalent comparison. case when end can be used to determine conditions.

SUBSTR

The substr () function returns part of the string.

Syntax: substr (string, start, length)

String-specifies the string to be truncated.

Start-required, specifying where the string starts.

Positive number-start at the specified position of the string

Negative number-starting from the specified position at the end of the string

0-Start at the first character in the string

Length-(optional) specifies the length of the string to be truncated. If the length is short, all characters before the end of the character expression value are returned.

For example, select substr ('abcdefg', 3,4) from dual; the result is cdef.

Select substr ('abcdefg',-3,4) from dual; Result efg

Oracle Regular Expression functions:

Regexp_like, regexp_substr, regexp_instr, regexp_replace

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

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

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

REPLACE

Replace (x, y, z) returns the result string after string X is replaced with string Z. If the Z parameter is omitted, the place where string X is string Y is deleted.

Select replace ('Jack and JUE ', 'J', 'bl') "Changes" from dual;

Changes

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

BLACK and BLUE

Example

The data imported for one column should be in the '2017-10-11 'format. The data imported for the result is in the '2017/11' format, and it is basically impossible to change more than 2011 records. Later I thought of the replace function. The specific usage is as follows:

Update Table 1 t set t. column 1 = replace (select column 1 from table 1 a where. primary Key column = t. primary Key column), '/', '-') solves our problem.

The replace function is used as follows:

Select "PARENTNAME", "MEMBERNAME", "ALIAS"

'Producttotal' membername,

From dual)

CONNECT

Oracle "connect by" is a hierarchical query clause, which is generally used for tree or hierarchical query of result sets. Its syntax is:

[Start with condition]

CONNECTBY [NOCYCLE] condition

1. Sequence Generation

Select rownum from dual connect by rownum <= 10;

2. Tree query can be implemented.

Connect by rownum <= length (p_str) traverse input strings one BY one

For details, see

RPAD

The rpad function fills the rpad (string, padded_length, [pad_string]) with the specified characters on the right side of the string.

String indicates the string to be filled.

Padded_length indicates the length of the character, which is the number of returned strings. If the number is shorter than the length of the original string, the rpad function truncates the string to n characters from left to right;

Pad_string is an optional parameter. This string is to be pasted to the right of the string. If this parameter is not written, the lpad function will paste a space on the right of the string.

For example:

Rpad ('tech ', 7); will return 'tech'

Rpad ('tech ', 2); will return 'te'

Rpad ('tech ', 8, '0'); 'tech0000' will be returned'

Rpad ('tech on the net', 15, 'z'); will return 'tech onthe net'

Rpad ('tech on the net', 16, 'z'); will return 'tech onthe netz'

TRUNC

The TRUNC function returns a part of the date value in the specified Element format.

1. select trunc (sysdate) from dual 2. select trunc (sysdate, 'mm') from dual 3. select trunc (sysdate, 'yy') from dual4.select trunc (sysdate, 'dd') from dual5.select trunc (sysdate, 'yyyy') from dual6.select trunc (sysdate, 'D ') from dual7.select trunc (sysdate, 'hh') from dual 8. select trunc (sysdate, 'mi') from dual 9. select trunc (123.458) from dual -- 123
10. select trunc (123.458, 0) from dual -- 123
11. select trunc (123.458, 1) from dual -- 123.4
12. select trunc (123.458,-1) from dual -- 120
13. select trunc (123.458,-4) from dual -- 0
14. select trunc (123.458, 4) from dual -- 123.458
15. select trunc (123) from dual -- 123
16. select trunc (123) from dual --
17. select trunc (123,-1) from dual -- 120

The following statements are often used, which is equivalent to inserting several data records without creating a table.

ADD_MONTHS

ADD_MONTHS (d, n) -- time point d plus n months

For more details, please continue to read the highlights on the next page: