Recent Pos

Working With JDBC CallableStatement Database Function

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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:

 

Example:

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

 
Example:

 
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 examples:CallableStatementExamples.zip
 
 
<-Previous: Working With JDBC CallableStatement IN and OUT Parameters | | Next->Closing JDBC Connection Statement and ResultSet

No Comments

Post a Comment