我想使用可能不存在的列更新表。我正在這樣做但出現錯誤，我試圖處理但仍然出現例外。有沒有辦法解決這個問題？僅當列存在時才更新表？我是 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
uj5u.com熱心網友回復：
這當然是一個奇怪的要求，但由于 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.
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/510542.html
標籤：sql甲骨文plsql
上一篇：需要幫助查找資料質量檢查的重復值