Informix
Informix 創建過程來查詢記錄,如果值不存在則使用 select 刪除
我正在嘗試創建一個過程,該過程將查詢表列以檢索表名,並在 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 ;