Small user experience (2)-Oracle uses a cursor to support result sets

Oracle stored procedures support result sets,

There is no research yet. For large data volume queries, this method is used to directly return oracledatareader,

Is it faster than using other methods (such as views.

The following describes how to use datareader to access the result set returned by the Stored Procedure select_job_history.

The following are package specifications:

Create or new package select_job_history

Type t_cursor is ref cursor;

Procedure getjobhistorybyemployeeid

(

P_employee_id in number,

Cur_jobhistory out t_cursor

);

End select_job_history;

The package body defines a process in which the result set of the specified employee's work experience is retrieved and returned as the ref cursor output parameter:

Create or new package body select_job_history

Procedure getjobhistorybyemployeeid

(

P_employee_id in number,

Cur_jobhistory out t_cursor

)

Is

Begin

Open cur_jobhistory

Select * From job_history

Where employee_id = p_employee_id;

End getjobhistorybyemployeeid;

End select_job_history;

BelowCodeExecute this process, create a datareader based on the result set, and output the datareader content to the console.

// Create connection

Oracleconnection conn = new oracleconnection ("Data Source = oracledb;

User ID = userid; Password = password ;");

// Create the command for the stored procedure

Oraclecommand cmd = new oraclecommand ();

Cmd. Connection = conn;

Cmd. commandtext = "select_job_history.getjobhistorybyemployeeid ";

Cmd. commandtype = commandtype. storedprocedure;

// Add the parameters for the stored procedure including the ref cursor

// To retrieve the result set

Cmd. Parameters. Add ("p_employee_id", oracletype. Number). value = 101;

Cmd. Parameters. Add ("cur_jobhistory", oracletype. cursor). Direction =

Parameterdirection. output;

// Open the connection and create the datareader

Conn. open ();

Oracledatareader DR = cmd. executereader ();

// Output the results and close the connection.

While (dr. Read ())

{

For (INT I = 0; I <dr. fieldcount; I ++)

Console. Write (Dr [I]. tostring () + ";");

Console. writeline ();

}

Conn. Close ();

For the default installation of the HR architecture, the console output shows the fields of each record of employee 101 (separated by semicolons ):

101; 12:00:00 am; 10/27/1993 12:00:00 am; ac_account; 110;

101; 10/28/1993 12:00:00 am; 3/15/1997 12:00:00 am; ac_mgr; 110;