Informix

Informix 創建過程來查詢記錄,如果值不存在則使用 select 刪除

  • August 26, 2019

我正在嘗試創建一個過程,該過程將查詢表列以檢索表名,並在 proc 中使用它來執行查詢以進行選擇(如果存在)。如果不存在,則從 systables_growth 表中刪除行。

以下是我到目前為止的內容:

CREATE function test_sp();
DEFINE v_tab CHAR(255);
     FOREACH SELECT unique(tabname) INTO v_tab from systables_growth
       BEGIN
           SELECT * FROM v_tab;
           ON EXCEPTION IN (-206) -- If no table was found, delete record from systables_growth table
           DELETE from systables_growth WHERE tabname = 'v_tab'
           END EXCEPTION WITH RESUME;
       END
   END FOREACH
END function;

創建時收到以下語法錯誤:

CREATE function test_sp();
DEFINE v_tab CHAR(255);
FOREACH SELECT unique(tabname) INTO v_tab from systables_growth
BEGIN
SELECT * FROM v_tab;
ON EXCEPTION IN (-206) -- If no table was found, delete record from systables_growth table
#^
#  201: A syntax error has occurred.
#
DELETE from systables_growth WHERE tabname = 'v_tab'
END EXCEPTION WITH RESUME;
END
END FOREACH
END function;

我正在嘗試定義一些邏輯來查詢從表中提取的記錄,以執行從提取的表名中進行選擇的查詢。如果選擇說表不存在,那麼我希望從表中刪除該行。我不知道我是否使用了正確的邏輯。

我不希望該過程生成任何輸出,我希望它保持沉默。

我在informix中找到了一個使用儲存過程的替代解決方案,它也適用於我:

create procedure systables_growth_tblclnup()
DELETE FROM systables_growth WHERE tabname NOT IN
(SELECT unique(tabname) FROM systables WHERE systables_growth.tabname = systables.tabname);
end procedure ;

引用自:https://dba.stackexchange.com/questions/246217