tune oracle java stored procedure result set

sponsored links
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
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of tune oracle java stored procedure result set

  • In the servlet use Bean

    According to Sun's definition, JavaBean is a reusable software components. In fact JavaBean is a Java class, through the package into a property and methods of treatment of a function or a business object, referred to as bean. Because JavaBean is ...

  • hibernate generic generic DAO

    package org.lzpeng.dao; import java.io.Serializable; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.Criterion; import org.springside.modules.orm.hibernate.Page; /** * * @version 2009-1-10 *

  • Servlet brief introduction

    Servlet brief introduction: Servlet is a small application server Are used to complete the B / S architecture, the client requests the response to treatment Platform independence, performance, able to run thread Servlet API for Servlet provides the s ...

  • can not be represented as java.sql.Timestamp

    Development of procedures for the use of hibernate when, some time there is no need to fill in the fields, but after the hibernate query time reported "Java.sql.SQLException: Value'0000-00-00 'can not be represented as java.sql.Timestamp ...

  • First Hibernate Example

    Curd a simple example. Source does not contain the dependent libraries, or playing too much of the package. PO object Note: One must have the default constructor 2 non-final modified. Otherwise useless lazy loading. UserDAOImpl category code, and other co

  • Hibernate annotation using notebook

    These are the basic common @Entity --Declared an entity bean @Table(name="promotion_info") --For the entity bean mapping for the specified table (Table name ="promotion_info) @Id --Declare that the identifying attribute of the entity bean @GeneratedValue

  • Struts2 + hibernate + spring problem user log in

    dao layer services layer action jsp <tr> <td align="center"> <b> user name: </ b> </ td> <td> <s: textfield name = "czyNumber" cssClass = "textstyle" theme = "simple" size = &q

  • The level Hibernate cache

    Hibernate cache level: (1) a cache is very short and the session life cycle consistent, also known as session-level cache-level cache or transaction-level cache (2) Ways of Supporting level cache: get (); load (); iterator (); only entity object cach ...

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries