stored procedure with cursors [message #398996] |
Mon, 20 April 2009 03:51  |
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 #399005 is a reply to message #398996] |
Mon, 20 April 2009 04:50   |
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   |
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   |
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   |
cookiemonster
Messages: 13963 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   |
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 #399018 is a reply to message #398996] |
Mon, 20 April 2009 05:17   |
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   |
cookiemonster
Messages: 13963 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   |
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   |
joy_division
Messages: 4963 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   |
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 #399291 is a reply to message #399124] |
Tue, 21 April 2009 07:59  |
joy_division
Messages: 4963 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.
|
|
|