DECLARE
V_FLAG NUMBER;--数量标志
BEGIN
--建表
SELECT COUNT(*) INTO V_FLAG FROM USER_TABLES UT WHERE UT.TABLE_NAME = 'TABLE_NAME_DEMO';
IF V_FLAG = 0 THEN
EXECUTE IMMEDIATE '
CREATE TABLE TABLE_NAME_DEMO(
ID VARCHAR2(30),
NAME VARCHAR2(50),
CONSTRAINT PK_TABLE_NAME_DEMO PRIMARY KEY (ID)
)
';
EXECUTE IMMEDIATE 'comment on table TABLE_NAME_DEMO is ''可重复执行sql''';
EXECUTE IMMEDIATE 'comment on column TABLE_NAME_DEMO.id is ''ID列''';
EXECUTE IMMEDIATE 'comment on column TABLE_NAME_DEMO.name is ''名称列''';
END IF;
--加字段
SELECT COUNT(*) INTO V_FLAG FROM USER_TAB_COLUMNS UC WHERE UC.TABLE_NAME = 'TABLE_NAME_DEMO' AND UC.COLUMN_NAME = 'AGE';
IF V_FLAG = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE TABLE_NAME_DEMO ADD AGE VARCHAR2(20)';
EXECUTE IMMEDIATE 'comment on column TABLE_NAME_DEMO.age is ''年龄20''';
END IF;
--改字段
SELECT COUNT(*) INTO V_FLAG FROM USER_TAB_COLUMNS UC WHERE UC.TABLE_NAME = 'TABLE_NAME_DEMO' AND UC.COLUMN_NAME = 'AGE';
IF V_FLAG = 1 THEN
EXECUTE IMMEDIATE 'alter table TABLE_NAME_DEMO modify age varchar2(50)';
EXECUTE IMMEDIATE 'comment on column TABLE_NAME_DEMO.AGE is ''年龄50''';
END IF;
--删除字段
SELECT COUNT(*) INTO V_FLAG FROM USER_TAB_COLUMNS UC WHERE UC.TABLE_NAME = 'TABLE_NAME_DEMO' AND UC.COLUMN_NAME = 'SCORE';
IF V_FLAG = 1 THEN
EXECUTE IMMEDIATE 'alter table TABLE_NAME_DEMO drop column SCORE';
END IF;
--插入数据(根据主键)
SELECT COUNT(*) INTO V_FLAG FROM TABLE_NAME_DEMO TB WHERE TB.ID = '123';
IF V_FLAG = 0 THEN
INSERT INTO TABLE_NAME_DEMO(ID,NAME) VALUES('123','123的名称');
END IF;
END;
/
适用于升级型增量脚本。