oracle字段重复新增错误,Oracle 判断表或字段是否存在新增/修改表结构可重复执行sql...

DECLARE

num NUMBER;

BEGIN

-- 新增学生表 student

SELECT

COUNT (1) INTO num

FROM

cols

WHERE

table_name = UPPER ('student') ;

IF num > 0 THEN

EXECUTE IMMEDIATE 'DROP TABLE student' ;

EXECUTE IMMEDIATE 'CREATE TABLE student (

id NUMBER NOT NULL,

name VARCHAR2(40) NULL ,

xb VARCHAR2(40) NULL ,

age int(3) NULL ,

birthday DATE NULL ,

className VARCHAR2(80) NULL

)' ;

EXECUTE IMMEDIATE 'ALTER TABLE student ADD PRIMARY KEY (id)' ;

ELSE

EXECUTE IMMEDIATE 'CREATE TABLE student (

id NUMBER NOT NULL,

name VARCHAR2(40) NULL ,

xb VARCHAR2(40) NULL ,

age int(3) NULL ,

birthday DATE NULL ,

className VARCHAR2(80) NULL

)' ;

EXECUTE IMMEDIATE 'ALTER TABLE student ADD CHECK (ID IS NOT NULL)' ;

EXECUTE IMMEDIATE 'ALTER TABLE student ADD PRIMARY KEY (ID)' ;

END IF ;

-- 学生表新增班级 className 字段

SELECT COUNT(1)

INTO num

from cols

where table_name = upper('student')

and column_name = upper('className');

IF num > 0 THEN

execute immediate 'alter table student MODIFY (className varchar2(20))';

ELSE

execute immediate 'alter table student add className varchar2(40)';

END IF;

END;