Useful functions provided by Oracle (to be continued)

Document directory

• Nvl

Decode Function

Decode is an exclusive function provided by Oracle. It is a powerful function. Although it is not an SQL
Standard, but very useful for performance. Up to now, other database vendors have not yet provided Decode-like functions, and some database vendors have even criticized Oracle's SQL standards. Actual
Such criticism is somewhat one-sided or inadequate. Just as some carriage manufacturers complained about Henry. Ford's carriage is not standard.
1. If-then-else logic in decode
In logical programming, if-then-else is often used for logical judgment. In decode syntax, this is actually the logic processing process. Its syntax is as follows:
Decode (value, if1, then1, if2, then2, if3, then3,... else)
Value
Represents any column of any type in a table or any result obtained by calculation. When each value is tested, if the value is if1, decode
The result of the function is then1; if the value is equal to if2, the result of the decode function is then2; and so on. In fact, multiple if/then
Pairing. 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.
Description:
Decode (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value)

The function has the following meanings:
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

2 simple decode example
In Oracle, many data dictionaries use decode.
This is an ideal design, for example, the V $ session data dictionary view that records session information. From Oracle8i/9i
The reference documents show that after a user successfully logs on to the V $ Session, the corresponding records of the user are displayed, however, the command operations performed by the user only record the code of the command in this view.
(0-no operation, 2-insert ...), Instead of the specific command keyword. Therefore, we need to know the name of each user and the operations they perform before using the following command.
Detailed results:
Select Sid, serial #, username,
Decode (command,
0, 'none ',
2, 'insert ',
3, 'select ',
6, 'update ',
7, 'delete ',
8, 'drop ',
'Other') cmmand
From v $ session where username is not null;
3 Decode: transpose a table
A table in the database is a two-dimensional table composed of columns and rows. The number of columns is usually limited in any database, and the number of rows changes greatly. If the table is large, the number of rows may be larger than 10 million rows. Different rows in the same column may have different values, and they are not pre-defined.
Example: housing provident fund report replacement instance:
1. Each organization opens an account at the local management office. An account is used to register the basic information and employee information of the Organization;
2. every month, the accountant of each organization submits the housing provident fund of all employees of the Unit to the handling bank. The system records the payment details of each employee and records the codes of the handling bank on each record;
3. The statement of the month, quarter, half year, and year-end must be changed to "column:
Handling bank: chengxi district Chengdong District
Month:
2001.01 xxxx1.xx xxxxx2.xx
2001.02 xxxx3.xx xxxxx4.xx
. . . . . .
The original data sequence is:
Chengxi district 2001.01 xxxxx1.xx
Chengdong district 2001.01 xxxxx2.xx
Chengxi district 2001.02 xxxxx3.xx
Chengdong district 2001.02 xxxxx4.xx
The housing provident fund system records the employee's pay-as-you-go every month. The detailed pay_lst table structure is:
Bank_code varchar2 (6) Not null, -- handle line code
Acc_no varchar2 (15) not null, -- unit code (unit account)
Emp_acc_no varchar2 (20) not null, -- employee account
Tran_date date not null, -- Payment Date
Tran_val number () not null, -- pay
Sys_date date default sysdate, -- system date
Oper_id varchar2 (10) -- operator code
In this table structure, it is easy to count rows as rows, but if you want to change rows to columns) it is difficult to output such a format. If the decode function is used for processing, it becomes very simple:
We create a view to query the current pay_lst table. You can change the management line code to some specific management line names:
Create or replace view bank_date_lst
Select to_char (tran_date, 'yyyy. mm '),
Sum (decode (bank_code, '001', tran_val, 0) chengxi district,
Sum (decode (bank_code, '002', tran_val, 0) south of the city,
Sum (decode (bank_code, '003 ', tran_val, 0) Chengdong District
From pay_lst
Group by to_char (tran_date, 'yyyy. mm ');
After creating a view, you can directly query the view to display the results by column.

Nvl

The nvl function provides a simple but useful function. If you give it a null value at any time, it returns a value of your choice. This ability to automatically replace null values helps provide more comprehensive output. The syntax of the nvl function is as follows:

Nvl (input_source, result_if_input_value_is_null)
Input_source is generally a column name. Result_if_input_value_is_null
It can be any value: direct value (hard encoding), reference to other columns, or expression

Note: The nvl function does not actually update the values in the table. The original data remains unchanged.

Nvl
It requires that the data types of input_source and result_if_input_value_is_null are the same. If you want this function to be empty

When the value is displayed, the popular "N/A" will cause problems. Because "N/A" is text, if input_value is a text column, there is no problem. However, if it is in a date or value column
To query null values, you need to apply the to_char function to the input_value column so that input_value can also become text.

If nvl is null, the specified value is used.
For example, nvl (yanlei777, 0)> 0
Nvl (yanlei777, 0) indicates that if yanlei777 is null, the value 0 is used.
Obtain the total value of a field through the query. If this value is null, a default value is provided.
For example:
Select nvl (sum (T. dwxhl), 1)
From tb_jhde t
It indicates that if sum (T. dwxhl) = NULL, 1 is returned.
Another useful method
Declare I integer
Select nvl (sum (T. dwxhl), 1) into I from tb_jhde t where zydm =-1 to store the obtained total value to the variable
In I, if the queried value is null, set its value to the default value of 1.
In orcale:
Select nvl (rulescore, 0) from zwjc_graderule where rulecode = 'fwtd ';
If the record does not contain data with rulecode = 'fwtd ', no data is found.
Select nvl (rulescore, 0) into rule_score from zwjc_graderule where rulecode = 'fwtd '; an error is reported if no data is found.
Select nvl (sum (rulescore), 0) from zwjc_graderule where rulecode = 'fwtd ';
If the record does not contain data with rulecode = 'fwtd ', you can still obtain data with a column name of nvl (rulescore, 0) and a value of 0.
Select nvl (sum (rulescore), 0) into rule_score from zwjc_graderule where rulecode = 'fwtd '; no error is reported