oracle数据结构修改,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;