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

This article focuses on "using java to call oracle stored procedures summary", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the summary of calling oracle stored procedures with java.

Summary of calling oracle stored procedures with java

/ / 1. Call+ package name + stored procedure name (for incoming or outgoing values? )

String str= "{call SMSBUSINESS.deleteZhZMember (?)}"

/ / 2. Establish a connection

Connection conn=null

Conn=DriverManager.getConnection ()

/ / 3. Use the java.sql.* class

CallableStatement cs=conn.prepareCall (str)

/ / 4. Pass in value

Cs.setInt (1jinid)

Cs.setInt (2Mae Murray 2)

/ / 5. Set the out value

Cs.registerOutParameter (3meme Types.NUMERIC)

/ / 6. Execution

Cs.excuse ()

/ / 7. Take out the out value

Int flag=cs.getInt (3)

/ / 8. Close the connection

Conn.close ()

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; / / create objects that execute stored procedures

Proc = conn.prepareCall ("{call HYQ.TESTA (,)}"); / / sets the stored procedure call as the keyword.

Proc.setString (1,100); / / set the first input parameter

Proc.setString (2, "TestOne"); / / set the second input parameter

Proc.execute (); / / 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 (?)}"); / / set the stored procedure

Proc.setString (1,100,100); / / set the first parameter input parameter

Proc.registerOutParameter (2, Types.VARCHAR); / / the second parameter output parameter, which is of type VARCHAR

Proc.execute (); / / execute

String testPrint = proc.getString (2); / / get the output parameters

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 (?)}"); / / stored procedure hyq package

Proc.registerOutParameter (1 refine oracle. JDBC. OracleTypes. Cursor); / / sets the output parameter to be a cursor. The first parameter, the cursor type

Proc.execute (); / / execute

Rs = (ResultSet) proc.getObject (1); / / get that the first parameter is a cursor that is converted to a ResultSet type

While (rs.next ()) / / get data

{

System.out.println ("" + rs.getString (1) + "" + rs.getString (2) + "")

}

}

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

}

}

}

}

It should be noted here that the driver package of oracle must be placed in the class path before execution, otherwise an error will be reported.

At this point, I believe you have a deeper understanding of "summing up oracle stored procedures with java". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.