C # Call the Oracle Stored Procedure

I. OracleAspect

1. Create an oracle Stored Procedure

1) create a table

create table users(usesNo number,userName varchar(50));

2) create a package

Create or replace package multirefcursors

Type test_cursor is ref cursor;

Procedure getrecord (p_cursor in out test_cursor );

End multirefcursors;

/

 

3) create a stored procedure

Create or replace package body multirefcursors

Procedure getrecord (p_cursor in out test_cursor) is

V_ SQL varchar2 (1000 );

Begin

V_ SQL: = 'select * From users ';

Open p_cursor for v_ SQL;

End;

End;

/

4) add data

Insert into users values (1, 'ljp ');

Insert into users values (2, 'dfa ');

Insert into users values (3, 'dff ');

Insert into users values (1, 'ljp ');

Insert into users values (2, 'dfa ');

Insert into users values (3, 'dff ');

 

II,C #Aspect

 

Using system;

Using system. Collections. Generic;

Using system. LINQ;

Using system. text;

Using system. Data;

Using system. Data. oracleclient;

 

Namespace consoleapplication1

{

Class Program

{

Static void main (string [] ARGs)

{

Oracleconnection conn = new oracleconnection (@ "Data Source = orcl; user id = Scott; Password = tiger; Unicode = true ");

Conn. open ();

Oraclecommand cmd = new oraclecommand ();

Cmd. Connection = conn;

Cmd. commandtype = commandtype. storedprocedure;

 

// Call the stored procedure to query data

Cmd. commandtext = "multirefcursors. getrecord ";

Oracleparameter [] parameters = new oracleparameter [2];

// Note that the userno, p_cursor parameter name and type number are the same as those in the stored procedure.

Parameters [0] = new oracleparameter ("userno", oracletype. int32 );

Parameters [1] = new oracleparameter ("p_cursor", oracletype. cursor );

Parameters [0]. value = 3;

Parameters [0]. Direction = parameterdirection. input;

Parameters [1]. Direction = parameterdirection. output;

 

Foreach (oracleparameter parameter in parameters)

{

Cmd. Parameters. Add (parameter );

}

 

Oracledataadapter ODA = new oracledataadapter (CMD );

Dataset DS = new dataset ();

 

Try

{

ODA. Fill (DS );

If (Ds. Tables [0]! = NULL)

{

Foreach (datarow DR in DS. Tables [0]. Rows)

{

System. Console. writeline ("userno:" + Dr [0] + "username:" + Dr [1]);

}

}

}

Catch (exception ex)

{

System. Console. writeline (ex. Message );

}

Finally

{

Conn. Close ();

}

System. Console. readkey ();
}

}

}