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.
DECODE(expr, search1, result1 [, search2, result2, ... , default])
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.
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.
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.
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!