问题描述:

I have a few procedures(pr_1, pr_2, pr_3) that use cursors to select data, and I would like to put result from these procedures into memory, use them several times in calculation to get several result values. See examples below:

All procedures statement are the same for all procedures except different queries in each one and look as below:

CREATE OR REPLACE PROCEDURE pr_1 (

proc_1_cursor IN OUT Reports.rep_type,

date_parameter IN system_days.daytime%TYPE)

AS

BEGIN

OPEN proc_1_cursor FOR

select * from table where -------- ;

END;

/

and result values should look like below, can be case or if or whatever is equivalent:

select

(

select CASE

WHEN (date_parameter >= TO_DATE('2012-01-01', 'YYYY-MM-DD')

AND date_parameter <= TO_DATE('2012-12-31', 'YYYY-MM-DD')) THEN

(pr_1.dry_mtd/1.07322) + pr_2.total_dry_mtd + pr_1.dry_flare_mtd

WHEN (date_parameter >= TO_DATE('2015-01-01', 'YYYY-MM-DD')

AND date_parameter <= TO_DATE('2015-12-31', 'YYYY-MM-DD')) THEN

(pr_1.dry_mtd/1.45) + pr_2.total_dry_mtd - pr_1.dry_flare_mtd

ELSE (pr_1.dry_mtd/1.98) + pr_2.total_dry_mtd + pr_1.dry_flare_mtd

END calc_1 from dual

),

(

select CASE

WHEN (date_parameter >= TO_DATE('2012-01-01', 'YYYY-MM-DD')

AND date_parameter <= TO_DATE('2012-12-31', 'YYYY-MM-DD')) THEN

(pr_1.dry_mtd_2/1.07322) + pr_2.total_dry_mtd_2 + pr_1.dry_flare_mtd_2

WHEN (date_parameter >= TO_DATE('2015-01-01', 'YYYY-MM-DD')

AND date_parameter <= TO_DATE('2015-12-31', 'YYYY-MM-DD')) THEN

(pr_1.dry_mtd_2/1.45) + pr_2.total_dry_mtd_2 - pr_1.dry_flare_mtd_2

ELSE (pr_1.dry_mtd_2/1.98) + pr_2.total_dry_mtd_2 + pr_1.dry_flare_mtd_2

END calc_2 from dual

)

from dual

how I can realize it in procedure?

thanks,

S

相关阅读:
Top