时间戳

首页 > 解决方案 > APEX 20 oracle 中的数据操作

oracle - APEX 20 oracle 中的数据操作

问题描述

目前我正在执行动态操作（执行服务器端代码），其中我从两个不同的表（XYZ 和 ABC）中选择值，执行计算并插入另一个表（ABC_TEMP）并在 apex（v20）中创建一个报告视图下面是我正在执行的。

BEGIN 
        INSERT INTO ABC_TEMP (
            A1,           --> VARCHAR2(4000)
            B1,           --> VARCHAR2(4000)
            C1,           --> NUMBER
            D1,           --> NUMBER
            E1,           --> NUMBER
            F,            --> NUMBER
            G1,           --> NUMBER
            H3,           --> NUMBER
            I3,           --> NUMBER
            J,            --> NUMBER
            K,            --> NUMBER
            L,            --> NUMBER
            timestamp     -->timestamp(6)
            )
        VALUES (
            :A_SELECT, 
            :B_SELECT, 
            :C_SELECT,
            (SELECT D2 FROM XYZ WHERE B2 = :B_SELECT AND C2 = :C_SELECT),
            (SELECT E2 FROM XYZ WHERE B2 = :B_SELECT AND C2 = :C_SELECT),
            (SELECT SUM(D2 + E2) FROM XYZ WHERE B2 = :B_SELECT AND C2 = :C_SELECT),
            (SELECT G2 FROM XYZ WHERE B2 = :B_SELECT AND C2 = :B_SELECT),
            (SELECT H2 FROM ABC WHERE A2 = :A_SELECT AND P2 = 'mock1' AND SE = 'mock2' AND Q2 = 'val1'),
            (SELECT H2 FROM ABC WHERE A2 = :A_SELECT AND P2 = 'mock1' AND SE = 'mock2' AND Q2 = 'val2'),
            (:J),  --> This value is derived from `ABC_TEMP` table only by dividing I3 BY F 
            (:K),  --> This value is derived from `ABC_TEMP` table only by dividing H3 BY G1 
            (:L),  --> this value is derived from  low of J & K column 
            (CURRENT_TIMESTAMP)
            );
END;

我的问题是如何在同一个查询中设置 J、K、L 列的值，因为它涉及从同一个表中选择并在我插入数据的地方执行计算。如果这是不可能的，这里还有其他方法。

标签： oracleoracle11goracle-sqldeveloperoracle-apex

解决方案

从字面上复制/粘贴这些列的源（select语句）并将它们分开：

values
  (:A_SELECT, 
   ...
   -- for J, literally copy/paste I3 / F
   (SELECT H2 FROM ABC WHERE A2 = :A_SELECT AND P2 = 'mock1' AND SE = 'mock2' AND Q2 = 'val2') /
   (SELECT SUM(D2 + E2) FROM XYZ WHERE B2 = :B_SELECT AND C2 = :C_SELECT)
   -- the same goes for other columns
  );

它有效吗？当然：

SQL> select (select sal from emp where rownum = 1) /
  2         (select empno from emp where rownum = 1) as j
  3  from dual;

         J
----------
,108562899

SQL>

优化了吗？当然不是，您将使用相同的查询两次（一次用于“原始”列，一次用于“派生”列）。

你能优化它吗？也许。尝试创建一堆 CTE（您使用的每个子查询一个），然后将其重新用于派生列。

另一方面，为什么要将这样的值存储到表中？那是多余的。从表中省略（删除）列 J、K 和 L 并在需要时计算它们的值，例如

select a1, 
       c1,
       i3 / f as J      --> this
from abc_temp
where ...

或者，您甚至可以使用相同的视图创建一个视图select（我在上面发布）并从视图中选择值。

推荐阅读