建立好的client如何删除？
我建了几个实验性的client，可是竟然删不掉，谁可教我？很久以前写过一个脚本,算是给你个思路
回复 2楼 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
* [email]zpshen@gmail.com[/email]
* 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;
热烈欢迎老大！
牛人啊～页:
[1]