The data is based on an Oracle database Scott/tiger the EMP (employee) and dept (departmental) tables under the demo account:
CREATE TABLE DEPT
(
DEPTNO number (2) NOT null primary key,
dname VARCHAR2 (),
LOC VARCHAR2 ( )
)
CREATE TABLE EMP
(
EMPNO number (4) NOT NULL primary key,
ename VARCHAR2,
JOB VARCHAR2 (9),
MGR number (4),
hiredate DATE,
SAL number (7,2),
COMM number (7,2)
, DEPTNO number (2),
constraint Fk_deptno foreign key (DEPTNO) references DEPT (DEPTNO)
)
Oracle Stored Procedure Code:
Create or replace PROCEDURE searchempbydept (
indeptno in number,
empcur out Sys_refcursor,
errormsg out varchar)
is
BEGIN
errormsg:= ';
OPEN empcur for
SELECT * from
emp
WHERE deptno = indeptno ORDER by
Empno;
EXCEPTION when
others THEN
errormsg:= sqlerrm;--is an exception number, Sqlcode is the details end sqlerrm of the exception
;
Use the PL/SQL procedure statement to test the above stored procedure:
DECLARE
errormsg varchar (1000);
Empcur Sys_refcursor;
EMP Scott.emp%rowtype;
Begin
Searchempbydept (10,empcur, errormsg);
If errormsg is not null then
dbms_output.put_line (errormsg);
End If;
Loop
fetch empcur into EMP;
EXIT when Empcur%notfound;
Dbms_output.put_line (emp.ename);
End Loop;
Close empcur;
End
Java calling code:
public class DBHelper {private Connection conn = null; Public Connection getconnection () throws ClassNotFoundException, SQLException {class.forname ("Oracle.jdbc.driver .
Oracledriver ");
conn = Drivermanager.getconnection ("Jdbc:oracle:thin:@192.168.1.6:1521:ntcsoft", "Scott",
"Tiger");
Return conn;
} public class Calloracleprocedure {public static void main (String args[]) {ResultSet rs = null;
CallableStatement st = null;
Connection con = null;
try {con = new DBHelper (). getconnection ();
String sql = "Call searchempbydept (?,?,?)";
st = Con.preparecall (SQL); St.setint (1, 20);//Set up the Reference Department number 20//register return type parameter.
The CURSOR type is not defined in Java.sql.Tyes, and St.registeroutparameter (2, Oracle.jdbc.driver.OracleTypes.CURSOR) is found in the driver package.
St.registeroutparameter (3, Types.varchar);
Boolean result = St.execute ();
Gets the return parameter RS = (ResultSet) st.getobject (2); STring msg = st.getstring (3); if (msg!= null) System.out.println (msg);//The Exception Information section is NULL to print System.out.println ("empno" + "" T "+" ename "+" "T" +
"Sal" + "" T "+" deptno ");
Output query Results StringBuilder output = new StringBuilder (); while (Rs.next ()) {Output.append (Rs.getint ("Empno")). Append ("" "T"). Append (Rs.getstring ("ename ). Append ("T"). Append (Rs.getdouble ("Sal"). Append ("" "T"). Append (Rs.getint ("Deptno
"));
System.out.println (Output.tostring ());
Output.delete (0, Output.length ());
output = null;
}catch (Exception e) {e.printstacktrace ();
}finally{try {if (rs!=null) rs.close ();
if (st!=null) st.close ();
if (con!=null) con.close ();
catch (SQLException e) {e.printstacktrace ();
}
}
}
}
Note that the Scott demo account for the Oracle database is locked by default and needs to be unlocked before it is used:
Alter user Scott account unlock;
Then log on to the database server with Scott/tiger, prompting the password to expire and asking you to enter the new password immediately.