oracle可重复执行脚本

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;
/

适用于升级型增量脚本。