Home » SQL & PL/SQL » SQL & PL/SQL » can function return multiple rows
icon6.gif  can function return multiple rows [message #178908] Fri, 23 June 2006 06:37 Go to next message
ankitvermamca
Messages: 4
Registered: June 2006
Junior Member
hi,

can i have a pl/sql function that can return multiple rows

may be the syntax will be like
create or replace function multiple() returns ...

begin
select candidateid from tbl_candidateinfo;

..code to return the result of above statement to calling
program..

end;

and functions will be called as

select candidateid from .. where candidateid in( select multiple());

thanks in advance,
ankit
Re: can function return multiple rows [message #178914 is a reply to message #178908] Fri, 23 June 2006 07:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Simply replace num_tab with a type of your choice, change the code in the package body, and you're go for launch.

SQL> CREATE OR REPLACE type num_tab is table of number;
  2  /

Type created.

SQL> create or replace package pipeline as
  2    function get_num_row return num_tab pipelined;
  3  end;
  4  /

Package created.

SQL> create or replace package body pipeline as
  2  
  3  function get_num_row return num_tab PIPELINED is
  4  begin
  5  for i in 1 .. 5 loop
  6    pipe row ( i );
  7  end loop;
  8  return;
  9  end get_num_row;
 10  end;
 11  /

Package body created.

SQL> 
SQL> 
SQL> select * from table(pipeline.get_num_row);

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
Re: can function return multiple rows [message #178922 is a reply to message #178914] Fri, 23 June 2006 07:22 Go to previous messageGo to next message
ankitvermamca
Messages: 4
Registered: June 2006
Junior Member
hi,

i tried to run the sample code u send me by replacing num_tab with int.But i m using mysql 5.0 and most of the syntax u send me doesnot seem to work I think mysql donot support the syntax.



AstaLavista

Ankit
Re: can function return multiple rows [message #178924 is a reply to message #178922] Fri, 23 June 2006 07:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be honest, I'd be really quite suprised if MySQL supported Pl/Sql.

Are you using an Oracle database at all?
Re: can function return multiple rows [message #178925 is a reply to message #178914] Fri, 23 June 2006 07:29 Go to previous messageGo to next message
ankitvermamca
Messages: 4
Registered: June 2006
Junior Member
Mysql support pl/sql to certain extent(It doesnot support intersect, except and minus for example). but it does provide facility to create functions and procedures

we are inclined to develop software using freeware products.So we are not using oracle

Regards,
Ankit
Re: can function return multiple rows [message #178929 is a reply to message #178925] Fri, 23 June 2006 07:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, true to my word, I'm suprised. Shocked

Other than what I've just read on the Web, I've got no idea what code structures MySql will support, so I'm afraid I'm not going to be much help.
Re: can function return multiple rows [message #178932 is a reply to message #178924] Fri, 23 June 2006 07:42 Go to previous messageGo to next message
ankitvermamca
Messages: 4
Registered: June 2006
Junior Member
The code u provided gave me an idea.why not create a temp table and insert all the candidateids in it through the function.Then we can use those ids further.

Thank u
Ankit
Re: can function return multiple rows [message #179079 is a reply to message #178932] Sat, 24 June 2006 18:51 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
From a quick look on the web, it seems this is an example of the MySQL stored procedure language, whatever it's called:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END

While it may have some superficial similarities with PL/SQL, it is clearly not the same language, and I could see no claim to provide Oracle compatibility (which Postgres and Fyracle attempt, for example) so I don't see any evidence at all that "Mysql support pl/sql to certain extent".

Quote:

we are inclined to develop software using freeware products.So we are not using oracle

So why not use the Oracle freeware product?
Re: can function return multiple rows [message #179781 is a reply to message #178908] Wed, 28 June 2006 13:10 Go to previous messageGo to next message
ullahsd
Messages: 2
Registered: June 2006
Location: Dhaka,Bangladesh
Junior Member
I think it is not possiable.
You may concate multivalues in a songle row and then return the single value.
Ullah
Re: can function return multiple rows [message #217184 is a reply to message #178908] Thu, 01 February 2007 01:01 Go to previous messageGo to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

Simply we use out and inout parameter Function can return the values ..... in Oracle 9i and 10g.
Re: can function return multiple rows [message #217230 is a reply to message #217184] Thu, 01 February 2007 04:39 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
victoryhendry wrote on Thu, 01 February 2007 01:01
Simply we use out and inout parameter Function can return the values ..... in Oracle 9i and 10g.

Or any other Oracle version since PL/SQL was first released.

However, the OP wanted to use this in SQL queries.
icon14.gif  Re: can function return multiple rows [message #217268 is a reply to message #178908] Thu, 01 February 2007 06:50 Go to previous messageGo to next message
mpniel
Messages: 1
Registered: February 2007
Junior Member
You did mention before that MySql supports pl/sql.
It is a mistake. You abviously meant that MySql supports Sql.
Re: can function return multiple rows [message #449090 is a reply to message #178914] Fri, 26 March 2010 10:14 Go to previous messageGo to next message
mikew12345
Messages: 1
Registered: March 2010
Junior Member
Thanks! Terrific example, just what I was looking for.
Mike
Re: can function return multiple rows [message #449181 is a reply to message #178908] Sat, 27 March 2010 11:35 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
There are three basic ways to return multiple rows:

1) use a refcursor as the return type
2) use an object table as the return type
3) use a mulit-delimited string as a return type (clob most likely)

Each has advantages and detractors. If you do not know about one of more of these remember: GOOGLE IS YOUR FRIEND.

Kevin
Previous Topic: Permission
Next Topic: Can't get this insert right?
Goto Forum:
  


Current Time: Sat Sep 24 21:27:10 CDT 2016

Total time taken to generate the page: 0.10005 seconds