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
Looking for help reading a .txt or .ini file and outputting the info.
I have a file called Info.ini and It has the following info:
Code: [General]
Online=0
I wou
Adding post count
How would I make it so everytime someone clicks submit on my form, their row in the database for the
insert PHP code into function
Hi,
I'm having problems when trying following:
I have:
Code: [Select]createBar("Do
Modal Popup Help
Hi guys, I have a modal popup with an iframe. How can i send data from a label or textbox to the ifr
update 2 columns by doing inner 2-column query
Hi,
is something like this possible?
update contract_all set col1,col2 =
(
Not sure why this script is not working?
Hi I am new to php programing and I was trying to make up a simple script like a captcha but I canno
Displaying data from MySql a different way
Hi all,
I'm pretty new to php and MySql and have hit a problem.
I am able to display data
understanding functions and classes
Code: [Select]
class person {
var $name = "Jimmy Goe";
function get_nam
Inserting a check in checkbox from array value
Hello all,
I have a bit of code that works for a select option box, but does not work for my chec
TinyMCE / Ajax Postback Problem
Hiya all,
I have a page which loads the TinyMCE editor. On the postback I obviously want to f