• 判断某个表是否存在
select  * from DBA_TABLES where TABLE_NAME='TBL_STKEX_NONMOTOR';
  • 判断某个表的某个字段是否存在
SELECT * FROM DBA_TAB_COLUMNS where TABLE_NAME='TBL_STKEX_NONMOTOR' and COLUMN_NAME = 'RENEW_TIME';
  • 判断某个表的某个约束是否存在
  • 主键
SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='P' AND OWNER='TEST'  AND TABLE_NAME='TA' and CONSTRAINT_NAME='PK_TA';
  • 唯一键
SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='U' AND OWNER='TEST'  AND TABLE_NAME='TA' and CONSTRAINT_NAME='UK_TA_NAME';
  • 外键
SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND OWNER='TEST'  AND TABLE_NAME='TA' and CONSTRAINT_NAME='FK_TB_NAME';

– check 约束

  • 如果直接在表定义中设置check约束, 此时check约束名称不是 见名知意
SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='C' AND OWNER='TEST'  AND TABLE_NAME='TA' ;
  • 如果check约束是通过 atler table add constraint 来添加, 此时可以通过 CONSTRAINT_NAME 知道 check约束名称
SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='C' AND OWNER='TEST'  AND TABLE_NAME='TA' and constraint_name='CHECK_TA_AGE' ;
  • 判断某个表的索引是否存在
select * from DBA_INDEXES WHERE OWNER='TEST' AND TABLE_NAME='TA'  
表默认有个cluster索引, 非唯一
对于主键/唯一键约束,对应会生成一个索引, 该索引名称无法看出是主键还是唯一键, 可以通过 DBA_CONSTRAINTS 来查看
对于普通索引,由于在定义的时候需要指定索引名称, 所以在 dba_indexes中是可以看到该索引具体名称的
  • 判断某个函数是否存在
SELECT * FROM DBA_PROCEDURES WHERE OWNER='TEST' AND OBJECT_NAME='FUNC_TEST'

*判断某个触发器是否存在

select * from DBA_TRIGGERS where  OWNER='TEST' AND TRIGGER_NAME='TRI_STKDD';
  • 判断某个视图是否存在
select * from DBA_VIEWS where  OWNER='TEST' AND  VIEW_NAME='T_STK' ;
  • 判断某个类型是否存在
select * from DBA_SOURCE where name='TYPE_INT';
TYPE定义如下:
  CLASS:自定义类型 通过 create type xx as object();来定义
  TRIG:触发器
  PROC: 函数
  PACKAGE: 包
  PACKAGE BODY: 包提
  TYPE: 通过 create type xx as ; 来定义
  • 判断某个序列是否存在
select * FROM dba_sequences where  OWNER='TEST' AND  sequence_name='SEQ_Q';
  • 动态 SQL 实例
    –测试用例
create table test(id int);
insert into test values(1);
insert into test values(2);

create or replace function func_test()
RETURN INTEGER as
DECLARE
    row_num int:=0;
BEGIN
    execute IMMEDIATE 'select count(*)  from test' into row_num;
    return row_num;
END;

/

SQL> select func_test();

行号     FUNC_TEST()
---------- -----------
1          2