首页 文章

用于检查表是否存在的简单PL / SQL无效

提问于
浏览 次
1

我正在将一些存储过程从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;

我收到以下错误消息，我做错了什么？！

从命令行中的第17行开始出错 - 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那么执行立即'DROP TABLE tbl_BSUK_PriceIssue'; t：= 0; SELECT COUNT（）INTO t FROM user_tables WHERE table_name = UPPER（'tbl_BSUK_PriceIssue'）;如果t = 1则升高t_error;万一;万一;例外t_error那么v_line：='<<< FAILED DROPPING表tbl_BSUK_PriceIssue >>>'; dbms_output.put_line（v_line）;当其他的时候v_line：='<<< << Unknown error >>>'; dbms_output.put_line（v_line）;结束;结束;错误报告 - ORA-06550：第30行，第1列：PLS-00103：遇到符号“END”06550 . 00000 - “行％s，列％s：\ n％s”*原因：通常是PL / SQL编译错误 . *行动：

我实际上试图用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
sql oracle plsql

2 回答

  • 8

    如果您使用 EXISTS() 并且您不需要 EXECUTE IMMEDIATE ，那么我不需要 EXECUTE IMMEDIATE 因为您在命令中没有可变数据：

    IF 1 = (SELECT 1 FROM user_tables WHERE table_name = 'TBL_BSUK_PRICEISSUE') THEN
  DROP TABLE tbl_BSUK_PriceIssue;
  IF 1 = (SELECT 1 FROM user_tables WHERE table_name = 'TBL_BSUK_PRICEISSUE') THEN
    RAISE EXCEPTION;
  END IF;
END IF;
    回复于
  • 0

    尝试删除本节中的 END;

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

    UPD . 实际上，你可以做得更短，不需要在删除后检查表是否存在

    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;
/
    回复于

相关问题