JAVA调用存储过程:
几经挫折,但总算搞通了,为了避免后来者少走弯路,特记述与此,同时亦对自己进行鼓励。
创建测试用表:
CREATE
TABLE
T_TEST(
I_ID
NVARCHAR
(
20
),
I_NAME
NVARCHAR
(
20
)
)
一:无返回值的存储过程
1、在SQL*PLUS中建立存储过程:
CREATE
OR
REPLACE
PROCEDURE
TESTA(PARA1
IN
VARCHAR2
,PARA2
IN
VARCHAR2
)
AS

BEGIN

INSERT
INTO
T_TEST (I_ID,I_NAME)
VALUES
(PARA1, PARA2);

END
TESTA;
2、相应的JAVA程序:
package
com.my.test;

import
java.sql.
*
;

import
java.io.OutputStream;

import
java.io.Writer;

import
java.sql.PreparedStatement;

import
java.sql.ResultSet;

import
oracle.jdbc.driver.
*
;



public
class
TestProcedureOne
...
{

public TestProcedureOne() ...{

}


public static void main(String[] args )...{

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

String strUrl = "jdbc:oracle:thin:@192.168.10.216:1521:ctbu";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;

CallableStatement cstmt = null;



try ...{

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, "dbname", "password");

CallableStatement proc = null;

proc = conn.prepareCall("{ call dbname.TESTA(?,?) }");

proc.setString(1, "100");

proc.setString(2, "TestOne");

proc.execute();

}


catch (SQLException ex2) ...{

ex2.printStackTrace();

}


catch (Exception ex2) ...{

ex2.printStackTrace();

}


finally...{


try ...{


if(rs != null)...{

rs.close();


if(stmt!=null)...{

stmt.close();

}


if(conn!=null)...{

conn.close();

}

}

}


catch (SQLException ex1) ...{

}

}

}

}
二:有返回值的存储过程(非列表)
1、存储过程为:
CREATE
OR
REPLACE
PROCEDURE
TESTB(PARA1
IN
VARCHAR2
,PARA2 OUT
VARCHAR2
)
AS

BEGIN

SELECT
INTO
PARA2
FROM
TESTTB
WHERE
I_ID
=
PARA1;

END
TESTB;
2、JAVA代码:
package
com.my.test;

public
class
TestProcedureTWO
...
{


public TestProcedureTWO() ...{

}


public static void main(String[] args )...{

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

String strUrl = "jdbc:oracle:thin:@192.168.10.216:1521:ctbu";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;


try ...{

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, "dbname", "password");

CallableStatement proc = null;

proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }");

proc.setString(1, "100");

proc.registerOutParameter(2, Types.VARCHAR);

proc.execute();

String testPrint = proc.getString(2);

System.out.println("=testPrint=is="+testPrint);

}


catch (SQLException ex2) ...{

ex2.printStackTrace();

}


catch (Exception ex2) ...{

ex2.printStackTrace();

}


finally...{


try ...{


if(rs != null)...{

rs.close();


if(stmt!=null)...{

stmt.close();

}


if(conn!=null)...{

conn.close();

}

}

}