Can a procedure contain only a SELECT statement?
in [Oracle]
|
Prev: semvmx
Next: 10.2.0.5 Patchset
From: Steve Howard on 19 Mar 2010 19:48 On Mar 19, 4:56 pm, Thomas Gagne <tgga...(a)gmail.com> wrote: > But for the purposes of this thread, the topic is stored procedures > and returns data sets either to an interactive user using SQLDeveloper > or an application. As far as I can tell, your initial question was "Can a procedure contain only a SELECT statement?", which David Fitzjarrell answered in the first response. If you are looking to *return* a data set, you need a function. If you want to use a resultset produced from a *procedure*, you need an out variable of sys_refcursor type, such as: SQL> create table t(c number); Table created. SQL> insert into t select rownum from all_objects where rownum <= 10; 9 rows created. SQL> commit; Commit complete. SQL> create or replace procedure p_data(p_data out sys_refcursor) is 2 begin 3 open p_data for select * from t; 4 end; 5 / Procedure created. SQL> variable b refcursor SQL> exec p_data(:b) PL/SQL procedure successfully completed. SQL> print b C ---------- 1 1 2 3 4 5 6 7 8 9 10 rows selected. SQL> You can use this with any modern language such as java, python, C#, etc. HTH, Steve
From: Mladen Gogala on 19 Mar 2010 23:31 On Fri, 19 Mar 2010 16:48:43 -0700, Steve Howard wrote: > You can use this with any modern language such as java, python, C#, etc. You forgot Perl. -- http://mgogala.byethost5.com
From: Steve Howard on 20 Mar 2010 08:56 On Mar 19, 11:31 pm, Mladen Gogala <gogala.mla...(a)gmail.com> wrote: > On Fri, 19 Mar 2010 16:48:43 -0700, Steve Howard wrote: > > You can use this with any modern language such as java, python, C#, etc.. > > You forgot Perl. > > --http://mgogala.byethost5.com No, that's what I meant by etc. :) Python has been my language of choice recently. I know you are a big perl guy based on your past posts, but I absolutely love the python model.
From: Robert Klemme on 20 Mar 2010 17:02 On 03/20/2010 01:56 PM, Steve Howard wrote: > On Mar 19, 11:31 pm, Mladen Gogala <gogala.mla...(a)gmail.com> wrote: >> On Fri, 19 Mar 2010 16:48:43 -0700, Steve Howard wrote: >>> You can use this with any modern language such as java, python, C#, etc. >> You forgot Perl. >> >> --http://mgogala.byethost5.com > > No, that's what I meant by etc. :) > > Python has been my language of choice recently. I know you are a big > perl guy based on your past posts, but I absolutely love the python > model. You should come and see Ruby. ;-) Cheers robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: Robert Klemme on 20 Mar 2010 17:08
On 03/19/2010 08:39 PM, Sybrand Bakker wrote: > On Thu, 18 Mar 2010 22:26:44 -0400, Thomas Gagne > <TandGandGAGNE(a)gmail.com> wrote: > >> My background is Sybase & SqlServer. On both, due I'm sure to a common >> heritage, a stored procedure is capable of being as simple or complex as >> the programmer wants. Sometimes, all that is needed is a select >> statement. Sometimes even simple projections may require multiple steps >> to prepare the last SELECT. Additionally, stored procedures are capable >> of returning multiple result sets. I assumed, incorrectly, such a thing >> was not so complicated that it couldn't be easily done inside Oracl > > Mickeysoft has never understood the Procedure concept, and ignored the > formal defintiion and abused it to return a result set. > It seems like you belong to the class of sqlserver 'developers' which > is so narrow-minded they automatically reject everything done > differently by Oracle and start bashing Oracle for it. > Luckily sqlserver is incapable of being an enterprise class product, > just because of its poor architecture and vendor lock-in, so your > 'objections' are futile. I would not be too sure of that. SQL Sever isn't as bad as people are trying to make it look - and it's gaining ground, especially in the area of dealing with larger data sets. Maybe it's not as "enterprise class" as Oracle is (or is claimed to be) but the management tools with good graphical user interface were there before Oracle had Grid Control. Yes, I know - real DBA's use command line, but there are situations where a graphical visualization can greatly help. Cheers robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/ |