Shulou(Shulou.com)06/02 Report--

Editor to share with you how to call ORACLE stored procedures in java, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

One: stored procedures with no return value

The stored procedure is:

CREATE OR REPLACE PROCEDURE TESTA (PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) AS

BEGIN

INSERT INTO HYQ.B_ID (PARA1 ID _ I _ name) VALUES (PARA1, PARA2)

END TESTA

Then, when you call it in java, use the following code:

Package com.hyq.src

Import java.sql.*

Import java.sql.ResultSet

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.setString (1,100,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 (stmtasking null) {

Stmt.close ()

}

If (connexual null) {

Conn.close ()

}

}

}

Catch (SQLException ex1) {

}

}

}

}

Of course, this first requires the creation of a table TESTTB, in which there are two fields.

Two: stored procedures with return values (non-list)

The stored procedure is:

CREATE OR REPLACE PROCEDURE TESTB (PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS

BEGIN

SELECT INTO PARA2 FROM TESTTB WHERE ID = PARA1

END TESTB

When called in java, use the following code:

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.setString (1,100,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 (stmtasking null) {

Stmt.close ()

}

If (connexual null) {

Conn.close ()

}

}

}

Catch (SQLException ex1) {

}

}

}

}

}

Note that the value 2 in proc.getString (2) here is not arbitrary, but corresponds to the out column in the stored procedure. If out is in the first position, it is proc.getString (1), and if it is the third position, it is proc.getString (3). Of course, there can also be multiple return values at the same time, that is, add a few more out parameters.

Three: return the list

Because the oracle stored procedure has no return value, all its return values are replaced by out parameters, and the list is no exception, but because it is a collection, you can't use general parameters, you have to use pagkage. So it's divided into two parts.

1, build a package. As follows:

CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE Test_CURSOR IS REF CURSOR

End TESTPACKAGE

2. Establish a stored procedure, which is:

CREATE OR REPLACE PROCEDURE TESTC (p_CURSOR out TESTPACKAGE.Test_CURSOR) IS

BEGIN

OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB

END TESTC

As you can see, it returns the value of the cursor (which can be understood as a pointer) as an out parameter.

When called in java, use the following code:

Package com.hyq.src

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 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.registerOutParameter (1century oracle.jdbc.OracleTypes.CURSOR)

Proc.execute ()

Rs = (ResultSet) proc.getObject (1)

While (rs.next ())

{

System.out.println ("")

}

}

Catch (SQLException ex2) {

Ex2.printStackTrace ()

}

Catch (Exception ex2) {

Ex2.printStackTrace ()

}

Finally {

Try {

If (rs! = null) {

Rs.close ()

If (stmtasking null) {

Stmt.close ()

}

If (connexual null) {

Conn.close ()

}

}

}

Catch (SQLException ex1) {

}

}

}

}

These are all the contents of the article "how to call ORACLE stored procedures in java". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.