java调用oracle job,Java调用Oracle的存储过程、存储函数

存储过程

create or replace procedure queryEmpInfo(eno in number,

pename out varchar2,

psal out number,

pjob out varchar2)

as

begin

select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;

end;

/

Java代码

@Test

public void testProcedure(){

//{call [(,, ...)]}

String sql = "{call queryEmpInfo(?,?,?,?)}";

Connection conn = null;

CallableStatement call = null;

try {

conn = JDBCUtils.getConnection();

call = conn.prepareCall(sql);

//对于in参数，赋值

call.setInt(1, 7839);

//对于out参数,申明

call.registerOutParameter(2, OracleTypes.VARCHAR);

call.registerOutParameter(3, OracleTypes.NUMBER);

call.registerOutParameter(4, OracleTypes.VARCHAR);

//执行

call.execute();

//取出结果

String name = call.getString(2);

double sal = call.getDouble(3);

String job = call.getString(4);

System.out.println(name);

System.out.println(sal);

System.out.println(job);

} catch (Exception e) {

e.printStackTrace();

}finally{

JDBCUtils.release(conn, call, null);

}

}

存储函数

create or replace function queryEmpIncome(eno in number)

return number

as

psal emp.sal%type;

pcomm emp.comm%type;

begin

select sal,comm into psal,pcomm from emp where empno=eno;

return psal*12+nvl(pcomm,0);

end;

/

Java代码

@Test

public void testFunction(){

// {?= call [(,, ...)]}

String sql = "{?=call queryEmpIncome(?)}";

Connection conn = null;

CallableStatement call = null;

try {

conn = JDBCUtils.getConnection();

call = conn.prepareCall(sql);

//对于out参数,申明

call.registerOutParameter(1, OracleTypes.NUMBER);

//对于in参数，赋值

call.setInt(2, 7839);

//执行

call.execute();

//取出结果

double income = call.getDouble(1);

System.out.println(income);

}catch (Exception e) {

e.printStackTrace();

}finally{

JDBCUtils.release(conn, call, null);

}

}

某个部门中 所有员工的所有信息，返回值是一个集合

--包头

CREATE OR REPLACE PACKAGE MYPACKAGE AS

type empcursor is ref cursor;

procedure queryEmpList(dno in number,empList out empcursor);

END MYPACKAGE;

--包体

CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS

procedure queryEmpList(dno in number,empList out empcursor) AS

BEGIN

open empList for select * from emp where deptno=dno;

END queryEmpList;

END MYPACKAGE;

Java代码

@Test

public void testCursor(){

String sql = "{call MYPACKAGE.queryEmpList(?,?)}";

Connection conn = null;

CallableStatement call = null;

ResultSet rs = null;

try {

conn = JDBCUtils.getConnection();

call = conn.prepareCall(sql);

//对于in参数，赋值

call.setInt(1, 20);

//对于out参数,申明

call.registerOutParameter(2, OracleTypes.CURSOR);

//执行

call.execute();

//取出结果

rs = ((OracleCallableStatement)call).getCursor(2);

while(rs.next()){

String name = rs.getString("ename");

double sal = rs.getDouble("sal");

System.out.println(name+"的薪水是"+sal);

}

} catch (Exception e) {

e.printStackTrace();

}finally{

JDBCUtils.release(conn, call, rs);

}

}

JDBCUtils

package demo.utils;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JDBCUtils {

private static String driver = "oracle.jdbc.OracleDriver";

private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";

private static String user = "scott";

private static String password = "tiger";

static{

//DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

try {

Class.forName(driver);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public static Connection getConnection(){

try {

return DriverManager.getConnection(url, user, password);

} catch (SQLException e) {

e.printStackTrace();

}

return null;

}

public static void release(Connection conn, Statement st, ResultSet rs){

if(rs != null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}finally{

rs = null;

}

}

if(st != null){

try {

st.close();

} catch (SQLException e) {

e.printStackTrace();

}finally{

st = null;

}

}

if(conn != null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}finally{

conn = null;

}

}

}

}