Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedures & SELECT statement.
Stored Procedures & SELECT statement. [message #10218] Thu, 08 January 2004 23:50 Go to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi,

I'm trying to build a common SELECT statment within a stored procedure. But I cant't compile the stored procedure??
Is it possible to have a simple single select statment within a stored procedure?

Assuming the select statment is correct (I've tested it alone..), is it possible to build a stored procedure like this? :

CREATE OR REPLACE PROCEDURE sp_name IS
BEGIN
SELECT ...
FROM table_name
WHERE ...
END sp_name;

When we call sp_name we should receive back the rows matching the select statment...

The meaning of this procedure would be that when a call is made from the application server to this procedure, this procedure simply retreives one or some more rows from the database and send it/them back to the application server.

Thank you very much in advance!

Regards,

Patrick.

Report message to a moderator

Re: Stored Procedures & SELECT statement. [message #10221 is a reply to message #10218] Fri, 09 January 2004 00:10 Go to previous messageGo to next message
resy
Messages: 86
Registered: December 2003
Member
If you want to retrieve only 1 column you can write like this.

SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE sp_name IS
2 sname1 table_name.column_name%type;
3 BEGIN
4 SELECT table_name
5 into sname1
6 FROM table_name 7 WHERE ....;
8* END sp_name;
SQL> /

Procedure created.

SQL> sho err
No errors.
SQL> exec sp_name

PL/SQL procedure successfully completed.

SQL>

Other way is to declare Out parameters in procedure and return that. Let me know whether you mean this or not.

Report message to a moderator

Re: Stored Procedures & SELECT statement. [message #10223 is a reply to message #10221] Fri, 09 January 2004 00:21 Go to previous messageGo to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Thank you so much for your quick answer!

Yes it's exactely what I ment! So if I have well understood, we have to use the SELECT INTO clause!! We can't have a usual SELECT!? I have 2 books of PL/SQL but none are expaining such things about stored procedures :(

In your:
CREATE OR REPLACE PROCEDURE sp_name IS
2 sname1 table_name.column_name%type;
3 BEGIN
4 SELECT table_name
5 into sname1
6 FROM table_name 7 WHERE ....;
8* END sp_name;

. sname1 table is automatically created?! A kind of "dummy" auxiliary table?
.table_name.column_name%type -> By type I have to code number if its a numeric value etc..!?
.line 6: What 7 stands for?

Many thanks for your answers!! It's very helpfull!!

Kind regards,

Patrick Tahiri.

Report message to a moderator

Re: Stored Procedures & SELECT statement. [message #10231 is a reply to message #10218] Fri, 09 January 2004 03:42 Go to previous message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
Please click on the link below for examples from Tom Kyte's web site of how to return a result set from an Oracle stored procedure or function.

Report message to a moderator

Previous Topic: Row count
Next Topic: constraint
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 18:05:40 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.