tune oracle java stored procedure result set

Jul 7 posted in Database (RSS), comments.

Advertisements

A: No return value of stored procedure call

Stored procedure:

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

BEGIN

INSERT INTO DBO.EMP (ID, NAME) VALUES (PARA1, PARA2);

END PRO_1;

Java code:

package com.icesoft.service;

import java.sql .*;
import java.sql.ResultSet;

public class CallProcedureTest1 (

public CallProcedureTest1 () (
super ();
)

public static void main (String [] args) (

String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc: oracle: thin: @ 127.0.0.1:1521: orcl";
String user = "admin";
String pwd = "password";

Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;

try (
Class.forName (driver);
conn = DriverManager.getConnection (url, user, pwd);
cs = conn.prepareCall ("(call DBO.PRO_1 (?,?))");
cs.setString (1, "10");
cs.setString (2, "Peter");
cs.execute ();
) Catch (SQLException e) (
e.printStackTrace ();
) Catch (Exception e) (
e.printStackTrace ();
) Finally (
try (
if (rs! = null) (
rs.close ();
)
if (cs! = null) (
cs.close ();
)
if (conn! = null) (
conn.close ();
)
) Catch (SQLException e) (
)
)

)

)

Note, the stored procedure PRO_1 used in the table EMP (ID, NAME), be built in advance

Second: There is a stored procedure return value (non-result set)

Stored procedure:

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

BEGIN

SELECT INTO PARA2 FROM EMP WHERE

END PRO_2;

Java code:

package com.icesoft.service;

import java.sql .*;

public class CallProcedureTest2 (

public CallProcedureTest2 () (
super ();
)

public static void main (String [] args) (

String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc: oracle: thin: @ 127.0.0.1:1521: orcl";
String user = "admin";
String pwd = "password";
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;

try (
Class.forName (driver);
conn = DriverManager.getConnection (url, user, pwd);
cs = conn.prepareCall ("(call DBO.PRO_2 (?,?))");
cs.setString (1, "10");
cs.registerOutParameter (2, Types.VARCHAR);
cs.execute ();

String name = cs.getString (2);
System.out.println ("name:" + name);
) Catch (SQLException e) (
e.printStackTrace ();
) Catch (Exception e) (
e.printStackTrace ();
) Finally (
try (
if (rs! = null) (
rs.close ();
)
if (cs! = null) (
cs.close ();
)
if (conn! = null) (
conn.close ();
)
) Catch (SQLException e) (
)
)

)

)

Note: cs.getString (2) the value of 2 is not arbitrary, but rather out and stored procedures in the corresponding column, and if out in the first place, that is proc.getString (1), if the third Location is proc.getString (3), of course, can also have multiple return values, that is, again, a few more out parameter.

3: Back to list

As the oracle stored procedure does not return value, it's all the return values are replaced by out parameters, the list also no exception, but because it is set, it can not use normal parameters, we must use pagkage the. Therefore, from two part

1. To build a package. As follows:

CREATE OR REPLACE PACKAGE MYPACKAGE AS

TYPE MY_CURSOR IS REF CURSOR;

end MYPACKAGE;

2. The establishment of a stored procedure, as follows:

CREATE OR REPLACE PROCEDURE PRO_3 (p_CURSOR out MYPACKAGE.MY_CURSOR) IS

BEGIN

OPEN p_CURSOR FOR SELECT * FROM DBO.EMP;

END PRO_3;

You can see, it is the cursor (which can be interpreted as a pointer), as a out parameter to return value.

Java code:

package com.icesoft.service;

import java.sql .*;
import java.sql.ResultSet;

public class CallProcedureTest2 (

public CallProcedureTest2 () (
super ();
)

public static void main (String [] args) (

String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc: oracle: thin: @ 127.0.0.1:1521: orcl";
String user = "admin";
String pwd = "password";

Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try (
Class.forName (driver);
conn = DriverManager.getConnection (url, user, pwd);
cs = conn.prepareCall ("(call DBO.PRO_3 (?))");
cs.registerOutParameter (1, oracle.jdbc.OracleTypes.CURSOR);
cs.execute ();
rs = (ResultSet) cs.getObject (1);

while (rs.next ()) (
System.out.println ("\ t" + rs.getString (1) + "\ t"
+ Rs.getString (2) + "\ t");
)
) Catch (SQLException e) (
e.printStackTrace ();
) Catch (Exception e) (
e.printStackTrace ();
) Finally (
try (
if (rs! = null) (
rs.close ();
if (cs! = null) (
cs.close ();
)
if (conn! = null) (
conn.close ();
)
)
) Catch (SQLException e) (
)
)

)

) This article comes from: Development Institute http://edu.codepub.com Original link: http://edu.codepub.com/2010/0529/23086.php