ORACLE修改数据结构语句

最新推荐文章于 2025-06-18 21:05:10 发布

原创最新推荐文章于 2025-06-18 21:05:10 发布 · 1.6k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据结构

ORACLE 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了数据库中序列、索引、字段增删改查、表创建、外键添加、字段类型转换、索引与外键删除等核心操作，以及通过实例展示了如何调整数据库表结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;