Home » SQL & PL/SQL » SQL & PL/SQL » New cursor if first cursor has no records
New cursor if first cursor has no records [message #228969] Wed, 04 April 2007 13:57 Go to next message
peace4theapes
Messages: 4
Registered: April 2007
Junior Member
Hi,

I need to have a function where i have to loop through a cursor. The logic here is that if the query in the cursor returns no records, then a new cursor has to be started with the same name. I am using the following code, but am getting errors

CREATE OR REPLACE FUNCTION myfunction(mynumber IN NUMBER)
RETURN VARCHAR2
IS
LIST VARCHAR2(1000);

BEGIN
LIST := NULL;

CURSOR CURSM IS
SELECT DISTINCT CM.FIRSTNAME || ' ' || CM.LASTNAME || '~' || CM.TELEPHONE AS MYCOLUMN
FROM table1 C, table2 T, table3 CM
WHERE C.ID = T.ID
AND T.CID = CM.CID
AND C.ID = mynumber
AND T.TYPE = 7;

IF CURSM%ROWCOUNT = 0
CURSOR CURSM IS
SELECT DISTINCT CM.FIRSTNAME || ' ' || CM.LASTNAME || '~' || CM.TELEPHONE AS MYCOLUMN
FROM table1 C, table2 T, table3 CM
WHERE C.ID = T.ID
AND T.CID = CM.CID
AND C.ID = mynumber
AND T.TYPE = 5;

END IF;


OPEN CURSM;
LOOP
IF LIST IS NOT NULL THEN
LIST := LIST || ', ' || CURSM.MYCOLUMN;
ELSE
LIST := CURSM.MYCOLUMN;
END IF;
END LOOP;
CLOSE CURSM;

RETURN LIST ;
END;
/


Could someone tell me how to use two cursors with the same name?

Thanks,
P
Re: New cursor if first cursor has no records [message #228972 is a reply to message #228969] Wed, 04 April 2007 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There are too many errors in this.
1/ your function does not compile -> start with that
2/ currently your function always returns NULL
3/ you don't need PL/SQL to do what you want.

Regards
Michel
Re: New cursor if first cursor has no records [message #228974 is a reply to message #228969] Wed, 04 April 2007 14:17 Go to previous messageGo to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
1. You should have posted this in PL/SQL forum.
2. You can't declare cursors in the executable part of a PL/SQL block.
3. <cursor>%rowcount is non-zero only after you've fetched from the cursor.
4. I'd suggest a different approach:
create or replace
function myfunction (mynumber number)
return varchar2 is
   lt_values dbms_sql.varchar2_table;
begin
   select distinct cm.firstname || ' ' || cm.lastname || '~' || cm.telephone as mycolumn
     bulk collect into lt_values
     from table1 c, table2 t, table3 cm
    where c.id   = t.id
      and t.cid  = cm.cid
      and c.id   = mynumber
      and t.type = 7;
   --
   if lt_values.count = 0
   then
      select distinct cm.firstname || ' ' || cm.lastname || '~' || cm.telephone as mycolumn
        from table1 c, table2 t, table3 cm
       where c.id   = t.id
         and t.cid  = cm.cid
         and c.id   = mynumber
         and t.type = 5;
   end if;
   --
   for lt_values.first .. lt_values.last
   loop
      -- build your list here
      null;
   end loop;
end myfunction;

I haven't tested the code, but use it as a guideline.
-Ivan
Re: New cursor if first cursor has no records [message #228977 is a reply to message #228974] Wed, 04 April 2007 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I still think it can be done in SQL.

Regards
Michel
Re: New cursor if first cursor has no records [message #228978 is a reply to message #228974] Wed, 04 April 2007 14:39 Go to previous messageGo to next message
peace4theapes
Messages: 4
Registered: April 2007
Junior Member
Thanks a lot Ivan. Works perfectly. Michael, I could have done it in SQL, but i have a query which gets data from multiple tables for the same id and am using functions to get specific type of data in a specific format. So, I had to go with functions. Thanks guys
Re: New cursor if first cursor has no records [message #228981 is a reply to message #228978] Wed, 04 April 2007 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In this case, I have a question.
Is the result still correct if there is no row when asking for type=7 but someone add this row of type 7 when you now query for type 5?

Regards
Michel
Re: New cursor if first cursor has no records [message #228982 is a reply to message #228981] Wed, 04 April 2007 14:58 Go to previous messageGo to next message
peace4theapes
Messages: 4
Registered: April 2007
Junior Member
You are right, that might be a issue when the DB transactions are fast and the function is being used live. But in this case I am using this function to transfer data from Oracle to MySQL with a cron job on a nightly basis, when nobody is working on the app.

Thanks for pointing it out.

Regards,
Re: New cursor if first cursor has no records [message #228983 is a reply to message #228981] Wed, 04 April 2007 14:59 Go to previous messageGo to next message
peace4theapes
Messages: 4
Registered: April 2007
Junior Member
What other solution do you suggest?
Re: New cursor if first cursor has no records [message #229016 is a reply to message #228983] Thu, 05 April 2007 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use something like:
select mycolumn 
from (
SELECT DISTINCT 
       CM.FIRSTNAME || ' ' || CM.LASTNAME || '~' || CM.TELEPHONE AS MYCOLUMN,
       t.type ctype,
       lag(t.type) over (order by t.type desc, CM.FIRSTNAME || ' ' || CM.LASTNAME || '~' || CM.TELEPHONE) ptype
FROM table1 C, table2 T, table3 CM
WHERE C.ID = T.ID
AND T.CID = CM.CID
AND C.ID = mynumber
AND T.TYPE in (5,7)
)
where ptype is null or ptype = ctype
/

We search in both types but only keep the first one (7 or, if it does not exist, 5).
There are many ways to do the same thing.
In this case, the query is consistent whatever happen outside it.

Regards
Michel
Re: New cursor if first cursor has no records [message #229026 is a reply to message #228969] Thu, 05 April 2007 01:41 Go to previous message
lmraochodisetti
Messages: 2
Registered: July 2006
Location: mumbai
Junior Member

Hi friend,

u have created cursors, so u can use cursor attributes like
%notfound and %found.

ex : if cursorname%notfound then
create new cursor(based on ur requirement)
else
..................
Previous Topic: BULK INSERT
Next Topic: Help me build this string
Goto Forum:
  


Current Time: Sun Dec 04 04:16:11 CST 2016

Total time taken to generate the page: 0.08317 seconds