SQL Functions

SQL Functions

Functions are exactly same as procedures except that function has return statement directly. Procedure can also returns values indirectly in the form of OUT parameters. Usually we can use procedure to define business logic and we can use functions to perform some calculations like getAverage() , getMax() etc.

create or replace function getAvg(id1 IN number,id2 IN number)return number 
as
sal1 number; 
sal2 number; 
BEGIN 
   select esal into sal1 from employees where eno = id1; 
   select esal into sal2 from employees where eno = id2; 
  
   return (sal1+sal2)/2; 
END; 
/

Function call can return some value.Hence the syntax of function call is

CallableStatement cst = con.prepareCall("{? = call getAvg(?,?)}");

return value of function call should be register as OUT parameter.

import java.sql.*; 
import java.util.*; 
import oracle.jdbc.*;// for OracleTyes.CURSOR and it is present in ojdbc6.jar
/**
 * 
 * @author ashok.mariyala
 *
 */
class StoredProceduresDemo { 
   public static void main(String[] args) throws Exception { 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","ashok","ashok");
      CallableStatement cst = con.prepareCall("{call getAvg(?,?)}"); 
      cst.setInt(2,100); 
      cst.setInt(3,200);
      cst.registerOutParameter(1,Types.FLOAT);
      cst.execute();
      System.out.println("Salary ..."+cst.getFloat(1));
      con.close(); 
   }
}
Statement vs PreparedStatement vs CallableStatement
  • We can use normal Statement to execute multiple queries.
st.executeQuery(query1)
st.executeQuery(query2)
st.executeUpdate(query2) 

i.e if we want to work with multiple queries then we should go for Statement object.

  • If we want to work with only one query,but should be executed multiple times then we should go for PreparedStatement.
  • If we want to work with stored procedures and functions then we should go for CallableStatement.
SQL Functions

Scroll to top