SQL Functions

SQL Functions

In this tutorial, we are going to discuss about SQL Functions. SQL functions are subprograms that encapsulate logic to perform specific tasks or calculations within a SQL statement. These functions accept input parameters, perform operations, and return a result. SQL functions can be built-in functions provided by the database management system (DBMS) or user-defined functions created by database developers.

SQL 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.

SQL Functions 
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.

That’s all about the SQL Functions. If you have any queries or feedback, please write us email at contact@waytoeasylearn.com. Enjoy learning, Enjoy Java.!!

SQL Functions
Scroll to top