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
Formatting echo from database
So I have a database that stores First and last names, then echos them back to a website, as of now the entire first and last name echos back (John Smith) I want the last name to just display the
Something Non-Traditional. Can we solve?
I have an idea for an application here. It's mostly flash, but it couldn't work without a somewhat non-traditional php functionality.here's the thing:I want to have a function which returns an
How to show a complete textarea ??
I enter Client Case Notes notes in a textarea field on a PHP form. The field is 5 rows deep and 70 chars wide , using the scroll bar to see beyond 5 lines.After entering notes, I want to echo or print
Progress bar tracking file downloads
Hey guys, was wondering if there was a way to track how far a file has been downloaded. I know there is a way to track uploading with APC and php 5.2 + ?? any suggestions thanks
Image DPI
Hi.I allow users to upload images, the user then is able to adjust several settings for the image:- Image quality (10 - 100)- watermark position(s) (top left/right, middle, bottom left/right)when the
Extract text from string
Hi folks,I have a string that looks like this:aaaaaaaaaa: bbbbbbbbbb (ccccccccc) dddddddddddddddddddddeeeeeeeeeeeeeeeeeeeee(format can't be changed - lengths of strings will vary) and I need to
Procedural to OOP
John Kleijn said that to avoid writing "crappy code", we should learn OOP and common OO principles. I've started reading OOP tutorials including the design patterns. And John is right, this
Revoking alter any table from a schema
Hi All,
Word Trimming
Hi Guys & Gals!!Hope you are all ok....I need some help with the followingI need to trim the following word as follows:$word = 'James Bond';output would be 'James B'There is a space between
Procedural to OOP
ohn Kleijn said that to avoid writing "crappy code", we should learn OOP and common OO principles. I've started reading OOP tutorials including the design patterns. And John is right, this
