本文转自:http://liye9801.blog.163.com/blog/static/6019703200901244448950/
今天学习了一个Oracle中的存储过程,一开始便被如果返回结果集难住了.经Google后,找到很多资料,发现一种最简便的方式(见下面的存储过程部分): 存储过程建好后,怎么调试它又成了问题,它不能像MS-SqlServer一样exec,但是可以通过下面的SQL语句调用(见调用存储过程SQL版),主要知识点是 参考游标的使用. 后面附上.NET调用存储过程返回结果集的方法. JAVA调用的方法还没有进行测试,不过应该没什么问题. --存储过程返回结果集 create or replace procedure getResult(p_cur out sys_refcursor) as begin open p_cur for select * from T_MyAccount; --你的sql 语句 end getResult; --SQL调用存储过程 DECLARE TYPE mytable IS TABLE OF t_myAccount%ROWTYPE; l_data mytable; l_refc sys_refcursor; BEGIN --调用存储过程 getresult(l_refc); FETCH l_refc BULK COLLECT INTO l_data; CLOSE l_refc; FOR i IN 1 .. l_data.COUNT LOOP DBMS_OUTPUT.put_line (l_data (i).fAccount || l_data (i).fPassword); END LOOP; END; --.NET调用存储过程 ''' <summary> ''' .NET 调用存储过程并返回结果集 VB.NET版 ''' 要注意的一个重点是orc.Parameters.Add("Rec", OracleDbType.RefCursor, ParameterDirection.Output) ''' </summary> ''' <remarks></remarks> Private Sub ExecOracleProc() Dim constr As String = "Data Source=192.168.0.209:1521/XE;Persist Security Info=True;User ID=LSH;password=" Dim con As New Oracle.DataAccess.Client.OracleConnection(constr) Dim orc As New OracleCommand() Dim oda As New OracleDataAdapter(orc) con.Open() orc.Connection = con orc.CommandText = "getResult" orc.Parameters.Add("Rec", OracleDbType.RefCursor, ParameterDirection.Output) orc.CommandType = CommandType.StoredProcedure 'd.ExecuteNonQuery() Dim ds As New DataSet oda.Fill(ds) Me.DataGridView2.DataSource = ds.Tables(0) con.Close() End Sub Java中调用存储过程: import java.sql.*; import oracle.jdbc.*; public class TestResultSet { public TestResultSet() { try { DriverManager.registerDriver (new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection(”jdbc:oracle:oci:@w2k1″, “scott”, “tiger”); CallableStatement stmt = conn.prepareCall(”BEGIN GetEmpRS(?, ?); END;”); stmt.setInt(1, 30); // DEPTNO stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR stmt.execute(); ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2); while (rs.next()) { System.out.println(rs.getString(”ename”) + “:” + rs.getString(”empno”) + “:” + rs.getString(”deptno”)); } rs.close(); rs = null; stmt.close(); stmt = null; conn.close(); conn = null; } catch (SQLException e) { System.out.println(e.getLocalizedMessage()); } } public static void main (String[] args) { new TestResultSet(); } }