建立好的client如何删除？

lognbow

我建了几个实验性的client，可是竟然删不掉，谁可教我？

pshen

pshen

/*************************************************************************
* The contents of this file are subject to the LGPL.  
* The Original Author is Peter Shen
* The Personal Information of the contributors:
*      1,  Peter Shen
*          zpshen@gmail.com
*          Shanghai, China
*
*************************************************************************
* $Id: RemoveClient.sql,v 1.1 2005/01/26 14:47:29 pshen Exp $
***
* Title:   Remove a Client in Compiere.
* Description:
*        - Disable all the constraints and triggers
*  - Delete the records from all the table
*  - Enable all  the constraints and triggers
*
* Warning:
*  - This script is only for Oracle 9.
*  - Please backup all the data before you use this script
*
* Guide:
*  - Log in the oracle sqlplus (or other sqlplus tools) with the schema from which you want to delete data
*  - Changed the v_Client_ID to AD_Client_ID which you want to delete
*  - This script would be a little slow, please wait patiently
************************************************************************/

  DECLARE
    /**
     * Please change this one to any client id you want to delete
     **/
    v_Client_ID                                                 NUMBER      := 1000003;
   
    v_SQL1                               VARCHAR2(1024);
   
    CURSOR Cur_Contraints  IS
                select table_name,constraint_name
                from user_constraints
                where constraint_type = 'R'
                AND status='ENABLED';
       
        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
       v_SQL1 := 'alter table '|| p.table_name ||' disable constraint '|| p.constraint_name;
        EXECUTE IMMEDIATE v_SQL1;
     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_Contraints  LOOP
        v_SQL1 := 'alter table '|| p.table_name ||' enable constraint '|| p.constraint_name;
        EXECUTE IMMEDIATE v_SQL1;
     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;

newhappy

牛人啊～

keren

我也在琢磨这个问题，系统初始化了好多个实体(客户)，怎么把不要的实体数据删除，而且各个表之间都有记录相互引用的，要直接删除很麻烦，看了很受启发。

qishunyong

pshen，好久不见了啊！期待中

netstring

SET SERVEROUTPUT ON;
EXEC DBMS_OUTPUT.ENABLE(200000);
DECLARE
  /*  Specify the Client you want to remove  */
  v_Client_ID NUMBER := 11;
  v_SQL       VARCHAR2(1024);
  v_Code      NUMBER;
  v_ErrMsg    VARCHAR2(500);

  TYPE ArrayType IS TABLE OF VARCHAR2(200);
  all_rec ArrayType;
  err_rec ArrayType;
  
  PROCEDURE performDelete(deleteSQList ArrayType) AS     
  BEGIN   
    FOR i IN deleteSQList.FIRST .. deleteSQList.LAST LOOP
      BEGIN
        v_SQL := deleteSQList(i);
        DBMS_OUTPUT.PUT_LINE('  performDelete==> Executing: ' || v_SQL);
        EXECUTE IMMEDIATE v_SQL;
      EXCEPTION
        WHEN OTHERS THEN
          v_Code   := SQLCODE;
          v_ErrMsg := SQLERRM;
          IF v_Code = 02292 THEN
            err_rec.extend;
            err_rec(i) := v_SQL;
          END IF;
      END;
    END LOOP;
    COMMIT;
    IF (err_rec IS NOT NULL AND err_rec.COUNT > 0) THEN
      performDelete(err_rec);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      v_Code   := SQLCODE;
      v_ErrMsg := SQLERRM;
      DBMS_OUTPUT.PUT_LINE('  performDelete Error==> ' || v_ErrMsg);
  END;

BEGIN
  SELECT 'delete from ' || TABLENAME || ' where AD_Client_ID=' || v_Client_ID BULK COLLECT INTO all_rec
    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'));
  DBMS_OUTPUT.PUT_LINE('Begin: ' || current_timestamp ||' ==> delete count: ' || all_rec.count);
  performDelete(all_rec);
  DBMS_OUTPUT.PUT_LINE('END: ' || current_timestamp );
  
EXCEPTION
  WHEN OTHERS THEN
    v_Code   := SQLCODE;
    v_ErrMsg := SQLERRM;
    DBMS_OUTPUT.PUT_LINE('Error: ' || v_Code || ': ' || v_ErrMsg);
END;
/