Shulou(Shulou.com)06/01 Report--

The DECODE function can be given a desired result according to the decision conditions given by the user

Grammar:

DECODE(expr,{search,result,}…., default) The search,result given here can be multiple, and the combination of all the elements in parentheses is at most 255.

Today, when writing a statement, there is a simple idea, that is, to automatically convert numerical values and return original values when encountering characters.

selectname,value

from v$parameter

wherenamein ('control_file_record_keep_time',

'db_block_size',

'db_cache_advice',

'db_cache_size',

'db_file_multiblock_read_count',

'db_flashback_retention_target',

'db_recovery_file_dest_size',

'job_queue_processes',

'optimizer_index_cost_adj',

'optimizer_mode',

'processes',

'sort_area_size')

For example, when querying the v$parameter view, the type of value is varchar2

The query results are as follows:

Obviously, this display is not human enough, so I tried to convert the value to the human-readable case. Knowing that the view is classified according to the type of value, you can look at the TYPE column.

The problem arises here. I found that the text type cannot be converted. I tried to report an error with the following statement.

selectname,decode(type,6,round(value/1024/1024,2),3,value,value)

from v$parameter

wherenamein ('control_file_record_keep_time',

'db_block_size',

'db_cache_advice',

'db_cache_size',

'db_file_multiblock_read_count',

'db_flashback_retention_target',

'db_recovery_file_dest_size',

'job_queue_processes',

'optimizer_index_cost_adj',

'optimizer_mode',

'processes',

'sort_area_size')

orderbyname;

Invalid value? When was a character type converted to a numeric type? Repeated attempts to find out that the problem is in the VALUE is a character parameter item, which is really a bit confusing, and the reverse attempt succeeded.

There are two sentences in the official document:

· If expr and search are character data, then Oracle compares them using nonpadded comparison semantics. expr, search, and result can be any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2.The string returned is of VARCHAR2 data type and is in the same character set as the first result parameter.

· If the first search-result pair are numeric, then Oracle compares all search-result expressions and the first expr to determine the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

When comparing DECODE, if expr and search are character classes, then subsequent results may be of any character type, which is determined according to the character type returned by the first result. That is to say, if the first expr and search are compared, the returned result is of type varchar2, then subsequent results will be returned as varchar 2.

Also, if expr and search of the first match are numeric data with higher priority, the result will convert all subsequent arguments to numeric types.

According to the above understanding, I type conversion as follows, found that the statement can be executed correctly:

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.