删除与LIKE匹配子字符串的行? [英] Delete rows matching substring with LIKE?

问题描述

如何从表中删除行，该表的一列包含一个子字符串，但该列的类型为'Long'. (是的，我知道我不应该使用Long，但是我正在维护其他人的混乱状态.)

How do you delete rows from a table, where a column contains a substring, but the type of that column is 'Long'. (Yes, I know I shouldn't use Long, but I'm maintaining someone else's mess).

我的第一次尝试是:

delete from longtable 
  where search_long(rowid) like '%hello%';  

^{(接着这将返回:}

SQL错误:ORA-04091:表blah.longtable正在变异，触发器/函数可能看不到

SQL Error: ORA-04091: table blah.longtable is mutating, trigger/function may not see it

推荐答案

我刚刚复制了您的问题，并得到了相同的错误-似乎该函数无法在DELETE语句中运行.错误的全文是:

I just replicated your problem and got the same error - it seems the function can't work from within a DELETE statement. The full text of the error is:

ORA-04091: table HOU.LONGTABLE is mutating, trigger/function may not see it
ORA-06512: at "TONY.SEARCH_LONG", line 4

此程序方法将起作用:

begin
  for r in (select id from longtable 
            where search_long(rowid) like '%hello%')
  loop
    delete longtable where id = r.id;
  end loop;
end;

这篇关于删除与LIKE匹配子字符串的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！