tune oracle java stored procedure result set

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

分类:Database 时间:2010-07-07 人气:184
分享到:
blog comments powered by Disqus

相关文章

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 黔ICP备15002463号-1

processed in 0.617 (s). 12 q(s)