Procedure and Function in Oracle

he requirement was simple.I had to change the embedded sql query used in my program to use a procedure.I had not worked much in java.I had to dig a lot into sites to get the diffence in use of both in java.

Procedure
It can have input and output parameters.
Cannot return,but output parameters are as good as return.


Functions
Return is a must.


To call a procedure we use the following format
CallableStatement cs=null;
cs=connection.preparecall("{call procedure_name(?,?,?)}");
cs.setString(1,var1);
cs.setString(2,var2);
cs.registerOutParamater(3,OracleType.VARCHAR);
cs.execute();
String outputParameter=cs.getString(3);
//1 &2 are i/p parameters and 3 is o/p parameter.



To call a function
CallableStatement cs=null;
cs=connection.preparecall("{call ? :=function_name(?,?,?)}");
cs.registerOutParamater(1,OracleType.VARCHAR);
cs.setString(2,var1);
cs.setString(3,var2);
cs.setString(4,var3);
//Here 1 is the return variable and 2 , 3 and 4 are i/p variables.

cs.execute();
String outputParameter=cs.getString(1);
Here the first ? is the return value and the rest are input parameters.