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;