读书人
栏目▼

用java调用oracle存储过程跟函数

发布时间： 2013-03-26 21:09:12 作者： rapoo

用java调用oracle存储过程和函数

创建测试用表：
CREATE TABLE USER
(
?? I_ID?? VARCHAR(50) NOT NULL,
?? I_NAME VARCHAR(50) NOT NULL
);

一：无返回值的存储过程
1，建立存储过程：
CREATE OR REPLACE
PROCEDURE TESTA(
??? PARA1 IN VARCHAR2,
??? PARA2 IN VARCHAR2)
AS
BEGIN
? INSERT INTO USER
??? (I_ID,I_NAME
??? ) VALUES
??? (PARA1, PARA2
??? );
END TESTA;

?

2，Java程序：

package com.hyq.src;

public class TestProcedureOne {

? public TestProcedureOne() {

? }

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

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

??? String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq ";

??? Statement stmt = null;

??? ResultSet rs = null;

??? Connection conn = null;

??? CallableStatement cstmt = null;

??? try {

????? Class.forName(driver);

????? conn =? DriverManager.getConnection(strUrl, " hyq ", " hyq ");

????? CallableStatement proc = null;

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

?????// proc = conn.prepareCall("begin HYQ.TESTA(?,?); end;");

????? 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 I_NAME INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;

?

2，Java程序：
package com.hyq.src;

public class TestProcedureTWO {

? public TestProcedureTWO() {

? }

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

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

??? String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";

??? Statement stmt = null;

??? ResultSet rs = null;

??? Connection conn = null;

??? try {

????? Class.forName(driver);

????? conn =? DriverManager.getConnection(strUrl, " hyq ", " hyq ");

????? CallableStatement proc = null;

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

????? proc = conn.prepareCall("begin HYQ.TESTB(?,?); end;");

????? proc.setString(1, "100");

????? proc.registerOutParameter(2, oracle.jdbc.OracleTypes.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();

????????? }

??????? }

????? }catch (SQLException ex1) {

????? }

??? }

? }

}

}


三：返回列表
由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.它是把游标（可以理解为一个指针），作为一个out 参数来返回值的.所以要分两部分:

1，建一个程序包：
CREATE OR REPLACE
PACKAGE TESTPACKAGE
AS
TYPE Test_CURSOR
IS
? REF
? CURSOR;
? END TESTPACKAGE;

2，建立存储过程：
CREATE OR REPLACE
PROCEDURE TESTC(
??? PARA1 IN VARCHAR2,p_CURSOR OUT TESTPACKAGE.Test_CURSOR)
IS
BEGIN
? OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB WHERE I_ID= PARA1;
END TESTC;


3，Java程序：
package com.hyq.src;

public class TestProcedureTHREE {

? public TestProcedureTHREE() {

? }

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

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

??? String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";

??? Statement stmt = null;

??? ResultSet rs = null;

??? Connection conn = null;

??? try {

????? Class.forName(driver);

????? conn =? DriverManager.getConnection(strUrl, "hyq", "hyq");

????? CallableStatement proc = null;

????? proc = conn.prepareCall("{ call hyq.testc(?,?) }");

?//?? proc = conn.prepareCall("begin hyq.testc(?,?); end;");

????? proc.setString(1, "100")

????? proc.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

????? proc.execute();

????? rs = (ResultSet)proc.getObject(2);

????? while(rs.next())

????? {

????????? System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");

????? }

??? }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
PACKAGE TESTPACKAGE
AS
TYPE Test_CURSOR
IS
? REF
? CURSOR;
END TESTPACKAGE;

2，建立函数：
CREATE OR REPLACE
FUNCTION TESTD(
??? PARA1 IN VARCHAR2)
RETURN TESTPACKAGE.Test_CURSOR
IS
rc TESTPACKAGE.Test_CURSOR;
BEGIN
? OPEN rc FOR SELECT * FROM HYQ.TESTTB WHERE I_ID= PARA1;
? return rc;
END TESTD;

?

3，Java程序：
package com.hyq.src;

public class TestProcedureTHREE {

? public TestProcedureTHREE() {

? }

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

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

??? String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";

??? Statement stmt = null;

??? ResultSet rs = null;

??? Connection conn = null;

??? try {

????? Class.forName(driver);

????? conn =? DriverManager.getConnection(strUrl, "hyq", "hyq");

????? CallableStatement proc = null;

????? proc = conn.prepareCall("{ call ? :=HYQ.TESTD(?) }");

?//?? proc = conn.prepareCall("begin ? :=HYQ.TESTD(?); end;");
?
?? proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

????? proc.setString(2, "100")

????? proc.execute();

????? rs = (ResultSet)proc.getObject(1);

????? while(rs.next())

????? {

????????? System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");

????? }

??? }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) {

????? }

??? }

? }

}


我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html

读书人网 >编程

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net