试试这个：

with items(ID,Name,Formula_Id) AS (
    select 1, 'Item name 1', 'f_3' from dual union all
    select 2, 'Item name 2', 'f_26' from dual
),
formulas (ID, EXPRESSION, ALIAS, NAME) as (
    select 'f_1', null, 'var_100', 'Ticket' from dual union all
    select 'f_2', null, 'var_200', 'Amount' from dual union all
    select 'f_3', 'var_100 * var_200', 'var_300', 'Some description' from dual 
),
rnformulas (id, EXPRESSION, ALIAS, NAME, rn) as (
    select fm.*, row_number() over(order by id) as rn from formulas fm
),
recsubstitute( lvl, item_id, rn, expression ) as (
    select 1, it.id, 0, fm.expression
    from items it
    join rnformulas fm on it.formula_id = fm.id
    
    union all
    select lvl+1, item_id, fm.rn, replace(r.expression, fm.alias, fm.name)
    from recsubstitute r
    join rnformulas fm on instr(r.expression, fm.alias) > 0 and fm.rn > r.rn
)
select item_id, expression from (
    select item_id, expression, row_number() over(partition by item_id order by lvl desc, rn asc) as rn   
    from recsubstitute 
)
where rn = 1
;

       ITEM_ID EXPRESSION                                                                                                                                                                                                                                                                      
    ---------- ------------------------------------------------------------            
1 Ticket * Amount

请注意，它还远远不能完全抵御所有情况，特别是别名中的递归。

另一组数据的一些改善：

with items(ID,Name,Formula_Id) AS (
    select 1, 'Item name 1', 'f_3' from dual union all
    select 2, 'Item name 2', 'f_4' from dual
),
formulas (ID, EXPRESSION, ALIAS, NAME) as (
    select 'f_1', null, 'var_100', 'Ticket' from dual union all
    select 'f_2', null, 'var_200', 'Amount' from dual union all
    select 'f_3', 'var_100 * var_200', 'var_300', 'Some description' from dual union all
    select 'f_4', 'var_300', null, 'Other description' from dual 
),
rnformulas (id, EXPRESSION, ALIAS, NAME, rn) as (
    select fm.*, row_number() over(order by id) as rn from formulas fm
),
recsubstitute( lvl, item_id, rn, expression ) as (
    select 1, it.id, 0, fm.expression
    from items it
    join rnformulas fm on it.formula_id = fm.id
    
    union all
    select lvl+1, item_id, fm.rn, replace(r.expression, fm.alias, nvl(fm.expression,fm.name))
    from recsubstitute r
    join rnformulas fm on instr(r.expression, fm.alias) > 0 
)
select item_id, expression from (
    select item_id, expression, row_number() over(partition by item_id order by lvl desc, rn asc) as rn   
    from recsubstitute 
)
where rn = 1
;

1   Ticket * Amount
2   Ticket * Amount

将以空格分隔的公式拆分为行。将表达式部分联接到别名，并用名称替换别名。使用LISTAGG将此表达式联接到item_table，以将行重新连接为单个列。

WITH formula_split AS (
    SELECT DISTINCT ft.id
          ,level                                        lvl
          ,regexp_substr(ft.expression,'[^ ]+',1,level) expression_part
      FROM formula_table ft
    CONNECT BY ( ft.id = ft.id
       AND level <= length(ft.expression) - length(replace(ft.expression,' ')) + 1 ) START WITH ft.expression IS NOT NULL
),readable_tbl AS (
    SELECT ft.id
          ,ft.lvl
          ,replace(ft.expression_part,ftn1.aliase,ftn1.name) readable_expression
      FROM formula_split ft
      LEFT JOIN formula_table  ftn1 ON ( ft.expression_part = ftn1.aliase )
)
SELECT it.name item_name
      ,LISTAGG(readable_expression,' ') WITHIN GROUP(ORDER BY lvl) readable_expression
  FROM item_table it
  JOIN readable_tbl rt ON ( it.formula_id = rt.id )
 GROUP BY it.name

1.使用示例数据创建CTE（calc_data）用于建模

WITH
    items (ITEM_ID, ITEM_NAME, FORMULA_ID) AS
        (
            Select 1,   'Item name 1',  'f_3' From Dual Union All
            Select 2,   'Item name 2',  'f_26' From Dual
        ),
    formulas (FORMULA_ID, EXPRESSION, ALIAS, ELEMENT_NAME) AS
        (
            Select 'f_1',   null,               'var_100',  'Ticket' From Dual Union All
            Select 'f_2',   null,               'var_200',  'Amount' From Dual Union All
            Select 'f_3',   'var_100 * var_200',    'var_300',  'Some description' From Dual
        ),
  calc_data AS
    (   SELECT  e.ITEM_NAME, e.FORMULA_ID, e.FORMULA, e.X, e.OPERAND, e.Y,  
                ROW_NUMBER() OVER(Partition By e.ITEM_NAME Order By e.FORMULA_ID) "RN", f.ELEMENT_NAME 
        FROM(   Select  CAST('.' as VARCHAR2(32)) "FORMULA", i.ITEM_NAME, f.FORMULA_ID,
                    SubStr(Replace(f.EXPRESSION, ' ', ''), 1, InStr(Replace(f.EXPRESSION, ' ', ''), '*') - 1) "X",
                    CASE 
                        WHEN InStr(f.EXPRESSION, '+') > 0 THEN '+'
                        WHEN InStr(f.EXPRESSION, '-') > 0 THEN '-'
                        WHEN InStr(f.EXPRESSION, '*') > 0 THEN '*'
                        WHEN InStr(f.EXPRESSION, '/') > 0 THEN '/'
                    END "OPERAND",
                    --
                    SubStr(Replace(f.EXPRESSION, ' ', ''), InStr(Replace(f.EXPRESSION, ' ', ''), '*') + 1) "Y"
                From    formulas f
                Inner Join items i  ON(f.FORMULA_ID = i.FORMULA_ID)
            ) e
        Inner Join formulas f ON(f.FORMULA_ID <> e.FORMULA_ID)
    )

1.* * 带MODEL子句的主SQL**

SELECT  ITEM_NAME, FORMULA
FROM    (   SELECT  * 
            FROM calc_data
            MODEL
                PARTITION BY    (ITEM_NAME)
                DIMENSION BY    (RN)
                MEASURES        (X, OPERAND, Y, FORMULA, ELEMENT_NAME)
                RULES       (  FORMULA[1] = ELEMENT_NAME[1] || ' ' || OPERAND[1] || ' ' || ELEMENT_NAME[2] )
        )
WHERE RN = 1

结果：
| 项目名称|配方|
| - ------| - ------|
| 项目名称1|金额 * 机票|
只是作为一个选项...
没有任何分析函数、伪列、联合等，结果都是一样的--只是一遍又一遍地选择。虽然不可读...... *

Select
    i.ITEM_NAME,
    REPLACE(  REPLACE(  (Select EXPRESSION From formulas Where FORMULA_ID = f.FORMULA_ID), 
                  (Select Min(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID),
                  (Select ELEMENT_NAME From formulas Where FORMULA_ID <> f.FORMULA_ID And ALIAS = (Select Min(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID) )
               )   ||
              REPLACE(  (Select EXPRESSION From formulas Where FORMULA_ID = f.FORMULA_ID), 
                        (Select Max(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID), 
                        (Select ELEMENT_NAME From formulas Where FORMULA_ID <> f.FORMULA_ID And ALIAS = (Select Max(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID) )  
              ), 
              (SELECT Max(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID  ) || (Select Min(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID) || 
                              SubStr(f.EXPRESSION, InStr(f.EXPRESSION, ' ', 1, 1), (InStr(f.EXPRESSION, ' ', 1, 2) - InStr(f.EXPRESSION, ' ', 1, 1)) + 1 ), ''
    ) "FORMULA"
From
    formulas f
Left Join
    items i ON(i.FORMULA_ID = f.FORMULA_ID)
Where i.ITEM_NAME Is Not Null

谢谢大家的回答！
我决定创建一个pl/sql函数，只是为了将一个公式修改为可读行。所以这个函数只是使用regex查找变量，并使用索引将每个变量替换为一个名称。

CREATE OR REPLACE FUNCTION READABLE_EXPRESSION(inExpression IN VARCHAR2)
    RETURN VARCHAR2
    IS
    matchesCount       INTEGER;
    toReplace          VARCHAR2(32767);
    readableExpression VARCHAR2(32767);
    selectString       VARCHAR2(32767);
BEGIN
    matchesCount := REGEXP_COUNT(inExpression, '(var_)(.*?)');

    IF matchesCount IS NOT NULL AND matchesCount > 0 THEN
        readableExpression := inExpression;

        FOR i in 1..matchesCount
            LOOP
                toReplace := substr(inExpression, REGEXP_INSTR(inExpression, '(var_)(.*?)', 1, i, 0),
                                    REGEXP_INSTR(inExpression, '(var_)(.*?)', 1, i, 1) -
                                    REGEXP_INSTR(inExpression, '(var_)(.*?)', 1, i, 0)
                    );

                SELECT DISTINCT F.NAME
                INTO selectString
                FROM FORMULA F
                WHERE F.ALIASE = toReplace FETCH FIRST 1 ROW ONLY;

                readableExpression := REPLACE(readableExpression,
                                              toReplace,
                                              selectString
                    );
            end loop;
    end if;
    return readableExpression;
END;

因此，这样的函数返回1个结果行，其中1个输入行的值被替换为FORMULA。您所需要做的就是在SELECT中连接ITEM和FORMULA表。

SELECT item.name, READABLE_EXPRESSION(formula.expression) 
FROM item
JOIN formula ON item.formula_id = formula.id;

请注意，这些表格是虚构的，不会显示实际的数据结构，因此可能会有一些不准确之处，但总体思路应该是清晰的。