CallableStatement interface:
- The interface used to call stored procedures and functions.
- SQL stored procedures and functions are used to write and store business logic inside the database.
| Stored Procedure | Function |
| It is used to perform business logic. | It is used to perform calculation. |
| It must not have the return type. | It must have the return type. |
| It may return 0 or more values. | It can return only one value. |
| We can call functions from the procedure. | Procedure cannot be called from function. |
| Procedure supports input and output parameters. | Function supports only input parameter. |
How to get the instance of CallableStatement?
- The prepareCall() method of Connection interface returns the instance of CallableStatement.
- CallableStatement stmt=con.prepareCall(“{call myprocedure(?,?)}”);
Create ‘test’ table in datatbase:
| SQL> create table test(num number(10)); Table created. |
Create stored procedure to insert data into ‘test’ table:
| SQL> create or replace procedure insertNum(num number) is begin insert into test values(num); end; / procedure created successfully. |
Insert Number into Table using StoredProcedure :
| import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class CallableInsertNum { static Connection con; public static void main(String args[]) throws Exception { try { Class.forName(“oracle.jdbc.driver.OracleDriver”); System.out.println(“Driver is ready”); con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”, “system”, “admin”); System.out.println(“Connection is ready”); String pro = “{call insertNum(?)}”; CallableStatement cs = con.prepareCall(pro); cs.setInt(1, 250); cs.executeUpdate(); System.out.println(“Record is inserted into test table through insertNum procedure!”); } finally { if(con != null) { con.close(); System.out.println(“Connection closed”); } } } } |