gdufo

           

          ad 刪除一個(gè)實(shí)體的SQL語(yǔ)句

          DECLARE
              /**
               * Please change this one to any client id you want to delete
               **/
              v_Client_ID                                                 NUMBER      := 1000014;
              
              v_SQL1                               VARCHAR2(1024);
              
              CURSOR Cur_Contraints  IS
                          select  table_name,constraint_name
                         from user_constraints  
                          where  status='ENABLED'  AND constraint_type='R' ;
                  
               CURSOR Cur_Contraints2  IS
                          select table_name,constraint_name
                          from user_constraints
                          where status='DISABLED' AND constraint_type='R';
                          
                  CURSOR Cur_Triggers  IS
                          select TRIGGER_NAME
                          from user_triggers
                          where status='ENABLED';
                                  
             CURSOR Cur_RemoveData  IS
                      select 'delete from '|| TABLENAME ||' where AD_Client_ID=' || v_Client_ID
                      AS v_SQL
                          from AD_Table a where a.ISVIEW='N'
                          AND exists ( select AD_Column_ID from AD_Column c where

          a.AD_Table_ID=c.AD_Table_ID
                          and upper(c.COLUMNNAME)= upper('AD_Client_ID') );
                      
                          
          BEGIN
              
              DBMS_OUTPUT.PUT_LINE('  Delete Client Where AD_Client_ID=' || v_Client_ID);
              
              /****************************************************************
               *  Disable all the constraints one by one
               ****************************************************************/
               DBMS_OUTPUT.PUT_LINE(' Disable the contraints ');
               FOR p IN Cur_Contraints  LOOP
                 BEGIN
                 v_SQL1 := 'alter table '|| p.table_name ||' disable constraint '|| p.constraint_name;
                  EXECUTE IMMEDIATE v_SQL1;        
                 END;  
               END LOOP;        --        Disable contraints
               
               
               DBMS_OUTPUT.PUT_LINE(' Disable the triggers ');
               FOR p IN Cur_Triggers  LOOP
                 v_SQL1 := 'alter trigger '|| p.TRIGGER_NAME ||' disable ';
                  EXECUTE IMMEDIATE v_SQL1;
               END LOOP;        --        Disable contraints
               
               /****************************************************************
               *  Remove all the records belongs to that client
               ****************************************************************/
              FOR p IN Cur_RemoveData LOOP
                  v_SQL1 := p.v_SQL;
                  EXECUTE IMMEDIATE v_SQL1;
                  
               END LOOP;        --        Remove data
               
               
               /****************************************************************
               *  Disable all the constraints one by one
               ****************************************************************/
               DBMS_OUTPUT.PUT_LINE(' Enable the contraints ');
              FOR p IN Cur_Contraints2  LOOP
                  BEGIN
                  v_SQL1 := 'alter table '|| p.table_name ||' enable constraint '|| p.constraint_name;
                  EXECUTE IMMEDIATE v_SQL1;        
                  END;
               END LOOP;        --        Enable contraints
               
               DBMS_OUTPUT.PUT_LINE(' Enable the triggers ');
               FOR p IN Cur_Triggers  LOOP
                 v_SQL1 := 'alter trigger '|| p.TRIGGER_NAME ||' enabled ';
                  EXECUTE IMMEDIATE v_SQL1;
               END LOOP;        --        Enable contraints
               
               COMMIT;


          END;

          posted on 2012-05-26 16:57 gdufo 閱讀(575) 評(píng)論(0)  編輯  收藏 所屬分類: idempiere Adempiere empiere

          導(dǎo)航

          統(tǒng)計(jì)

          常用鏈接

          留言簿(6)

          隨筆分類

          隨筆檔案

          文章分類

          文章檔案

          收藏夾

          Hibernate

          友情鏈接

          搜索

          最新評(píng)論

          閱讀排行榜

          評(píng)論排行榜

          主站蜘蛛池模板: 温泉县| 林甸县| 益阳市| 富蕴县| 乡宁县| 保德县| 营山县| 嫩江县| 犍为县| 林芝县| 尚志市| 阿城市| 襄樊市| 肥城市| 麻城市| 福安市| 莱州市| 张家港市| 繁峙县| 凤山市| 华蓥市| 连平县| 奉贤区| 三江| 通许县| 林甸县| 刚察县| 资溪县| 云林县| 来安县| 子长县| 乌拉特中旗| 安顺市| 博野县| 正安县| 习水县| 高台县| 肃宁县| 车致| 高平市| 靖江市|