我有一個以下格式的復合觸發器。我需要確保我的自定義宣告 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;
uj5u.com熱心網友回復：
在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。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/407678.html
標籤：
下一篇：僅選擇聯系人長度不是5的用戶