DECLARE ROWS INTEGER;
BEGIN
--删除表
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_ALL_TABLES WHERE UPPER(TABLE_NAME)=UPPER(''BM_RATIODATA_TEMP'')' INTO ROWS;
IF ROWS>0 THEN
EXECUTE IMMEDIATE 'DROP TABLE BM_RATIODATA_TEMP';
END IF;
--增加列
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=''SJDWZT'' AND COLUMN_NAME =''ISVIRTUAL''' INTO ROWS;
IF ROWS<1 THEN
EXECUTE IMMEDIATE 'ALTER TABLE SJDWZT ADD ISVIRTUAL INT NULL';
END IF;
--修改表的某一列
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME=''SJFAGROUP'' AND COLUMN_NAME =''LEIBIE''' INTO ROWS;
IF ROWS<1 THEN
EXECUTE IMMEDIATE 'ALTER TABLE SJFAGROUP ALTER COLUMN LEIBIE VARCHAR(30)';
END IF;
--删除外键
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_CONSTRAINTS WHERE TABLE_NAME=''TEST2'' AND CONSTRAINT_NAME=''FOR_TEST''' INTO ROWS;
IF ROWS>0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE TEST2 DROP CONSTRAINT FOR_TEST';
END IF;
--检查表名:
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 表名(大写);
--检查字段名:
SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME= 'BM_ARCHITECT' AND COLUMN_NAME= 'BENCHMARKID'
SELECT * FROM USER_CONS_COLUMNS; --所有列
--查找主键
SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE='P' AND CONSTRAINT_NAME = 'PK_BM_DATAGROUP' AND TABLE_NAME='TEST'
--查找索引
SELECT * FROM DBA_INDEXES WHERE INDEX_NAME = 'IDX_DATAGROUP'
--创建索引
--CREATE INDEX IDX_SJOBJECTCOLLECTION_OBJECTTYPE ON SJOBJECTCOLLECTION(OBJECTTYPE)
--级连删除表
--DROP TABLE BM_RATIO_TYPE CASCADE CONSTRAINTS;
END;