Coming from a Microsoft background I use stored procedures all the time. They are great for many reasons, not the least being the rule of least privileges, for being precompiled (speed), for helping avoid SQL injection attacks etc.
Writing a stored procedure in SQL server is cake, here is an example of a simple stored procedure:
create PROCEDURE myProcedure (@inParm varchar(500)) AS SELECT column1, column2 FROM tTableName where column3 = @inParm
Nothing to it really, so when I had to write a stored procedure for Oracle silly me thought it would be just as easy, after all it is a enterprise class RDBMS right? Well, let’s just say things are allot different in Oracle land. In order for me to accomplish what seems to be a simple task first I had to create a new package. I liken this to C’s header files where you have to declare your method interface. Here is the SQL to create the package:
create or replace PACKAGE mypackage AS TYPE MyPackageType IS REF CURSOR RETURN tTheTableName%ROWTYPE; PROCEDURE GET_TheTableRecord ( MyInParm in VARCHAR2, MyReturnVal IN OUT MyPackageType); END mypackage;
Now that I have the package created with the Varchar2, MyPackageType interface I can modify the procedure: (presumably you can write both this and the following as one statement, I like splitting it out as I find it easier to follow)
create or replace PACKAGE BODY mypackage AS PROCEDURE GET_TheTableRecord ( MyInParm in VARCHAR2, MyReturnVal IN OUT MyPackageType) IS BEGIN OPEN MyReturnVal FOR SELECT * FROM tTheTableName where tTheTableName.column1 = MyInParm; END GET_TheTableRecord; END mypackage;
Now that you have a new procedure the questions is, how do you test it? I know that’s what my first question was. Back in SQL server land the test would look like this:
exec myProcedure('find this')
But not so in Oracle, this is what you have to run to test your new stored proc:
var my_cur refcursor exec mypackage.GET_TheTableRecord('test',:my_cur); print my_cur
I hope this saves you allot of time!
Here’s a link to a good Microsoft Article on this topic: http://support.microsoft.com/kb/321718