Create stored procedures in oracle and call cursors in stored procedures
CREATE OR REPLACE PROCEDURE aa
Is
CURSOR prodCombind_cur IS SELECT * FROM proddata.product_combind_info pci
ProdCombindInfo prodCombind_cur%ROWTYPE
Id_marketproduct_info varchar2 (32)
CURSOR combindPackage_cur IS SELECT * FROM proddata.combind_package_info
CombindPackageInfo combindPackage_cur%ROWTYPE
Id_package_info varchar2 (32)
Tmpid_marketproduct_info varchar2 (32)
-query the product portfolio that contains the sub-products to be deleted
CURSOR delete_combind_cur (id_sub_product VARCHAR2) IS
Select * from proddata.product_combind_rel pcr where pcr.id_product=id_sub_product
DelCombindInfo delete_combind_cur%ROWTYPE
Begin
Begin
A57 and A72 that already exist in the marketproduct_ info table are deleted first.
Select m.id_marketproduct_info into tmpid_marketproduct_info from proddata.marketproduct_info m where m.marketproductproducing codewords MP02000057' and m.isometric combinedproducts 1'
-- delete the product portfolio corresponding to the market product
OPEN delete_combind_cur (tmpid_marketproduct_info);-- Open the cursor
Loop
FETCH delete_combind_cur INTO delCombindInfo;-takes the id of the portfolio to be deleted from the cursor
-- first delete all sub-products under the package.
Delete from proddata.combind_package_rel cpr where cpr.id_combind_package_info in (
Select cpi.id_combind_package_info from proddata.combind_package_info cpi
Where cpi.id_product_combind_info=delCombindInfo.id_product_combind_info)
-- deleting the package
Delete from proddata.combind_package_info cpi
Where cpi.id_product_combind_info=delCombindInfo.id_product_combind_info
-- Delete sub-products under the portfolio
Delete from proddata.product_combind_rel pcr where pcr.id_product_combind_info=delCombindInfo.id_product_combind_info
Delete from proddata.marketproduct_rule_param mrp where mrp.id_marketproduct_info= delCombindInfo.id_product_combind_info
Delete from proddata.marketproduct_department md where md.id_marketproduct_info=delCombindInfo.id_product_combind_info
Delete from proddata.product_combind_info pci where pci.id_product_combind_info=delCombindInfo.id_product_combind_info
Exit when delete_combind_cur%notfound
End loop
Close delete_combind_cur;-closes the cursor
Delete from proddata.marketproduct_rule_param mr where mr.id_marketproduct_info= tmpid_marketproduct_info
Delete from proddata.marketproduct_department md where md.id_marketproduct_info=tmpid_marketproduct_info
Delete from proddata.marketproduct_plan_duty_rel mpdr where mpdr.id_marketproduct_info=tmpid_marketproduct_info
Delete from proddata.marketproduct_info m where m.id_marketproduct_info=tmpid_marketproduct_info
--
Select m.id_marketproduct_info into tmpid_marketproduct_info from proddata.marketproduct_info m where m.marketproductproducing codewords MP02000072' and m.isometric combinedproducts 1'
-- delete the product portfolio corresponding to the market product
OPEN delete_combind_cur (tmpid_marketproduct_info);-- Open the cursor
Loop
FETCH delete_combind_cur INTO delCombindInfo;-take a value from a cursor
-- first delete all sub-products under the package.
Delete from proddata.combind_package_rel cpr where cpr.id_combind_package_info in (
Select cpi.id_combind_package_info from proddata.combind_package_info cpi
Where cpi.id_product_combind_info=delCombindInfo.id_product_combind_info)
-- deleting the package
Delete from proddata.combind_package_info cpi
Where cpi.id_product_combind_info=delCombindInfo.id_product_combind_info
-- Delete sub-products under the portfolio
Delete from proddata.product_combind_rel pcr where pcr.id_product_combind_info=delCombindInfo.id_product_combind_info
Delete from proddata.marketproduct_rule_param mrp where mrp.id_marketproduct_info= delCombindInfo.id_product_combind_info
Delete from proddata.marketproduct_department md where md.id_marketproduct_info=delCombindInfo.id_product_combind_info
Delete from proddata.product_combind_info pci where pci.id_product_combind_info=delCombindInfo.id_product_combind_info
Exit when delete_combind_cur%notfound
End loop
Close delete_combind_cur;-closes the cursor
Delete from proddata.marketproduct_rule_param mr where mr.id_marketproduct_info= tmpid_marketproduct_info
Delete from proddata.marketproduct_department md where md.id_marketproduct_info=tmpid_marketproduct_info
Delete from proddata.marketproduct_plan_duty_rel mpdr where mpdr.id_marketproduct_info=tmpid_marketproduct_info
Delete from proddata.marketproduct_info m where m.id_marketproduct_info=tmpid_marketproduct_info
--
Commit
Exception
When others then
Dbms_output.put_line ('exception occurred while deleting A57 and A72 products')
End
-- tables to be processed: copy the data in the table product_combind_info to the table marketproduct _ info
For prodCombindInfo in prodCombind_cur loop
Begin
Select m.id_marketproduct_info into id_marketproduct_info from proddata.marketproduct_info m
Where m.id_marketproduct_info = prodCombindInfo.id_product_combind_info
Dbms_output.put_line ('product already exists:' | | prodCombindInfo.marketproduct_code | |'-'| | prodCombindInfo.MARKETPRODUCT_NAME)
Exception
The exception thrown here indicates that the above select failed to query the record.
When others then
Dbms_output.put_line ('product does not exist:' | | prodCombindInfo.marketproduct_code | |'-'| | prodCombindInfo.marketproduct_name)
Insert into proddata.marketproduct_info
(CREATED_BY
CREATED_DATE
UPDATED_BY
UPDATED_DATE
Marketproduct_code
Marketproduct_name
Product_class
Status
Is_combined
Id_marketproduct_info
Department_code
Version
Policy_type)
Values
(prodCombindInfo.CREATED_BY
ProdCombindInfo.CREATED_DATE
ProdCombindInfo.UPDATED_BY
ProdCombindInfo.UPDATED_DATE
ProdCombindInfo.marketproduct_code
ProdCombindInfo.marketproduct_name
ProdCombindInfo.MAIN_PRODUCT_CLASS
ProdCombindInfo.status
'1'
ProdCombindInfo.ID_PRODUCT_COMBIND_INFO
ProdCombindInfo.DEPARTMENT_CODE
ProdCombindInfo.version
ProdCombindInfo.POLICY_TYPE)
End
End loop
Commit
-- tables to be processed: copy the data in the table combind_package_info to the table package _ info
For combindPackageInfo in combindPackage_cur loop
Begin
Select p.id_package_info into id_package_info from proddata.package_info p where p.id_package_info=combindPackageInfo.ID_COMBIND_PACKAGE_INFO
Dbms_output.put_line ('package already exists:' | | combindPackageInfo.PACKAGE_CODE | |'-'| | combindPackageInfo.PACKAGE_NAME)
Exception
The exception thrown here indicates that the above select failed to query the record.
When others then
Insert into proddata.package_info
(ID_PACKAGE_INFO
ID_MARKETPRODUCT_INFO
PACKAGE_CODE
PACKAGE_NAME
CREATED_BY
CREATED_DATE
UPDATED_BY
UPDATED_DATE
Status)
Values
(combindPackageInfo.ID_COMBIND_PACKAGE_INFO
CombindPackageInfo.ID_PRODUCT_COMBIND_INFO
CombindPackageInfo.PACKAGE_CODE
CombindPackageInfo.PACKAGE_NAME
CombindPackageInfo.CREATED_BY
CombindPackageInfo.CREATED_DATE
CombindPackageInfo.UPDATED_BY
CombindPackageInfo.UPDATED_DATE
'1')
Dbms_output.put_line ('package does not exist:' | | combindPackageInfo.PACKAGE_CODE | |'-'| | combindPackageInfo.PACKAGE_NAME)
End
End loop
Commit
End
