达梦数据库查看系统表信息

• 判断某个表是否存在

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