检查表是否存在的简单PL/SQL不起作用

Question

我正在将一些存储过程从Sybase TSQL转换为Oracle PL/SQL，我已经遇到了一个我正在努力解决的问题！

DECLARE

t INT := 0;
t_error EXCEPTION;
v_line VARCHAR2(100);

BEGIN

SELECT COUNT(*) INTO t FROM user_tables WHERE table_name = UPPER('tbl_BSUK_PriceIssue');

IF t = 1 THEN
  EXECUTE IMMEDIATE 'DROP TABLE tbl_BSUK_PriceIssue';
  t := 0;
  SELECT COUNT(*) INTO t FROM user_tables WHERE table_name = UPPER('tbl_BSUK_PriceIssue');
  IF t = 1 THEN
    RAISE t_error;
  END IF;
END IF;

EXCEPTION
  WHEN t_error THEN
  v_line := '<<< FAILED DROPPING table tbl_BSUK_PriceIssue >>>';
  dbms_output.put_line (v_line);
  WHEN OTHERS THEN
    v_line := '<<< Unknown Error >>>';
    dbms_output.put_line (v_line);
  END;


END;

命令-DECLARE中从第17行开始出错

t INT:=0；t_error异常；v_line VARCHAR2(100)；

开始

实际上，我正在尝试用PL/SQL版本替换以下TSQL：

-- Create temp table for relevant trev_id's 
IF OBJECT_ID('dbo.tbl_BSUK_PriceIssue') IS NOT NULL
BEGIN
    DROP TABLE dbo.tbl_BSUK_PriceIssue
    IF OBJECT_ID('dbo.tbl_BSUK_PriceIssue') IS NOT NULL
        PRINT '<<< FAILED DROPPING TABLE dbo.tbl_BSUK_PriceIssue >>>'
    ELSE
        PRINT '<<< DROPPED TABLE dbo.tbl_BSUK_PriceIssue >>>'
END
go

Answer 1

尝试删除本节中的end；

 WHEN OTHERS THEN
    v_line := '<<< Unknown Error >>>';
    dbms_output.put_line (v_line);
 END;

UPD.实际上，您可以做得更短一点，不需要检查drop后表是否存在

declare
  eTableNotExists exception;
  pragma exception_init(eTableNotExists, -00942);
begin
    EXECUTE IMMEDIATE 'DROP TABLE tbl_BSUK_PriceIssue';  
    dbms_output.put_line('<<< DROPPED TABLE dbo.tbl_BSUK_PriceIssue >>>');
exception 
  when eTableNotExists then null
  when others then    
    dbms_output.put_line ('<<< Unknown Error >>>' || sqlerrm);
end;
/