在Oracle PL/SQL中，使用字段值来定义变量或绑定变量，以便与DECODE函数一起使用。

Question

如果我能在我的SELECT语句开头以WITH子句的形式实现这个功能，那就更好了，因为这段代码会在查询的不同pcode组合中被重复使用。

--set a bind variable
VARIABLE pcode VARCHAR(3) --tried a bunch of various syntax with BEGIN/END/DECLARE/DEFINE...etc

--initiate the variable with
:pcode := (field1 || field2 || field 3) -- from my_table ? can't get syntax right here

--next I want to use the variable as follows
SELECT 

--some field variables may be null so that only 1 or 2 characters are returned
DECODE (
    pcode, 'A', amount_field * -1,
    pcode, 'A1A', amount_field * -1,
    pcode, 'A5', 0,
    pcode, 'A6', 0,
    pcode, 'B1', amount_field * -1,
    pcode, 'B5', amount_field * 0,
    pcode, 'B6', 0,
    amount_field -- default
    ) as my_new_column -- set the value for each row in the table

FROM my_table

我已经尝试了各种组合

DECLARE
DEFINE
BEGIN END
EXEC
/
SELECT INTO
...etc

我不知道自己做错了什么。

Dai · Answer

如果你试图从一个表中读取列，并且对于每一行，将三个列值连接起来以确定应用于该行第四个列的转换操作，这时可以使用 DECODE 函数直接处理列值，而不需要（或不希望）使用绑定变量。只需从表中执行 SELECT 并在 DECODE 中直接对列值进行操作。

你只需要在 DECODE 的第一个参数位置使用一次 pcode：

SELECT field1 || field2 || field3 AS pcode,
       DECODE (
         field1 || field2 || field3,
         'A',   amount_field * -1,
         'A1A', amount_field * -1,
         'A5',  0,
         'A6',  0,
         'B1',  amount_field * -1,
         'B5',  amount_field * 0,
         'B6',  0,
                amount_field -- 默认值
       ) as my_new_column -- 为表中的每一行设置值
FROM   my_table;

对于以下示例数据：

CREATE TABLE my_table (field1, field2, field3, amount_field) AS
SELECT 'A', NULL, NULL, 1 FROM DUAL UNION ALL
SELECT 'A', '1',  'A',  2 FROM DUAL UNION ALL
SELECT 'A', '5',  NULL, 3 FROM DUAL UNION ALL
SELECT 'A', '6',  NULL, 4 FROM DUAL UNION ALL
SELECT 'B', NULL, '1',  5 FROM DUAL UNION ALL
SELECT 'B', '5',  NULL, 6 FROM DUAL UNION ALL
SELECT 'B', '6',  NULL, 7 FROM DUAL;

查询结果如下：

| PCODE | MY_NEW_COLUMN |
|-------|---------------|
| A | -1 |
| A1A | -2 |
| A5 | 0 |
| A6 | 0 |
| B1 | -5 |
| B5 | 0 |
| B6 | 0 |

如果你想在查询中使用 WITH 子句，只需将其包裹在查询外部：

WITH codes (pcode, my_new_column) AS (
  SELECT field1 || field2 || field3 AS pcode,
         DECODE (
           field1 || field2 || field3,
           'A',   amount_field * -1,
           'A1A', amount_field * -1,
           'A5',  0,
           'A6',  0,
           'B1',  amount_field * -1,
           'B5',  amount_field * 0,
           'B6',  0,
                  amount_field
         )
  FROM   my_table
)
SELECT *
FROM   codes;

这将得到与之前相同的输出结果。

在线演示

Yishai · Answer

根据我的理解，您已经在数据库中拥有所有数据，但正在寻找一种方法来一次性定义新列的值（使用WITH子句），并在下面的查询中重复使用这些数据。您可以创建一个包含进一步处理所需所有数据的公共表表达式（CTE）。

首先，我们来看一些用于演示的数据：

WITH  -- 示例数据
    tbl (ID, FIELD1, FIELD2, FIELD3, AMOUNT_FIELD) AS
        (Select 1, NULL, NULL, 'A',  10 From Dual Union All
         Select 2, 'A', '1',   'A',  15 From Dual Union All
         Select 3, 'A', '5',   NULL,  5 From Dual Union All
         Select 4, 'A', '6',   NULL, 12 From Dual Union All
         Select 5, 'B', '1',   NULL, 17 From Dual Union All
         Select 6, 'B', '5',   NULL,  9 From Dual Union All
         Select 7, 'B', '6',   NULL,  2 From Dual
         -- 假设还有其他更多行...
        ),

接下来，创建一个CTE，其中包含你需要进一步处理的所有列，以及生成PCODE（由三列连接而成）和PCODE_AMOUNT（按照你的DECODE()函数计算）。这里我使用了CASE表达式替代DECODE()，但你也可以使用DECODE()...

    pcode_amounts AS -- 在WITH子句中创建的CTE
        (Select ID, FIELD1, FIELD2, FIELD3, AMOUNT_FIELD,
                FIELD1 || FIELD2 || FIELD3 "PCODE",
                Case FIELD1 || FIELD2 || FIELD3
                    When 'A' Then AMOUNT_FIELD * -1
                    When 'A1A' Then AMOUNT_FIELD * -1
                    When 'A5' Then 0
                    When 'A6' Then 0
                    When 'B1' Then AMOUNT_FIELD * -1
                    When 'B5' Then AMOUNT_FIELD * 0
                    When 'B6' Then 0
                 Else AMOUNT_FIELD
                End "PCODE_AMOUNT"
         From tbl
         -- 如果需要过滤特定PCODE值，可以在WHERE子句中添加条件，例如：Where FIELD1 || FIELD2 || FIELD3 IN('A1', 'B1')
        )
Select * 
From pcode_amounts

运行上述代码后，得到的结果集如下：

/* 结果：
            pcode_amounts CTE 记录集：
        ID FIELD1 FIELD2 FIELD3 AMOUNT_FIELD PCODE PCODE_AMOUNT
---------- ------ ------ ------ ------------ ----- ------------
         1               A                10 A              -10
         2 A      1      A                15 A1A            -15
         3 A      5                        5 A5               0
         4 A      6                       12 A6               0
         5 B      1                       17 B1             -17
         6 B      5                        9 B5               0
         7 B      6                        2 B6               0
         ... 更多行...
*/