Oracle DECODE function detailed explanation and usage examples

The DECODE function in Oracle is a conditional expression that is often used to return different results based on different conditions in query statements. This article will introduce the syntax, usage and sample code of the DECODE function in detail.

1. DECODE function syntax

DECODE(expr, search1, result1 [, search2, result2, ... , default])

• expr: The expression or field to be compared.
• search1, search2, ...: Values ​​to be compared.
• result1, result2, ...: If expr is equal to the corresponding value of search1, search2, etc., then the corresponding result1, result2, etc. are returned.
• default: If expr is not equal to any search value, the default value is returned.

2. DECODE function usage examples

1. Simple example:

Suppose there is a table named "employees" with fields including employee_id and job_id. Now We want to give different job titles to different employees based on their job IDs. The sample code is as follows:

SELECT employee_id, job_id, DECODE(job_id, 'IT_PROG', 'IT专员', 'SA_MAN', '销售经理', 'OTHER') AS job_title
FROM employees;

In the above code, different job titles are returned according to different job_ids. "IT_PROG" returns "IT Specialist", "SA_MAN" returns "Sales Manager", and in other cases, "OTHER" is returned.

2. Multi-condition example:

If we want to apply multi-condition judgment in the DECODE function, we can proceed as follows:

SELECT employee_id, job_id,
DECODE(job_id,
'SA_MAN', '销售经理',
'IT_PROG', 'IT专员',
'MK_REP', '市场代表',
'AC_ACCOUNT', '会计',
'OTHER') AS job_title
FROM employees;

In this example, according to different The job_id returns the corresponding job title.

3. Use default value:

If the expr of the DECODE function does not match all search values, you can set a default value. An example is as follows:

SELECT employee_id, job_id,
DECODE(job_id,
'SA_MAN', '销售经理',
'IT_PROG', 'IT专员',
DEFAULT '其他职位') AS job_title
FROM employees;

In this example, if the job_id is not 'SA_MAN' or 'IT_PROG', the default value "Other positions" is returned.

Conclusion

Through the above examples, we can see the flexibility and practicality of the DECODE function in Oracle database. Whether it is a simple conditional judgment or a multi-conditional judgment, the DECODE function can help us return different results according to different conditions, improving the flexibility and efficiency of SQL queries. I hope this article will help you understand and use the DECODE function.

The above is the detailed content of Oracle DECODE function detailed explanation and usage examples. For more information, please follow other related articles on the PHP Chinese website!