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

Preventing SQL Injection
I have a question about SQL Injection, In some of our code we use this: view plaincopy to clipboa

Table colours fail when extra row added
Hey Gurus,

I got a weird problem with formating the colour of a table made in php. Everything

how to populate a drop down box
Hi buddies!

PROCEDURE DOWNLOAD_REPORT_FORM (report_name varchar) is begin DOWNLO

form class help (oop php5)
Hidy Ho Neighbors,

I'm forcing myself to learn oop/classes for php5. It seems like a good id

losing session vars when arriving from an email link
Hi,

I've got a confirmation script made that sends an email with a link for a user to confirm

problem when runing a funciton
I am trying to connect to database using a funciton in a functions.php

<?php
funct

Deleting a record php
Attempting to setup delete a record page. The below code I put together doesn't seem to work.

Applet JTextField Size Problem
Hello,

I recently started making an applet to calculate certain values for airplanes. However

Can't get the unicode character
I'm trying to get some text from various parts of a file. I have converted the file to hex (bin2hex)

pointer 102 question
I read a book
1
2
3
4
5
6
7
8
9
10
11
12
int main() {