-
-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
Lecture1.7
-
Lecture1.8
-
Lecture1.9
-
Lecture1.10
-
Lecture1.11
-
Lecture1.12
-
Lecture1.13
-
Lecture1.14
-
Stored Procedures-Example
A Stored Procedure is set of SQL statements which performs one or more specific task. For calling Stored Procedure in JDBC, we uses CallableStatement
.
The following is the syntax of creating a stored procedure:
[php]
Create or [Replace] Procedure procedure_name
[(parameter[,parameter])]
IS
[Declarations]
BEGIN
executables
[EXCEPTION exceptions]
END [procedure_name]
[/php]
A stored procedure encapsulates the values of the following types of parameters:
IN- Refers to the parameter that can be referenced by the stored procedure.The value of this parameter cannot be overwritten by the procedure.
OUT- Refers to the parameter that cannot be referenced by the stored procedure.The value of this parameter can be overwritten by the procedure.
IN OUT- Refers to the parameter that can be referenced by the stored procedure.The value of this parameter can be overwritten by the procedure.
Executing a stored procedure with IN Parameters using CallableStatement
[php]
package com.tech2our.jdbc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class CallableStatementExample {
public static void main(String[] args) {
System.out.println("Insert records example using statement!");
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbctutorial", "root",
"tech2our");
try {
CallableStatement callableStatement = con.prepareCall("
{Call CreateEmployee(?,?,?,?)}");
callableStatement.setInt(1, 1001);
callableStatement.setString(2, "Tech2Our");
callableStatement.setString(3, "New Delhi");
callableStatement.setString(4, "9999999999");
callableStatement.execute();
System.out.println("Employee Created Successfully.");
con.close();
} catch (SQLException s) {
System.out.println("SQL statement is not executed!"+s);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
[/php]
Download code here(Eclipse)
Executing a stored procedure with OUT Parameters using CallableStatement
[php]
package com.tech2our.jdbc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class CallableStatementExample {
public static void main(String[] args) {
System.out.println("Insert records example using statement!");
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbctutorial", "root",
"tech2our");
try {
CallableStatement callableStatement = con.prepareCall("{Call getEmployee(?,?)}");
callableStatement.setInt(1, 1001);
callableStatement.registerOutParameter(2, Types.DOUBLE);
callableStatement.execute();
System.out.println("Employee Salary: "+callableStatement.getDouble(2));
con.close();
} catch (SQLException s) {
System.out.println("SQL statement is not executed!"+s);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
[/php]
Download code here(Eclipse)