Re: A Big cursor problem in SPL. Anybody help?
Ravi Krishna wrote:
> "TBP" <TBP@Nospam.Nothere.Co.Uk> wrote in message news:ka6Db.736$S63.521@newsfep3-gui.server.ntli.net...
>
>
>>>this will work but this is a very bad solution. creating a view inside a SP means an
>>>object is created inside a SP. Depending on how your SP is called, this can lead to
>>>problem. If your SP is called inside transaction, Informix will recompile this SP and
>>>all dependent SPs, leading to a lock on sysprocplan. That will reduce concurrency
>>>drastically.
>>>
>>>
>>>
>>
>>What about selecting into a temp table ...
>>
>> if a=1 then
>> select col1, col2 from tab1 into temp t1 with no log; "SELECT1"
>> if a=2 then
>> select col1, col2 from tab1 into temp t1 with no log; "SELECT2"
>> if a=3 then
>> select col1, col2 from tab1 into temp t1 with no log; "SELECT3"
>> ......>>
>> FOREACH MYCURSOR FOR SELECT * FROM t1 ....
>> END FOREACH;
>> DROP t1;
>
>
> same issue with creating temp tables too. Temp table is also an object.
> What happens is that the moment any object referenced by the SP changes
> (which includes creating and destroying it), the SP is recompiled. That's
> the way Informix is designed.
>
> My suggestion: Unless the SP is guaranteed to run in single user mode(as
> in reports), avoid creating/modifying any object inside it.
>
>
Oh no it doesn't ...
The following is the sort of approach I was thinking of (crude but it
demonstrates the idea) :
=====================================
create procedure which_cursor(select_sql int) returning int, char(32);
define l_customer_num int;
define l_name char(32);
if select_sql = 1 then
select customer_num, fname name from customer where fnamematches "F*"
into temp t1 with no log;
else
if select_sql = 2 then
select customer_num, lname name from customer where lnamematches "B*"
into temp t1 with no log;
end if;
end if;
foreach
select *
into l_customer_num, l_name
from t1 where 1=1
return l_customer_num, l_name with resume;
end foreach;
drop table t1;
end procedure;
execute procedure which_cursor(1);
execute procedure which_cursor(2);=====================================
produces :
=======================================
(expression) (expression)
111 Frances
114 Frank
120 Fred
(expression) (expression)
113 Beatty
118 Baxter
=======================================
Still ...
Working with a temp table sounds more reliable, after all. Perheaps, I need to fetch row by row. There is another way to do this instead FOREACH cursor?