oracle触发器，表多行转置插入一行多列，急！！！

Question

oracle触发器行列转置

Answer 1

96条数据的数据生成时间是一样的,这96条是怎么插入的，一条一条Insert?

Answer 2

如果是按顺序插入，倒可以在触发器中判断，当时间是23:45时，往load表中添加

Answer 3

写了行sql语句，可以插入 insert into w.load (ADID, TIME, EDITTIME, FLAG, V0, V1, V2, V3, V4, V5, V6, V7, V8, V9, V10,
V11, V12, V13, V14, V15, V16, V17, V18, V19, V20, V21, V22, V23, V24, V25, V26, V27, V28, V29, V30,
V31, V32, V33, V34, V35, V36, V37, V38, V39, V40, V41, V42, V43, V44, V45, V46, V47, V48, V49, V50,
V51, V52, V53, V54, V55, V56, V57, V58, V59, V60, V61, V62, V63, V64, V65, V66, V67, V68, V69, V70,
V71, V72, V73, V74, V75, V76, V77, V78, V79, V80, V81, V82, V83, V84, V85, V86, V87, V88, V89, V90,
V91, V92, V93, V94, V9) select :new.adid,trunc(max(time),'mi'), :new.edittime,0,SUM(decode(to_char(time,'hh24:mi'),'00:00',v)),SUM(decode(to_char(time,'hh24:mi'),'00:15',v)),SUM(decode(to_char(time,'hh24:mi'),'00:30',v)),SUM(decode(to_char(time,'hh24:mi'),'00:45',v)),SUM(decode(to_char(time,'hh24:mi'),'01:00',v)),SUM(decode(to_char(time,'hh24:mi'),'01:15',v)),SUM(decode(to_char(time,'hh24:mi'),'01:30',v)),SUM(decode(to_char(time,'hh24:mi'),'01:45',v)),SUM(decode(to_char(time,'hh24:mi'),'02:00',v)),SUM(decode(to_char(time,'hh24:mi'),'02:15',v)),SUM(decode(to_char(time,'hh24:mi'),'02:30',v)),SUM(decode(to_char(time,'hh24:mi'),'02:45',v)),SUM(decode(to_char(time,'hh24:mi'),'03:00',v)),SUM(decode(to_char(time,'hh24:mi'),'03:15',v)),SUM(decode(to_char(time,'hh24:mi'),'03:30',v)),SUM(decode(to_char(time,'hh24:mi'),'03:45',v)),SUM(decode(to_char(time,'hh24:mi'),'04:00',v)),SUM(decode(to_char(time,'hh24:mi'),'04:15',v)),SUM(decode(to_char(time,'hh24:mi'),'04:30',v)),SUM(decode(to_char(time,'hh24:mi'),'04:45',v)),SUM(decode(to_char(time,'hh24:mi'),'05:00',v)),SUM(decode(to_char(time,'hh24:mi'),'05:15',v)),SUM(decode(to_char(time,'hh24:mi'),'05:30',v)),SUM(decode(to_char(time,'hh24:mi'),'05:45',v)),SUM(decode(to_char(time,'hh24:mi'),'06:00',v)),SUM(decode(to_char(time,'hh24:mi'),'06:15',v)),SUM(decode(to_char(time,'hh24:mi'),'06:30',v)),SUM(decode(to_char(time,'hh24:mi'),'06:45',v)),SUM(decode(to_char(time,'hh24:mi'),'07:00',v)),SUM(decode(to_char(time,'hh24:mi'),'07:15',v)),SUM(decode(to_char(time,'hh24:mi'),'07:30',v)),SUM(decode(to_char(time,'hh24:mi'),'07:45',v)),SUM(decode(to_char(time,'hh24:mi'),'08:00',v)),SUM(decode(to_char(time,'hh24:mi'),'08:15',v)),SUM(decode(to_char(time,'hh24:mi'),'08:30',v)),SUM(decode(to_char(time,'hh24:mi'),'08:45',v)),SUM(decode(to_char(time,'hh24:mi'),'09:00',v)),SUM(decode(to_char(time,'hh24:mi'),'09:15',v)),SUM(decode(to_char(time,'hh24:mi'),'09:30',v)),SUM(decode(to_char(time,'hh24:mi'),'09:45',v)),SUM(decode(to_char(time,'hh24:mi'),'10:00',v)),SUM(decode(to_char(time,'hh24:mi'),'10:15',v)),SUM(decode(to_char(time,'hh24:mi'),'10:30',v)),SUM(decode(to_char(time,'hh24:mi'),'10:45',v)),SUM(decode(to_char(time,'hh24:mi'),'11:00',v)),SUM(decode(to_char(time,'hh24:mi'),'11:15',v)),SUM(decode(to_char(time,'hh24:mi'),'11:30',v)),SUM(decode(to_char(time,'hh24:mi'),'11:45',v)),SUM(decode(to_char(time,'hh24:mi'),'12:00',v)),SUM(decode(to_char(time,'hh24:mi'),'12:15',v)),SUM(decode(to_char(time,'hh24:mi'),'12:30',v)),SUM(decode(to_char(time,'hh24:mi'),'12:45',v)),SUM(decode(to_char(time,'hh24:mi'),'13:00',v)),SUM(decode(to_char(time,'hh24:mi'),'13:15',v)),SUM(decode(to_char(time,'hh24:mi'),'13:30',v)),SUM(decode(to_char(time,'hh24:mi'),'13:45',v)),SUM(decode(to_char(time,'hh24:mi'),'14:00',v)),SUM(decode(to_char(time,'hh24:mi'),'14:15',v)),SUM(decode(to_char(time,'hh24:mi'),'14:30',v)),SUM(decode(to_char(time,'hh24:mi'),'14:45',v)),SUM(decode(to_char(time,'hh24:mi'),'15:00',v)),SUM(decode(to_char(time,'hh24:mi'),'15:15',v)),SUM(decode(to_char(time,'hh24:mi'),'15:30',v)),SUM(decode(to_char(time,'hh24:mi'),'15:45',v)),SUM(decode(to_char(time,'hh24:mi'),'16:00',v)),SUM(decode(to_char(time,'hh24:mi'),'16:15',v)),SUM(decode(to_char(time,'hh24:mi'),'16:30',v)),SUM(decode(to_char(time,'hh24:mi'),'16:45',v)),SUM(decode(to_char(time,'hh24:mi'),'17:00',v)),SUM(decode(to_char(time,'hh24:mi'),'17:15',v)),SUM(decode(to_char(time,'hh24:mi'),'17:30',v)),SUM(decode(to_char(time,'hh24:mi'),'17:45',v)),SUM(decode(to_char(time,'hh24:mi'),'18:00',v)),SUM(decode(to_char(time,'hh24:mi'),'18:15',v)),SUM(decode(to_char(time,'hh24:mi'),'18:30',v)),SUM(decode(to_char(time,'hh24:mi'),'18:45',v)),SUM(decode(to_char(time,'hh24:mi'),'19:00',v)),SUM(decode(to_char(time,'hh24:mi'),'19:15',v)),SUM(decode(to_char(time,'hh24:mi'),'19:30',v)),SUM(decode(to_char(time,'hh24:mi'),'19:45',v)),SUM(decode(to_char(time,'hh24:mi'),'20:00',v)),SUM(decode(to_char(time,'hh24:mi'),'20:15',v)),SUM(decode(to_char(time,'hh24:mi'),'20:30',v)),SUM(decode(to_char(time,'hh24:mi'),'20:45',v)),SUM(decode(to_char(time,'hh24:mi'),'21:00',v)),SUM(decode(to_char(time,'hh24:mi'),'21:15',v)),SUM(decode(to_char(time,'hh24:mi'),'21:30',v)),SUM(decode(to_char(time,'hh24:mi'),'21:45',v)),SUM(decode(to_char(time,'hh24:mi'),'22:00',v)),SUM(decode(to_char(time,'hh24:mi'),'22:15',v)),SUM(decode(to_char(time,'hh24:mi'),'22:30',v)),SUM(decode(to_char(time,'hh24:mi'),'22:45',v)),SUM(decode(to_char(time,'hh24:mi'),'23:00',v)),SUM(decode(to_char(time,'hh24:mi'),'23:15',v)),SUM(decode(to_char(time,'hh24:mi'),'23:30',v)),SUM(decode(to_char(time,'hh24:mi'),'23:45',v))from wds.temp group by adid,edittime;
但是触发器实在不知道怎么弄。而且要判别条件

Answer 4

如果是temp表是按顺序插入的话，那么插入23:45的记录时，可以认为该id的所有记录都已插入id表
因此写个行级触发器，当:new.time为23:45时，
insert into load(...)
select sum(decode())....
from temp
where id=:new.id
即可

Answer 5

那样就比较麻烦了。理论上可以在每条插入时都去判断是否所有时间的都已插入完整，例如
select count(1) from temp where id=:new.id
当count出来的值为96时，再对load表做插入动作。但是注意，如果数据量大或插入频率较高，该触发器可能导致性能问题从设计上来修改会比较好一些

Answer 6

如果你的数据库是11G的话，可以考虑使用组合触发器，它的特点是可以在同一表的不同类触发器中共享变量。以你的问题为例，可以组建如下类似逻辑的触发器:CREATE OR REPLACE TRIGGER compound_trigger_name
FOR INSERT ON w.temp
COMPOUND TRIGGER
     v_id INT;
     v_count INT;
     --在for each row触发器中保存id
     BEFORE EACH ROW IS
     BEGIN
        v_id:=:new.id;
     END BEFORE EACH ROW;
     
     --在after stmt触发器中检查是否满足同步的条件
     AFTER STATEMENT IS
     BEGIN
        SELECT COUNT(1) INTO v_count FROM w.temp
        WHERE ID=v_id;
        --当temp达到96条记录时开始插入
        IF v_count< THEN 
            RETURN; 
        END IF;
        INSERT INTO w.load 
        SELECT ID,trunc(a.TIME),MAX(edittime),
               MAX(DECODE(seq,1,v)) v0,
               ...
               MAX(DECODE(seq,96,v)) v95,
        FROM (
            SELECT a.*,
                   row_number() OVER(ORDER BY a.TIME) seq
            FROM w.temp a) a
        GROUP BY ID,trunc(a.TIME);
     END AFTER STATEMENT;
 
END compound_trigger_name;