达梦数据库常用SQL之约束的启用和禁用，索引的失效和重建，触发器的禁用和启用

--禁用指定模式下的约束
create or replace procedure test_varchar_insert
        ( var1 varchar(100))
        as
        c_count  int := 0;
        con_name varchar(100);
        tab_name varchar(100);
        sql1     varchar(500);
        sql2     varchar(500);
        c1 cursor;
        begin
                sql1 = 'SELECT TABLE_NAME, CONSTRAINT_NAME FROM  DBA_CONSTRAINTS WHERE OWNER = '''||var1||''';';
                open c1 for sql1;
                        LOOP
                                fetch c1 into tab_name, con_name;
                        EXIT
                WHEN c1%NOTFOUND;
                        sql2 = 'ALTER TABLE ' || var1 || '.' ||tab_name || ' DISABLE CONSTRAINT "' || con_name || '";' ;
                        execute immediate sql2;
                end loop;
                close c1;
        end
 
call test_varchar_insert('DMHR')--其中DMHR为模式名

--1.禁用约束，索引失效
declare
		var1     varchar(20) := 'SCHEMA_NAME'; --此处为具体的模式名
        con_name varchar(100);
        tab_name varchar(100);
        idx_name varchar(100);
        sql1     varchar(500);
        sql2     varchar(500);
        sql3     varchar(500);
        sql4     varchar(500);
        c1 cursor;
        c2 cursor;
        begin
               --禁用所有约束
                sql1 = 'SELECT TABLE_NAME, CONSTRAINT_NAME FROM  DBA_CONSTRAINTS WHERE OWNER = '''||var1||''';';
                open c1 for sql1;
                        LOOP
                                fetch c1 into tab_name, con_name;
                        EXIT
                WHEN c1%NOTFOUND;
                        sql2 = 'ALTER TABLE ' || var1 || '.' ||tab_name || ' DISABLE CONSTRAINT "' || con_name || '";' ;
                        execute immediate sql2;
                end loop;
                close c1;
              --失效所有索引
               sql3 = 'SELECT INDEX_NAME FROM DBA_INDEXES WHERE INDEX_TYPE=''NORMAL'' AND OWNER = '''||var1||''';';
               open c2 for sql3;
               		   LOOP
               		   		     fetch c2 into idx_name;
                       EXIT
               WHEN c2%NOTFOUND;
                        sql4 = 'ALTER INDEX ' || var1 || '.' ||idx_name || ' UNUSABLE' ;
                        execute immediate sql4;
               end loop;
               close c2;
               		   
        end
 
--2.启用约束，重建索引
declare
		var1     varchar(20) := 'SCHEMA_NAME';
        con_name varchar(100);
        tab_name varchar(100);
        idx_name varchar(100);
        sql1     varchar(500);
        sql2     varchar(500);
        sql3     varchar(500);
        sql4     varchar(500);
        c1 cursor;
        c2 cursor;
        begin
               --启用所有约束
                sql1 = 'SELECT TABLE_NAME, CONSTRAINT_NAME FROM  DBA_CONSTRAINTS WHERE OWNER = '''||var1||''';';
                open c1 for sql1;
                        LOOP
                                fetch c1 into tab_name, con_name;
                        EXIT
                WHEN c1%NOTFOUND;
                        sql2 = 'ALTER TABLE ' || var1 || '.' ||tab_name || ' ENABLE CONSTRAINT "' || con_name || '";' ;
                        execute immediate sql2;
                end loop;
                close c1;
              --重建所有索引
               sql3 = 'SELECT INDEX_NAME FROM DBA_INDEXES WHERE INDEX_TYPE=''NORMAL'' AND OWNER = '''||var1||''';';
               open c2 for sql3;
               		   LOOP
               		   		     fetch c2 into idx_name;
                       EXIT
               WHEN c2%NOTFOUND;
                        sql4 = 'ALTER INDEX ' || var1 || '.' ||idx_name || ' REBUILD' ;
                        execute immediate sql4;
               end loop;
               close c2;
               		   
        end

--批量生成启用约束禁用约束的SQL语句
 
--先禁用外键约束
select 'alter table '||OWNER||'.'||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';',
CONSTRAINT_TYPE,DBA_CONSTRAINTS.STATUS 
from DBA_CONSTRAINTS where owner='DMHR' AND CONSTRAINT_TYPE='R';
--再禁用其他约束
select 'alter table '||OWNER||'.'||TABLE_NAME||' DISABLE CONSTRAINT '||CONSTRAINT_NAME||';',
CONSTRAINT_TYPE,DBA_CONSTRAINTS.STATUS 
from DBA_CONSTRAINTS where owner='DMHR' AND CONSTRAINT_TYPE IN ('C','P','U');
--先启用其他约束
select 'alter table '||OWNER||'.'||TABLE_NAME||' ENABLE CONSTRAINT '||CONSTRAINT_NAME||';',
CONSTRAINT_TYPE,DBA_CONSTRAINTS.STATUS 
from DBA_CONSTRAINTS where owner='DMHR' AND CONSTRAINT_TYPE IN ('C','P','U');
--再启用外键约束
select 'alter table '||OWNER||'.'||TABLE_NAME||' ENABLE CONSTRAINT '||CONSTRAINT_NAME||';',
CONSTRAINT_TYPE,DBA_CONSTRAINTS.STATUS 
from DBA_CONSTRAINTS where owner='DMHR' AND CONSTRAINT_TYPE='R';

--查詢所有索引
SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE INDEX_TYPE='NORMAL' AND OWNER = 'DMHR' AND TABLE_NAME NOT LIKE '%_P_%';
 
--生成禁用normal索引的语句
SELECT 'ALTER INDEX '|| OWNER||'.'||INDEX_NAME ||' UNUSABLE;' FROM DBA_INDEXES WHERE INDEX_TYPE='NORMAL' AND OWNER = 'DMHR' AND TABLE_NAME NOT LIKE '%_P_%';
 
--查询所有约束
SELECT TABLE_NAME,CONSTRAINT_NAME,STATUS FROM DBA_CONSTRAINTS WHERE OWNER = 'DMHR' AND TABLE_NAME NOT LIKE '%_P_%';
 
--TBL_MSV_SEND_SCHEME_DETAIL的外键FK_SENDDETAIL_REF_SEND默认为DISABLED状态

--批量生成禁用触发器的语句
SELECT 'ALTER TRIGGER "'||TABLE_OWNER||'"."'||TRIGGER_NAME ||'" DISABLE;' FROM USER_TRIGGERS WHERE STATUS='Y';
--批量生成启用触发器的语句
SELECT 'ALTER TRIGGER "'||TABLE_OWNER||'"."'||TRIGGER_NAME ||'" ENABLE;' FROM USER_TRIGGERS WHERE STATUS='N';
--测试语句
--创建学生表  STUDENT
CREATE TABLE STUDENT (ID INT,NAME VARCHAR(10),PHONE VARCHAR(11),CREATE_TIME DATETIME DEFAULT SYSDATE);
--创建用户表 USERS
CREATE TABLE USERS (ID INT,NAME VARCHAR(10),CREATE_TIME DATETIME DEFAULT SYSDATE);
--插入一条数据
INSERT INTO STUDENT VALUES  (1,'TEST1','1234567',SYSDATE);
INSERT INTO STUDENT VALUES (2,'TEST2','1234567',SYSDATE);
INSERT INTO STUDENT VALUES (3,'TEST3','1234567',SYSDATE);
INSERT INTO STUDENT VALUES (4,'TEST4','1234567',SYSDATE);
INSERT INTO STUDENT VALUES (5,'TEST5','1234567',SYSDATE);
COMMIT;
--创建 BEFORE 触发器
CREATE OR REPLACE TRIGGER TRG_INS_STU_BEFORE
    BEFORE INSERT ON STUDENT 
    FOR EACH ROW 
    BEGIN 
        :NEW.ID:=:NEW.ID+1;
END;
--再次插入一条数据
INSERT INTO STUDENT VALUES (18,'TEST2','12312323',SYSDATE);
COMMIT;
--创建视图
CREATE VIEW V_STUDENT AS SELECT * FROM STUDENT;
--在视图V_STUDENT 上创建 INSTEAD OF 触发器。
CREATE OR REPLACE TRIGGER INS_OF_STUDENT
INSTEAD OF UPDATE ON V_STUDENT 
BEGIN
    INSERT INTO STUDENT VALUES (21,'TEST21','1234567',SYSDATE); --替换动作
END;
--执行UPDATE更新语句
UPDATE V_STUDENT SET ID = 5 WHERE ID=1;
COMMIT;
SELECT 'ALTER TRIGGER "'||TABLE_OWNER||'"."'||TRIGGER_NAME ||'" DISABLE;' FROM USER_TRIGGERS WHERE STATUS='Y';
SELECT 'ALTER TRIGGER "'||TABLE_OWNER||'"."'||TRIGGER_NAME ||'" ENABLE;' FROM USER_TRIGGERS WHERE STATUS='N';