Home » SQL & PL/SQL » SQL & PL/SQL » SELECT statement in procedure
SELECT statement in procedure [message #390221] Thu, 05 March 2009 08:59 Go to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
Hello everybody.
Is there anyway to write procedure which return select statement ?

For example:

create or replace PROCEDURE adnan
( p_f1 out test.f1%TYPE, 
  p_f2 out test.f2%TYPE)
AS
BEGIN
  select f1, f2 
  into p_f1, p_f2  from TEST; 
END adnan;
Re: SELECT statement in procedure [message #390222 is a reply to message #390221] Thu, 05 March 2009 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which return select statement ?

It depends on what you mean with this.
Values or cursor?

What happened when you tried your code?

Regards
Michel

[Updated on: Thu, 05 March 2009 09:01]

Report message to a moderator

Re: SELECT statement in procedure [message #390224 is a reply to message #390221] Thu, 05 March 2009 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
What happens if/when more than 1 row exists in table TEST?
Re: SELECT statement in procedure [message #390226 is a reply to message #390224] Thu, 05 March 2009 09:10 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
BlackSwan wrote on Thu, 05 March 2009 16:05
What happens if/when more than 1 row exists in table TEST?


I would like to show all returned rows.
Re: SELECT statement in procedure [message #390228 is a reply to message #390221] Thu, 05 March 2009 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>into p_f1, p_f2 from TEST;
above can only hold 1 row (2 data elements).
Re: SELECT statement in procedure [message #390230 is a reply to message #390228] Thu, 05 March 2009 09:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I would like to show all returned rows.



The procedure will run on the server. How will you "show" anything on the server?
Re: SELECT statement in procedure [message #390233 is a reply to message #390228] Thu, 05 March 2009 09:35 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
BlackSwan wrote on Thu, 05 March 2009 16:14
>into p_f1, p_f2 from TEST;
above can only hold 1 row (2 data elements).


I tried to create procedure similar to one from this link:


Re: SELECT statement in procedure [message #390234 is a reply to message #390221] Thu, 05 March 2009 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

>I tried to create procedure similar to one from this link:
LINK?

[Updated on: Thu, 05 March 2009 09:40]

Report message to a moderator

Re: SELECT statement in procedure [message #390235 is a reply to message #390234] Thu, 05 March 2009 10:04 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
ok, i'm apologize for bad question. So, I'll try to be clear - how to achieve this as result of procedure execution ?
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7902 FORD
7934 MILLER
Re: SELECT statement in procedure [message #390236 is a reply to message #390221] Thu, 05 March 2009 10:10 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
There are various ways of getting data back from procedures, which is best depends on what you're trying to do and what client you're using to call the DB.
Try looking up ref cursors.
Re: SELECT statement in procedure [message #390237 is a reply to message #390221] Thu, 05 March 2009 10:14 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Declare a Ref Cursor and open it for the select statement.

Open Refcursor for
Select empno,ename from emp;
Re: SELECT statement in procedure [message #390238 is a reply to message #390221] Thu, 05 March 2009 10:18 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
Is it possible to implement without cursor ?
Re: SELECT statement in procedure [message #390239 is a reply to message #390235] Thu, 05 March 2009 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> var c refcursor;
SQL> create or replace procedure test (c out sys_refcursor)
  2  as
  3  begin
  4    open c for select * from emp;
  5  end;
  6  /

Procedure created.

SQL> set autoprint on
SQL> exec test (:c)

PL/SQL procedure successfully completed.

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 23/05/1987 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10

14 rows selected.

Regards
Michel
Re: SELECT statement in procedure [message #390240 is a reply to message #390221] Thu, 05 March 2009 10:21 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Have you tried anything without cursor ? if so then post it here, we will help to make it better.

Regards,
Ashoka BL
Bengaluru
Re: SELECT statement in procedure [message #390241 is a reply to message #390238] Thu, 05 March 2009 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
adnanBIH wrote on Thu, 05 March 2009 17:18
Is it possible to implement without cursor ?

Yes, with dbms_output for instance.

Regards
Michel

Re: SELECT statement in procedure [message #390256 is a reply to message #390240] Thu, 05 March 2009 11:59 Go to previous messageGo to next message
adnanBIH
Messages: 41
Registered: November 2007
Location: BiH, Sarajevo
Member
ashoka_bl wrote on Thu, 05 March 2009 17:21
Have you tried anything without cursor ? if so then post it here, we will help to make it better.

Regards,
Ashoka BL
Bengaluru


Yes, i tried this:
create or replace PROCEDURE testna
(
  v_param3 OUT VARCHAR2
)
AS
BEGIN
   SELECT * INTO v_param3
     FROM test_tab;
END;;


Something similar is possible in MSSQL Server, so i thougth that is possible in Oracle too.
Re: SELECT statement in procedure [message #390266 is a reply to message #390256] Thu, 05 March 2009 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, in Oracle this is cursor and cursors are very efficient in Oracle not like MSSQL or Sybase.

Regards
Michel
Re: SELECT statement in procedure [message #390280 is a reply to message #390256] Thu, 05 March 2009 13:03 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
adnanBIH wrote on Thu, 05 March 2009 17:59
Something similar is possible in MSSQL Server, so i thougth that is possible in Oracle too.


AFAIK SQL Server returns a ref cursor implicitly. The only difference in Oracle is that you have to declare it.
Re: SELECT statement in procedure [message #390290 is a reply to message #390280] Thu, 05 March 2009 13:38 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Blackswun,
>into p_f1, p_f2 from TEST; 
above can only hold 1 row (2 data elements).


Cursor c as select empno,ename from emp.
open cursor c
fetch c into p_f1,p_f2;


But if we fetch cusror into these values,It will store more
Than 1 row.Can you please explain why it is so?
Please correct me if I am wrong.
Regards,
Varun Punj
Re: SELECT statement in procedure [message #390291 is a reply to message #390290] Thu, 05 March 2009 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I recommend you to learn the basics in Oracle:
Database Concepts
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: SELECT statement in procedure [message #390328 is a reply to message #390290] Fri, 06 March 2009 00:13 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
varunvir wrote on Thu, 05 March 2009 20:38

fetch c into p_f1,p_f2;

But if we fetch cusror into these values,It will store more Than 1 row.

No, it will store just one row (first in the set). If you want to fetch another one, you'll need to issue another FETCH ... INTO. As long as you can do that as
FETCH c INTO p_f1, p_f2;
FETCH c INTO p_f1, p_f2;
FETCH c INTO p_f1, p_f2;
FETCH c INTO p_f1, p_f2;
...
people usually do that in a LOOP (because the above example is quite stupid).

Anyway: read the documentation. Doing so, you'll avoid too many trivial questions (and answers).
Re: SELECT statement in procedure [message #390478 is a reply to message #390328] Fri, 06 March 2009 09:09 Go to previous message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks Littlefoot to clear my doubt on such basic question.
I will read the developer's documentation too.
Regards,
Varun Punj
Previous Topic: trigger with rollback
Next Topic: Joining two tables
Goto Forum:
  


Current Time: Sun Dec 04 06:22:02 CST 2016

Total time taken to generate the page: 0.06865 seconds