下面是测试代码和数据表存储过程
import java.sql.*;
/**
数据库表
CREATE TABLE TB_MONITOR (
ID NUMBER(20) NOT NULL,
MONITOR_OBJECT_CODE CHAR(10),
MONITOR_OBJECT_NAME VARCHAR2(180),
BRANCH_CODE CHAR(10),
SYSTEM_CODE CHAR(10),
DIREC_NAME VARCHAR2(180),
FILE_NUM NUMBER,
STATUS CHAR(10),
BEGIN_TIME TIMESTAMP,
END_TIME TIMESTAMP,
DATA_TIME TIMESTAMP,
MONITOR_TIME TIMESTAMP,
REMARK VARCHAR2(180),
CONSTRAINT PK_TB_MONITOR PRIMARY KEY (ID)
);
*/
public class OracleProcedureCall {
private static String driver = "oracle.jdbc.driver.OracleDriver";
private static String strUrl = "jdbc:oracle:thin:@192.168.1.90:1521:odsdb";
private static String userName = "odsdb";
private static String password = "ods";
private static Connection conn = null;
//获得数据库连接
public static Connection getConnection(){
try{
if(conn == null){
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, userName, password);
}
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
return conn;
}
/*无返回值的存储过程
CREATE OR REPLACE PROCEDURE AddMonInfo
(
n_id tb_monitor.id%TYPE,
n_oc tb_monitor.monitor_object_code%TYPE,
n_on tb_monitor.monitor_object_name%TYPE,
n_bc tb_monitor.branch_code%TYPE,
n_sc tb_monitor.system_code%TYPE,
n_fn tb_monitor.file_num%TYPE,
n_st tb_monitor.status%TYPE,
n_rk tb_monitor.remark%TYPE
)
AS
BEGIN
--向表中插入数据
INSERT INTO tb_monitor(id,monitor_object_code,monitor_object_name,branch_code,system_code,file_num,status,remark)
VALUES(n_id,n_oc,n_on,n_bc,n_sc,n_fn,n_st,n_rk);
END AddMonInfo;
*/
public void testInsert(){
try {
CallableStatement proc = getConnection().prepareCall("{ call odsdb.AddMonInfo(?,?,?,?,?,?,?,?) }");
proc.setString(1, "100");
proc.setString(2, "o_code");
proc.setString(3, "o_name");
proc.setString(4, "b_code");
proc.setString(5, "s_code");
proc.setString(6, "1");
proc.setString(7, "status");
proc.setString(8, "remark");
proc.execute();
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(conn!=null){
conn.close();
}
}
catch (SQLException ex1) {
}
}
}
/*有返回值的存储过程(非列表)
CREATE OR REPLACE PROCEDURE QueryMonInfo
(
n_id IN tb_monitor.id%TYPE,
n_oc OUT VARCHAR2,
n_on OUT VARCHAR2
) AS
BEGIN
SELECT monitor_object_code, monitor_object_name into n_oc,n_on FROM tb_monitor WHERE ID= n_id;
END QueryMonInfo;
*/
public String[] testQueryArray(){
String[] resultArr = null;
try {
CallableStatement proc = getConnection().prepareCall("{ call odsdb.QueryMonInfo(?,?,?) }");
proc.setInt(1, 100);
proc.registerOutParameter(2, Types.VARCHAR);
proc.registerOutParameter(3, Types.VARCHAR);
proc.execute();
resultArr = new String[2];
resultArr[0] = proc.getString(2);
resultArr[1] = proc.getString(3);
System.out.println("=code=is= "+resultArr[0]);
System.out.println("=name=is= "+resultArr[1]);
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(conn!=null){
conn.close();
}
}
catch (SQLException ex1) {
ex1.printStackTrace();
}
}
return resultArr;
}
/*返回列表,需要使用package方式
先创建Package
CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
然后创建procedure
CREATE OR REPLACE PROCEDURE QueryMonResultSet(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM tb_monitor;
END QueryMonResultSet;
*/
public ResultSet testQueryResultSet(){
ResultSet rs = null;
try {
CallableStatement proc = getConnection().prepareCall("{ call odsdb.QueryMonResultSet(?) }");
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
while(rs.next()) {
System.out.println("ID:" + rs.getString(1) + "/tCODE:"+rs.getString(2)+"");
}
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
return rs;
}
public static void main(String[] args){
OracleProcedureCall call = new OracleProcedureCall();
call.testQueryResultSet();
}
}
//----2222222222222222------------------------------------------------------------------------------------------------------------------
import java.sql.*;
public class n {
public static void main(String args [])
{
Connection myConnection = null; //与特定数据库的连接(会话)。在连接上下文中执行 SQL 语句并返回结果
Statement myStatement = null; //用于执行静态 SQL 语句并返回它所生成结果的对象
CallableStatement cs;
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); //管理一组 JDBC 驱动程序的基本服务并向 DriverManager 注册给定驱动程序
myConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hr", "hr"); // 试图建立到给定数据库 URL 的连接
//myConnection.setAutoCommit(false); //将此连接的自动提交模式设置为给定状态
cs = myConnection.prepareCall("{call sencond(?)}");
cs.registerOutParameter(1, Types.VARCHAR);
//cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
String retValue = cs.getString(1);
System.out.println(retValue);
}
catch(SQLException e)
{
System.out.println("Error code = " + e.getErrorCode());
System.out.println("Error message = " + e.getMessage());
System.out.println("Error state = " + e.getSQLState());
}
finally
{
try
{
if (myStatement != null)
{
myStatement.close(); //立即释放此 Statement 对象的数据库和 JDBC 资源,而不是等待该对象自动关闭时发生此操作
}
if (myConnection != null)
{
myConnection.close(); //立即释放此 Connection 对象的数据库和 JDBC 资源,而不是等待它们被自动释放
}
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
核心部分:
CallableStatement cs;
cs = myConnection.prepareCall("{call sencond(?,?)}");
cs.registerOutParameter(1, Types.VARCHAR); //out
cs.setString(2, "a string"); //in
cs.execute(); //run
String retValue = cs.getString(1);
System.out.println(retValue);
过程如下:
create or replace procedure
sencond(name out varchar2,search in varchar2)
as
begin
select name from emp where name=search;
end;
/