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

CHECH A STRING FOR ' " ' (DOUBLE QUOTES)
I am checking for delimiter (.!?) and if there is a " after a sentence then it should neglect a

sql error
I have been looking at this code for 20mins and can't work out what I am doing wrong. There is somet

C problem struct and string
Hi guys,

This is my first post and I'm hoping the experts here can help me solve my problem.<

Setting condidtions for an input box
Hi all,

I would like to set a condition in a txt box that says the number a user enters has t

Multi Level Array Problem
hi all,

For example I have array like below:

$temp = array(array('north america', 'us'

Auto install
Hi I have a directory lets say "apps" that I then have more folders ie "email",

dropdown box help - open php files to textarea
Hi, I am using tinymce to edit content located in several php files. The code I attached works but i

Load Animated Image while cache is serve
I have a page that loads up using cache and took about 1 to 2 minutes before it loads to the page. <

first id from db not showing
I have a php script which displays the content of a mysql table as a html table with sorting, delete

GMail like Chat in ASP.NET
Hi,Can anyone suggest me, how to incorporate GMail like chat in my existing ASP.Net application.I wa