[Oracle] efficient PL/SQL programming (5)-call a stored procedure to return a result set

 

Oracle supports returning the result set after calling the stored procedure through the ref cursor. Using the cursor is much better than returning array variables in terms of memory consumption and time!

Example: Create a package for the database

Create or replace package ref_cur_demo is
Type RC is ref cursor;
Procedure ref_cursor (p_owner in varchar2, p_cursor in out RC );
End ref_cur_demo;

Create or replace package body ref_cur_demo is

Procedure ref_cursor (p_owner in varchar2, p_cursor in out RC)
Is
Begin
Open p_cursor for select object_name, object_type from all_objects where owner = p_owner and rownum <3;
End;

End ref_cur_demo;

C # is used to create a small application. The main code is as follows:

Oracle. dataaccess. Client. oracleconnection oracleconnection1 = new oracleconnection ("Data Source = precolm2; user id = colmtest; Password = colmtest ");
Oracleconnection1.open ();
String strsql = @ "ref_cur_demo.ref_cursor ";

Oracle. dataaccess. Client. oracledataadapter da = new Oracle. dataaccess. Client. oracledataadapter ();
Oracle. dataaccess. Client. oraclecommand cmd = new Oracle. dataaccess. Client. oraclecommand (strsql, oracleconnection1 );
Cmd. commandtype = commandtype. storedprocedure;

Oracle. dataaccess. Client. oracleparameter pram = new Oracle. dataaccess. Client. oracleparameter ("p_owner", Oracle. dataaccess. Client. oracledbtype. varchar2 );
Pram. value = "colmtest ";
Cmd. Parameters. Add (PRAM );

Oracle. dataaccess. Client. oracleparameter pram1 = new Oracle. dataaccess. Client. oracleparameter ("p_cursor", Oracle. dataaccess. Client. oracledbtype. refcursor );
Pram1.direction = parameterdirection. output;
Cmd. Parameters. Add (pram1 );

Da. selectcommand = cmd;

Dataset DS = new dataset ();

Da. Fill (DS );

This. datagrid1.datasource = Ds. Tables [0]. defaultview;