2020-11-03

Oracle常用SQL

随时更新

–获取建表语句
SELECT DBMS_METADATA.GET_DDL(‘TABLE’,‘大写TABLENAME’) FROM DUAL;

–获取字段注释
SELECT ’ COMMENT ON TABLE ‘||TABLE_NAME||’.’||COLUMN_NAME||’ IS ‘’’||COMMENTS||’’’;’ FROM USER_COL_COMMENTS WHERE TABLE_NAME =‘大写TABLENAME’;

–获取表注释
SELECT ‘COMMENT ON TABLE ‘||TABLE_NAME||’ IS ‘’’||COMMEMTS||’’’;’ FROM USER_TAB_COMMENTS WHERE TABLE_NAME = ‘大写TABLENAME’;

–同时使用MAX MIN函数时，优化SQL使其走INDEX FULL SCAN
SELECT MAX,MIN FROM (SELECT MAX(INDEX_FIELD) FROM T) A,(SELECT MIN(INDEX_FIELD) FROM T) B;
注意，这里不能使用UNION，因为UNION前后两条语句执行时间点不一致，可能导致取到的数据有问题。

–获取表主键字段
SELECT * FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME =(SELECT CONSTRAINT_NAME FROM USER_CONTRAINTS WHERE TABLE_NAME=‘大写TABLENAME’ AND CONSTRAINT_TYPE =‘P’);

–获取索引字段
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME =‘大写TABLENAME’;

–调用存储过程
BEGIN
PROC_NAME(参数1,参数2);
COCOMMIT;
END;