首页 文章

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

提问于
浏览 1410
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

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;
    /
    

相关问题