ORA-00904无效标识符预言机

Question

我想使用可能不存在的列更新表。我正在这样做，但遇到了一个错误，我试图处理，但仍然遇到了异常。有办法解决这个问题吗？只在列存在的情况下更新表？我是Oracle PL/SQL的初学者，如有任何帮助，不胜感激。谢谢

declare
column_not_exists_exception exception;
pragma exception_init (column_not_exists_exception , -06550);
begin
update table1 
set column = case 
when column_may_not_exists = 0 then 0 
when column_may_not_exists = 1 then 1
else 0 
end;
exception when column_not_exists_exception then
dbms_output.put_line('error column does not exists');
end;

我得到的错误是ORA-06550 and ORA-00904 : "column_may_not_exists" : invalid identifier , cause: usually a pl/sql compilation error

Answer 1

这当然是一个奇怪的要求，但由于PL/SQL在编译时必须有效，因此不能直接引用不存在的列。

因此，您可以执行以下

SQL> create table t ( x int, y int );
Table created.
SQL> insert into t values (0,0);
1 row created.
SQL>
SQL> set serverout on
SQL> declare
2    column_not_exists_exception exception;
3    pragma exception_init (column_not_exists_exception , -904);
4  begin
5   execute immediate '
6     update t
7     set y = case
8      when y = 0 then 10
9      when y = 1 then 20
10      else 30
11   end';
12   dbms_output.put_line('All good');
13  exception
14    when column_not_exists_exception then
15     dbms_output.put_line('error column does not exists');
16  end;
17  /
All good
PL/SQL procedure successfully completed.
SQL>
SQL> set serverout on
SQL> declare
2    column_not_exists_exception exception;
3    pragma exception_init (column_not_exists_exception , -904);
4  begin
5   execute immediate '
6     update t
7     set z = case
8      when z = 0 then 10
9      when z = 1 then 20
10      else 30
11   end';
12   dbms_output.put_line('All good');
13  exception
14    when column_not_exists_exception then
15     dbms_output.put_line('error column does not exists');
16  end;
17  /
error column does not exists
PL/SQL procedure successfully completed.