达梦(禁用|启动)constraint

本文主要是介绍达梦(禁用|启动)constraint，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

---

### Code Reference

• URL:p133 DM8系统管理员手册
• DESC:达梦(禁用|启动)constraint(和oracle的语法有些差异)
• Last Update:2020-7-2 15:54
  • 四种状态
    • disable novalidate 既不会约束新增数据也不会验证已有数据，等同于disable
    • enable novalidate 约束新增数据但不会验证已有数据
    • disable validate 约束新增数据但不会验证已有数据，启用后禁止DML
    • enable validate 约束新增数据并验证已有数据，等同于enable
  • 设置 constraint 状态

      select * from SYS.DBA_CONSTRAINTS T where T.TABLE_NAME = 'WhichCanEncrypt';alter table test."WhichCanEncrypt" enable|disable constraint "CK_6_1";
    

---

### Code Reference

• DESC:dameng 修改check constraint 状态过程
• Last Update:2020-7-2 16:56
  • 接口(pi_mode 为0是置check constraint 为disable,其他为置为enable)
    
    call “TEST”.“disposeCheckConstraint”(‘TEST’,‘WhichCanEncrypt’,1);
  • 授权
    
    grant execute on test.“disposeCheckConstraint” to PUBLIC;
    
    grant select on SYS.DBA_CONSTRAINTS to public;
  • 实现

      create or replace procedure test."disposeCheckConstraint"("pv_schemaName" varchar2(100),"pv_tableName"  varchar2(100),"pi_mode"       number(1)) asdeclare  type L_cursortyp is ref cursor;L_cursor       L_cursortyp;Lv_sql         varchar2(200);Lv_disposeMode varchar2(200):=0;beginif "pi_mode" = 0 thenLv_disposeMode := '" DISABLE CONSTRAINT "';elseLv_disposeMode := '" ENABLE CONSTRAINT "';end if;open L_cursor forselect 'ALTER TABLE "' || "OWNER" || '"."' || "TABLE_NAME" ||Lv_disposeMode || "CONSTRAINT_NAME" || '"'from SYS.DBA_CONSTRAINTS Twhere 1 = 1and T.OWNER = "pv_schemaName"and T.TABLE_NAME = "pv_tableName"and T.CONSTRAINT_TYPE = 'C';loopfetch L_cursorinto Lv_sql;exit when L_cursor%notfound;beginexecute IMMEDIATE Lv_sql;print Lv_sql;exceptionwhen others thennull;end;end loop;close L_cursor;end;
    

这篇关于达梦(禁用|启动)constraint的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---