Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure with cursors
stored procedure with cursors [message #398996] Mon, 20 April 2009 03:51 Go to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Hi.
I'm not sure if what I am doing is overkill, I will try to explain and then show the code.

I need to get a list of recomendations for a customer, like amazons "People who bought this item also bought...X and X"

My stored procedure takes a part number as input. The logic, I think, is to get all order numbers where that part has been purchased and then from that list of order numbers to get the other part numbers that were bought on that order.

Here is my stored procedure so far
create or replace PROCEDURE View_Recommendations_SP (var_in_partNo IN NUMBER,results_out_cursor OUT sys_refcursor) AS 
var_Rows NUMBER;
var_OrderNo OrderLine."OrderNo"%TYPE;
var_out_partNo StockItem."PartNo"%TYPE;
var_Desc StockItem."Description"%TYPE;

      CURSOR results_cursor IS 
         SELECT si."PartNo",si."Description"
         FROM StockItem si
         JOIN OrderLine ol
         ON ol."PartNo" = si."PartNo"
         WHERE ol."OrderNo" = var_OrderNo;

BEGIN
SELECT COUNT(DISTINCT "OrderNo")
INTO var_Rows
FROM OrderLine
WHERE "PartNo" = var_in_partNo;

FOR i IN 1..var_Rows LOOP 
SELECT "OrderNo"
INTO var_OrderNo
FROM OrderLine
WHERE "PartNo" = var_in_partNo;

FETCH results_cursor INTO var_out_partNo, var_Desc ;
EXIT WHEN results_cursor%ROWCOUNT > var_Rows OR results_cursor%NOTFOUND;
    
END LOOP;
END;


I think my logic in the procedure may be correct but I'm not sure what I am doing in the loop is correct. I have used a cursor there but I dont know how to return it as an out parameter. I have been reading about global temp tables but when I try to create one anywhere within the stored procedure it doesn't like it. I get this error:

Error at line 30: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

   ( begin case declare end exit for goto if loop mod null
   pragma raise return select update while with 


I think if I can manage to create a global temp table within the stored procedure and then insert into the temp table on each execution of the loop and then select everything from the temp table into the
results_out_cursor OUT sys_refcursor

to return the data then that should work.

With this stored procedure I am feeling I might be doing too much as it is really a query with a subquery but its building the data up I am struggling with.

this is my StockItem table
CREATE TABLE  "STOCKITEM" 
   (	"PartNo" NUMBER(8,0), 
	"Description" VARCHAR2(4000), 
	"Bin" NUMBER(6,0) NOT NULL ENABLE, 
	"QuantityIn" NUMBER(5,0) NOT NULL ENABLE, 
	"Category" VARCHAR2(15) NOT NULL ENABLE, 
	"VatAmount" NUMBER(3,1) NOT NULL ENABLE, 
	"UnitPrice" NUMBER(10,2) NOT NULL ENABLE, 
	"Price" NUMBER(10,2) NOT NULL ENABLE, 
	 CONSTRAINT "CHK_PRICES" CHECK ("UnitPrice"<"Price") ENABLE, 
	 CONSTRAINT "PK_PARTNOONSTOCKITEM" PRIMARY KEY ("PartNo") ENABLE
   )
/

This is my OrderLine table
CREATE TABLE  "ORDERLINE" 
   (	"LineNo" NUMBER(8,0), 
	"OrderNo" NUMBER(8,0), 
	"PartNo" NUMBER(8,0), 
	"OrderStatusID" NUMBER(8,0), 
	"Quantity" NUMBER(5,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_ORDERLINE" PRIMARY KEY ("LineNo", "OrderNo") ENABLE
   )
/


and this is the temp table I was trying to put in the Stored Procedure
create global temporary recom_results_T
(  part_no     numeric(8)     not null,
   desc        varchar2(4000)    not null
)ON COMMIT DELETE ROWS;

ps.I have tried to use SQL Formatter but it just makes it worse! Im not sure If im using it right.
Any help appreciated. Thanks
Re: stored procedure with cursors [message #398998 is a reply to message #398996] Mon, 20 April 2009 04:06 Go to previous messageGo to next message
lraok
Messages: 5
Registered: April 2009
Location: Bangalore
Junior Member
You have defined ref cursor type as parameter and you have created general cursor in the declaration.

[Updated on: Mon, 20 April 2009 04:06]

Report message to a moderator

Re: stored procedure with cursors [message #399005 is a reply to message #398996] Mon, 20 April 2009 04:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) You don't want to dynamically create a table in your procedure.
If you think the temporary table would be useful, then create the same way you'd create any other table, and use it.

2) REALLY don't use quote delimited mixed case column names - it's a royal pain in the backside to maintain.

3) You can implement the logic you've got so far (to get a list of the distinct order numbers) much more efficiently like this:

FOR rec IN (SELECT DISTINCT "OrderNo"
            FROM   OrderLine
            WHERE "PartNo" = var_in_partNo) LOOP;

<do processing>    
END LOOP;


4) I think youcan implement your whole requirement:
Quote:
to get all order numbers where that part has been purchased and then from that list of order numbers to get the other part numbers that were bought on that order
like this (obviously untested, as you gave us no test case script):
create or replace PROCEDURE View_Recommendations_SP (var_in_partNo      IN  NUMBER
                                                    ,results_out_cursor OUT sys_refcursor) AS 

BEGIN

OPEN results_out_cursor FOR
  SELECT si.partno
        ,si.description
  FROM   stockitem  si
        ,orderline  ol1
        ,orderline  ol2
  WHERE  ol2.partno  = var_in_partno
  AND    ol2.orderno = ol1.orderno
  and    ol1.partno  = si.partno;

END;
Re: stored procedure with cursors [message #399006 is a reply to message #398998] Mon, 20 April 2009 04:50 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Hi
thanks for reply.
But I i put something like the below then I get this error

PLS-00201: identifier 'CURSOR' must be declared 

create or replace PROCEDURE View_Recommendations_SP (var_in_partNo IN NUMBER,results_cursor OUT CURSOR) AS 
var_Rows NUMBER;
var_OrderNo OrderLine."OrderNo"%TYPE;
var_out_partNo StockItem."PartNo"%TYPE;
var_Desc StockItem."Description"%TYPE;



      CURSOR results_cursor IS 
         SELECT si."PartNo",si."Description"
         FROM StockItem si
         JOIN OrderLine ol
         ON ol."PartNo" = si."PartNo"
         WHERE ol."OrderNo" = var_OrderNo;

BEGIN
SELECT COUNT(DISTINCT "OrderNo")
INTO var_Rows
FROM OrderLine
WHERE "PartNo" = var_in_partNo;

FOR i IN 1..var_Rows LOOP 
SELECT "OrderNo"
INTO var_OrderNo
FROM OrderLine
WHERE "PartNo" = var_in_partNo;

FETCH results_cursor INTO var_out_partNo, var_Desc ;
EXIT WHEN results_cursor%ROWCOUNT > var_Rows OR results_cursor%NOTFOUND;  
END LOOP;


Re: stored procedure with cursors [message #399009 is a reply to message #399006] Mon, 20 April 2009 04:57 Go to previous messageGo to next message
lraok
Messages: 5
Registered: April 2009
Location: Bangalore
Junior Member
You have to define record type as parameter to retreive more than one column.

FYI check with this statement.

FETCH results_cursor INTO var_out_partNo, var_Desc;
Re: stored procedure with cursors [message #399014 is a reply to message #398996] Mon, 20 April 2009 05:12 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) You can't just put a create command in a stored procedure. There are ways of creating database objects at run time but you don't want or need them - just create the global temp table seperately.

2) I doubt you need a global temp table either - this should be doable in a single select.
You just need to select all the parts that have an order no IN the list of order no's for the part you've passed in.
So you shouldn't need any loops either.

3) Please, please stop wrapping all your table and column names in double quotes - you're just making life hard for yourself.
Re: stored procedure with cursors [message #399016 is a reply to message #399009] Mon, 20 April 2009 05:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
You have to define record type as parameter to retreive more than one column.


Not true.
You can fetch a row from a cursor into pretty much any set of variables variable or type that will hold all the columns returned.
create table test_180 (col_1 number, col_2 number, col_3 number, col_4 number);

insert into test_180 values (10,20,30,40);
insert into test_180 values (11,21,31,41);
insert into test_180 values (12,22,32,42);
insert into test_180 values (13,23,33,43);

declare
  cursor c_test is 
    select col_1
          ,col_2
          ,col_3
          ,col_4
    from   test_180;
    
  
  r_cur    c_test%rowtype;
  r_tab    test_180%rowtype;
  
  v_num_1  number;
  v_num_2  number;
  v_num_3  number;
  v_num_4  number;
  
begin  
  
  open c_test;
  
  fetch c_test into r_cur;
  
  fetch c_test into r_tab;
  
  fetch c_test into v_num_1
                   ,v_num_2
                   ,v_num_3
                   ,v_num_4;
                   
end;
/

Re: stored procedure with cursors [message #399017 is a reply to message #398996] Mon, 20 April 2009 05:14 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Hello JRowbottom

Many thanks for your detailed reply!

I have just been playing with the code you suggested.

It is the type of statement I thought I needed from the start Smile

This is what I entered
SELECT DISTINCT 
         si."PartNo"
        ,si."Description"
  FROM   StockItem si
        ,OrderLine ol1
        ,OrderLine ol2
  WHERE  ol2."PartNo"  = 26
  AND    ol2."OrderNo" = ol1."OrderNo"
  AND    ol1."PartNo"  = si."PartNo";


and these were my results
PartNo Description 
26      Text
27      Text 

But I dont want to include the PartNo in the results that I passed in. i.e. 26 is passed in here and shown in the results.


Many Thanks
Re: stored procedure with cursors [message #399018 is a reply to message #398996] Mon, 20 April 2009 05:17 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
hello cookiemonster

Thanks for your reply!

If I dont wrap them in double quotes I get

ORA-00904: invalid identifier


Must be something to do with the way I declared the table in the first place

Thanks.
Re: stored procedure with cursors [message #399023 is a reply to message #398996] Mon, 20 April 2009 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Remove the double quotes from the create tables as well.

Quote:

But I dont want to include the PartNo in the results that I passed in. i.e. 26 is passed in here and shown in the results.


A simple not equals check would fix this right up.
Re: stored procedure with cursors [message #399027 is a reply to message #398996] Mon, 20 April 2009 05:34 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Hi, I think I have my solution

SELECT DISTINCT 
         si."PartNo"
        ,si."Description"
  FROM   StockItem si
        ,OrderLine ol1
        ,OrderLine ol2
  WHERE  ol2."PartNo"  = 26
  AND    ol2."OrderNo" = ol1."OrderNo"
  AND    ol1."PartNo"  = si."PartNo"
  AND    ol2."PartNo" != ol1."PartNo" ;



Many thanks for all your help and advice, much appreciated!
Re: stored procedure with cursors [message #399112 is a reply to message #399027] Mon, 20 April 2009 13:18 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
hooharhar wrote on Mon, 20 April 2009 06:34

Many thanks for all your help and advice, much appreciated!


But are you acting on it? Every singe question from you has forced mixed case table and column names. Why? You have not stated why you are doing this. Why do you insist on making things more difficult?
Re: stored procedure with cursors [message #399122 is a reply to message #398996] Mon, 20 April 2009 15:13 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Yes, I am acting on it.

I clearly did not realise that I should not create Tables with " " around the columns.

I will be creating my tables without "" from now on.

My examples continue to show "" because the point of this topic was another problem and I wasn't about to drop re-create and insert my data again mid way through the problem.

Thanks again!
Re: stored procedure with cursors [message #399123 is a reply to message #399122] Mon, 20 April 2009 15:16 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
That's fine Hooharhar.

Believe me, you'll grow tired of having to put double quotes around every table and column name when you code, plus, you'll forget it at times and then your code will not work. and in due time you'll be preaching to others not to ever use double quotes again. It's a vicious cycle Wink
Re: stored procedure with cursors [message #399124 is a reply to message #398996] Mon, 20 April 2009 15:19 Go to previous messageGo to next message
hooharhar
Messages: 28
Registered: April 2009
Junior Member
Haha yeah I am getting tired of it now and I havent been coding in this for long!

Why are "" allowed? what is the difference if they are there or not? weird.

Cool
Re: stored procedure with cursors [message #399257 is a reply to message #399124] Tue, 21 April 2009 05:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Largely for compatability with non-oracle data sources and non-oracle code.
Re: stored procedure with cursors [message #399291 is a reply to message #399124] Tue, 21 April 2009 07:59 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
hooharhar wrote on Mon, 20 April 2009 16:19
what is the difference if they are there or not?



As you have seen, if you use double quotes around your objects when you create them with mixed case, you will ALWAYS have to refer to them exactly the same way, with mixed case and in double quotes. Just having one character in the wrong case renders your query useless. If created them in the proper Oracle way, then you can change case to your hearts desire and it always means the same thing.
Previous Topic: Parse SQL: Extract column expression and labels from SQL query
Next Topic: shuffling of data
Goto Forum:
  


Current Time: Sat Dec 03 22:10:49 CST 2016

Total time taken to generate the page: 0.15004 seconds