Oracle 刪表前驗證表名是否存在並且刪除

標籤：

DECLARE num NUMBER;BEGINSELECT COUNT(1) INTO num FROM USER_TABLES WHERE TABLE_NAME = UPPER(‘tableName‘) ;IF num > 0 THEN  EXECUTE IMMEDIATE ‘DROP TABLE tableName‘ ;END IF;END;

　　

 

 

 

多表情況

declare num number; num1 number;num2 number;begin SELECT COUNT(1) INTO num FROM USER_TABLES WHERE TABLE_NAME = UPPER(‘BAK_XX1‘) ;SELECT COUNT(1) INTO num1 FROM USER_TABLES WHERE TABLE_NAME = UPPER(‘BAK_XX2‘) ;SELECT COUNT(1) INTO num2 FROM USER_TABLES WHERE TABLE_NAME = UPPER(‘BAK_XX3‘) ;if num=1 then execute immediate ‘drop table BAK_XX1‘; end if; if num1=1 then execute immediate ‘drop table BAK_XX2‘; end if; if num1=1 then execute immediate ‘drop table BAK_XX3‘;

　　

Oracle 刪表前驗證表名是否存在並且刪除