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
No comments posted yet
Your Answer:
Login to answer
141 
52 
Other forums
Remove letter from numeric textbox
I have a textbox that will search the employee database by entering in the employee ID and it will r
Comma seperated implode & modifying returned string.
I have a PHP post form that posts up to 4 variables. I need whatever variables are passed to be comb
Dealing with code in db query
I am dealing with C code and I need to make sure it is encoded some how to ensure its integrity and
PHP Error
On my .php page I have a drop down box that has several names in it. When a user clicks the name &am
Session login issue
I'm wondering how to fix a problem I'm having with a session-based login system
Say I go to h
URL Rewrite issue
Im created a series of Rewrites and on page checks to make sure the correct url is being called. But
Facebook status update API
Hello,
So, I'm trying to create my first Facebook application with PHP.
Basic ideas fo
Dynamic Data + Sql Server 2005 Enterprise?
Hi! I have just started to learn ASP.NET, and it looks like it is quite a lot to learn. Im not reall
Adding post count
How would I make it so everytime someone clicks submit on my form, their row in the database for the
Allegro crashes when running load_bitmap. Why?
I can not get Allegro to load any BMP images. Here is a section of test code. The picture is in the
