Can a procedure contain only a SELECT statement?
in [Oracle]
|
Prev: semvmx
Next: 10.2.0.5 Patchset
From: Thomas Gagne on 18 Mar 2010 22:26 Thank you all for your help. 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 Oracle. I'm curious about packages and the procedures inside them. I take it the cursor must be treated as a cursor inside the application rather than as a result set? I'm using .Net, OracleDataAdapter, and DataSet. It seems as though result sets from stored procedures will require something else.
From: Thomas Gagne on 18 Mar 2010 22:33 John Hurley wrote: > On Mar 17, 9:53 pm, Thomas Gagne <TandGandGA...(a)gmail.com> wrote: > > snip > > # I'm looking around for Oracle 10g CREATE PROCEDURE syntax and > looking > >> for examples with simple SELECT statements inside. >> > > Why exactly would you want to do that in the first place? > > Have you looked at something like this? http://www.oradev.com/ref_cursor.jsp > I've used it before in other RDBs. The example was deliberately simplified. My intent would be to allow more complicated processing before the final SELECT. In some cases the procedure could do some simple parameter checking, call other procedures, and preprocess into temporary tables before the final projection. Though I haven't had the need inside MySQL, I understand MySQL has provided this since 5.1.
From: John Hurley on 19 Mar 2010 05:40 On Mar 18, 10:33 pm, Thomas Gagne <TandGandGA...(a)gmail.com> wrote: snip > I've used it before in other RDBs. The example was deliberately > simplified. My intent would be to allow more complicated processing > before the final SELECT. In some cases the procedure could do some > simple parameter checking, call other procedures, and preprocess into > temporary tables before the final projection. Though I haven't had the > need inside MySQL, I understand MySQL has provided this since 5.1. You rarely and really want to stay away from using temporary tables when processing work in Oracle. Really this is a technique that for the most part can be avoided in almost any relational dbms. Using temporary tables kinda/sorta looks like a crutch for most experienced DBAs and developers. Do it all in 1 SQL statement and harness the power of the database engine. Give it as much work as you can in 1 SQL statement!
From: Mladen Gogala on 19 Mar 2010 11:01 On Fri, 19 Mar 2010 02:40:47 -0700, John Hurley wrote: > You rarely and really want to stay away from using temporary tables when > processing work in Oracle. Not really. There are databases that support local temporary tables very well, SQL Server, Postgres and DB2 are among them. For those database, using local temporary table is something that comes naturally, a very useful feature. Oracle doesn't support transactional DDL, which is the basis for local temporary tables, but that is a lack in Oracle features, not a basis for conclusion that "temporary tables are for wimps because real programmers(TM) use cursors". -- http://mgogala.byethost5.com
From: Thomas Gagne on 19 Mar 2010 14:51
So is there a way to use packaged procedures to select data without using cursors? It would be great if I could use the same syntax inside SQLDeveloper as I might inside a program, and process the results similarly to how normal selects are handled. |