DECODE表达式_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

在SQL查询中，您可能需要根据列的值进行条件判断并返回不同的结果，例如将状态码1、2、3转换为待处理、处理中、已完成。虽然可以使用CASE表达式实现，但在处理简单的等值判断时，代码会显得较为冗长。为了解决这一问题，PolarDB PostgreSQL版（兼容Oracle）提供了DECODE表达式。它以更紧凑的函数式语法实现了类似CASE的功能，能够有效简化代码，提高SQL的可读性。

功能简介

DECODE是一个高度兼容Oracle的SQL表达式，用于在查询中执行基于等值比较的条件逻辑判断。它将输入表达式与一系列的搜索-结果对进行匹配，如果找到相等的值，则返回对应的结果；如果所有值都不匹配，则返回一个可选的默认值。

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

工作原理
DECODE的工作方式类似于一个简化的IF-THEN-ELSE链条或CASE表达式：

它首先计算expr的值。
然后，它将expr的值依次与search1、search2等值进行比较。
如果expr等于某个search值（例如search1），DECODE就返回其对应的result值（例如result1），并停止后续比较。
如果expr与所有search值都不相等，DECODE将返回最后的default值。
如果省略了default值且没有找到匹配项，DECODE将返回NULL。

适用范围

您的PolarDB PostgreSQL版（兼容Oracle）集群的修订版本需为2.0.14.17.36.0及以上

说明

您可在控制台查看内核小版本号，也可以通过SHOW polardb_version;语句查看。如未满足内核小版本要求，请升级内核小版本。

优势

语法简洁：相比CASE表达式，DECODE的语法更紧凑，尤其在进行多个简单的等值判断时，能显著减少代码量。
可读性强：对于值映射（Code-Value Mapping）这类常见场景，DECODE的搜索-结果对形式直观易懂。

使用示例

以下示例将演示DECODE表达式在不同业务场景中的应用。

准备数据

在执行示例前，请先连接到您的PolarDB集群并创建以下测试表。

-- 创建库存表
CREATE TABLE inventories (
    product_id NUMBER,
    warehouse_id NUMBER,
    quantity NUMBER
);

-- 插入库存数据
INSERT INTO inventories VALUES (1774, 1, 100);
INSERT INTO inventories VALUES (1775, 2, 200);
INSERT INTO inventories VALUES (1776, 3, 150);
INSERT INTO inventories VALUES (1777, 4, 300);
INSERT INTO inventories VALUES (1778, 5, 250);

-- 创建员工表
CREATE TABLE employees (
    employee_id NUMBER,
    department_id NUMBER,
    salary NUMBER,
    job_id VARCHAR2(10)
);

-- 插入员工数据
INSERT INTO employees VALUES (100, 10, 5000, 'IT_PROG');
INSERT INTO employees VALUES (101, 20, 6000, 'SA_REP');
INSERT INTO employees VALUES (102, 30, 4500, 'ST_CLERK');
INSERT INTO employees VALUES (103, 10, 7000, 'IT_PROG');

示例1：进行值映射

这是DECODE最常见的用法，用于将代码或ID转换为可读的名称。本示例将仓库ID（warehouse_id）映射为具体的仓库地点名称。

SELECT
    product_id,
    warehouse_id,
    DECODE(warehouse_id,
           1, 'Southlake',
           2, 'San Francisco',
           3, 'New Jersey',
           4, 'Seattle',
           'Non domestic') AS "Location"
FROM inventories
WHERE product_id < 1779
ORDER BY product_id;

返回结果：

 product_id | warehouse_id |   Location    
------------+--------------+---------------
       1774 |            1 | Southlake
       1775 |            2 | San Francisco
       1776 |            3 | New Jersey
       1777 |            4 | Seattle
       1778 |            5 | Non domestic
(5 rows)

结果说明：

当warehouse_id为1时，Location列显示Southlake。
当warehouse_id为2时，Location列显示San Francisco，以此类推。
对于其他warehouse_id（如本例中的5），由于没有匹配项，将返回默认值Non domestic。

示例2：在计算中使用

DECODE可以嵌入到数值计算中，根据不同条件返回不同的计算表达式。本示例根据员工所在的部门ID（department_id）计算不同的奖金。

SELECT
    employee_id,
    salary,
    DECODE(department_id,
           10, salary * 0.15,  -- IT部门奖金系数为15%
           20, salary * 0.12,  -- 销售部门奖金系数为12%
           30, salary * 0.10,  -- 运营部门奖金系数为10%
           salary * 0.05) AS bonus  -- 其他部门奖金系数为5%
FROM employees;

返回结果：

 employee_id | salary | bonus 
-------------+--------+-------
         100 |   5000 |   750
         101 |   6000 |   720
         102 |   4500 |   450
         103 |   7000 |  1050
(4 rows)

结果说明：bonus列的值会根据department_id的不同，由不同的表达式计算得出。

示例3：处理无匹配且无默认值的情况

如果DECODE表达式中没有提供default值，且输入值expr没有匹配任何search值，则表达式将返回NULL。

SELECT
    employee_id,
    department_id,
    DECODE(department_id,
           10, 'IT Department',
           20, 'Sales Department'
           -- 此处没有为department_id=30提供匹配，也没有默认值
          ) AS dept_name
FROM employees;

返回结果：

 employee_id | department_id |    dept_name     
-------------+---------------+------------------
         100 |            10 | IT Department
         101 |            20 | Sales Department
         102 |            30 | 
         103 |            10 | IT Department
(4 rows)

结果说明：对于department_id为30的员工，dept_name列的值将为NULL。

兼容性说明

在Oracle中，DECODE表达式返回值的最终数据类型和长度会受到所有result参数和default参数的影响。为了更好地兼容这一特性，PolarDB PostgreSQL版（兼容Oracle）提供了polar_enable_decode_type_mode参数。

当polar_enable_decode_type_mode参数开启时，DECODE表达式返回值的类型长度将遵循以下规则，以模拟Oracle的行为：

表达式的所有参数需是字符串常量、NULL、表列或子查询之一。
所有result参数的类型长度都不能是无限长（例如，TEXT类型）。
在满足以上条件时，DECODE表达式的最终返回类型长度将取所有result参数类型长度中的最大值。

示例：验证返回值类型长度

本示例创建一个视图，其中DECODE的返回值类型长度应为所有可能结果中最长的一个。

-- 开启兼容模式
SET polar_enable_decode_type_mode = ON;

-- job_id列的类型为VARCHAR2(10)，其长度为10
-- 'AA' 的长度为2, 'BBB' 的长度为3
-- 因此，DECODE表达式的最终类型长度应为三者中的最大值，即10
CREATE OR REPLACE VIEW decode_char_type_test AS
SELECT
    DECODE('A',
           'A', 'AA',    -- 长度为2
           'B', 'BBB',   -- 长度为3
           job_id       -- 长度为10
    ) AS a
FROM employees;

-- 查询视图中列的元数据
SELECT
    column_name,
    data_type,
    character_maximum_length
FROM information_schema.columns
WHERE table_name = 'decode_char_type_test'
ORDER BY ordinal_position;

预期输出：
结果显示character_maximum_length为10，符合预期。

 column_name |     data_type     | character_maximum_length 
-------------+-------------------+--------------------------
 a           | character varying |                       10
(1 row)