ORACLE修改数据结构语句

1. 添加序列
DECLARE V1 NUMBER;
BEGIN 
SELECT COUNT(1) INTO V1 FROM USER_SEQUENCES WHERE SEQUENCE_NAME='UN_TB_TJ_SICKNES';
IF V1 = 0
THEN 
EXECUTE IMMEDIATE
'CREATE SEQUENCE UN_TB_TJ_SICKNES
MINVALUE 0
MAXVALUE 9999999999999999999999999
START WITH 1
INCREMENT BY 1
NOCACHE';
END IF;
END;

2. 添加索引
DECLARE NUM NUMBER; 
BEGIN SELECT COUNT(1) INTO NUM FROM USER_INDEXES 
WHERE INDEX_NAME='INX_TD_TJ_BHKIMPORT_UPNUM'; 
IF NUM=0 THEN EXECUTE IMMEDIATE 
'CREATE INDEX INX_TD_TJ_BHKIMPORT_UPNUM ON TD_TJ_BHKIMPORT (UPDATE_NUM)'; 
END IF;
END;

3. 删除字段
DECLARE
  NUM INT;
BEGIN
  SELECT COUNT(1)
    INTO NUM
    FROM COLS
   WHERE TABLE_NAME = UPPER('AAA')
     AND COLUMN_NAME = UPPER('FFF');
  IF NUM = 1 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE AAA DROP COLUMN FFF';
  END IF;
END;

4. 添加字段
DECLARE
  NUM INT;
BEGIN
  SELECT COUNT(1)
    INTO NUM
    FROM COLS
   WHERE TABLE_NAME = UPPER('TB_TJ_BHKSCH')
     AND COLUMN_NAME = UPPER('PYXNAM');
  IF NUM = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE TB_TJ_BHKSCH ADD PYXNAM VARCHAR2(50)';
  END IF;
END;

5. 修改字段
DECLARE NUM NUMBER; 
BEGIN 
SELECT COUNT(1) INTO NUM FROM COLS WHERE  TABLE_NAME = UPPER('TD_TJ_BHKIMPORT') 
       AND  COLUMN_NAME=UPPER('ORGCOD') AND NULLABLE='N';
IF NUM=1 
THEN 
EXECUTE IMMEDIATE 
'ALTER TABLE TD_TJ_BHKIMPORT MODIFY ORGCOD VARCHAR2(50) NULL';
END IF;
END;

6. 添加新表
DECLARE NUM NUMBER; 
BEGIN SELECT COUNT(1) INTO NUM FROM USER_TABLES WHERE TABLE_NAME='TD_TJ_TJSORT'; 
IF NUM=0 THEN EXECUTE IMMEDIATE 
'
CREATE TABLE TD_TJ_TJSORT  (
   RID                  INTEGER                         NOT NULL,
   MTBIDX               VARCHAR2(36),
   TJSORTID             INTEGER,
   TJSORTNAM            VARCHAR2(100),
   CONSTRAINT PK_TD_TJ_TJSORT PRIMARY KEY (RID),
   CONSTRAINT FK_TD_TJ_TJSORT1 FOREIGN KEY (MTBIDX) REFERENCES TD_ZWTJ_BHK(RID),
   CONSTRAINT FK_TD_TJ_TJSORT2 FOREIGN KEY (TJSORTID) REFERENCES TB_TJ_TJSORT(RID)    
)
'; 
END IF; 
END;

7. 添加外键
DECLARE NUM NUMBER; 
BEGIN 
SELECT COUNT(1) INTO NUM FROM USER_CONSTRAINTS WHERE 
CONSTRAINT_NAME='FK_TS_RPT_REF_XT_UNIT' AND TABLE_NAME='TS_RPT' ;
IF NUM=0 
THEN 
EXECUTE IMMEDIATE 
'ALTER TABLE TS_RPT
   ADD CONSTRAINT FK_TS_RPT_REF_XT_UNIT FOREIGN KEY (UNIT_ID)
      REFERENCES XT_UNIT (RID)';
END IF;
END;

8. 更改INTEGER为NUMBER
ALTER TABLE TD_FILL_REC ADD FILL_MONEY2 NUMBER(8,2);
ALTER TABLE TD_FILL_REC MODIFY FILL_MONEY NULL;
UPDATE TD_FILL_REC SET FILL_MONEY2=FILL_MONEY,FILL_MONEY=NULL;
COMMIT;
ALTER TABLE TD_FILL_REC MODIFY FILL_MONEY NUMBER(8,2);
UPDATE TD_FILL_REC SET FILL_MONEY=FILL_MONEY2,FILL_MONEY2=NULL;
COMMIT;
ALTER TABLE TD_FILL_REC DROP COLUMN FILL_MONEY2;
ALTER TABLE TD_FILL_REC MODIFY FILL_MONEY NOT NULL;

9. 删除索引
DECLARE
  NUM NUMBER;
BEGIN
  SELECT COUNT(1)
    INTO NUM
    FROM USER_INDEXES
   WHERE INDEX_NAME = 'INX_TD_NEWCOLLECT_DATA1';
  IF NUM = 1 THEN
    EXECUTE IMMEDIATE 'DROP INDEX TD_NEWCOLLECT_DATA_TIME_SJ';
  END IF;
END;

10. 删除AK
DECLARE
  NUM NUMBER;
BEGIN
  SELECT COUNT(1)
    INTO NUM
    FROM USER_CONSTRAINTS
   WHERE CONSTRAINT_NAME = 'AK_MENU_TEMPLATE'
     AND TABLE_NAME = 'MENU_TEMPLATE';
  IF NUM = 1 THEN
    EXECUTE IMMEDIATE 
    'ALTER TABLE MENU_TEMPLATE DROP CONSTRAINT AK_MENU_TEMPLATE CASCADE DROP INDEX';
  END IF;
END;

11. 添加AK
DECLARE
  NUM NUMBER;
BEGIN
  SELECT COUNT(1)
    INTO NUM
    FROM USER_CONSTRAINTS
   WHERE CONSTRAINT_NAME = 'AK_TB_NEWEQU_PUR_TIME'
     AND TABLE_NAME = 'TB_NEWEQU_PUR_TIME';
  IF NUM = 0 THEN
    EXECUTE IMMEDIATE 'ALTER TABLE TB_NEWEQU_PUR_TIME ADD CONSTRAINT AK_TB_NEWEQU_PUR_TIME UNIQUE (EQUTYPE_ID)';
  END IF;
END;