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