在SQL Server 数据库中,我们在创建表之前删除表，有if exit()这样的语句，但是在oracle中却没有。如果直接使用drop table那么如果表不存在会报错，导致后续语句无法运行。因此可以通过一个存储过来来进行判断。如果表存在，则执行execute PROC_CREATE_INFO 即可

代码如下：

1 CREATE OR REPLACE PROCEDURE PROC_CREATE_INFO(P_TABLE_NAME IN USER_TABLES.TABLE_NAME%TYPE) IS 2     V_SQL   VARCHAR2(32767); 3     V_BEGIN NUMBER; 4     V_END   NUMBER; 5     V_TOTAL NUMBER;     6   TYPE TYPE_TABLE IS TABLE OF USER_TABLES.TABLE_NAME%TYPE; 7   TT TYPE_TABLE; 8   CURSOR CUR_TABLE_NAME IS 9     SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = P_TABLE_NAME;10 BEGIN11   SELECT DBMS_UTILITY.GET_TIME INTO V_BEGIN FROM DUAL;12   OPEN CUR_TABLE_NAME;13   LOOP14     FETCH CUR_TABLE_NAME BULK COLLECT15       INTO TT;16     IF P_TABLE_NAME IS NULL THEN17  V_SQL := 'CREATE TABLE ' || P_TABLE_NAME || '(' ||18                   'workdate       NVARCHAR2(10) not null,19                   attendancetype NVARCHAR2(40)  not null,20                   starttime      NVARCHAR2(5)   not null,21                   personid       NVARCHAR2(40)  not null,22                   endtime        NVARCHAR2(5),23                   resulttime     NUMBER(18,2),24                   departmentid   NVARCHAR2(40),25                   optime         NVARCHAR2(23),26                   lastoptime     DATE,27                   statustype     NVARCHAR2(23),28                   businessunitid NVARCHAR2(40) default "11125050-4860-4d84-b875-ed45db9c222"' || ')';29       EXECUTE IMMEDIATE V_SQL;30     END IF;31     EXIT WHEN CUR_TABLE_NAME%NOTFOUND;32   END LOOP;33   CLOSE CUR_TABLE_NAME;34   SELECT DBMS_UTILITY.GET_TIME INTO V_END FROM DUAL;35   SELECT TO_CHAR(V_END - V_BEGIN) / 100 INTO V_TOTAL FROM DUAL;36 EXCEPTION37   WHEN NO_DATA_FOUND THEN38     NULL;39   WHEN OTHERS THEN40     DBMS_OUTPUT.PUT_LINE(SQLCODE || '---' || SQLERRM);41 END;

 

另外SQL Server 中语句如下：

1 IF ( EXISTS (SELECT *  FROM   sys.objects 2              WHERE  UPPER(name) = UPPER('TABLE_NAME')) ) 3   DROP table TABLE_NAME 4 GO 5 CREATE TABLE TABLE_NAME( 6     [no] [int] NOT NULL, 7     [COLUMNNAME] [nvarchar](50) NULL, 8     [MONTH] [nvarchar](40) NULL 9 ) ON [PRIMARY]10 GO