Oracle:在复合触发器中，仅将唯一记录插入到自定义记录中

Question

我有一个如下格式的复合触发器。我需要确保我的自定义声明ints_rows只取"；唯一行"；。这意味着如果ints_rows已经有一个与正在插入的记录类似的记录，它应该忽略它。更像SET数据结构。我该如何在预言中做到这一点？(我对oracle很陌生，因此我不擅长语法(我想我必须更改BULK COLLECT语句或int_records的声明，但我可能错了。非常感谢您的帮助/提示。

这是我的复合触发代码。

CREATE OR REPLACE
TRIGGER MY_COMPOUND_TRIGGER
FOR UPDATE OF some_random_column ON some_random_table
COMPOUND TRIGGER
TYPE int_records IS RECORD (
column_one another_table.column_one%TYPE,
column_two another_table.column_two%TYPE
);
TYPE row_list IS TABLE OF int_records INDEX BY simple_integer;
ints_rows row_list;
BEFORE STATEMENT IS
BEGIN
ints_rows.delete;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
SELECT column_one, column_two BULK COLLECT INTO ints_rows 
FROM some_table_x WHERE some_col_id=:OLD.some_col_id;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
FOR i IN 1 .. ints_rows.COUNT LOOP
-- DO SOMETHING
END LOOP;
auctions_rows.delete;
END AFTER STATEMENT;
END;

此语句根本不应该重复。

SELECT column_one, column_two BULK COLLECT INTO ints_rows 
FROM some_table_x WHERE some_col_id=:OLD.some_col_id;

Answer 1

在AFTER EACH ROW部分中，您覆盖整个ints_rows。原则上可能是这样的：

CREATE TABLE SOME_RANDOM_TABLE (some_random_column NUMBER, some_col_id NUMBER);
CREATE TABLE ANOTHER_TABLE (column_one NUMBER, column_two NUMBER);
CREATE TABLE SOME_TABLE_X (some_col_id NUMBER, column_one NUMBER, column_two NUMBER);
CREATE OR REPLACE TYPE int_records AS OBJECT (
column_one NUMBER,
column_two NUMBER,
MAP MEMBER FUNCTION getId RETURN VARCHAR2
);
CREATE OR REPLACE TYPE BODY int_records AS 
MAP MEMBER FUNCTION getId RETURN VARCHAR2 IS
BEGIN
RETURN column_one ||','|| column_two;
END getId;
END;
/
CREATE OR REPLACE TYPE row_list IS TABLE OF int_records; 

CREATE OR REPLACE TRIGGER MY_COMPOUND_TRIGGER
FOR UPDATE OF some_random_column ON SOME_RANDOM_TABLE
COMPOUND TRIGGER

ints_rows row_list;
BEFORE STATEMENT IS
BEGIN
ints_rows := row_list();
END BEFORE STATEMENT;

AFTER EACH ROW IS
int_row row_list;
BEGIN
SELECT int_records(column_one, column_two) 
BULK COLLECT INTO int_row 
FROM SOME_TABLE_X  x
WHERE x.some_col_id = :OLD.some_col_id;
ints_rows := ints_rows MULTISET UNION DISTINCT int_row;

END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
FOR i IN 1 .. ints_rows.COUNT LOOP
NULL;
END LOOP;
END AFTER STATEMENT;
END;

请测试，如果不起作用，请告诉我们。您可以通过循环手动使记录不同，或者您需要为记录实现MAP MEMBER FUNCTION。