Tag Archives: Functions

JDBC : Function Execution Example

A SQL stored function is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.

SQL stored function called a user function or user-defined function.
See Also :

Types of parameters of functions

  • IN: A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
  • OUT: A parameter whose value is supplied by the SQL statement it returns. You retrieve values from the OUT parameters with the getXXX() methods.\
  • INOUT: A parameter that provides both input and output values. You *bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Example :

In this example will focus on how to execute user defined function by JDBC instead of writing of user defined function.
Table and Function implementationn : Here is small function getAverageStudentAge based on student name.

Create Table
CREATE TABLE `facingissuesonitdb`.`student` (
`name` VARCHAR(25) NULL,
'age' INT,
UNIQUE INDEX `name_UNIQUE` (`name` ASC));

Insert Records  
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`,'age') VALUES ('1', 'Saurabh Gupta','25');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`,'age') VALUES ('2', 'Rajesh Gupta','30');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`,'age') VALUES ('3', 'Ramesh Gupta','35');

Create User defined function

USE `facingissuesonitdb`;
DROP function IF EXISTS `getAverageStudentAge`;

USE `facingissuesonitdb`$
CREATE FUNCTION getAverageStudentAge (name_prefix VARCHAR(25))
     DECLARE avg_age NUMERIC;
        select avg(s.age) into avg_age from student as s where s.name like '%name_prefix%';
     RETURN avg_age;


Java code to execute user defined function by JDBC.

package procedure_functions;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

import connection.MyConnection;

public class FunctionTest {

public static void main(String[] args) {
try {
   Connection conn = MyConnection.getConnectionMain("MySql");

   CallableStatement stmt = conn.prepareCall("{?= call getAverageStudentAge(?)}");
   stmt.setString("Saurabh Gupta");
   stmt.registerOutParameter(1, Types.INTEGER);

} catch (SQLException ex) {




MySql Connection created successfully !!
MySql Connection Creation end !!

More on JDBC

Follow below links to know more on JDBC and solving JDBC issues :

Difference between Stored Procedure and functions

The differences between Stored procedures and Functions are given below:

See also :

Stored Procedure Function
Is used to perform business logic. Is used to perform calculation.
Must not have return type. Must have the return type.
May return zero or more values. May return only one values.
We can call function from procedure. Procedure can not be call from function.
Procedure supports input and output parameters Function supports only input parameters.
Exception handling using try/catch block can be used in stored procedures. Exception handling using try/catch can’t be used in user defined functions.

More on JDBC

Follow below links to learn more posts on JDBC and solving JDBC related issues :