Combining refCursors and Summing

Posted on 16th Feb 2014 07:03 pm by admin

Given the following DDL:

CREATE TABLE FOODSALESTEST ("WEEKNBR" NUMBER, "STOREID" NUMBER NOT NULL, "GUESTS" NUMBER NOT NULL, "TACOSALES" NUMBER NOT NULL, "SOUPYSALES" NUMBER NOT NULL); Insert into FoodSalesTest VALUES (1,74,302,3021.71,3021.71); Insert into FoodSalesTest VALUES (1,83,231,2106.58,2106.58); Insert into FoodSalesTest VALUES (1,101,251,2163.5,2163.5); Insert into FoodSalesTest VALUES (1,113,298,2953.82,2953.82); Insert into FoodSalesTest VALUES (2,74,131,1168.11,1168.11); Insert into FoodSalesTest VALUES (2,83,156,1258.22,1258.22); Insert into FoodSalesTest VALUES (2,101,164,1550.87,1550.87); Insert into FoodSalesTest VALUES (2,113,129,1384.26,1384.26); Insert into FoodSalesTest VALUES (3,74,144,1594.02,1594.02); Insert into FoodSalesTest VALUES (3,83,196,1867.2,1867.2); Insert into FoodSalesTest VALUES (3,101,196,2052.96,2052.96); Insert into FoodSalesTest VALUES (3,113,142,1490.63,1490.63); Insert into FoodSalesTest VALUES (7,74,192,2496.17,2496.17); Insert into FoodSalesTest VALUES (7,83,178,1971.54,1971.54); Insert into FoodSalesTest VALUES (7,101,294,3477.88,3477.88); Insert into FoodSalesTest VALUES (7,103,336,3398.42,3398.42); --=== CREATE OR REPLACE PROCEDURE GETWEEKLYFOODSALES (inweeknbr number , p_rcWeekly in out sys_refcursor ) is BEGIN OPEN p_rcWeekly FOR -- This has been simplified and as is not very practical. -- It is actually a huge select statement with 6 "WITH" clauses and 15 joins and many selection criteria parameters Select * from FoodSalesTest where weeknbr = inWeekNbr; END GetWeeklyFoodSales; --=== CREATE OR REPLACE PROCEDURE GETALLFOODSALES( NbrOfWeeks In number , p_rcWeek1 Out sys_refcursor , p_rcWeek2 out sys_refcursor , p_rcWeek3 out sys_refcursor , p_rcWeek7 out sys_refcursor ) is begin GetWeeklyFoodSales( 1 , p_rcWeek1); GetWeeklyFoodSales( 2 , p_rcWeek2); if NbrOfWeeks = 3 then GetWeeklyFoodSales( 3 , p_rcWeek3); end if; GetWeeklyFoodSales( 7 , p_rcWeek7); END GetAllFoodSales;
GetWeeklyfoodSales is actually a huge select statement with 6 "WITH" clauses and 15 joins and many selection criteria parameters. It is probably possible to combine all functionality in 1 stored procedure but I want to modularize.

1) How do I select all cursors into 1 single cursor in GetAllFoodSales?

2) Why Cant I do this? = why isn't the cursordata maintained since it is defined as IN OUT in GetWeeklyFoodSales?
GetWeeklyFoodSales( 1 , p_TheOnlyCursor);
GetWeeklyFoodSales( 2 , p_TheOnlyCursor);
if NbrOfWeeks = 3 then
GetWeeklyFoodSales( 3 , p_TheOnlyCursor);
end if;
GetWeeklyFoodSales( 7 , p_TheOnlyCursor);

WHERE invoked procedure has these parms:
inweeknbr IN number
, p_rcWeekly IN OUT sys_refcursor

3) I would also like to have 2 more output cursor in GetAllFoodSales. Descriptively:
1. p_SummedResultsByStoreID
2. p_SummedResultsByWeekNumber

These must be separate cursors in order for program to consume for report

Your Answer:

Login to answer

Other forums

Email Script does not reach destination
Hey Guys look at this code snippet :
Code:
//read a line from the file

$myFile = "

Form always sends to error page...
Hello,
Any help will be greatly appreciated. I am having trouble getting multiple fields to be re

Default TimeZone
The server I'm working with is hosted in America so all times inserted into the database are coming

bind error during socket programming
i am kinda new to socket programming. wrote the below code for a server. but getting the bind error

Multithreading in Oracle (Java, SQLJ, Pro*C,??) on 10g
I am investigating how to run a Java stored procedure in multithread mode.
I know that if I chang

scandir clients directory
hi,
how can i scandir the clients directory? i need a script that when i click a button it will u

how to make database item unique
Hey guys,

is it possible to do this:

I have the database item $title being pulled for

rename the file
File.txt

Code: ***DOCUMENT***
..DN:
000044255
..CB:
..SN:
..PY:
2009
..E

add text and number image
hi guys ..

ok see i want the user to add their name and pick a number then click get it and i

New Login Script
Hi all, i attempted to create a whole new login script witch isnt working for some reason i dont kno