Oracl基本操作SQL

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;