Oracle SQL中的动态度量计算

nwsw7zdq 于 2023-06-22 发布在 Oracle

关注(0)|答案(3)|浏览(146)

我正在尝试在Oracle中自动化一些度量计算。我确实有大约50个指标，需要计算的基础上一些低层次的信息。每个指标都有不同的计算逻辑，尽管它们可以共享基础数据点。我有一个字段中捕获的所有计算文本沿着表中的基础数据点。
我试图写一个使用动态SQL的函数来完成这项工作。下面只是我所遵循的方法中的一个代码块。当然，我没有以正确的方式做，因此它会提示错误：

DECLARE
    A NUMBER :=10;
    B NUMBER :=20;
    C NUMBER :=40;
    D NUMBER :=35;
    OUTPUT_VAR NUMBER;
    SQL_TEXT VARCHAR2(50);
BEGIN
--    SQL_TEXT:='OUTPUT_VAR:=A+B;';
--    SQL_TEXT:='OUTPUT_VAR:=A+B+C;';
--    SQL_TEXT:='OUTPUT_VAR:=A/D;';
--    SQL_TEXT:='OUTPUT_VAR:=A*B;';
--    SQL_TEXT:='OUTPUT_VAR:=B+C;';
--    SQL_TEXT:='OUTPUT_VAR:=C/D;';
--    SQL_TEXT:='OUTPUT_VAR:=B/C;';
    SQL_TEXT:='OUTPUT_VAR:=A+D;';
    
    EXECUTE IMMEDIATE SQL_TEXT;
    DBMS_OUTPUT.PUT(OUTPUT_VAR);
END;

下面是我得到的错误：

Error starting at line : 1 in command -
DECLARE
    A NUMBER :=10;
    B NUMBER :=20;
    C NUMBER :=40;
    D NUMBER :=35;
    OUTPUT_VAR NUMBER;
    SQL_TEXT VARCHAR2(50);
BEGIN
--    SQL_TEXT:='OUTPUT_VAR:=A+B;';
--    SQL_TEXT:='OUTPUT_VAR:=A+B+C;';
--    SQL_TEXT:='OUTPUT_VAR:=A/D;';
--    SQL_TEXT:='OUTPUT_VAR:=A*B;';
--    SQL_TEXT:='OUTPUT_VAR:=B+C;';
--    SQL_TEXT:='OUTPUT_VAR:=C/D;';
--    SQL_TEXT:='OUTPUT_VAR:=B/C;';
    SQL_TEXT:='OUTPUT_VAR:=A+D;';

    EXECUTE IMMEDIATE SQL_TEXT;
    DBMS_OUTPUT.PUT(OUTPUT_VAR);
END;
Error report -
ORA-00900: invalid SQL statement
ORA-06512: at line 18
00900. 00000 -  "invalid SQL statement"
*Cause:    
*Action:

请求帮助使这项工作。感谢您的回复，谢谢！

oracle

来源：https://stackoverflow.com/questions/62900665/dynamic-metric-calculations-in-oracle-sql

3条答案

按热度按时间

wfsdck301#

DECLARE
    A NUMBER :=10;
    B NUMBER :=20;
    C NUMBER :=40;
    D NUMBER :=35;
    OUTPUT_VAR NUMBER;
    SQL_TEXT VARCHAR2(50);
function calc(p_cmd in varchar2) return number as
   res number;
begin
    execute immediate '
       Declare
           A NUMBER :=:a;
           B NUMBER :=:b;
           C NUMBER :=:c;
           D NUMBER :=:d;
           OUTPUT_VAR number;
        Begin 
           '||p_cmd||'  
           :res:= output_var; 
        end;' 
        Using a, b, c, d, out res;
     Return res;
End;
BEGIN
--    SQL_TEXT:='OUTPUT_VAR:=A+B;';
--    SQL_TEXT:='OUTPUT_VAR:=A+B+C;';
--    SQL_TEXT:='OUTPUT_VAR:=A/D;';
--    SQL_TEXT:='OUTPUT_VAR:=A*B;';
--    SQL_TEXT:='OUTPUT_VAR:=B+C;';
--    SQL_TEXT:='OUTPUT_VAR:=C/D;';
--    SQL_TEXT:='OUTPUT_VAR:=B/C;';
    SQL_TEXT:='OUTPUT_VAR:=A+D;';
    
    OUTPUT_VAR:=calc(SQL_TEXT) ;
    DBMS_OUTPUT.PUT_LINE(OUTPUT_VAR);
END;

赞(0）回复(0）举报 2023-06-22

igetnqfo2#

另一种在SQL中不使用PL/SQL的方法是使用XMLTABLE或XMLQUERY。XQUERY允许您非常轻松地进行简单计算，例如：

select * 
from xmltable(
       '$A + $B - $C'
       passing 1 as A, 2 as B, 3 as C
       columns res number path '.'
     );

       RES
----------
         0

select * 
from xmltable(
       '$A + $B * $C'
       passing 1 as A, 2 as B, 3 as C
       columns res number path '.'
     );

       RES
----------
         7

所以你可以很容易地进行动态计算，你只需要在变量名中添加'$'：

with
data(a,b,c,d) as (
   select 1,1,1,1 from dual union all
   select 1,2,3,4 from dual union all
   select 5,5,5,5 from dual 
)
,calc(id, expression) as (
    select 1, 'A + B' from dual union all
    select 2, 'A + B - C' from dual union all
    select 3, 'A * B * C' from dual union all
    select 4, '(A + B) * C - D' from dual
)
select
   *
from
    data d
   ,calc c
   ,xmltable(
        (regexp_replace(c.expression,'(\w)','$\1'))
        passing
            d.A as A,
            d.B as B,
            d.C as C,
            d.D as D
        columns
            res number path '.'
   ) x;

         A          B          C          D         ID EXPRESSION             RES
---------- ---------- ---------- ---------- ---------- --------------- ----------
         1          1          1          1          1 A + B                    2
         1          1          1          1          2 A + B - C                1
         1          1          1          1          3 A * B * C                1
         1          1          1          1          4 (A + B) * C - D          1
         1          2          3          4          1 A + B                    3
         1          2          3          4          2 A + B - C                0
         1          2          3          4          3 A * B * C                6
         1          2          3          4          4 (A + B) * C - D          5
         5          5          5          5          1 A + B                   10
         5          5          5          5          2 A + B - C                5
         5          5          5          5          3 A * B * C              125
         5          5          5          5          4 (A + B) * C - D         45

12 rows selected.

PS注意，你需要在'-'符号前加一个空格，否则它会被认为是'B-'变量：

SQL> select * from xmltable('$A+$B-$C' passing 1 as A, 2 as B, 3 as C columns res number path '.');
select * from xmltable('$A+$B-$C' passing 1 as A, 2 as B, 3 as C columns res number path '.')
                               *
ERROR at line 1:
ORA-19228: XPST0008 - undeclared identifier: prefix 'B-' local-name ''

赞(0）回复(0）举报 2023-06-22

wz8daaqr3#

我们可以在这些表达式中使用聚合函数吗？例如$A + $B*GREATEST（5，6）。

赞(0）回复(0）举报 2023-06-22