VentureFree
Well-known member
- Joined
- Jan 9, 2008
- Messages
- 54
- Programming Experience
- 5-10
I had been under the impression that Stored Procedures were essentially queries stored in the database for easier consumption and alteration without having to change consumer code. Clearly I was mistaken, since Oracle apparently doesn't use SPs in that way at all.
In particular, coming from Sql Server, I'm used to writing something like the following:
This lets me define a regular select query on the server so that any changes to the query require changing the stored procedure, and I don't have to track down every bit of code that would need to use that actual query.
Clearly this will not work in Oracle. In fact, I'm not sure how to actually do something similar in Oracle. Looking around on the internet, I find a lot of references to cursors, but I'm not really sure how to incorporate that into my existing code. Most of my code (VB.net) treats data retrieval as though it's using a regular select query, but of course it's just calling that SP.
So how do I define an Oracle SP (or possibly a function if necessary) in such a way that calling it appears the same as running an actual query, more like a Sql Server SP? Or do I have to change my code base to specifically handle Oracle SPs (i.e. handle cursors)?
In particular, coming from Sql Server, I'm used to writing something like the following:
VB.NET:
CREATE PROCEDURE GetEmployeeData
@EmployeeID int
AS
BEGIN
SELECT * FROM Employees WHERE EmpID = @EmployeeID
END
Clearly this will not work in Oracle. In fact, I'm not sure how to actually do something similar in Oracle. Looking around on the internet, I find a lot of references to cursors, but I'm not really sure how to incorporate that into my existing code. Most of my code (VB.net) treats data retrieval as though it's using a regular select query, but of course it's just calling that SP.
So how do I define an Oracle SP (or possibly a function if necessary) in such a way that calling it appears the same as running an actual query, more like a Sql Server SP? Or do I have to change my code base to specifically handle Oracle SPs (i.e. handle cursors)?