I have tried to unravell what you are trying to do and it seems to me you
may be able to achieve what you want without any cursors. As far as I can
tell what you are trying to do can be rewritten something like this
FROM t1 as x, t1 as y
WHERE x.f1 = y.f1
AND y.f2 > x.f2
AND y.f3 <= x.f3
AND x.f6 NOT IN ('CNL')
AND x.f7 <> '0'
AND x.f8 <> 'null'
AND y.f4 NOT IN ('CNL')
AND y.f5 IS NOT NULL
AND y.f6 <> 'null'
What you put at <?> will depend on what you want to do at "//do something
here" and which fields you need. You could perhaps make a cursor out of the
above query to do whatever you want.
Also I think f4 NOT IN ('CNL') is the same as f4 <> 'CNL' which probably
I am also suspicious that your table design may not be very good. It looks
to me that you have two different things stored in fields of the one table.
Life may be a lot simpler if there were two tables. While I don't understand
what you are doing, when I see things like f4 NOT IN ('CNL') and f6 NOT IN
('CNL') plus f6 <> 'null' and f8 <> 'null' I get the feeling that f4, f6 and
f8 may be storing multiple instances of the same sort of data in the one
table. This sort of thing violates first normal form. You will find SQL
works much better if you design your tables to at least 3rd normal form.
Anyway, there are some thoughts.
From: Amit_Wadhwa@stripped [mailto:Amit_Wadhwa@stripped]
Sent: Wednesday, 11 August 2004 4:53 AM
Subject: Calling a Stored Procedure crashing MySQL!!
Im a Newbie with Cursors,
What I basically need to do is:
Execute one SQL Statement:
For every Record, execute a query based on results of the first query
Check a Condition based on these results.
Execute Another Query
Execute Another Query
Currently am using JDBC/Java Program to do the above in 10 Minutes for over
400,000 Records. But am experimenting with Cursors to see if it can be done
The Procedure is given below.
It Stores Fine. But when I call it, my MySqld-NT crashes immediately.(...has
caused an error and needs to close)
Any Help is greatly Appreciated!
CREATE PROCEDURE calculatehits()
DECLARE cur1 CURSOR FOR SELECT f1,f2,f3,f4 FROM t1 WHERE f5 IS NOT NULL AND
f6 NOT IN ('CNL') AND f7 <> '0' AND f8<> 'null' ; DECLARE a CHAR(16);
DECLARE b,c DATETIME; DECLARE d int(16); DECLARE e int(16); OPEN cur1;
REPEAT FETCH cur1 INTO a, b,c,d; SELECT f9 INTO @e FROM t1 WHERE f1= @a and
f2 > @b And f3 <= @c AND f4 NOT IN ('CNL') AND f5 IS NOT NULL AND f6 <>
'null'; IF d=e THEN //do something here END IF; UNTIL done END REPEAT; CLOSE