在 oracle 中實現 sql server 中的在 create object 時的判斷與刪除功能

/* Formatted on 2006/07/06 14:34 (Formatter Plus v4.8.7) */
DECLARE
i_count INT := 0;
BEGIN
SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_ALLOTSLIPSTYLE_TO_HEAD';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table ALLOTSLIPSTYLE drop constraints FK_ALLOTSLIPSTYLE_TO_HEAD';
i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_COMPANYDOSSIER_SERVERDBCON';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table COMPANYDOSSIER drop constraints FK_COMPANYDOSSIER_SERVERDBCON';
i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_COMPANYDOSSIER_TO_BRANCH';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table COMPANYDOSSIER drop constraints FK_COMPANYDOSSIER_TO_BRANCH';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_COMPANYDOSSIER_TO_ORGANIZE';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table COMPANYDOSSIER drop constraints FK_COMPANYDOSSIER_TO_ORGANIZE';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_COMPANYDOSSIER_V_OR_P';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table COMPANYDOSSIER drop constraints FK_COMPANYDOSSIER_V_OR_P';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_COMPANYPERIOD_ACCOUNTANTCYC';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table COMPANYPERIOD drop constraints FK_COMPANYPERIOD_ACCOUNTANTCYC';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_FUTURESORDERFORM_S_DOSS';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table FUTURESORDERFORM drop constraints FK_FUTURESORDERFORM_S_DOSS';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_INSTORAGEBDST_INSTORAGEBD';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table INSTORAGEBDST drop constraints FK_INSTORAGEBDST_INSTORAGEBD';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_INSTORAGEBDST_MEASUREUNITS';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table INSTORAGEBDST drop constraints FK_INSTORAGEBDST_MEASUREUNITS';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_ORDERFORM_SHOPPEDOSSIER';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table ORDERFORM drop constraints FK_ORDERFORM_SHOPPEDOSSIER';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_OUTSTORAGEST_OUTSTORAGEHEAD';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table OUTSTORAGEST drop constraints FK_OUTSTORAGEST_OUTSTORAGEHEAD';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_PERSONNELBASEINFO_COMPD';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table PERSONNELBASEINFO drop constraints FK_PERSONNELBASEINFO_COMPD';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_PERSONNELBASEINFO_TO_COMPB';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table PERSONNELBASEINFO drop constraints FK_PERSONNELBASEINFO_TO_COMPB';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_PRODMAIND_PRODORGCLA';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table PRODUCTMAINDOSSIER drop constraints FK_PRODMAIND_PRODORGCLA';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_PRODMAIND_SELLPRODCLA';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table PRODUCTMAINDOSSIER drop constraints FK_PRODMAIND_SELLPRODCLA';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_PRODMAIND_MEASU';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table PRODUCTMAINDOSSIER drop constraints FK_PRODMAIND_MEASU';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_REQORDFORMST_MEASUREUNITS';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table REQUIRORDERFORMST drop constraints FK_REQORDFORMST_MEASUREUNITS';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_REQORDFORMST_REQORDFORM';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table REQUIRORDERFORMST drop constraints FK_REQORDFORMST_REQORDFORM';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_SALESSLIPST_SALESSLIP';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table SALESSLIPST drop constraints FK_SALESSLIPST_SALESSLIP';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_SHPCHGGDSST_SHPCHGGDS';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table SHOPPECHANGEGOODSST drop constraints FK_SHPCHGGDSST_SHPCHGGDS';
i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_SHOPPED_COMPD';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table SHOPPEDOSSIER drop constraints FK_SHOPPED_COMPD';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_STORAGE_SERVERDBCON';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table STORAGE drop constraints FK_STORAGE_SERVERDBCON';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_STOREPER_PERBASEINFO';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table STORAGEPERSONNEL drop constraints FK_STOREPER_PERBASEINFO';
i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_STOREPER_STORAGE';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table STORAGEPERSONNEL drop constraints FK_STOREPER_STORAGE';
i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_SYSUSERACC_ACCPOP';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table SYSUSERACCOUNT drop constraints FK_SYSUSERACC_ACCPOP';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_SYSUSERACC_PERBASEINFO';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table SYSUSERACCOUNT drop constraints FK_SYSUSERACC_PERBASEINFO';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_VENDCONTCRED_VENDCONTMAINTB';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table VENDITIONCONTRACTCREDITED drop constraints FK_VENDCONTCRED_VENDCONTMAINTB';

i_count := 0;
END IF;

SELECT COUNT (*)
INTO i_count
FROM user_constraints
WHERE constraint_name = 'FK_VENDCONTPRC_VENDCONTMAINTB';

IF i_count > 0
THEN
EXECUTE IMMEDIATE 'alter table VENDITIONCONTRACTPRICE drop constraints FK_VENDCONTPRC_VENDCONTMAINTB';

i_count := 0;
END IF;
end;
/
/****** Object: Table AllotSlipStyle Script Date: 2006-7-4 8:39:16 ******/
ALTER TABLE AllotSlipStyle ADD (
CONSTRAINT FK_AllotSlipStyle_To_Head FOREIGN KEY
(
AllotSlipHeadID
) REFERENCES AllotSlipHead (
AllotSlipHeadID
)
NOVALIDATE ) ;

/****** Object: Table CompanyDossier Script Date: 2006-7-4 8:39:17 ******/
ALTER TABLE CompanyDossier ADD (
CONSTRAINT FK_CompanyDossier_To_Branch FOREIGN KEY
(
GatherCompanyID
) REFERENCES CompanyBranch (
BranchID
)NOVALIDATE,
CONSTRAINT FK_CompanyDossier_To_Organize FOREIGN KEY
(
CompanyOrganiseID
) REFERENCES CompanyOrganize (
CompanyOrganizeID
)NOVALIDATE,
CONSTRAINT FK_CompanyDossier_ServerDBCon FOREIGN KEY
(
ServerDBID
) REFERENCES ServerDBCon (
ServerDBID
)NOVALIDATE,
CONSTRAINT FK_CompanyDossier_V_Or_P FOREIGN KEY
(
VenditionOrProvide
) REFERENCES VenditionOrProvide (
VenditionOrProvideID
) NOVALIDATE
) ;

/****** Object: Table CompanyPeriod Script Date: 2006-7-4 8:39:17 ******/
ALTER TABLE CompanyPeriod ADD (
CONSTRAINT FK_CompanyPeriod_AccountantCyc FOREIGN KEY
(
AccountantCycID
) REFERENCES AccountantCyc (
AccountantCycID
)
NOVALIDATE ) ;

/****** Object: Table FuturesOrderForm Script Date: 2006-7-4 8:39:18 ******/
ALTER TABLE FuturesOrderForm ADD (
CONSTRAINT FK_FuturesOrderForm_S_Doss FOREIGN KEY
(
InceptStorageID
) REFERENCES ShoppeDossier (
ShoppeID
)
NOVALIDATE );

/****** Object: Table InStorageBDST Script Date: 2006-7-4 8:39:18 ******/
ALTER TABLE InStorageBDST ADD (
CONSTRAINT FK_InStorageBDST_InStorageBD FOREIGN KEY
(
InStorageFormHeadID
) REFERENCES InStorageBD (
InStorageFormHeadID
)NOVALIDATE,
CONSTRAINT FK_InStorageBDST_MeasureUnits FOREIGN KEY
(
MesureUnit
) REFERENCES MeasureUnits (
UnitsID
) NOVALIDATE
) ;

/****** Object: Table OrderForm Script Date: 2006-7-4 8:39:19 ******/
ALTER TABLE OrderForm ADD (
CONSTRAINT FK_OrderForm_ShoppeDossier FOREIGN KEY
(
InceptStorageID
) REFERENCES ShoppeDossier (
ShoppeID
) NOVALIDATE
);

/****** Object: Table OrderFormST Script Date: 2006-7-4 8:39:19 ******/
ALTER TABLE OrderFormST ADD (
CONSTRAINT FK_OrderFormST_MeasureUnits FOREIGN KEY
(
MeasureUnits
) REFERENCES MeasureUnits (
UnitsID
) NOVALIDATE , -- NOT FOR REPLICATION
CONSTRAINT FK_OrderFormST_OrderForm FOREIGN KEY
(
OrderFormID
) REFERENCES OrderForm (
OrderFormID
) NOVALIDATE
);

/****** Object: Table OutStorageST Script Date: 2006-7-4 8:39:20 ******/
ALTER TABLE OutStorageST ADD (
CONSTRAINT FK_OutStorageST_OutStorageHead FOREIGN KEY
(
OutStorageFormHeadID
) REFERENCES OutStorageHead (
OutStorageFormHeadID
)NOVALIDATE
) ;

/****** Object: Table PersonnelBaseInfo Script Date: 2006-7-4 8:39:21 ******/
--CompanyBranch --&gt CompB
--CompanyDossier --&gt ComD
ALTER TABLE PersonnelBaseInfo ADD (
CONSTRAINT FK_PersonnelBaseInfo_to_CompB FOREIGN KEY
(
PersonnelBelongBranch
) REFERENCES CompanyBranch (
BranchID
)NOVALIDATE,
CONSTRAINT FK_PersonnelBaseInfo_CompD FOREIGN KEY
(
BelongCompanyID
) REFERENCES CompanyDossier (
CompanyID
)NOVALIDATE
);

/****** Object: Table ProductMainDossier Script Date: 2006-7-4 8:39:22 ******/
--MeasureUnits --&gt MeasU
--ProductOrganiseClass --&gt ProdOrgCla
--SellProductClass --&gt SellProdCla
--ProductMainDossier --&gt ProdMainD
ALTER TABLE ProductMainDossier ADD (
CONSTRAINT FK_ProdMainD_MeasU FOREIGN KEY
(
BasicMeasureUnits
) REFERENCES MeasureUnits (
UnitsID
)NOVALIDATE,
CONSTRAINT FK_ProdMainD_ProdOrgCla FOREIGN KEY
(
ProductMainClass
) REFERENCES ProductOrganiseClass (
ProductClassID
)NOVALIDATE,
CONSTRAINT FK_ProdMainD_SellProdCla FOREIGN KEY
(
Cards
) REFERENCES SellProductClass (
SellProductClassID
)NOVALIDATE
) ;

/****** Object: Table RequirOrderFormST Script Date: 2006-7-4 8:39:22 ******/
--RequirOrderFormST --&gt ReqOrdFormST
--RequirOrderForm --&gt ReqOrdForm
ALTER TABLE RequirOrderFormST ADD (
CONSTRAINT FK_ReqOrdFormST_MeasureUnits FOREIGN KEY
(
MeasureUnits
) REFERENCES MeasureUnits (
UnitsID
) NOVALIDATE , ---NOT FOR REPLICATION
CONSTRAINT FK_ReqOrdFormST_ReqOrdForm FOREIGN KEY
(
OrderFormID
) REFERENCES RequirOrderForm (
OrderFormID
) NOVALIDATE
);

/****** Object: Table SYSUserAccount Script Date: 2006-7-4 8:39:22 ******/
--SYSUserAccount --&gt SYSUserAcc
--AccountsPopedom --&gt AccPop
--PersonnelBaseInfo --&gt PerBaseInfo
ALTER TABLE SYSUserAccount ADD (
CONSTRAINT FK_SYSUserAcc_AccPop FOREIGN KEY
(
UseAccount
) REFERENCES AccountsPopedom (
PopedomID
)NOVALIDATE,
CONSTRAINT FK_SYSUserAcc_PerBaseInfo FOREIGN KEY
(
PersonnelID
) REFERENCES PersonnelBaseInfo (
PersonnelID
)NOVALIDATE
);

/****** Object: Table SalesSlipST Script Date: 2006-7-4 8:39:23 ******/
ALTER TABLE SalesSlipST ADD (
CONSTRAINT FK_SalesSlipST_SalesSlip FOREIGN KEY
(
SystemInvoiceID
) REFERENCES SalesSlip (
SystemInvoiceID
)NOVALIDATE
) ;

/****** Object: Table ShoppeChangeGoodsST Script Date: 2006-7-4 8:39:24 ******/
--ShoppeChangeGoodsST --&gt ShpChgGdsST
--ShoppeChangeGoods --&gt ShpChgGds
ALTER TABLE ShoppeChangeGoodsST ADD (
CONSTRAINT FK_ShpChgGdsST_ShpChgGds FOREIGN KEY
(
ChangeGoodsID
) REFERENCES ShoppeChangeGoods (
ChangeGoodsID
)NOVALIDATE
);

/****** Object: Table ShoppeDossier Script Date: 2006-7-4 8:39:25 ******/
--ShopperDossier --&gt ShoppeD
--CompanyDossier --&gt CompD
ALTER TABLE ShoppeDossier ADD (
CONSTRAINT FK_ShoppeD_CompD FOREIGN KEY
(
SquareCompanyID
) REFERENCES CompanyDossier (
CompanyID
)NOVALIDATE
) ;

/****** Object: Table Storage Script Date: 2006-7-4 8:39:25 ******/
ALTER TABLE Storage ADD (
CONSTRAINT FK_Storage_ServerDBCon FOREIGN KEY
(
ServerDBID
) REFERENCES ServerDBCon (
ServerDBID
)NOVALIDATE
) ;

/****** Object: Table StoragePersonnel Script Date: 2006-7-4 8:39:25 ******/
--StoragePersonnel --&gt StorePer
--PersonnelBaseInfo --&gt PerBaseInfo
ALTER TABLE StoragePersonnel ADD (
CONSTRAINT FK_StorePer_PerBaseInfo FOREIGN KEY
(
PersonnelID
) REFERENCES PersonnelBaseInfo (
PersonnelID
)NOVALIDATE,
CONSTRAINT FK_StorePer_Storage FOREIGN KEY
(
StorageID
) REFERENCES Storage (
StorageID
)NOVALIDATE
) ;

/****** Object: Table VenditionContractCredited Script Date: 2006-7-4 8:39:25 ******/
--VenditionContractCredited --&gt VendContCred
--VenditionContractMainTB --&gt VendContMainTB
ALTER TABLE VenditionContractCredited ADD (
CONSTRAINT FK_VendContCred_VendContMainTB FOREIGN KEY
(
ContractID
) REFERENCES VenditionContractMainTB (
ContractID
)NOVALIDATE
) ;

/****** Object: Table VenditionContractPrice Script Date: 2006-7-4 8:39:27 ******/
--VenditionContractPrice --&gt VendContPrc
--VenditionContractMainTB --&gt VendContMainTB
ALTER TABLE VenditionContractPrice ADD (
CONSTRAINT FK_VendContPrc_VendContMainTB FOREIGN KEY
(
ContractID
) REFERENCES VenditionContractMainTB (
ContractID
)NOVALIDATE
) ;