Shulou(Shulou.com)05/31 Report--

This article shows you what functions are commonly used in oracle, which are concise and easy to understand, which can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

If-then-else logic in 1 DECODE

In logic programming, If-Then-Else is often used for logic judgment. In the syntax of DECODE, this is actually the logical process. Its syntax is as follows:

DECODE (value, if1, then1, if2,then2, if3,then3,. . . Else)

Value represents any type of column of a table or any result obtained by calculation. When each value value is tested, if the value of value is if1,Decode function, the result is then1; if value is equal to if2,Decode function, the result is then2;, and so on. In fact, multiple if/then pairs can be given. If the value result is not equal to any pairs given, the Decode result returns else.

It is important to note that if, then, and else here can all be functions or evaluation expressions.

Meaning explanation:

DECODE (condition, value 1, translation value 1, value 2, translation value 2. Value n, translation value n, default)

This function has the following meaning:

IF condition = value 1 THEN

RETURN (translation value 1)

ELSIF condition = value 2 THEN

RETURN (translation value 2)

.

ELSIF condition = value n THEN

RETURN (translation value n)

ELSE

RETURN (default)

END IF

A simple example of 2 DECODE

Many data dictionaries in Oracle system are designed using the idea of decode, such as the view of V$SESSION data dictionary that records session information. We know from the "Oracle8i/9i Reference" material that when the user logs in successfully, there is a corresponding record of the user in the V$SESSION, but the command operations performed by the user only record the code of the command in this view (0-nothing, 2-Insert... Instead of specific command keywords Therefore, when we need to know the names of the current users and what they are doing, we need to use the following command to get detailed results:

Select sid,serial#,username

DECODE (command

0pm None`s

2Magnesia Insert'

3Reclamation Select`

6. Write an update

7. Write Delete

Eight minutes after Drop'

'Other') cmmand

From v$session where username is not null

3 DECODE to realize the transposition of table

A table in a database is a two-dimensional table consisting of columns and rows. Generally, the number of columns is limited in any database, while the number of rows varies greatly, and if the table is large, the number of rows may be tens of millions of rows. Different rows in the same column may have different values and are not predefined.

Example: housing provident fund report replacement example:

1. Each unit opens an account with the local agency bank. To open an account is to register the basic information of the unit and the information of the staff and workers.

two。 Every month, the accountant of each unit shall go to the handling bank to hand over the housing accumulation fund of all the employees of the unit, and the system records the payment details of each employee and the code of the handling bank on each record.

3. Each month, quarter, half a year and the end of the year are required to change the handling bank into a "column" to give a detailed report of the month:

Management bank: West side, east side

Month:

2001.01 xxxx1.xx xxxxx2.xx

2001.02 xxxx3.xx xxxxx4.xx

. . . . . .

The original data order is:

2001.01 xxxxx1.xx in the west of the city.

East District 2001.01 xxxxx2.xx

2001.02 xxxxx3.xx in the west of the city.

East District 2001.02 xxxxx4.xx

The pay_ LST table structure of the housing provident fund system for recording the monthly payment details of employees is as follows:

Bank_code varchar2 (6) NOT NULL,-- handling line code

Acc_no varchar2 (15) not null,-- Unit Code (Unit account)

Emp_acc_no varchar2 (20) not null,-- employee account number

Tran_date date not null,-date of delivery

Tran_val Number (7 ~ 2) not null,-- delivery amount

Sys_date date default sysdate,-- system date

Oper_id varchar2 (10)-operator code

In such a table structure, it is generally easy to count the handling row as a row, but it is difficult to output it if you want to change the handling row to a column format. If you use the DECODE function to handle it, it becomes very simple:

We create a view to query the current pay_ LST table. Just change the agent line code to some specific agent bank name:

CREATE OR REPLACE VIEW bank_date_lst AS

Select to_char (tran_date,'yyyy.mm')

SUM (DECODE (bank_code,'001', tran_val,0)) West District

SUM (DECODE (bank_code,'002', tran_val,0)) South District

SUM (DECODE (bank_code,'003', tran_val,0)) East District

FROM pay_lst

GROUP BY to_char (tran_date,'yyyy.mm')

After the view is established, you can query the view directly and display the results according to the column.

What are the functions commonly used in oracle? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.