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;