对表结构的修改

一、新增一列：
ORACLE版：
DECLARE num NUMBER;
BEGIN
    num :=0;
select count(column_name) INTO num  from cols where upper(table_name)=upper('LMS_COURSE') and upper(column_name)=upper('STUDY_HOURS');
IF num < 1 THEN
EXECUTE IMMEDIATE  'alter table LMS_COURSE add  STUDY_HOURS Decimal(18,2) DEFAULT 0 NOT NULL';
END IF;
END;
/
SQL版：
if not exists (select * from dbo.syscolumns where id = object_id(N'LMS_COURSE') and name='STUDY_HOURS')
alter table LMS_COURSE add STUDY_HOURS Decimal(18,2) DEFAULT 0 NOT NULL 
GO

二、删除一列：
Oracle：
DECLARE num NUMBER;
BEGIN

    num :=0;
select count(column_name) INTO num  from cols where upper(table_name)=upper('LMS_COURSE') and upper(column_name)=upper('STUDY_HOURS');
IF num = 1 THEN
EXECUTE IMMEDIATE  'alter table LMS_COURSE DROP COLUMN STUDY_HOURS;';
END IF;
END;
/
SQL：
if exists (select * from dbo.syscolumns where id = object_id(N'LMS_COURSE') and name='STUDY_HOURS')
alter table LMS_COURSE DROP COLUMN STUDY_HOURS ;
GO
三、修改表字段长度
oracle
ALTER TABLE pefpackjudgegrade MODIFY ( reason NVARCHAR2(800));
sql server

ALTER TABLE PEFPACKJUDGEGRADE ALTER COLUMN REASON NVARCHAR(800)

四、修改字段的名字，先插入新的字段，将字段的值重新赋值，在删除原来的字段
五、修改字段的默认值
Oracle

alter table lms_course modify course_train_type default 4101