a script to remove a client

pshen

This script will be included in compiere utils project.
The hosted url of this projects is in

http://sourceforge.net/projects/windowutil/

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;

major

"Changed the v_Client_ID to AD_Client_ID which you want to delete"
这一段是什么意思？
比如要将Compiere中自带的GardenWorld Client删除
那么这个Script如何使用？
谢谢
// 我对compiere刚开始接触，另外对oracle并不是很熟悉...