Java calls to Oracle stored procedures

--Write a procedure that asks for an employee number and returns the employee's name.
Create or Replace procedure Getnamebyno (no in number, name out VARCHAR2) is
Begin
Select ename into name from emp where empno = no;
End

-Enter the department number and return to all employees in the department
--Build a package first, define a cursor type
Create or Replace package pkg_cursor is
Type my_cursor_type is REF CURSOR;
End Pkg_cursor;

--Creation process
Create or Replace procedure Getbydeptno (depno in number, emp_cursor out Pkg_cursor.my_cursor_type) is
Begin
Open Emp_cursor for
SELECT * from emp where deptno = Depno;

End

2) procedure called in Java

A. register the driver class and get the database connection

B. Invoke the SQL statement (enclosed in {} ) with the procedure to get The CallableStatement object.

C. set the input parameters , such as:cs.setint (1, 7788);

D. register the output parameters , such as:cs.Registeroutparameter(2, Oracle.jdbc.OracleTypes.VARCHAR);

E. implementation process Cs.execute ();

F. get the process return results , such as:ResultSet rs = (ResultSet) cs.getobject (2);

G. finally , close the resource in finally.

Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;




public class TestPro1 {
static final int empno = 7788;
static final int depno = 10;
public static void Main (string[] args) {
Connection con = null;
CallableStatement cs = null;

try {
Registering the JDBC Driver class
Class.forName ("Oracle.jdbc.driver.OracleDriver");
Get connections
Con = DriverManager.
Getconnection ("Jdbc:oracle:thin: @localhost: 1521:orcltest", "Test", "MM");
/************************* Getnamebyno Start ****************************/
CS = Con.preparecall ("{Call Getnamebyno (?,?)}");
Setting parameters
Cs.setint (1, empno);
Registering Output parameters
Cs.registeroutparameter (2, Oracle.jdbc.OracleTypes.VARCHAR);
Execution process
Cs.execute ();

Get results
String ename = cs.getstring (2);
System.out.println ("number" + Empno + "name is:" + ename);
The name number 7788 is: SCOTT
/************************* Getnamebyno End ****************************/

/************************* Getbydeptno Start ****************************/
CS = Con.preparecall ("{Call Getbydeptno (?,?)}");
Setting parameters
Cs.setint (1, depno);
Registering Output parameters
Cs.registeroutparameter (2, Oracle.jdbc.OracleTypes.CURSOR);
Cs.execute ();
Get the result set
ResultSet rs = (ResultSet) cs.getobject (2);
SYSTEM.OUT.PRINTLN ("The department number is" + Depno + "All employees are as follows:");
while (Rs.next ()) {
SYSTEM.OUT.PRINTLN ("Employee Number:" + rs.getint (1) +
", Employee Name:" + rs.getstring (2));
}
/************************* Getbydeptno End ****************************/
Close the resource (should be at finally, as the example is closed here)
Cs.close ();
Con.close ();
} catch (Exception e) {
E.printstacktrace ();
}
}


}

Java calls to Oracle stored procedures