oracle 触发器使用2行数据

64jmpszr 于 2023-01-12 发布在 Oracle

关注(0)|答案(2)|浏览(121)

先讲一点背景知识：
我有两个表，HOLD和CALC。当我启动一个事务时，在HOLD表中创建了一行，其中包含JOB_ID、STATUS：START和TIME：SYSDATETIME。作业完成后，将在HOLD表中创建另一行，该行具有相同的JOB_ID、STATUS：END和TIME：SYSDATETIME。因此，当事务完成时，HOLD表将如下所示：
| 作业ID|状态|时间|
| - ------|- ------|- ------|
| 1234|启动|时间1|
| 1234|结束|时间2|
我计划创建一个在插入状态END数据后激活的触发器。此触发器将提取JOB_ID并计算（TIME2-TIME1），然后将其仅作为分钟或秒插入CALC表中。因此，最后，每个JOB_ID只有一行。
| 作业ID|所用时间|
| - ------|- ------|
| 1234|05时|
我目前在选择具有相同JOB_ID的行时遇到了麻烦，因为当我尝试使用where子句时，我一直得到错误，以及如何将时间插入变量以便我可以使用TIME_DIFF。

CREATE OR REPLACE TRIGGER "TEST".CALC_TRIGGER AFTER INSERT ON "HOLD" REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_STATUS VARCHAR2(1 Byte);
BEGIN
IF (:NEW.STATUS = 'END') THEN
BEGIN
    INSERT INTO CALC(JOB_ID, TIME_TAKEN)
VALUES (:OLD.JOB_ID, NULL);
END;
END IF;
END;
/

先谢了!

oracle

来源：https://stackoverflow.com/questions/75065311/trigger-to-use-data-from-2-rows

2条答案

按热度按时间

6ovsh4lw1#

示例数据如下所示：

WITH
    tbl AS
        (
            Select 1234 "JOB_ID", 'START' "STATUS", To_Date('01.01.2023 17:12:35', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All
            Select 1234 "JOB_ID", 'END' "STATUS", To_Date('04.01.2023 10:10:10', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All
            
            Select 4567 "JOB_ID", 'START' "STATUS", To_Date('01.01.2023 17:12:35', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All
            Select 4567 "JOB_ID", 'END' "STATUS", To_Date('03.01.2023 11:12:13', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All 
            
            Select 6789 "JOB_ID", 'START' "STATUS", To_Date('01.01.2023 17:12:35', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual Union All
            Select 6789 "JOB_ID", 'END' "STATUS", To_Date('06.01.2023 17:12:35', 'dd.mm.yyyy hh24:mi:ss') "TIME" From Dual 
        ),

您可以创建一个CTE，以便在同一行中获取开始日期和结束日期（时间）...

jobs AS
    (   Select   t.JOB_ID "JOB_ID",
                (Select TIME From tbl Where JOB_ID = t.JOb_ID And STATUS = 'START') "STARTED",
                (Select TIME From tbl Where JOB_ID = t.JOb_ID And STATUS = 'END') "ENDED"
        From    tbl t
        Group By  JOB_ID
    )

...现在您可以获取任何作业ID的日期-时间差...

Select  JOB_ID "JOB_ID",
        To_Char(STARTED, 'dd.mm.yyyy hh24:mi:ss' ) "STARTED",
        To_Char(ENDED,   'dd.mm.yyyy hh24:mi:ss' ) "ENDED",
        Trunc( ENDED - STARTED ) "DAYS", 
        Trunc( (ENDED - STARTED ) * 24 ) - ( Trunc( ENDED - STARTED ) * 24 ) "HOURS",
        Trunc( mod( (ENDED - STARTED ) *24 * 60, 60 ) ) "MINUTES",
        Trunc( mod( (ENDED - STARTED ) *24 *60 * 60, 60 ) ) "SECONDS"
From    jobs
Where   JOB_ID = 1234
--  
--  R e s u l t :
--      JOB_ID STARTED             ENDED                     DAYS      HOURS    MINUTES    SECONDS
--  ---------- ------------------- ------------------- ---------- ---------- ---------- ----------
--        1234 01.01.2023 17:12:35 04.01.2023 10:10:10          2         16         57         34

赞(0）回复(0）举报 2023-01-12

qeeaahzv2#

只要在for each row触发器中无法访问表，就需要为此使用复合触发器：
一个一个
| 作业ID|所用时间|
| - ------|- ------|
| 1个|+00 00时00分558130秒|
fiddle

赞(0）回复(0）举报 2023-01-12