| help with procedure [message #11142] |
Tue, 09 March 2004 08:21  |
Andy G
Messages: 25
Registered: May 2003
|
Junior Member |
|
|
I created this procedure but it takes way to long to go through all the records and that could be because of the cursor. I'm wondering if I can get some help to change it , even into a bsic SQL statement that will determine if the warranty is still active for the product.
What I'm doing is quesrying this procedure when I enter a product_id number on a form...so that I cna populate a field that gives me a yes or no wether that product has an active warranty.
What I'm thinking is this is not the best solution cause it really slows down when there are 1000 records for one product to look through...there has got to be a better where , maybe where I can do like if v_w_end_date > sysdate is found or something
I appreciate the help...here is what I have this far andit works just slow..
Declare
v_product_id number(8);
v_counter number(8);
v_w_end_date date;
Cursor end_date_cursor is
select product_id, w_end_date
from product_table
where product_id = prod.product_id;
BEGIN
v_counter :=0;
open end_date_cursor;
loop
fetch end_date_cursor into
v_product_id, v_w_end_date;
exit when end_date_cursor%notfound;
if v_w_end_date > trunc(sysdate) then
v_counter := v_counter + 1;
else
null;
End if;
end loop;
close end_date_cursor;
If v_counter > 0 then
V_VALID_WARRANTY := ' Yes';
else
V_VALID_WARRANTY := ' No';
End if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
|
|
|
|
| Re: help with procedure [message #11143 is a reply to message #11142] |
Tue, 09 March 2004 08:56  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
declare
v_warranty_active varchar2(3);
begin
select decode(count(*), 1, 'Yes', 'No')
into v_warranty_active
from product_table
where product_id = :product_id
and w_end_date > trunc(sysdate)
and rownum <= 1;
end; |
|
|
|
| Re: help with procedure [message #11144 is a reply to message #11142] |
Tue, 09 March 2004 08:57 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
If you only care that v_counter is greater than zero, then why are you counting all your rows up?BEGIN
SELECT NULL
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM product_table
WHERE product_id = prod.product_id
AND w_end_date > TRUNC(SYSDATE));
v_valid_warranty := 'Yes';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_valid_warranty := 'No';
END;
A.
|
|
|
|
|
|
| Re: help with procedure [message #11146 is a reply to message #11142] |
Tue, 09 March 2004 10:13 |
Jitendra Agrawal
Messages: 71
Registered: December 2003
|
Member |
|
|
Hi,
Do you really need a cursor? If I understand you correctly following should solve your problem.
Declare
v_product_id number(8);
v_counter number(8);
v_w_end_date date;
BEGIN
v_counter :=0;
select count(*) into v_counter from product_table where product_id=v_product and v_w_end_date > trunc(sysdate);
If v_counter > 0 then
V_VALID_WARRANTY := ' Yes';
else
V_VALID_WARRANTY := ' No';
End if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
Correct me if I am wrong.
If you still insist on using cursor.. you should return as soon as you get this :
--------------
if v_w_end_date > trunc(sysdate) then
v_counter := v_counter + 1;
--------------
Regards,
Jitendra Agrawal.
http://www.telemune.com/
|
|
|
|
|
|
| Re: help with procedure [message #11171 is a reply to message #11143] |
Wed, 10 March 2004 04:41 |
andy
Messages: 92
Registered: December 1999
|
Member |
|
|
thx Barry I applied both this and Art's sugestion...both worked both are about the same...faster then the cursor still a little slow but I'm going to see if other factors are involved in my form that could be the cause of this...
thanks for the help
|
|
|
|
| Re: help with procedure [message #11175 is a reply to message #11171] |
Wed, 10 March 2004 07:41 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I assumed you had an index on the product table on at least the product_id column. The best would be a composite index on (product_id, w_end_date).
There will be little or no difference between the query I supplied and Art's version. In fact, the first draft I did used exactly Art's approach.
The key is the index usage - with the correct index (and assuming the optimizer uses it), either query will fly.
What does EXPLAIN PLAN show for:
select decode(count(*), 1, 'Yes', 'No')
from product_table
where product_id = :product_id
and w_end_date > trunc(sysdate)
and rownum <= 1; |
|
|
|
|
|
| Re: help with procedure [message #11178 is a reply to message #11177] |
Wed, 10 March 2004 09:05 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You need to have a table to hold the explain plan results per the docs. There is a script supplied with Oracle to create it, but here is what I use:
create table plan_table (
statement_id varchar2 (32),
timestamp date,
remarks varchar2 (80),
operation varchar2 (30),
options varchar2 (30),
object_node varchar2 (128),
object_owner varchar2 (30),
object_name varchar2 (30),
object_instance number,
object_type varchar2 (30),
search_columns number,
id number,
cost number,
parent_id number,
position number,
cardinality number,
optimizer varchar2 (255),
bytes number,
other_tag varchar2 (255),
partition_id number,
partition_start varchar2 (255),
partition_stop varchar2 (255),
distribution varchar2 (30),
other long );
create index plan_table_ndx on
plan_table(statement_id); |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
