Recent Pos

Working With JDBC CallableStatement Database Function


jdbc callable statement database function

The Database function:

⇒ The database function is a set of Statements compiled and saved into the database with a unique name for further execution.

⇒ The one structural difference that we find between database function and procedure is, the function is required to have a return, where the procedure doesn’t have.

The following is the syntax for creating a database function:



⇒ Creating a function that can return the location of the given Dept:


Q. How to execute this function in Java application?

-We use the CallableStatement to execute the DB function.

-This is same as done to execute the DB procedure.

-Only that we need to have the statement in the prepareCall() method with the following syntax:

-The first ‘?'(parameter) represents the return of the function and is considered as OUT parameter.

-The following code can be used to execute the above listed function.

Q. What are the differences between procedure and function?

⇒ The function is allowed to use in DML (Data Manipulation Language) and DRL (Data Retrieval Language) statements, where procedure is not allowed.

⇒ The function is recommended to not have the DML statements.

⇒ The function has a return but procedure doesn’t has. But the procedure can return the values using OUT parameter.

Q. How to read multiple records from the database execute the stored procedure?

⇒ In case of a requirement to return multiple records we need to create the Cursor and return to the client.

Q. How to create a Cursor Type in Oracle DB?

⇒ Following syntax is used to create the Cursor in Oracle DB.


Using the Cursor type in the function:

⇒ Lets create a function that can return all the emp records whose deptno matches with the given deptno.

Accessing this function from Java application:

⇒ We know that the DB Cursor is represented by the ResultSet object in the JDBC.

⇒ Thus we will get the return value of the above created function into ResultSet and read the records.

⇒ The following code can be used to execute this function and read the result.

Note: The OracleTypes class is in the oracle.jdbc.driver package.
Download all of the CallableStatement
<-Previous: Working With JDBC CallableStatement IN and OUT Parameters | | Next->Closing JDBC Connection Statement and ResultSet

No Comments

Post a Comment