acle 10g Release2 new features of the Ref Cursor

  Ref Cursor is our definition of the server side of the result set of reference.    When we open a Ref Cursor the time, no data returned to the client, on the contrary, data on the server's address will be returned to the client.    So users can decide what their own time and in that way through the Ref Cursor Ququ data. 
  In a previous version of ODP.NET, we can Ref Cursor from the data, but we can not Ref Cursor as a Input parameters passed to the PL / SQL process of storage and storage function.    However, in Oracle Database 10g Release2, we can be very simple to Ref Cursor as Input parameters passed to the PL / SQL process of storage and storage function.    This is the Oracle Database 10g Release2 the new features. 
  We turn next to routines on the way to introduce to you the new features. 
  We have to prepare the database in the database and generate a form of a package, we will use the following example. 
  Please use HR users log database, and then run the following script. 
  create table processing_result 
(
  status varchar2 (64) 
);
  create or replace package cursor_in_out as 
  type emp_cur_type is ref cursor return employees% rowtype; 
  procedure process_cursor (p_cursor in emp_cur_type); 
end;
/
  create or replace package body cursor_in_out as 
  procedure process_cursor (p_cursor in emp_cur_type) is 
  employee employees% rowtype; 
begin
loop
  fetch p_cursor into employee; 
  exit when p_cursor% notfound; 
  insert into processing_result 
  values ( 'Processed employee #' | | 
  employee.employee_id | | ':' | | 
  employee.first_name | | '' | | 
employee.last_name);
  end loop; 
end;
end;
/
  Create. NET code database is ready, then we are prepared to create. NET code. 
  using System; 
  using System.Data; 
  using Oracle.DataAccess.Client; 
  using Oracle.DataAccess.Types; 
  namespace CursorInCursorOut 
  ( 
  / / / <summary> 
  / / / Summary description for Class1. 
  / / / </ Summary> 
  class Class1 
  ( 
  / / / <summary> 
  / / / The main entry point for the application. 
  / / / </ Summary> 
[STAThread]
  static void Main (string [] args) 
  ( 
  / / Create connection to database 
  / / Change for your environment 
  string constr = "User Id = hr; Password = hr; Data Source = oramag; Pooling = false"; 
  OracleConnection con = new OracleConnection (constr); 
  con.Open (); 
  / / Command and parameter objects to get ref cursor 
  OracleCommand cmd = con.CreateCommand (); 
  cmd.CommandText = "begin open: 1 for select * from employees where manager_id = 101; end;"; 
  OracleParameter p_rc = cmd.Parameters.Add ( "p_rc", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output); 
  / / Get the ref cursor 
  cmd.ExecuteNonQuery (); 
  / / Clear parameters to reuse 
  cmd.Parameters.Clear (); 
  / / Command and parameter objects to pass ref cursor 
  / / As an input parameter 
  cmd.CommandText = "cursor_in_out.process_cursor"; 
  cmd.CommandType = CommandType.StoredProcedure; 
  OracleParameter p_input = cmd.Parameters.Add ( "p_input", OracleDbType.RefCursor, p_rc.Value, ParameterDirection.Input); 
  / / Process the input cursor 
  cmd.ExecuteNonQuery (); 
  / / Clean up objects 
  p_input.Dispose (); 
  p_rc.Dispose (); 
  cmd.Dispose (); 
  con.Dispose (); 
  ) 
  ) 
  ) 

  Operation of the above code, this procedure does not export, but we can SQL * PLUS can very easily see the following output. 
  SQL> select * from processing_result; 
STATUS
—————————————-
  Processed employee # 108: Nancy Greenberg 
  Processed employee # 200: Jennifer Whalen 
  Processed employee # 203: Susan Mavris 
  Processed employee # 204: Hermann Baer 
  Processed employee # 205: Shelley Higgins 
  5 rows selected. 
  I am here just give us a very simple example, we hope that the full application of the new features of Oracle Database, so that your project a more stable, more efficient. 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Tags: