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

keeps going back to index.html ???????
ok so i started making my site using mostly.html files now i have added a fair chunk of php. My inde

Most basic form question ever?
Hello,

I want to use this snippet to make sure the fields in a form are ok before processing

How to copy a part of a vector in a raw memory
Hi,

How can I copy a part of a vector into a memory:

1
2
3
4
5

natcasesort works on one server but not on another
Hi

I have a problem that I was hoping that someone can help me with.

I'm trying to use

Creating a function
Basically i wanna put all this code in a seperate file

Code: <?php

Which practice of iteration through containers is preferred
In the "real world" what kind of loop do most people use to iterate through a container like a vecto

isset undefined variable
Hi all,

Hope someone can point out the obvious. I've a log in script, if you dont enter a use

Redirect not working after making a POST/GET
Hi Everyone,

I am a novice in PHP. Here I have 2 pages, one page with a textbox and button an

question about header() security
is is safe to just use the header() function to redirect someone if they are, say, not logged in? or

Grids not displaying decimals, and behaving differently on different PCs?
Hello,

I coded a relatively simple MII application that allows data from a form to be add