Oracle Database Functions

Practical 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

Decode (tr. PERIOD_NUM, 1, 'p01', 2, 'p02', 3, 'p03', 'err ')

 

INSTR

Returns the position of the character to be searched.

 

CASEWHEN

See http://www.cnblogs.com/eshizhan/archive/2012/04/06/2435493.html

-- 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

 

What is the difference between decode and case when?

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.

Http://blog.sina.com.cn/s/blog_6da521f90100tcm4.html

 

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

 

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:

Replace ('string to be changed ', 'replaced string', 'replaced string ')

 

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

From (select 'product' parentname,

'Producttotal' membername,

'Product summary' alias

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

Selectrownumfrom dualCONNECTBYrownum <= length ('qwertyui ')

 

See http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html

 

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.

-- Oracle trunc () function usage
********************/
1. select trunc (sysdate) from dual -- 2011-3-18 today's date is 2011-3-18
2. select trunc (sysdate, 'mm') from dual -- 2011-3-1 returns the first day of the current month.
3. select trunc (sysdate, 'yy') from dual -- 2011-1-1 return the first day of the current year
4. select trunc (sysdate, 'dd') from dual -- 2011-3-18 return current year month day
5. select trunc (sysdate, 'yyyy') from dual -- 2011-1-1 return the first day of the current year
6. select trunc (sysdate, 'D') from dual -- 2011-3-13 (Sunday) returns the first day of the current week
7. select trunc (sysdate, 'hh') from dual -- 2011-3-18 14:00:00 current time is
8. select trunc (sysdate, 'mi') from dual -- 2011-3-18 14:41:00 the TRUNC () function has no precision in seconds.
********************/
/*
TRUNC (number, num_digits)
Number.
Num_digits is used to specify the number to take an integer. The default value of Num_digits is 0.
When TRUNC () function is intercepted, No rounding is performed.
*/
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.

Select 'Jan 'membername, 'february 11' name from dual union select 'feb' membername, 'february 11' name from dual;

 

ADD_MONTHS

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

 

There are two simple examples to illustrate the efficiency of "exists" and "in ".

1) select * from T1 where exists (select 1 from T2 where T1.a = T2.a );

Small Data size of T1 and large data size of T2, T1 <T2, 1) high query efficiency.

2) select * from T1 where T1.a in (selectT2.a from T2 );

T1 has a large data volume and T2 has an hour, T1> T2, 2) High query efficiency.

 

In is suitable for situations where both the internal and external tables are large, and exists is suitable for situations where the external result set is small.

 

When EXISTS is used, Oracle first checks the primary query, and then runs the subquery until it finds the first match, which saves time.

When Oracle executes the IN subquery, it first executes the subquery and stores the obtained result list IN a temporary table with an index.

Before executing a subquery, the system suspends the primary query. After the subquery is executed, it is stored in the temporary table and then executes the primary query.

This is why EXISTS is faster than IN queries.

 

Functions and usage of the OVER Function

RANK () OVER ([query_partition_clause] order_by_clause)

DENSE_RANK () OVER ([query_partition_clause] order_by_clause)

You can group and sort the results set of the same group fields by specified fields,

Where partition by is the grouping field, and order by specifies the sorting field.

 

Over cannot be used independently. It must be used together with analysis functions such as rank (), dense_rank (), and row_number.
Parameter: over (partition by columnname1 order by columnname2)
Meaning: sort by columname1 by group, or by the value of columnname1 by group.
For example, in the employees table, there are two Department records: department_id = 10 and 20.
Select department_id, rank () over (partition by department_idorder by salary) from employees refers to the salary ranking in department 10 and in department 20. If it is partition by org_id, It is ranked within the company.

 

TO_CHAR TO_DATE

To_char and to_date Experience Sharing

(I) Compare the following two methods:

To_char (start_time, 'yyyy-mm-ddhh24: mi: ss') = '2017-06-06 16:18:30'

And

Start_time = to_date ('2017-06-06 16:18:30 ', 'yyyy-mm-ddhh24: mi: ss ')

The latter is recommended at any time !!

(Ii) Simple implementation of converting numbers into English

Sys @ ORCL> selectto_char (to_date ('201312', 'J'), 'jsp ') as "I love 1314" from dual;

I love 1314.

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

One Thousand Three Hundred Fourteen

TO_CHAR (aDate, 'jsp ') can get the spelling of a number, for example:
SELECT TO_CHAR (SYSDATE, 'jsp ') from dual;
The query result of this SQL statement is:
Two million four hundred ty-THREE THOUSAND FIVE HUNDRED NINE
The value is 20004530509.
Why?
Because TO_CHAR (aDate, 'jsp ') refers to the number of days between the date aDate and the first day of the JULIAN Date, that is, the number of days from January 1, to aDate. cool!
The range of JULIAN dates is from 4712-01-01 BC to 9999-01-01 a.m. the number of days in the period is the maximum value that we can spell. The minimum value is 1.
So we want to get the spelling of a number, as long as we know the date after these days, it is very easy to get this date:
TO_DATE (adig1_, 'J') Where adig1_is the number you want to represent.
The spelling of a number can be as follows (for example, 101 ):
SELECT TO_CHAR (TO_DATE (101, 'J'), 'jsp ') FROM dual
The result is: one hundred one.

(3) A to_date trap

We can use the to_date function to construct any time we want.

If HH, MI, and SS are omitted, oracle is set to 0 by default, that is, the whole day is used.

If DD is omitted, oracle is set to 1 by default, that is, the value is rounded to month.

If MM is omitted, Will oracle take the whole year? We will all think in inertia: Yes. Of course, neither!

Oracle will not get the whole year, and it will get the whole year to the current month!

Select to_date ('20140901', 'yyyy') from dual;

The result is 1991-6-1.